Learning Objectives

Create a pivot table in Excel

Add new calculated field to pivot table

Create a pivot chart with appropriate labels

Apply appropriate numeric format on pivot table and chart

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:

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'.

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.$\text{'}$

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 Y axis should display percentage intervals carried to two decimal places. The $x$ axis should display the company$/$year$.$ Change the title of the of the pivot chart to "Investments in Equity to Noncurrent Assets Bar Chart."

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.?