OnlineNursingOwl
← All nursing posts

Business & Finance · Studypool

Business Question

Post ID: 3541408

Project Orientation

  • This case study has been developed and adapted for participants to perform evaluation procedures related to the revenue account balance for a retail company.
  • In the first part of the case, you will individually inspect the case data, and learn the basics of Tableau to create data visualizations. You will then evaluate the visualizations to understand what insights might be gained about the revenue account.
  • In the second part of the case, you may work in teams to suggest internal controls to target specific risks identified with the revenue account. To help you accomplish this, you will review company responses to interview questions and rely on your analysis of the data in the first part to create additional visualizations.

Tableau A Instructions

Task 0 - Preparation

As a student, you will have a free Tableau Desktop license. Follow the instructions to download Tableau Desktop and activate with the product key. Tableau is compatible with Windows, Mac, and Linux operating systems.

🙋🏻‍♀️Frequent student questions:

    1. If we are starting with Excel files, why do we not just create visualizations with Excel?
      • Because having expose to different types of software gives you marketable skills to add to your experience and resume
    2. The company where I work/intern doesn't use Tableau, they use Power BI, can we use that?
      • Academically we use Tableau because of wider operating system compatibility. The concepts you learn in Tableau are easily transferable to Power BI.

Task 1 - Extract (ETL)

You have obtained the subledger account detail from KeyKeyLime. To verify the data extraction you will need to verify some file totals and reconcile the data back to the financial statements.

    1. Open “S_Data_KKL Revenue Report_19-21Download S_Data_KKL Revenue Report_19-21" Excel File.
    2. Inspect the data to determine if the extract was complete and the data is usable.
      1. Are there any missing rows or columns?
      2. Are columns in the correct format (dates formatted as dates, dollar amounts as currency, etc.)?
      3. Calculate the following check figures for the entire file:
        • Total number of records: 2,640
        • Total Sales Amount: $94,063.84
        • Total Actual Standard Unit Cost: $34,291.24
    3. Reconcile the 2019, 2020, and 2021 totals to the Income Statement, "S_Data_Income Statement_19-21Download S_Data_Income Statement_19-21" Excel File.
      1. Reconcile the amounts in the Income Statement by totaling the Sales Amount in the revenue report for each of the years and ensuring it ties to the numbers provided.
        • Note the numbers in the Income Statement are rounded to the nearest dollar.

Task 2 - Load (ETL)

After we validate that our data set is usable and reconciles to the financial statements, our next task will be to load the data into Tableau so we can create graphs and charts.

    1. Upload the data
      1. Open Tableau and select "Connect to Data".
      2. Choose Microsoft Excel under "Connect". Select your file and open.
      3. Tableau will show all sheets (tabs) from the Excel workbook in the Data Source page. For this project you are only working with one Excel sheet so no table relations are needed.
      4. Ensure you have the correct number of records
        • This should be the same number of total records as your check figure in Task 1.
      5. Name your Tableau file “Tableau_A_last name_first name”. twbx, by going to "File > Save As".
        • It is really important to save as a .twbx not a .twb file
      6. If your data looks correct, click on "Sheet 1" at the bottom to move into the main visualization workspace.

Task 3 - Create Data Visualizations

