Description
Complete the file below
Unformatted Attachment Preview
Quarterly Balance Sheet Data
Using the balance sheet data shown to the right, create the following pivot table and pivot table chart:
Place the pivot table and chart on this worksheet.
(this is an image)
(this is an image)
Data:
* The Quarterly Balance Sheet Data (L3:S27) MUST be in a form usable by a pivot table (with financial statement categories as column headers)
Transform the data and place the transformed data in the range L30:AJ37
* All fields in the transposed data must have a label in row 30. Add the label Period to cell L30.)
Pivot Table:
Cash And Cash Equivalents
Short Term Investments
Net Receivables
Inventory
Other Current Assets
Total Current Assets
Long Term Investments
Prop Plant & Equip
Goodwill
Intangible Assets
Other Assets
Total Assets
Accounts Payable
Short/Current Long Term Debt
Other Current Liabilities
Total Current Liabilities
Long Term Debt
Other Liabilities
Deferred LT Liability Charges
Total Liabilities
Common Stock
Retained Earnings
Other Stockholder Equity
Total Stockholder Equity
* Create a pivot table based on the transposed balance sheet data, and add the date field as a row field
Place the pivot table on THIS worksheet beginning approximately in cell J41.
* Group the ‘period’ field by year and quarter (do NOT include the month value for grouping).
* Change the pivot table layout to tabular form
* This pivot table has NO column fields and NO filter fields
* Add Total Assets as a ‘Values’ field.
– In the ‘Value Field Settings’ dialog box, change this field’s custom name to ‘Total Assets per Period’ and format the field as currency with 0 decimal places of precision.
* Create a calculated field to display the Quick Ratio.
– The quick ratio is a calculated fields and = (Total Current Assets – Inventory) / Total Current Liabilities
– In the calculated field dialog box, name this field ‘QR’.
– In the ‘Value Field Settings’ dialog box, apply the custom name ‘Quick Ratio’ and format the quick ratio as a percentage with 2 decimal places of precision
* Show subtotals at the bottom of each group (year). Include grand totals for columns.
* Name the Pivot table ‘Pivot_1’
Pivot Chart
* Create a pivot chart based on the ‘Pivot_1’ pivot table.
* The pivot chart is of type combo with total assets as a clustered column chart and Quick Ratio as a line chart.
– Ensure you add a secondary vertical axis for the Quick Ratio
* Add the chart titles ‘Financial Analysis’
* Add a blue chart border
* Move the legend to the chart bottom
* The secondary vertical axis values range from 75% to 150% in increments of 15%
* The primary vertical axis values range from $0 to $360,000,000 in increments of $40,000,000
* Name the pivot chart ‘Chart 1’
Mar 2022
20,484,000
46,671,000
29,299,000
2,132,000
8,283,000
106,869,000
170,430,000
27,010,000
5,414,000
3,206,000
8,757,000
321,686,000
59,321,000
11,605,000
8,080,000
79,006,000
75,427,000
36,074,000
2,930,000
193,437,000
31,251,000
96,364,000
634,000
128,249,000
Jun 2022
21,120,000
20,481,000
30,343,000
2,349,000
15,085,000
89,378,000
164,065,000
22,471,000
5,116,000
3,893,000
5,422,000
290,345,000
60,671,000
10,999,000
8,940,000
80,610,000
53,329,000
33,427,000
3,624,000
170,990,000
27,416,000
92,284,000
(345,000)
119,355,000
Sep 2022
13,844,000
11,233,000
31,537,000
2,111,000
9,806,000
68,531,000
130,162,000
20,624,000
4,616,000
4,142,000
3,764,000
231,839,000
48,649,000
6,308,000
8,491,000
63,448,000
28,987,000
24,826,000
3,031,000
120,292,000
23,313,000
87,152,000
1,082,000
111,547,000
Dec 2022
2,119,392
2,055,268
3,044,920
235,722
1,513,780
8,969,082
16,463,923
2,254,965
513,391
390,663
544,098
29,136,121
6,088,335
1,103,750
897,129
8,089,214
5,351,565
3,354,399
363,668
17,158,847
2,751,196
9,260,699
(34,621)
11,977,274
Mar 2023
20,258,676
46,157,619
28,976,711
2,108,548
8,191,887
105,693,441
168,555,270
26,712,890
5,354,446
3,170,734
8,660,673
318,147,454
58,668,469
11,477,345
7,991,120
78,136,934
74,597,303
35,677,186
2,897,770
191,309,193
30,907,239
95,303,996
627,026
126,838,261
Jun 2023
14,093,192
11,435,194
32,104,666
2,148,998
9,982,508
69,764,558
132,504,916
20,995,232
4,699,088
4,216,556
3,831,752
236,012,102
49,524,682
6,421,544
8,643,838
64,590,064
29,508,766
25,272,868
3,085,558
122,457,256
23,732,634
88,720,736
1,101,476
113,554,846
Sep 2023
13,567,120
11,008,340
30,906,260
2,068,780
9,609,880
67,160,380
127,558,760
20,211,520
4,523,680
4,059,160
3,688,720
227,202,220
47,676,020
6,181,840
8,321,180
62,179,040
28,407,260
24,329,480
2,970,380
117,886,160
22,846,740
85,408,960
1,060,360
109,316,060
93992
Purchase answer to see full
attachment
