Case FA-3801 Tableau: Financial statement analysis for Jubilee Company

The CEO of Jubilee Company wants a brief analysis of Jubilee’s ability to pay its current liabilities, its ability to pay its debts, and its profitability over the past three years.  Selected financial statements for Jubilee Company for the years of 2021 through 2023 can be found at this link.  Be sure to download the file from Dropbox rather than simply viewing it (see this link for additional information.)  

The links contained in the requirements below are for short tutorial videos on the Tableau skills or accounting skills needed to complete the requirement. You are not required to watch the videos but are posted for you in case you need to review certain concepts.

Case Requirements

  1. Connect to data source in Tableau and import the excel file (link).
  2. Add the sheets you will be working with (BAL SHEET Upload and INC STMT Upload) into the view (link).
  3. Click on Sheet 1 and create a calculated field for the following measures/ratios for each of the three years (link). 
    • Working capital (link)
    • Current ratio (link)
    • Quick ratio (link)
    • Debt to equity (link)
    • Debt ratio (link)
    • Times-interest-earned ratio (link)
    • Gross margin percentage (link)
    • Return on sales (link)
  4. For each measure/ratio, create a new sheet (link), rename the sheet, and make a line graph of the three years. (link).  Label the line graphs and format the numbers as indicated. (link). What does each of these ratios measure?
    • Working capital:  Currency, 0 decimal places.
    • Current ratio: Numbers, 2 decimal places.
    • Quick ratio: Numbers, 2 decimal places.
    • Debt to equity:  Numbers, 2 decimal places.
    • Debt ratio:  Percentage, 2 decimal places.
    • Times-interest-earned ratio: Numbers, 1 decimal place.
    • Gross margin percentage: Percentage, 1 decimal place.
    • Return on sales: Percentage, 1 decimal place.
  5. Create a dashboard with a centered title to display your (8) line graphs (link).  Name the Title: “Jubilee Company Ratios” by ____ (your name).
  6. Add eight containers (both vertical and horizontal) to your dashboard so that all graphs will be the same size (link).
  7. Drag all eight graphs onto your dashboard (link).  Position your liquidity ratios (4) across the top of the dashboard and your solvency ratios (2) and profitability ratios (2) along the bottom.
  8. Save your Tableau workbook as a .twbx file, using your last name combined with the word “ratios” as the file name. (link). 
  9. What is your overall assessment of Jubilee’s ability to pay its current liabilities, its ability to pay its debts, and its profitability over the past three years? Write a short summary (no more than 100 -200 words) analyzing the graphs you have prepared and make suggestions as to possible improvements or additional data you think should be analyzed.