custom ), 2 decimal places B u s i n e s s F i n a n c e
Data analytics is the process of examining data sets in order to draw conclusions about the information they contain. If you have not completed any of the prior data analytics cases, follow the instructions listed in the Chapter 1 Data Analytics case to get set up. You will need to watch the videos referred to in the Chapters 1 – 3 Data Analytics cases. No additional videos are required for this case. All short training videos can be found here.
In prior chapters, you applied Tableau to examine a data set and create calculations to compare two companies’ financial information. In this case, you continue in your role as an analyst conducting introductory research into the relative merits of investing in one or both of these companies. This time you assess the companies’ receivables management and how effective the companies are in managing the investment in receivables.
You have available to you an extensive data set that includes detailed financial data for 2012-2021 for both Discount Goods and Big Store. The data set is in the form of four Excel files available to download from Connect, or under Student Resources within the Library tab. Download the file “Discount_Goods_Big_Store_Financials.xlsx” to the computer, save it, and open it in Tableau.
For this case, you will create several calculations to produce a text and line chart of the receivables analysis to allow you to compare and contrast the two companies’ receivables management.
After you view the training videos and review instructions in Chapters 1-3, follow these steps to create the charts you’ll use for this case:
- Click on the “Sheet 1” tab at the bottom of the canvas, to the right of the Data Source at the bottom of the screen.
- Drag “Year” and “Company” under “Dimensions” to the Rows shelf. Change “Year” to discrete data type by right-clicking and selecting “Discrete.”
- Drag the “Net sales” and “Average accounts receivable net” under Measures to the Rows shelf. Change each to discrete. Format each to Times New Roman, 10-point font, black, center alignment, and Currency (custom), 0 decimal places by right-clicking each and selecting “Format.” Format selections will open to the left of the canvas.
- Create a calculated field by clicking the “Analysis” tab from the Toolbar at the top of the screen and selecting “Create Calculated field.” A calculation box will pop up. Name the calculation “Receivables Turnover.” In the Calculation Editor window, from the Rows shelf, drag “Net sales” to the window, type a division sign, then drag “Average accounts receivable net” beside it. Make sure the window says that the calculation is valid and click OK.
- Repeat the process one more time by creating a calculated field “Average Collection Period” that consists of the number 365 divided by the newly created “Receivables Turnover.”
- Drag the “Receivables Turnover” and “Average Collection Period” to the Rows shelf. Change both to discrete data type. Format both to Times New Roman, 10-point font, black, center alignment, and Number (Custom), 2 decimal places.
- Change the title of the sheet to be “Receivables Management” by right-clicking and selecting “Edit title.” Format the title to Times New Roman, bold, black and 15-point font. Change the title of “Sheet 1” to match the sheet title by right-clicking, selecting “Rename” and typing in the new title.
- Click on the New Worksheet tab on the lower left (“Sheet 2” should open). Click on the “Sheet 2” tab and drag “Year” to the Columns shelf and “Receivables Turnover” and “Average Collection Period” to the Rows shelf.
- Drag “Company” under “Dimensions” to the Color in the “All” section of the “Marks” card. You should now see two colored lines in the two graphs.
- Format all the labels on the sheet (“Receivables Turnover,” “Average Collection Period,” and “Year”) to Times New Roman, 10-point font, and black.
- Change the title of the sheet to be “Graph of Receivables Management” by right-clicking and selecting “Edit title.” Format the title to Times New Roman, bold, black and 15-point font. Change the title of “Sheet 2” to match the sheet title by right-clicking, selecting “Rename” and typing in the new title.
- Once complete, save the file as “DA7_Your initials.twbx.”
Based upon what you find, answer the following questions:
A. What is the receivables turnover ratio for Big Store (a) in 2012 and (b) in 2021?
B. What is the receivables turnover ratio for Discount Goods (a) in 2012 and (b) in 2021?
C. Which company exhibited more favorable receivables management, as measured by average collection period, in 2020, Big Store or Discount Goods? What was that company’s average collection period, and is it longer or shorter than the industry average in 2020?
The average collection period for the retail industry sector is 4.0 in 2020 and 5.1 in 2021.
D. Which company exhibited more favorable receivables management, as measured by average collection period, in 2021, Big Store or Discount Goods? What was that company’s average collection period, and is it longer or shorter than the industry average in 2021?