Learning Objectives 1. Create a pivot table in Excel 2. Add new calculated field to pivot...

Transcribed Image Text:

Learning Objectives 1. Create a pivot table in Excel 2. Add new calculated field to pivot table 3. Create a pivot chart with appropriate labels 4. Apply appropriate numeric format on pivot table and chart 5. Analyze and interpret outcome of pivot tables and calculated fields Data Set Background As an analyst you are asked to conduct research into the relative merits of investing of two companies operating in the discount retail industry. Circuit Discount TV and General Brands, Inc. invest in equity securities on a long-term basis. To do so, you should evaluate the percentage of noncurrent assets invested in equity securities. The investment in equity to noncurrent assets formula is: Total Equity Investments/Total Noncurrent Assets. The data set contains the elements of the balance sheet of both companies for the five years 2019 through 2023. Requirements: 1. Create a pivot table to summarize the investment in equity securities and total noncurrent assets of the for the five-year period 2019 through 2023 on a separate worksheet. The data should be summarized by each company for all relevant years. Label the worksheet 'InvToNCAssets'. 2. On the pivot table add a calculated field to calculate the investment equity to noncurrent assets percentage (carry out 4 decimal places) for each company for the five-year period 2019 through 2023. Label calculated field 'Investment to Noncrt Asset." 3. On the 'InvToNCAssets' worksheet, create a pivot clustered column chart to summarize the investment equity to noncurrent assets percentage (from pivot table) for each company and all years, 2019 through 2023. Each company's data for each year should be displayed side by side and labels on outside end of each bar to display percentage. The X axis should display percentage intervals carried to two decimal places. The Y axis should display the company/year. Change the title of the of the pivot chart to "Investments in Equity to Noncurrent Assets Bar Chart." 4. Based on your output, answer the following questions on a separate worksheet labeled *Responses": a. What is the percentage of noncurrent assets invested in equity securities for each company, Circuit Discount TV and General Brands Inc.? AC 400 Intermediate Accounting III, Spring 2024 b. When comparing the percentage of noncurrent assets invested in equity securities ratios over the most recent five-year period, is Circuit Discount TV equity investment generally increasing or decreasing from year to year, or remained roughly the same? c. In general, which company invests the higher amount in equity securities as a percentage of its noncurrent assets during the most recent five-year period? 5. The worksheets should be formatted with appropriate numeric values using accounting format with zero decimals unless indicated otherwise. Learning Objectives 1. Create a pivot table in Excel 2. Add new calculated field to pivot table 3. Create a pivot chart with appropriate labels 4. Apply appropriate numeric format on pivot table and chart 5. Analyze and interpret outcome of pivot tables and calculated fields Data Set Background As an analyst you are asked to conduct research into the relative merits of investing of two companies operating in the discount retail industry. Circuit Discount TV and General Brands, Inc. invest in equity securities on a long-term basis. To do so, you should evaluate the percentage of noncurrent assets invested in equity securities. The investment in equity to noncurrent assets formula is: Total Equity Investments/Total Noncurrent Assets. The data set contains the elements of the balance sheet of both companies for the five years 2019 through 2023. Requirements: 1. Create a pivot table to summarize the investment in equity securities and total noncurrent assets of the for the five-year period 2019 through 2023 on a separate worksheet. The data should be summarized by each company for all relevant years. Label the worksheet 'InvToNCAssets'. 2. On the pivot table add a calculated field to calculate the investment equity to noncurrent assets percentage (carry out 4 decimal places) for each company for the five-year period 2019 through 2023. Label calculated field 'Investment to Noncrt Asset." 3. On the 'InvToNCAssets' worksheet, create a pivot clustered column chart to summarize the investment equity to noncurrent assets percentage (from pivot table) for each company and all years, 2019 through 2023. Each company's data for each year should be displayed side by side and labels on outside end of each bar to display percentage. The X axis should display percentage intervals carried to two decimal places. The Y axis should display the company/year. Change the title of the of the pivot chart to "Investments in Equity to Noncurrent Assets Bar Chart." 4. Based on your output, answer the following questions on a separate worksheet labeled *Responses": a. What is the percentage of noncurrent assets invested in equity securities for each company, Circuit Discount TV and General Brands Inc.? AC 400 Intermediate Accounting III, Spring 2024 b. When comparing the percentage of noncurrent assets invested in equity securities ratios over the most recent five-year period, is Circuit Discount TV equity investment generally increasing or decreasing from year to year, or remained roughly the same? c. In general, which company invests the higher amount in equity securities as a percentage of its noncurrent assets during the most recent five-year period? 5. The worksheets should be formatted with appropriate numeric values using accounting format with zero decimals unless indicated otherwise.