Applying Tableau Profitability Ratios $[$LO$4-10]$

In the Chapter $3$ Applying Tableau, you compared two companies$$ liquidity. In this case, you continue in your role as an analyst conducting research into the relative merits of investing in one or both of these companies. For this case, you will assess the companies$$ return on assets and how that return is related to both profit margin and asset turnover.

Tableau Instructions:

For this case, you will create several calculations and produce a text chart of profitability ratios to compare and contrast the two companies. To do so$,$ you will need to be able to change data from continuous to discrete.

Use the following steps to create the charts you will need for this case:

Download the Excel file $$Discount$\_$Goods$\_$Big$\_$Store$\_$Financials.$$

Open Tableau and connect to the Excel file.

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" to the Rows shelf. Change "Year" to discrete by right$-$clicking and selecting "Discrete."

Drag the "Net income$/($loss$),"$ "Net sales," and "Average total assets" under Measure Names to the Rows shelf. Change each to discrete, following the process above.

Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated Field." A calculation box will pop up$.$ Name the calculation "Return on Assets." In the calculation box, from the Rows shelf, drag "Net income$/($loss$),"$ type a division sign, then drag "Average total assets" beside it$.$ Make sure the box says that the calculation is valid and click OK$.$

Repeat the process by creating a calculated field named "Profit Margin on Sales" that consists of "Net income$/($loss$),"$ divided by "Net sales."

Repeat the process one more time by creating a calculated field "Asset Turnover" that consists of "Net sales" divided by "Average total assets."

Drag the newly created "Return on Assets" to add it to the Rows shelf. Format it to discrete. Right click on Return on Assets and click Format. On the left, you will see a format bar. Under Default, choose Alignment, then Horizontal Center. Then click on Numbers and choose Percentage.

Drag the newly created "Profit Margin on Sales" and "Asset Turnover" to the Rows shelf. Format them both as discrete and center aligned. "Profit Margin on Sales" should be formatted as percentage. "Asset Turnover" should be formatted as Number $($Custom$)$ with $2$ decimal places.

Right$-$click the "Net income $/($loss$),"$ "Net sales," and "Average total assets" on the Rows shelf and un$-$select" Show header." This will hide these items from view but still allow them to be used in formulas.

Double$-$click the tab at the bottom of the page and type Profitability Ratios to change the name of the sheet.

To hide the abc column, which appears when all variables are discrete, click on the Marks Card, and choose Polygon. This will hide the letters. Then click and drag the edge of the column to make it as small as possible.

Click on the New Worksheet tab on the lower left $("$Sheet $2"$ should open$).$ Drag "Year" to the Columns shelf and "Return on Assets," "Profit Margin on Sales" and "Asset Turnover" to the Rows shelf.

Drag "Company" under Tables to "Color" on the "All" section of the Marks card. You should now see two lines in each of the three graphs.

Double$-$click the tab at the bottom of the page and type Graph of Profitability Ratios to change the name of the sheet.

Save your work.

Required:

Based on the visualization you created, answer the following questions:

What is the Big Store$$s return on assets in $($a$)2012$ and $($b$)2021?$

What is the Discount Goods$$s return on assets in $($a$)2012$ and $($b$)2021?$

To help determine why the relative profitability of the two companies has shifted over the ten$-$year period and to get a better company$-$to$-$company comparison, drag the second "pill": $($Year Dimension$)$ to the left of the "pill": $($Company Dimension in the text chart$).$

The return on assets is a result of the profit margin and the asset turnover. Demonstrate this for Big Store in $2021$ by showing that the profit margin times the asset turnover equals return on assets.

Analyzing the asset turnover ratios over the ten$-$year period, is Big Store$$s asset turnover $($a$)$ generally increasing, $($b$)$ roughly the same, or $($c$)$ generally decreasing from year to year?

Analyzing the asset turnover ratios over the ten$-$year period, is Discount Goods$$ turnover $($a$)$ generally increasing, $($b$)$ roughly the same, or $($c$)$ generally decreasing from year to year?

As of $2021,$ which company reports a more favorable return on assets and is this primarily attributable to its asset turnover or profit margin?