14.4 Download the spreadsheet from the course website and perform the following tasks. Required a. Create a formula in column F to calculate how many days a particula invoice has been outstanding. b. Create a formula in column G that attaches the correct label to each invoice (e.g., if the invoice is 0-30 days old, the label should be "0-30"; it is 31-60 days old, the label should be "31-60", etc.) according to the table in the upper right corner of the spreadsheet. c. Create a pivot table that shows the sum, count, and average values fo each of the four categories of accounts receivable. d. Create a pivot table that shows total amounts and count of invoices b both category (e.g., 0-30, 31–60, etc.) and region (East, West, North, South). e. Modify the pivot table in step d to be able to filter the results by salesperson. Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Define Name ✓ fx Σ - Insert Function F9 ✓ ? ✓ A ✓ AutoSum Recently Financial Logical Text Used fx A ✓ Ꮎ ✓ Date & Time Lookup & Math & More Name Reference Trig Functions Manager Use in Formula ✓ Create from Selection Trace Precedents Trace Dependents Remove Arrows √ Fx Show M 165 Error Watch Formulas Checking Window B C D E F G 1 Aging Date: 9/24/2020 2 3 Customer Region Salesperson Balance Invoice Date Age (Days) Group 4 1 East A 435.22 9/1/2020 5 2 South B 598.55 8/20/2020 6 3 East C 124.55 9/1/2020 7 4 West D 199.99 7/15/2020 8 5 North E 945.00 9/2/2020 9 6 South F 831.64 9/2/2020 10 7 West G 728.95 9/2/2020 11 8 East H 362.59 9/3/2020 12 9 North A 333.79 9/10/2020 13 10 North B 184.65 6/6/2020 14 11 South C 299.99 8/15/2020 15 12 East D 499.99 9/11/2020 16 13 West E 399.99 6/30/2020 17 14 West F 259.95 9/12/2020 18 15 North G 19 16 South 20 17 South 21 18 South HAB A 784.75 9/13/2020 666.66 9/11/2020 444.44 7/15/2020 234.56 9/17/2020 33 Ready Sheet2 Sheet1 Accessibility: Investigate H
14.4 Download the spreadsheet from the course website and perform the following tasks. Required a. Create a formula in column F to calculate how many days a particula invoice has been outstanding. b. Create a formula in column G that attaches the correct label to each invoice (e.g., if the invoice is 0-30 days old, the label should be "0-30"; it is 31-60 days old, the label should be "31-60", etc.) according to the table in the upper right corner of the spreadsheet. c. Create a pivot table that shows the sum, count, and average values fo each of the four categories of accounts receivable. d. Create a pivot table that shows total amounts and count of invoices b both category (e.g., 0-30, 31–60, etc.) and region (East, West, North, South). e. Modify the pivot table in step d to be able to filter the results by salesperson. Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Define Name ✓ fx Σ - Insert Function F9 ✓ ? ✓ A ✓ AutoSum Recently Financial Logical Text Used fx A ✓ Ꮎ ✓ Date & Time Lookup & Math & More Name Reference Trig Functions Manager Use in Formula ✓ Create from Selection Trace Precedents Trace Dependents Remove Arrows √ Fx Show M 165 Error Watch Formulas Checking Window B C D E F G 1 Aging Date: 9/24/2020 2 3 Customer Region Salesperson Balance Invoice Date Age (Days) Group 4 1 East A 435.22 9/1/2020 5 2 South B 598.55 8/20/2020 6 3 East C 124.55 9/1/2020 7 4 West D 199.99 7/15/2020 8 5 North E 945.00 9/2/2020 9 6 South F 831.64 9/2/2020 10 7 West G 728.95 9/2/2020 11 8 East H 362.59 9/3/2020 12 9 North A 333.79 9/10/2020 13 10 North B 184.65 6/6/2020 14 11 South C 299.99 8/15/2020 15 12 East D 499.99 9/11/2020 16 13 West E 399.99 6/30/2020 17 14 West F 259.95 9/12/2020 18 15 North G 19 16 South 20 17 South 21 18 South HAB A 784.75 9/13/2020 666.66 9/11/2020 444.44 7/15/2020 234.56 9/17/2020 33 Ready Sheet2 Sheet1 Accessibility: Investigate H
Excel Applications for Accounting Principles
4th Edition
ISBN:9781111581565
Author:Gaylord N. Smith
Publisher:Gaylord N. Smith
ChapterMB: Model-building Problems
Section: Chapter Questions
Problem 1M: The general ledger of Jay Consulting shows the following balances at July 31: Jay has asked you to...
Related questions
Question
I need help with parts b - e.
Transcribed Image Text:14.4 Download the spreadsheet from the course website and perform the
following tasks.
Required
a. Create a formula in column F to calculate how many days a particula
invoice has been outstanding.
b. Create a formula in column G that attaches the correct label to each
invoice (e.g., if the invoice is 0-30 days old, the label should be "0-30";
it is 31-60 days old, the label should be "31-60", etc.) according to the
table in the upper right corner of the spreadsheet.
c. Create a pivot table that shows the sum, count, and average values fo
each of the four categories of accounts receivable.
d. Create a pivot table that shows total amounts and count of invoices b
both category (e.g., 0-30, 31–60, etc.) and region (East, West, North,
South).
e. Modify the pivot table in step d to be able to filter the results by
salesperson.
Transcribed Image Text:Home
Insert
Draw
Page Layout Formulas Data Review View Automate
Tell me
Define Name ✓
fx Σ -
Insert
Function
F9
✓
?
✓
A
✓
AutoSum Recently Financial Logical Text
Used
fx
A
✓
Ꮎ
✓
Date &
Time
Lookup & Math & More
Name
Reference Trig Functions Manager
Use in Formula ✓
Create from Selection
Trace Precedents
Trace Dependents
Remove Arrows
√ Fx
Show
M
165
Error Watch
Formulas Checking Window
B
C
D
E
F
G
1 Aging Date: 9/24/2020
2
3
Customer
Region Salesperson
Balance Invoice Date Age (Days)
Group
4
1 East
A
435.22
9/1/2020
5
2 South
B
598.55
8/20/2020
6
3 East
C
124.55 9/1/2020
7
4 West
D
199.99
7/15/2020
8
5 North
E
945.00
9/2/2020
9
6 South
F
831.64
9/2/2020
10
7 West
G
728.95
9/2/2020
11
8 East
H
362.59 9/3/2020
12
9 North
A
333.79
9/10/2020
13
10 North
B
184.65
6/6/2020
14
11 South
C
299.99 8/15/2020
15
12 East
D
499.99
9/11/2020
16
13 West
E
399.99
6/30/2020
17
14 West
F
259.95 9/12/2020
18
15 North
G
19
16 South
20
17 South
21
18 South
HAB
A
784.75
9/13/2020
666.66 9/11/2020
444.44 7/15/2020
234.56 9/17/2020
33
Ready
Sheet2
Sheet1
Accessibility: Investigate
H
AI-Generated Solution
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
Recommended textbooks for you
Excel Applications for Accounting Principles
Accounting
ISBN:
9781111581565
Author:
Gaylord N. Smith
Publisher:
Cengage Learning
Fundamentals of Financial Management, Concise Edi…
Finance
ISBN:
9781305635937
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Fundamentals of Financial Management, Concise Edi…
Finance
ISBN:
9781285065137
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Excel Applications for Accounting Principles
Accounting
ISBN:
9781111581565
Author:
Gaylord N. Smith
Publisher:
Cengage Learning
Fundamentals of Financial Management, Concise Edi…
Finance
ISBN:
9781305635937
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Fundamentals of Financial Management, Concise Edi…
Finance
ISBN:
9781285065137
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
