ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
TITLETHINK LIKE AN ACCOUNTANT
2
Chapter 14
3
DATA ANALYSISThe Data worksheet contains a table of unpaid invoices as of December 31 of the prior year. Included in the list are the total payments received on each invoice during the current year. The final column of the table shows the amount written off as uncollectible. The Analysis worksheet will be used to perform your analysis.
4
STEPS1Read all steps before working with the workbook.
5
2Click the Data tab to view the data.
6
3Key your name in cell C2.
7
To create a PivotTable:
8
4Click any single cell in the data.
9
5On the Insert tab in the Tables group, click PivotTable.
10
6Select the Existing Worksheet option.
11
7Navigate to the Analysis worksheet, click on cell A4, and click OK. Your worksheet should appear similar to the following image.
12
13
8In the PivotTable Field List, click and drag Invoice Amount, Amount Paid, and Written Off to the Values box.
14
9In the PivotTable Field List, click and drag Invoice Date to the Row Labels box. Your field list should appear as follows.
15
16
10Right click on any date in column A.
17
11Select Group.
18
12Ensure that Months is selected.
19
13Click OK. The PivotTable displays the totals of the three values for each month.
20
14Click on the arrow in cell A4 and select Sort Newest to Oldest.
21
Creating formulas from values in a PivotTable requires advanced Excel skills. The following steps instruct you to create a copy of the PivotTable. Creating formulas using this copy will be less complex.
22
15Highlight the PivotTable in cells A4:D12.
23
16Copy the range to the Clipboard.
24
17Position the cell pointer at A16.
25
18On the Home tab in the Clipboard group, select the arrow under the Paste option.
26
19Select Paste Values.
27
20Format the numbers in the table to be displayed with dollar signs and two decimal places.
28
To calculate the percent of accounts written off each month:
29
21At cell E16, enter the label Percentage.
30
22At cell E17, enter a formula to divide the Sum of Written Off by the Sum of Invoice Amount.
31
23Copy the formula in cell E17 to cells E18:E23.
32
24Format number values in column E to be displayed with a percentage sign and one decimal place.
33
25Modify the labels in column A to reflect the aging of accounts receivable. For example, change Dec to Current, Nov to 1-30 Days, etc. On December 31, any invoice having a December date would be considered a current accounts receivable, any invoice having a November date would be 1-30 days past due, etc.
34
26Make any other changes in the table headings and format you consider appropriate.
35
27Use the table to answer the questions.
36
28Print the Analysis worksheet, as instructed by your teacher.
37
29Save your work.
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100