Data Analytics Case Cash Performance Ratios [LO21-3] Data Analytics Case Chapter 21 Cash Performance Ratios Data analytics is the process of examining data sets in order to draw conclusions about the information they contain. If completed any of the prior data analytics cases, follow the instructions listed in the Chapter 1 Data Analytics case to ge need to watch the videos referred to in the Chapters 1- 3 Data Analyics cases. No additional videos are required for th short training videos can be found here. In the Data Analytics Cases in previous chapters, we applied Tableau to examine two (hypothetical) publicly traded con Corporation and Tru, Inc regarding various aspects of their earnings performance and financial position. In this case, yo performance in terms of the ability of the two companies to generate a cash return on its assets. Tableau instructions: Download the "GPS Tru Financials x15x Excel file available in Connect, or under Student Resources within the Lbrary the computer on which you will be using Tableau. For this case, you will create several calculations to produce two long-term profitability ratios to allow you to compare two companies. After you view the training videos, follow these steps to create the charts you'll use for this case: 1. Open Tableau and connect to the Excei spreadsheet you downloaded. 2. 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. 3. Drag "(\$ in 000s) Company" and "Year" under "Dimensions" to the Columns shelt Change "Year" to discrete by right-clicking and selecting "Discrete," Select "Show Filter" and uncheck all the years except the last five years (2017 - 2021). Drag "Net income I (loss)". "Total assets" and "Net cash flows from operating activities" under "Measures" into the Rows shelf. Change each to discrete by right-clicking and selecting "Discrete." 4. Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated Field " Name the calculation "Rate of Return on Assets." In the Calculation Editor window, from "Measures," drag "Net Income /(loss)". type a division. sign, and drag "Total assets" beside it. Make sure the window says that the calculation Is valid and cick OK. 5. Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated Field" " Name the calculation "Cash return on assets." In the Calculation Editor window from "Measures" drag "Net cash flows from operating activities", type a division sign, and then drag "Total assets'. Make sure the window says that the calculation is valid and click OK. 6. Drag the newly created "Cash return on assets" and "Rate of Return on Assets inder l Measures" to the Rows shelf. Add labels to the bars by clicking on "Label" under the "Marks" card and clicking the boxit show mark labels F Format the labels to Times New Roman, bold, black and 10-point font. Edit the color on the Color marks card if desired 7. Change the title of the sheet to be "Return on Assets vs. Cash Retum on Assets/ by right-clicking and selectingl "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of isheet 17 to match the sheet title by right-clicking. selecting "Rename" and typing in the new title. Format the year company and numbers to Times New Roman, bold black and 12 . point font. In the Axis tab, change the numbers to Percentage with zero decimal places in the Pane tab. change the numbers to Percentage with one decimal place. 8. Click on the New Worksheet tob ("Sheet 2 should open) to the right of the newly named freturn on Assets vs. Cash Retym on Assets" sheet at the bottom of the screen. 9. Follow the procedures outlined in instruction #3 for the "Company" and "Yeari but drag the Company field to Columns sheif and the year field to Rows shelf. 10. Double click on "Rate of Return on Assets" under "Measures." Cilick on "Analysis " select "Totals" and then select" "Show Coluirni Grand Totals." Select "Totals" again, then select "Total All Using and cick on "Averagef 11. Right-cick one of the number fields and select "Format. Format the numbers to Times New Roman, bold, pink and 12 point font. Select Field and then SUM(Rate of Retum on Assets) in the upper right corner and change to Percentage with one decimal place. and change the Alignment to "Center" 12. Change the titie of the sheet to be 'Average Return on Assets' by night-ciciang and selecting 'Edit tille: Format the title to Times New Roman, bold, black and 15. point font. Change the tite of "Sheer. 2 ; fo match the sheet title by nght-clicking. sefccting "Rename" and typing in the new title. Format labels to Times New Roman. bold, black and 12.point font. 13. Click on the "Average Return on Assets" tab at che bottom of the canvas, fight click and select "Duplicate." Select the duplicated sheet (now named Average Return on Assets (2)). 14. Drag the "SUM (Rate of Return on Assets)" pill from the 'Marks" card back to "Measures " Double click on "Cash Return on Assets" under Measures. Follow the procedure above to change the Totals to "Average through the "Analysis" tab. 15. Format the numbers to Times New Roman, bold blue and 2.point font select Filds and the SUM(Cash Return on Assets) in the upper right corner and change to Percentage with one decimal place and change the Alignment to "Center". 16 Change the tite of the sheet to be "Average Cash Return on Assets" by right-cicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15 -point font Change the tule of "Sheet 2 " to match the sheet title by right clicking. selecting "Renamal and turinat in the nowitite 12. Lnange the title of the sheet to be "Average Keturn on Assets by ngnt-cicking and selecting "tdit titie format the titie to fimes New Roman, bold, black and 15-point font. Change the title of "Sheet 2" to match the sheet title by nght-clicking, selecting "Rename' and typing in the new title. Format labels to Times New Roman, bold, black and 12 point font. 13. Click on the "Average Return on Assets" tab at the bottom of the carvas. right-click and select "Duplicate," Select the duplicated sheet (now named "Average Return on Assets (2)). 14. Drag the "SUM (Rate of Return on Assets)" pill from the "Marks" card back to "Measures." Double click on "Cash Return on Assets" under Measures. Follow the procedure above to change the Totals to "Average" through the "Analysis" tab. 15. Format the numbers to Times New Roman, bold, blue and 12.point font. Select Fields and the SUM(Cash Return on Assets) in the upper right comer and change to Percentage with one decimal place and change the Alignment to "Center" 16. Change the title of the sheet to be "Average Cash Return on Assets" by right-clicking and selecting "Edit title. Format the itile to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 2 ' to match the sheet tite by right-clicking, selecting. "Rename" and typing in the new title. 17. Once complete, save the file as "DA21_Your initials twbx" Required: Based upon what you find, answer the following questions: A. What was (a) the retum on assets in 2021 and (b) the cash retum on assets for the mos recemt five years for Tru. Inc? (Round your answers to 1 decimol place.) B. What was (a) the cash return on assets in 2021 and (b) the average cashretum on ansets for the most recent five years for Tru. Inc? (Round your answers to 1 decimal place.) C. Which of the two companies, GPS Corporation of Tru, the indicates the most erratk pattern of performance during the period 2017. 2021 as measured by the cash retum on assets ratio? Data Analytics Case Cash Performance Ratios [LO21-3] Data Analytics Case Chapter 21 Cash Performance Ratios Data analytics is the process of examining data sets in order to draw conclusions about the information they contain. If completed any of the prior data analytics cases, follow the instructions listed in the Chapter 1 Data Analytics case to ge need to watch the videos referred to in the Chapters 1- 3 Data Analyics cases. No additional videos are required for th short training videos can be found here. In the Data Analytics Cases in previous chapters, we applied Tableau to examine two (hypothetical) publicly traded con Corporation and Tru, Inc regarding various aspects of their earnings performance and financial position. In this case, yo performance in terms of the ability of the two companies to generate a cash return on its assets. Tableau instructions: Download the "GPS Tru Financials x15x Excel file available in Connect, or under Student Resources within the Lbrary the computer on which you will be using Tableau. For this case, you will create several calculations to produce two long-term profitability ratios to allow you to compare two companies. After you view the training videos, follow these steps to create the charts you'll use for this case: 1. Open Tableau and connect to the Excei spreadsheet you downloaded. 2. 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. 3. Drag "(\$ in 000s) Company" and "Year" under "Dimensions" to the Columns shelt Change "Year" to discrete by right-clicking and selecting "Discrete," Select "Show Filter" and uncheck all the years except the last five years (2017 - 2021). Drag "Net income I (loss)". "Total assets" and "Net cash flows from operating activities" under "Measures" into the Rows shelf. Change each to discrete by right-clicking and selecting "Discrete." 4. Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated Field " Name the calculation "Rate of Return on Assets." In the Calculation Editor window, from "Measures," drag "Net Income /(loss)". type a division. sign, and drag "Total assets" beside it. Make sure the window says that the calculation Is valid and cick OK. 5. Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated Field" " Name the calculation "Cash return on assets." In the Calculation Editor window from "Measures" drag "Net cash flows from operating activities", type a division sign, and then drag "Total assets'. Make sure the window says that the calculation is valid and click OK. 6. Drag the newly created "Cash return on assets" and "Rate of Return on Assets inder l Measures" to the Rows shelf. Add labels to the bars by clicking on "Label" under the "Marks" card and clicking the boxit show mark labels F Format the labels to Times New Roman, bold, black and 10-point font. Edit the color on the Color marks card if desired 7. Change the title of the sheet to be "Return on Assets vs. Cash Retum on Assets/ by right-clicking and selectingl "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of isheet 17 to match the sheet title by right-clicking. selecting "Rename" and typing in the new title. Format the year company and numbers to Times New Roman, bold black and 12 . point font. In the Axis tab, change the numbers to Percentage with zero decimal places in the Pane tab. change the numbers to Percentage with one decimal place. 8. Click on the New Worksheet tob ("Sheet 2 should open) to the right of the newly named freturn on Assets vs. Cash Retym on Assets" sheet at the bottom of the screen. 9. Follow the procedures outlined in instruction #3 for the "Company" and "Yeari but drag the Company field to Columns sheif and the year field to Rows shelf. 10. Double click on "Rate of Return on Assets" under "Measures." Cilick on "Analysis " select "Totals" and then select" "Show Coluirni Grand Totals." Select "Totals" again, then select "Total All Using and cick on "Averagef 11. Right-cick one of the number fields and select "Format. Format the numbers to Times New Roman, bold, pink and 12 point font. Select Field and then SUM(Rate of Retum on Assets) in the upper right corner and change to Percentage with one decimal place. and change the Alignment to "Center" 12. Change the titie of the sheet to be 'Average Return on Assets' by night-ciciang and selecting 'Edit tille: Format the title to Times New Roman, bold, black and 15. point font. Change the tite of "Sheer. 2 ; fo match the sheet title by nght-clicking. sefccting "Rename" and typing in the new title. Format labels to Times New Roman. bold, black and 12.point font. 13. Click on the "Average Return on Assets" tab at che bottom of the canvas, fight click and select "Duplicate." Select the duplicated sheet (now named Average Return on Assets (2)). 14. Drag the "SUM (Rate of Return on Assets)" pill from the 'Marks" card back to "Measures " Double click on "Cash Return on Assets" under Measures. Follow the procedure above to change the Totals to "Average through the "Analysis" tab. 15. Format the numbers to Times New Roman, bold blue and 2.point font select Filds and the SUM(Cash Return on Assets) in the upper right corner and change to Percentage with one decimal place and change the Alignment to "Center". 16 Change the tite of the sheet to be "Average Cash Return on Assets" by right-cicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15 -point font Change the tule of "Sheet 2 " to match the sheet title by right clicking. selecting "Renamal and turinat in the nowitite 12. Lnange the title of the sheet to be "Average Keturn on Assets by ngnt-cicking and selecting "tdit titie format the titie to fimes New Roman, bold, black and 15-point font. Change the title of "Sheet 2" to match the sheet title by nght-clicking, selecting "Rename' and typing in the new title. Format labels to Times New Roman, bold, black and 12 point font. 13. Click on the "Average Return on Assets" tab at the bottom of the carvas. right-click and select "Duplicate," Select the duplicated sheet (now named "Average Return on Assets (2)). 14. Drag the "SUM (Rate of Return on Assets)" pill from the "Marks" card back to "Measures." Double click on "Cash Return on Assets" under Measures. Follow the procedure above to change the Totals to "Average" through the "Analysis" tab. 15. Format the numbers to Times New Roman, bold, blue and 12.point font. Select Fields and the SUM(Cash Return on Assets) in the upper right comer and change to Percentage with one decimal place and change the Alignment to "Center" 16. Change the title of the sheet to be "Average Cash Return on Assets" by right-clicking and selecting "Edit title. Format the itile to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 2 ' to match the sheet tite by right-clicking, selecting. "Rename" and typing in the new title. 17. Once complete, save the file as "DA21_Your initials twbx" Required: Based upon what you find, answer the following questions: A. What was (a) the retum on assets in 2021 and (b) the cash retum on assets for the mos recemt five years for Tru. Inc? (Round your answers to 1 decimol place.) B. What was (a) the cash return on assets in 2021 and (b) the average cashretum on ansets for the most recent five years for Tru. Inc? (Round your answers to 1 decimal place.) C. Which of the two companies, GPS Corporation of Tru, the indicates the most erratk pattern of performance during the period 2017. 2021 as measured by the cash retum on assets ratio