BYU Student Author: @MitchFrei
Reviewers: @Brett_Lowe, @Spencer
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
- Tableau
Need a program? Click here.
Overview
The automobile industry has taken a big hit recently due to supply chain issues stemming from some major bank collapses. You are the assistant controller for Rolling Thunder Automotive, a premier sports car manufacturer. One of your duties is to oversee the health of your accounts receivable and make sure that customers are paying on time. As you prepare the financial close for 2022, you dig into your customer payment data and find out that during October, customers on average took longer to pay than they ever have before. If you and your team don’t act quickly, Riding Racers may find itself out of cash soon. You would like to present your findings to the controller in a clean and simple Tableau dashboard so your team can take necessary action.
Instructions
- Download the data and load into Tableau.
- Create a calculation for Days Sales Outstanding (DSO). DSO is the number of days between the invoice date and date paid. This represents how long it took the customer to pay.
- Create a trend line to show the average DSO by month for 2022. This represents the average number of days it took customers to pay for each month.
- Annotate the point for October 2022 with the label “October 2022 Avg DSO: ###”
- Create a second trend line on the same axis to show the rolling 3-month average DSO by month. This represents the average DSO across the month and the 2 months prior. For example, the data point for May will represent the average DSO across March, April, and May. The data point for June will represent the average DSO across April, May, and June, etc.
- On a separate sheet, create a table with the same information as your trend lines, but in numerical form. Your table should have 4 columns: Year, Month, Monthly Avg DSO, and Rolling 3-Month Avg DSO. Your table should have 12 rows – one for each month in 2022. Round to 2 decimal places.
- Combine these 2 sheets into one dashboard. Include a title at the top for “DSO Dashboard” and place the line chart on the left and the table on the right. Include a legend to differentiate the two trend lines.
Data Files
Suggestions and Hints
- Compute the days between two dates by subtracting one from the other.
- To annotate a point, right click on the point, hover over “Annotate,” and select “Point.”
- To create a dual axis in Tableau, drag the second measure onto rows, right click on the second measure, and select “Dual axis.” Since our axes have the same scale, right click on the axis on the right and select “Synchronize Axis.” To clean up the view, right click on the axis on the right again and deselect “Show Header.”
- For the rolling 3-month calculation, right click on the measure, hover over “Quick Table Calculation,” and select “Moving Average.” Right click on the measure again and select “Edit Table Calculation.” In the dropdown that includes “prev X, next X,” select previous 2, next 0, and make sure that “Current value” is checked. This will calculate the average across the current month and the prior 2 months.
- For the table, drag measure names to columns and measure values to text so that it displays the column names. Change the DSO calculation from SUM to AVG, then drag the DSO calculation to the Measure Values card and repeat the above steps to calculate the rolling 3-month average. Then delete the measures you don’t need from the Measure Values card.
- Edit the displayed name of a column or legend by right clicking and selecting “Edit Alias.” If you get an error saying that the alias already exists, simply put a space after it so Tableau thinks it’s different.
- Display a legend on a dashboard by selecting a sheet, clicking on the dropdown arrow, and selecting “Legend.”
Solution
Challenge104_Solution.tbwx
Solution Video: Challenge 104|TABLEAU – They See Me Rollin