80.2K

Verified Solution

Link Copied!

Part B - PivotTable and PivotChart: Investment Fair Value vs. Cost

Refer to the RawData-Investment FV vs. Cost worksheet.

How to choose the Combo Chart-Type for the PivotChart:

Create a PivotTable using this data to show the Total Fair Value and Total Cost for each investment category for the year 2020 only.

Place Investment Category in rows. Place both Fair Value and Cost in values and ensure the SUM is being calculated for both.

Place Year in filters and filter the years to show 2020 only.

Format Fair Value and Cost columns to modify the custom name for each and display data as accounting format with zero decimals.

(for Fair Value, from the values field, select "Sum of Fair Value" and choose "Value Field Settings" (last option in the list). Change custom name to

"Total Fair Value", then click on number format, choose accounting, and reduce to 0 decimals. Repeat steps for Cost (custom name: "Total Cost").

Change "Row Labels" header to "Investment Category" (modify this label by typing directly in the cell).

Create a PivotChart using this newly created PivotTable. With your cursor on the PivotTable, click on Insert from the top menu, then PivotChart.

Choose Combo Chart (clustered column and line chart - see image for assistance). Ensure Fair Value will be the clustered column and Cost will

be the line. Position your PivotChart to beside your PivotTable.

Add a proper Chart Title ("2020 Investments - Fair Value vs. Cost") and hide all field buttons (PivotChart Analyze/Field Buttons/Hide All).

Move legend to the bottom.

QUESTIONS:

1) Which investment category saw an impairment in value when the fair value was assessed in 2020?

2) Calculate the percentage decrease in value of this investment category.

3) Which investment category saw the largest increase % when adjusted for its fair value?

4) Calculate the percentage increase in value of this investment category.

Student Work Area (position PivotTable and PivotChart here):

Insert Chart ? All Charts r) Recent Templates Column Lox Line Pie Bar Area : XY (Scatter) (9) Map Stock w Surface Radar Treemap (Q) Sunburst Ih Histogram Box \& Whisker Funnel Combo Clustered Column - Line Choose the chart type and axis for your data series: Series Name Chart Type Secondary Axis - Total Fair Value Total Cost Line OK Cancel Insert Chart ? All Charts r) Recent Templates Column Lox Line Pie Bar Area : XY (Scatter) (9) Map Stock w Surface Radar Treemap (Q) Sunburst Ih Histogram Box \& Whisker Funnel Combo Clustered Column - Line Choose the chart type and axis for your data series: Series Name Chart Type Secondary Axis - Total Fair Value Total Cost Line OK Cancel

Answer & Explanation Solved by verified expert

See Answer