Power BI Instructions:

For this case, you will create several calculations to produce two long$-$term profitability ratios to allow you to compare and contrast the two companies.

Use the following steps to create the charts you need for the case. Additional instructions and short training videos for Power BI can be found here.

Download the Excel file $$GPS$\_$Tru$\_$Financials.$$

Open Power BI and select $$Import data from Excel,$$ then navigate to the Excel file you downloaded. Select $$Open$$ and check the box by $$Financials$,$ then click $$Load$.$

In the Fields list, expand all the fields under $$Financials$$ and click on the field $$Year$.$ At the top of the screen in the summarization drop$-$down update the properties to $$Don$$t summarize.$$

Scroll down to $$Year$$ again and right$-$click on it$.$ Select $$Add to filters$$ and $$page$-$level filters.$$ In the $$Filters$$ pane, change the filter type to $$Basic filtering$$ and select the checkboxes beside the years $2017-2021.$

Navigateto $$Data$,$ the $$Home$$ tab, $$Transform Data,$$ and then the $$Add Column$$ tab.Select $$Custom Column.$$ Name the new column $$Rate of Return on Assets,$$ and set it equal to $[$Net income $/($loss$)]/[$Total assets$].$

Create another $$Custom Column$$ called $$Cash Return on Assets$$ using the formula $[$Net cash flows from operating activities$]/[$Total assets$].$ Navigateto the $$Home$$ tab and select $$Close & Apply.$$

Highlight each of your newly created fields, go to the $$Column tools$$ tab, and change the $$Data Type$$ to $$Decimal Number.$$

Return to the $$Report$$ area, select each of your newly created fields, and change them to display as a $$Percentage$$ under the $$Column tools$$ tab. Add two new visuals to your report. Set both to be $$Clustered column charts.$$

For both, drag $$Year$$ to the $$X$-$axis$$ and $($$ in $000$s$)$ Company$$ to $$Small multiples.$$

For the top visual, add $$Cash Return on Assets$$ as the $$Y$-$axis.$$ For the bottom, add $$Rate of Return on Assets.$$

Holding the $$ctrl$$ key, select both visuals at the same time. In the $$Format your visual$$ section in the $$Visual$$ tab, expand $$Small multiple grid$$ and reduce the rows to $1.$ Toggle $$Data labels$$ on and further expand $$Values$$ to change the decimal places to $1.$ In the $$Y$-$axis$$ drop$-$down, set $$Minimum$$ to $-0\mathrm{.}05$ and $$Maximum$$ to $0\mathrm{.}15.$ In the $$Title$$ drop$-$down in the $$General$$ tab, change the title to $$Return on Assets vs$.$ Cash Return on Assets.$$ Update the sheet name to match.

Add a new page by clicking the $+$ button.

Repeat step $4$ to add a new page filter by $$Year$.$

Select the $$Matrix$$ visual in the $$Visualizations$$ pane.

From the $$Fields$$ list, drag $$Year$$ to $$Rows$,($$ in $000$s$)$ Company$$ to $$Columns$,$ and $$Rate of Return on Assets$$ to $$Values$.$ Click on the dropdown arrow next to $$Rate of Return on Assets$$ and change the value to $$Average$.$

In the $$Format your visual$$ section in the $$Visual$$ tab, toggle the $$Column subtotals$$ off. Expand the $$Specific columns$$ section and further expand $$Values$$ to reduce the decimal places to $1.$ Navigate to the $$General$$ tab and toggle $$Title$$ on and change the title of the visual to $$Average Return on Assets.$$ Update the sheet name to match.

Right$-$click on your newly titled page and select $$Duplicate Page.$$

Replace $$Values$$ in the $$Add data to your visual$$ section with $$Cash Return on Assets.$$

In the $$Format your visual$$ section in the $$Visual$$ tab, expand the $$Specific columns$$ drop$-$down and further expand $$Values$$ to reduce decimal places to $1.$ Navigate to the $$General$$ tab and expand the $$Title$$ drop$-$down, change the title to $$Average Cash Return on Assets.$$ Rename $$Page $1$ to match.

Save your work.

Required:

Based on your output, answer the following questions:

$(1)$ What was Tru, Incorporated's Return on Assets in $2021?(2)$ What was Tru, Incorporated's Average Return on Assets for $2017-2021?$

Note: Round your answers to $1$ decimal place.

$(1)$ What was Tru, Incorporated's Cash Return on Assets in $2021?(2)$ What was Tru, Incorporated's Average Cash Return on Assets for $2017-2021?$

Note: Round your answers to $1$ decimal place.

Which of the two companies, GPS Corporation or Tru, Incorporated, indicates the most erratic pattern of performance during the period $2017-2021$ as measured by the cash return on assets ratio?