Below are the steps on how to create the required visualizations. Please save your Tableau file. You will submit it in the Canvas assignment.

    1. Create an dual axis area graph for yearly sales and costs.
      1. In the Data pane (left side), locate your fields:
        • Sales Amount
        • Actual Standard Unit Cost
        • Fiscal Year
          • Right-click on the Fiscal Year in the Data pane and select Convert to Discrete.
      2. Drag:
        • Fiscal Year > to the Columns shelf
        • Sales Amount > to the Rows shelf
      3. Tableau will draw a basic chart (default is a line or bar).
        • On the Marks card (middle-left area), click the dropdown and choose Area.
      4. To add a second measure drag:
        • Actual Standard Unit Cost > to the Rows shelf next to Sales Amount
        • Tableau will create two separate charts, one for each measure.
      5. Convert to Dual Axis:
        • In the Rows shelf, right-click on the second measure (SUM(Actual Standard Unit Cost)).
        • Choose Dual Axis. Both measures will now share the same X-axis (Fiscal Year).
      6. To synchronize the axes:
        • Right-click on the right-hand axis (the secondary one) > Select Synchronize Axis.
          • Both lines or areas now align on the same numeric scale.
      7. On the Marks card (All) > click Label > check Show Mark Labels.
      8. Double-click the title area (where it says "Sheet #") at the top and change the title to: “Sales and Cost by Fiscal Year”
      9. At the bottom of the Tableau window, right-click on the Sheet # > then select Rename, to change to "V1".
      10. From the top menu, click Worksheet > Show Caption.
      11. Reconcile the 2019, 2020, and 2021 totals to the Income Statement Excel file again.
  1. Create a line graph for monthly sales trend by fiscal year
      1. Click the new sheet icon (bottom left corner)
      2. In the Data pane (left side), locate your fields:
        • Fiscal Month
        • Sales Amount
        • Fiscal Year
      3. Drag:
        • Fiscal Month > to the Columns shelf
        • Sales Amount > to the Rows shelf
      4. On the Marks card (middle-left area), click the dropdown and choose Line.
      5. Drag Fiscal Year > to the Color shelf in the Marks card.
        • This will split the line into multiple lines — one for each fiscal year.
        • Each line will have its own color.
        • Click on Color in the Marks card > then Edit Colors to format colors.
        • Select the Color Blind color palatte > then Assign Palette > click Okay
      6. Change the title to: “Sales Trends by Fiscal Month”
      7. Rename the sheet to "V2".
      8. Show Captions
  1. Create a bar chart for sales by GL posted date
      1. Click the new sheet icon (bottom left corner)
      2. In the Data pane (left side), locate your fields:
        • GL Posted Date
        • Sales Amount
      3. Drag:
        • GL Posted Date > to the Columns shelf
        • Sales Amount > to the Rows shelf
      4. On the Marks card, click the dropdown and choose Bar.
      5. Add data labels:
        • On the Marks card > click Label > check Show Mark Labels.
      6. Change the title to “Sales Amount by GL Posted Date”, and rename the sheet to "V3"
      7. Show Captions
      8. (Optional) Adjust the date granularity
        • Click the plus sign next to Year(GL Posted Date) in the Columns shelf to view Quarters and/or view Months.
  1. Create a bar chart for sales by ship date
      1. Click the new sheet icon (bottom left corner)
      1. In the Data pane (left side), locate your fields:
        • Shipped Date
        • Sales Amount
      2. Drag:
        • Shipped Date > to the Columns shelf
        • Sales Amount > to the Rows shelf
      3. On the Marks card, click the dropdown and choose Bar.
      4. Add data labels:
        • On the Marks card > click Label > check Show Mark Labels.
      5. Change the title to “Sales Amount by Shipped Date”, and rename the sheet to "V4"
      6. Show Captions
      7. (Optional) Adjust the date granularity
        • Click the plus sign next to Year(Shipped Date) in the Columns shelf to view Quarters and/or view Months.

Tableau B Instructions

Task 4 - Inspect and Analyze Visualizations

Once you have created the visualizations, you need to inspect and analyze the trends in the graphs. It is important to document what you are seeing and talk about the “why”. For each visualization V2, V3 and V4, document a minimum of 3 key takeaways or items of note. You will submit your notes in the Canvas assignment.

    1. When analyzing the visualizations, consider the overall trends and patterns and also the following questions:
      • Are there any unexpected fluctuations or inconsistent relationships when comparing year over year?
      • Are there an unexpected number (or dollar amount) of transactions occurring at or near quarter or year-end?
  1. To give you an idea, takeaways or items or items of note in evaluating V1 might include any of the following:
      • Sales decreased from 2019 to 2020 by $2,479 (~8%) and then increased from 2020 to 2021 by $8,088 (~29%).
      • Costs declined from 2019 to 2020 by $1,429 (~13%) and then rose from 2020 to 2021 by $3,276 (~33%).
      • Overall, both sales and costs were highest in 2021 and lowest in 2020.
      • 2020 marked a dip year for both sales and cost, while 2021 shows recovery.
      • In every year, Sales Amount exceeds Actual Standard Unit Cost, indicating positive margins, which corresponds to the Net Income in the Income Statement
      • Both measures followed a similar pattern for all years, with costs averaging around 36% of sales.
      • Sales increased overall by about 19% and costs increased overall by about 16%.