Data analytics is the process of examining data sets in order to draw conclusions about the information they contain. If you havent 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 the Chapter 10 Data Analytics Case, you used Tableau to examine a data set and create calculations to compare two companies fixed-asset turnover. 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 accumulated depreciation to fixed assets ratio to determine if new assets are being employed or utilized. The accumulated depreciation to fixed assets ratio = accumulated depreciation / fixed assets.

This ratio allows the investor or analyst to examine factors like the age of the assets and therefore their remaining useful life. A low ratio means that the assets have plenty of life left in them and should be able to be used for years to come. A high ratio means the opposite. Low metrics are not necessarily a good sign though. For example, a company with a very low ratio may be spending huge amounts of money replacing fixed assets too soon. So its useful to track this metric over time to detect patterns. A continuing increase in the companys accumulated depreciation to fixed assets ratio can indicate that management is struggling to find the cash necessary to acquire new assets.

Tableau Instructions:

You have available to you an extensive data set that includes detailed financial data for Discount Goods and Big Store and for 2012-2021. The data set is in the form of four Excel files available to download from Connect, or under Student Resources within the Library tab. The file for use in this chapter is named "Discount_Goods_Big_ Store_Financials.xlsx". Download this file and save it to the computer on which you will be using Tableau.

For this case, you will create some calculations to produce a bar chart of the accumulated depreciation to fixed assets ratio to allow you to compare and contrast the two companies.

After you view the training videos, follow these steps to create the charts youll use for this case:

Open Tableau and connect to the Excel spreadsheet you downloaded.

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

Drag the Buildings and Improvements, Computer hardware and software, Construction in progress, Fixtures and Equipment, and Less accumulated depreciation under "Measures" to the Rows shelf. Change each to discrete. Right click on the drop-down menu of each of the accounts and uncheck Show Header so they are not visible in the field.

Under the Analysis tab, select Create Calculated Field named AD to FA ratio In the calculation box, from the Rows shelf, drag Less accumulated depreciation then type a multiplication sign (*) and a negative 1, followed by a division sign and an open parenthesis, then drag Buildings and Improvements, type a plus sign, then Computer hardware and software, plus sign, Construction in progress, plus sign, Fixtures and Equipment, then a closed parenthesis. Make sure the window says that the calculation is valid and click OK.

Drag the newly created "AD to FA ratio" from "Measures" to the Rows shelf.

Click on the "Show Me" and select "side-by-side bars." You should now see Accumulated Depreciation to Fixed Assets Ratio for each of the years; for each of the stores. Add labels to the bars by clicking on "Label" under the "Marks" card and clicking the box "Show mark labels." Format the labels to Times New Roman, bold, black and 9-point font.

Change the title of the sheet to be "Accumulated Depreciation to Fixed Assets Ratio Bar Chart" 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.

Format the labels on the left of the sheet ("AD to FA ratio") to Times New Roman, 9-point font, bold, and black.

Format all other labels to be Times New Roman, bold, black and 12-point font.

Once complete, save the file as "DA11_Your initials.twbx."

Required:

Based upon what you find, answer the following questions:

A. Comparing the accumulated depreciation to fixed assets ratios over the first five years of the ten-year period, is Big Stores accumulated depreciation to fixed assets ratio (a) generally increasing, (b) roughly the same, or (c) generally decreasing from year to year?

B. Comparing the accumulated depreciation to fixed assets ratios over the second five years of the ten-year period, is Big Stores accumulated depreciation to fixed assets ratio (a) generally increasing, (b) roughly the same, or (c) generally decreasing from year to year?

C. Comparing the accumulated depreciation to fixed assets ratios over the first five years of the ten-year period, is Discount Goods accumulated depreciation to fixed assets ratio (a) generally increasing, (b) roughly the same, or (c) generally decreasing from year to year?

D. Comparing the accumulated depreciation to fixed assets ratios over the second five years of the ten-year period, is Discount Goods accumulated depreciation to fixed assets ratio (a) generally increasing, (b) roughly the same, or (c) generally decreasing from year to year?

E. Comparing the accumulated depreciation to fixed assets ratios over the entire ten-year period, which of the two companies appears to maintain fixed assets with longer remaining useful lives?

Company Big Store Big Store Big Store Big Store Big Store Big Store Big Store Big Store Big Store Big Store Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Cash and cash Year equivalents 2012 6,867 2013 8,705 2014 9,135 2015 7,281 2016 7,781 2017 6,550 2018 7,395 2019 7,907 2020 8,569 2021 6,851 2012 7,257 2013 4,871 2014 7,727 2015 10,122 2016 8,859 2017 9,929 2018 8,501 2019 7,906 2020 8,145 2021 5.922 Assets of Prepaid Receivables, Inventor discontinued expenses and Total current net Y operations other assets 5,835 43,046 0 1,941 57,689 5,624 44,469 0 1,441 60,239 6,778 45,141 0 2,224 63,278 6,677 44,858 460 1,909 61,185 6,768 43,803 0 1,588 59,940 5,937 40,714 0 1,774 54,975 5,089 36,318 131 2,960 51,893 4,144 32,713 140 1,473 48,032 5,986 46,523 -211 3,625 64,491 3,613 32,724 -150 2,958 45,996 3,908 36,929 307 4,889 53,289 6,294 45,044 1,431 5,160 62,800 3,438 37,724 5,924 7,796 62,609 1,042 28,604 3,524 8,143 51,436 2,306 35.124 18,280 8.267 72,836 1,235 26,343 27.555 8,044 73,107 2,663 27,347 30,204 7,787 75,502 3,258 31,907 29,573 9,240 81,884 2.746 32517 46.730 7 158 97 295 4,964 42,335 18,540 6,464 78 225 Company Big Store Big Store Big Store Big Store Big Store Big Store Big Store Big Store Big Store Big Store Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Discount Goods Cash and cash Year equivalents 2012 6,867 2013 8,705 2014 9,135 2015 7,281 2016 7,781 2017 6,550 2018 7,395 2019 7,907 2020 8,569 2021 6,851 2012 7,257 2013 4,871 2014 7,727 2015 10,122 2016 8,859 2017 9,929 2018 8,501 2019 7,906 2020 8,145 2021 5.922 Assets of Prepaid Receivables, Inventor discontinued expenses and Total current net Y operations other assets 5,835 43,046 0 1,941 57,689 5,624 44,469 0 1,441 60,239 6,778 45,141 0 2,224 63,278 6,677 44,858 460 1,909 61,185 6,768 43,803 0 1,588 59,940 5,937 40,714 0 1,774 54,975 5,089 36,318 131 2,960 51,893 4,144 32,713 140 1,473 48,032 5,986 46,523 -211 3,625 64,491 3,613 32,724 -150 2,958 45,996 3,908 36,929 307 4,889 53,289 6,294 45,044 1,431 5,160 62,800 3,438 37,724 5,924 7,796 62,609 1,042 28,604 3,524 8,143 51,436 2,306 35.124 18,280 8.267 72,836 1,235 26,343 27.555 8,044 73,107 2,663 27,347 30,204 7,787 75,502 3,258 31,907 29,573 9,240 81,884 2.746 32517 46.730 7 158 97 295 4,964 42,335 18,540 6,464 78 225