Excel Assignment 2019 S1 Instructions
Scenario
The Blues Brothers guitar Company makes three types of guitar. The Company has used a traditional
product costing system with direct labour hours as the only cost driver for overhead application, but
has asked you to develop a full activity based costing method solution for product costing and
profitability measurement.
Start by downloading the assessment workbook from iLearn and copying/moving it to an appropriate
folder. When you open the workbook it is very important that you Enable Macros/Content. You will
then be asked to enter your Student Number (you will not be able to edit it afterwards, so type it in
carefully) and then enter your Student Name.
General Guidelines
•
•
•
•
•
•
•
•
All the light blue cells require you to insert some form of calculation, i.e. start with an “=”, just
typing in the answer will result in 0 marks.
Do not change the structure of the workbook in anyway or put any workings anywhere other
than directed.
Only use rounding functions where specifically requested.
The use of named ranges and tables is encouraged, and in certain cases required.
Because you are developing a model that we will wish to use for future months and different values
it is very important that all calculations will return a correct answer regardless of the values in the
spreadsheet and must still work correctly when the lists of data (Labour Costs, BOM & Inventory)
are sorted in a different order.
Double check your answers as you go as one wrong result may affect other results.
Remember to save often.
When you are ready to submit, save, close and upload the completed Excel File to iLearn (Excel
Submission). You do not need to rename it as your student number will be automatically attached
by iLearn, but please ensure that it is an Excel file you submit, (Numbers files will not be accepted)
and that there are no “non-English” characters in the file name as these files cannot be opened
in an English version of Excel.
Detailed instructions are provided on the following pages. Please follow each of the instructions
precisely.
Section
A
The following questions are to be answered on the Labour Costs Sheet:
Marks
10
A
1
Calculate Monthly Super for each employee using the specified Super rate
in E3. Apply an absolute cell reference to E3 so that the formula can be
dragged down.
2
A
2
Calculate the Total Package (monthly) by adding salary and super for
each employee.
1
A
3
In row 19, calculate total monthly salaries, super and package.
1
A
4
In E4 calculate the average monthly package rounded to the nearest 10.
2
A
5
In E5 calculate the average hourly wage by dividing the value in E4 by 20
and then by 8.
2
A
6
In E6 calculate the highest employee salary (total package).
1
A
7
In E7 calculate the lowest employee salary (total package).
1
Section
B
The following questions are to be answered on the Activity Based Costs
Sheet:
B
1
Direct labour costs are calculated as the average hourly salary of the
employees assigned to that product line (e.g. calculate the average total
packages for employees working on Tenor Electric, then divide by 8 and
divide by 20). These assignments can change from one month to the next,
and the calculation must still work, i.e. if E10026 is assigned to Bass Electric
your calculation should stop including his salary.
3
B
2
In row 13 calculate the number of employees working on each product
line (as with the above example if the employees are reassigned you
calculations should still give the correct answer.)
2
B
3
In B25 calculate the total monthly overheads
1
Section
C
The following questions are to be answered on the Cost Drivers Sheet (using
the values in the ABC sheet):
C
1
In C4 calculate the total number of setup hours for one month. In D4
calculate the cost for 1 hour.
3
C
2
In C5 calculate the total machine hours for one month. In D5 calculate the
cost of one machine hour.
3
C
3
In C6 calculate the total number of sales invoices for one month. In D6
calculate the cost of one sales invoice.
2
C
4
In C7 calculate the total number of orders (supplier invoices) for one
month. In D7 calculate the cost of placing one order.
2
C
5
In C8 calculate the total number of employees. In D8 calculate the cost of
payroll for one employee.
2
C
6
In C9 calculate the total number of units made in one month. In D9
calculate the cost of packaging for one unit.
2
C
7
In C10 calculate the total square metres used. In D10 calculate the cost
per square metre.
2
Marks
6
Marks
16
Section
D
The following questions are to be answered on the BOM and Inventory
Sheets:
D
1
In the Inventory sheet convert the data in A3:I61 to a table. Change the
name of the table to Inventory.
2
D
2
In the Inventory sheet name the range K4:L9 Suppliers. (This should be a
named range NOT a table).
1
D
3
In the BOM sheet convert the data in A3:H39 to a table. Change the name
of the table to BOM. Answer all the rest of this section in the BOM sheet.
2
D
4
For each component part code look up the description in the Inventory
sheet.
2
D
5
For each component part code look up the unit of measure (per) in the
Inventory sheet.
2
D
6
For each component part code look up the unit price in the Inventory
sheet.
2
D
7
For each component part code look up the discount rate for the supplier
of that component. (This is a challenge question, if it is too hard skip it and
just leave blank).
4
D
8
Calculate the total cost of each component less the discount. (1 mark will
be awarded if the cost is calculated correctly without the discount.)
2
Section E
The following questions are to be answered on the Product Costs Sheet:
Marks
17
Marks
21
E
1
Calculate the Set-up cost for each product using the calculated cost
driver and information in the ABC sheet.
2
E
2
Calculate the Machinery running for each product using the calculated
cost driver and information in the ABC sheet.
1
E
3
Calculate the Receiving for each product using the calculated cost driver
and information in the ABC sheet.
2
E
4
Calculate the Payables administration for each product using the
calculated cost driver and information in the ABC sheet.
2
E
5
Calculate the Payroll preparation and payment for each product using
the calculated cost driver and information in the ABC sheet.
2
E
6
Calculate the Packing for each product using the calculated cost driver
and information in the ABC sheet.
1
E
7
Calculate the Utilities for each product using the calculated cost driver
and information in the ABC sheet.
2
E
8
In B11:D11 Calculate the total cost of overheads for each product.
1
E
9
Using information in the BOM, calculate the cost of Direct Materials for
each product.
2
E
10
Calculate the cost of Direct Labour for each product using values in the
ABC sheet
1
E
11
In row 17 pull through the calculated overheads from row 11
1
E
12
Calculate the total cost per unit for each product
2
E
13
Using the sell price in the ABC sheet, calculate the profit made on each
product (in some cases this may be negative, indicating a loss)
1
E
14
Calculate the Gross Margin for each product (as a percentage).
1
Section
F
The following questions are to be answered on the Inventory Sheet:
F
1
In column G use a formula to determine if the stock level is below the
minimum (specified in L18), if it is put a "Y" in the reorder column otherwise
leave it blank (i.e. put “”)
2
F
2
In column H, if the stock level is below the minimum look up the number to
reorder in the Reorder Quantities data. Reorder amounts are based on
unit, so if it is a set we reorder 20 but if it is a litre we reorder 75. Note these
values can change and your formula should still work.
3
F
3
In column I, calculate the cost of the reorder using the unit price, quantity
to be reordered and supplier discount.
4
F
4
In column M, calculate how many products (unique product codes) are
supplied by each supplier.
2
F
5
In column N, calculate the total cost to Re-order from each supplier.
2
F
6
In L19 calculate the total value of the current inventory (stock on hand). To
do this you will need to find out how to use the SUMPRODUCT function.
2
TOTAL MARKS
Marks
15
85
ACCG200 – Excel Overview
Nicky Bull
Welcome
o
o
o
Phones
Resources
Assessment: Friday 10th May before 6pm
Outline
o
o
o
o
o
o
o
o
o
o
Formulas
Absolute References
Basic Functions
Working across sheets
Named Ranges
COUNTIFS & SUMIFS
Logical Functions
Tables
VLOOKUP
Charts
Useful Shortcuts
Operation
Windows
Mac
Save
Ctrl+S
Cmd+S
New
Ctrl+N
Cmd+N
Open
Ctrl+O
Cmd+O
Close
Ctrl+W
Cmd+W
Undo
Ctrl+Z
Cmd+Z
Redo
Ctrl+Y
Cmd+Y
Copy
Ctrl+C
Cmd+C
Cut
Ctrl+X
Cmd+X
Paste
Ctrl+V
Cmd+V
Go to A1
Ctrl+Home
Cmd+Home
Select All
Ctrl+A
Cmd+A
Absolute
F4
Cmd+T
Formulas
Operator
Reference
= (A1 + A2) * 5
Equals Sign
Constant
Operators
Addition
+
Subtraction
-
Multiplication
*
Division
/
Exponent
^
Percent
%
Order of Evaluation
B
• Brackets
O
• Order
D
• Division
M
• Multiplication
A
• Addition
S
• Subtraction
Functions
=SUM(B2:B10)
=AVERAGE(B2:B10)
=MAX(B2:B10)
=MIN(B2:B10)
=ROUND(B6,2)
Create Names
o Name Box
o Create from Selection
o Name Manager
Range Names
INCORRECT
CORRECT
SALES TAX
SALES_TAX
SALES-TAX
SALES_TAX
FIRST QUARTER
FIRST.QUARTER
2000
YR2000
%
RATE
ACT1
ACT_1
•
•
•
•
•
•
Must be unique
Start with letter or “_”
Letters, numbers or “_”
No spaces
Max 255 characters
Not a cell reference
SUMIFS & COUNTIFS
=COUNTIFS(Criteria Range, Criteria)
=SUMIFS(Sum Range, Criteria Range, Criteria)
IF Function
Tables
o Create and Name
o Working with Tables
VLOOKUP
=VLOOKUP(value, range, Column, [range-Lookup])
Assessment
• Deadline:
• Consult:
•
•
•
•
Friday 10th May before 6pm
Tuesday 7 May 10-12 & 1-3
Download from iLearn and Enable macros
Save often, submit Excel workbook
Don’t change the workbook structure or data
Only enter calculations in answer cells
• email: nicole.bull@mq.edu.au
Customer Invoice
Product
E-Tec USB 2 Drive
Lenolo Tab 2
Reflux A4 paper White 500
Laser Jet Toner Cartridge Black
Price
$19.99
$196.98
$24.99
$194.00
Totals
Add
Subtract
Multiply
Divide
Exponent
+
*
/
^
Quantity
45
12
28
3
Sub Tot
Discount %
5%
10%
5%
5%
Discount Amt
Total
Sales Ledger 2016
Name
Prentice
Chirillo
Wong
Baker
Richards
Hite
de Moret
McWilliams
Howard
Silvis
Morris
Lewis
Jefferson
Patrino
TrownsNguyen
Johnsen
OBrien
Tantalo
Parker
TOTALS
Q1
23100
22700
20900
15900
25000
24110
25600
22600
22700
23300
23800
54826
Q2
13100
24110
22600
22700
34000
54812
48752
58445
48648
24600
27700
59224
Q3
16800
48752
20140
17600
21000
15200
30300
16800
36855
21380
12600
24100
Q4
37422
39566
22185
12334
38443
28464
18443
20454
29430
42204
17790
23667
31200
50224
15500
30900
14815
24500
20900
15900
23100
17300
19000
24400
13100
25600
22600
22700
17700
15200
30300
16800
11580
22000
20140
17600
34210
27330
34482
29843
15590
38843
29466
39003
Total
Average
Max
Min
Commission
Commission rate:
4%
Summary Data 2016
Total Sales
Average Sales
Total Commission
Bonus
Emp ID
1
2
3
4
5
6
7
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fran
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Sales
$120,000.00
$70,000.00
$78,000.00
$45,000.00
$89,000.00
$88,000.00
$80,000.00
5%
Bonus
Book Sales by Author
Author ID
1755
1932
1704
1328
1048
1818
1387
1325
1158
1747
1035
1142
1262
1206
1259
1676
1487
1006
1238
1162
1773
1939
1620
1372
1414
1928
1327
1929
1222
1871
1024
1428
1664
1950
1622
1179
1289
1514
1863
1482
1914
1911
1680
1246
1622
1513
1451
Contract Date Years Employed
1/15/2012
7.32
1/7/2012
7.34
11/24/2011
7.46
10/10/2011
7.58
7/19/2011
7.81
5/5/2011
8.01
5/2/2011
8.02
3/9/2011
8.17
3/3/2011
8.19
3/2/2011
8.19
1/20/2011
8.31
12/21/2010
8.39
10/7/2010
8.59
9/16/2010
8.65
8/23/2010
8.71
6/22/2010
8.88
5/12/2010
8.99
5/5/2010
9.01
4/15/2010
9.07
1/17/2010
9.31
12/27/2009
9.37
12/27/2009
9.37
9/23/2009
9.63
5/28/2009
9.95
5/6/2009
10.01
4/27/2009
10.04
3/16/2009
10.15
12/3/2008
10.44
10/22/2008
10.55
10/1/2008
10.61
9/21/2008
10.64
6/29/2008
10.86
3/23/2008
11.13
3/2/2008
11.19
2/25/2008
11.21
2/14/2008
11.24
12/13/2007
11.41
10/24/2007
11.54
9/22/2007
11.63
8/7/2007
11.76
7/26/2007
11.79
4/29/2007
12.03
1/8/2007
12.34
12/24/2006
12.38
12/15/2006
12.40
10/4/2006
12.60
9/29/2006
12.61
Books in Print
5
12
8
5
4
8
11
6
8
5
11
1
4
1
2
6
6
15
12
1
12
8
4
13
8
1
1
9
4
8
2
9
8
5
10
11
8
14
14
9
14
11
15
1
5
15
13
Books Sold
478985
274491
96684
66937
43921
192681
304729
102016
214442
70283
83399
225503
188565
213766
491853
222954
58989
316237
159593
295514
494065
283414
384719
326649
69750
230678
237358
9483
307741
454064
255650
16274
439448
133993
69105
43062
366628
341694
128447
459115
35987
14412
68935
269278
469732
170817
130656
1534
1510
1803
1925
1817
1339
1045
1862
1376
1755
1021
1773
1326
1495
1390
1915
1647
1230
1958
1843
1804
1066
1951
1799
1210
1816
1902
1362
1701
1329
1042
1612
1416
1872
1111
1619
1198
1298
1699
1594
1546
1693
1185
1535
1952
1512
2222
9/28/2006
8/9/2006
7/1/2006
2/8/2006
12/22/2005
11/8/2005
6/24/2005
11/21/2004
10/24/2004
9/29/2004
8/6/2004
6/13/2004
6/11/2004
6/10/2004
11/27/2003
9/16/2003
6/22/2003
6/16/2003
6/8/2003
5/3/2003
3/30/2003
12/19/2002
9/26/2002
8/4/2002
6/1/2002
11/29/2001
10/18/2001
7/1/2001
6/23/2001
5/17/2001
4/2/2001
3/4/2001
12/27/2000
11/24/2000
11/8/2000
10/20/2000
9/28/2000
9/28/2000
8/1/2000
7/17/2000
6/14/2000
6/7/2000
4/29/2000
3/19/2000
2/13/2000
1/15/2000
6/30/2018
12.62
12.75
12.86
13.26
13.38
13.51
13.88
14.47
14.54
14.61
14.76
14.91
14.91
14.92
15.45
15.65
15.88
15.90
15.92
16.02
16.11
16.39
16.62
16.77
16.94
17.45
17.56
17.86
17.88
17.98
18.11
18.18
18.37
18.46
18.51
18.56
18.62
18.62
18.78
18.81
18.91
18.93
19.03
19.14
19.24
19.32
0.86
15
10
13
14
7
8
7
12
6
1
14
7
12
13
1
3
9
5
12
11
14
13
13
10
12
12
9
3
12
1
15
13
11
7
11
7
1
14
1
14
7
3
3
6
8
8
5
20501
481840
113217
74256
494672
56188
283588
404443
55193
433029
344944
156940
142362
251418
202863
53061
357692
380640
490910
86193
53003
231823
292451
89636
365614
346024
424493
189282
207992
325510
16892
277507
493759
234903
140462
459194
496293
115011
329506
387860
339998
79116
197522
282843
194145
446184
555555
Sell Price
2.99
12.99
9.99
9.99
5.99
3.99
2.99
2.99
10.99
3.99
10.99
2.99
2.99
23.99
12.99
9.99
12.99
3.99
3.99
12.99
7.99
9.99
7.99
5.99
3.99
5.99
9.99
2.99
5.99
10.99
7.99
2.99
5.99
5.99
3.99
23.99
23.99
2.99
12.99
23.99
2.99
12.99
2.99
3.99
9.99
12.99
10.99
Income Earned
1432165.15
3565638.09
965873.16
668700.63
263086.79
768797.19
911139.71
305027.84
2356717.58
280429.17
916555.01
674253.97
563809.35
5128246.34
6389170.47
2227310.46
766267.11
1261785.63
636776.07
3838726.86
3947579.35
2831305.86
3073904.81
1956627.51
278302.5
1381761.22
2371206.42
28354.17
1843368.59
4990163.36
2042643.5
48659.26
2632293.52
802618.07
275728.95
1033057.38
8795405.72
1021665.06
1668526.53
11014168.85
107601.13
187211.88
206115.65
1074419.22
4692622.68
2218912.83
1435909.44
Total Income Earned:
Total Books Sold:
Average Sell Price:
Average No Books in Print:
First Contract Date:
Create Named
Ranges using Name
Box and Create
10.99
10.99
5.99
7.99
2.99
10.99
10.99
12.99
9.99
15.99
5.99
2.99
9.99
9.99
2.99
2.99
12.99
7.99
3.99
15.99
10.99
3.99
9.99
7.99
9.99
2.99
2.99
10.99
15.99
7.99
2.99
15.99
5.99
2.99
2.99
15.99
23.99
2.99
7.99
12.99
12.99
5.99
10.99
7.99
7.99
2.99
3.99
225305.99
5295421.6
678169.83
593305.44
1479069.28
617506.12
3116632.12
5253714.57
551378.07
6924133.71
2066214.56
469250.6
1422196.38
2511665.82
606560.37
158652.39
4646419.08
3041313.6
1958730.9
1378226.07
582502.97
924973.77
2921585.49
716191.64
3652483.86
1034611.76
1269234.07
2080209.18
3325792.08
2600824.9
50507.08
4437336.93
2957616.41
702359.97
419981.38
7342512.06
11906069.07
343882.89
2632752.94
5038301.4
4416574.02
473904.84
2170766.78
2259915.57
1551218.55
1334090.16
2216664.45
eate Named
nges using Name
x and Create
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fran
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Gender
F
M
F
F
F
M
F
Department
IT
Marketing
Finance
IT
Finance
Finance
Marketing
Salary
$120,000.00
$70,000.00
$78,000.00
$45,000.00
$89,000.00
$88,000.00
$80,000.00
Number of Staff in Finance
Number of Staff who earn over 80K
Total Salaries in Finance
Total Salaries in IT
Total Salaries in Marketing
Finance
Challenge: Change the SUMIFS to get total salaries for FEMALES in each department
Finance
IT
Marketing
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fred
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Attendance
100
65
80
80
65
100
100
Results
84
57
68
71
63
75
52
Grade
60 - 100
0 - 59
Logical
Functions
=IF
=OR
Pass
Fail
Logical
Functions
=IF
=OR
Last
First
DECKER
BARRY
COLE
CHAFFEE
DONNELL
FERN
ELLIS
CHUNG
FLANDERS
CLARK
BOLLER
CAROL
CLARK
CLARKE
COMUNTZIS
Erica
Angela
Daniel
Adam
Susan
Elizabeth
Janet
Bob
Sabrina
John
Mary
Elizabeth
Anna
Joe
Mark
KPI
100000
120000
120000
100000
80000
120000
120000
120000
100000
100000
120000
150000
100000
120000
120000
Sales KPI Met Y/N
85736
135647
61085
137491
91386
136424
154849
143610
119338
176077
120440
119887
91684
136519
147034
Bonus
Bonus Rates
KPI Met
KPI Not Met
10%
3%
Invoice # Invoice Amt Invoice Date
30245
30246
30247
30248
30249
30250
30251
30252
30253
30254
30255
30256
30257
30258
30259
$1,460.00
$7,338.00
$3,910.00
$7,045.00
$4,733.00
$8,756.00
$8,282.00
$4,455.00
$816.00
$2,987.00
$8,717.00
$6,537.00
$4,758.00
$558.00
$1,649.00
12/7/2017
12/15/2017
12/16/2017
12/24/2017
12/24/2017
1/6/2018
1/13/2018
1/13/2018
1/17/2018
1/26/2018
1/26/2018
1/27/2018
1/30/2018
1/31/2018
2/2/2018
Due Date
Paid Date
12/21/2017
12/29/2017
12/30/2017
1/7/2018
1/7/2018
1/20/2018
1/27/2018
1/27/2018
1/31/2018
2/9/2018
2/9/2018
2/10/2018
2/13/2018
2/14/2018
2/16/2018
12/8/2017
12/17/2017
1/5/2018
1/5/2018
1/25/2018
1/14/2018
2/21/2018
2/21/2018
2/6/2018
3/5/2018
2/18/2018
2/24/2018
3/5/2018
2/21/2018
2/27/2018
Clients who pay before the due Date are elligible for a 10% discount
Clients who pay more than 14 days after the Due Date pay a $100 late
penalty
Days After Extension Early Discount
-13
-12
6
-2
18
-6
25
25
6
24
9
14
20
7
11
Y
Y
ble for a 10% discount
ue Date pay a $100 late
Adjusted Amt
Book Sales by Author
Author ID
1006
1021
1024
1035
1042
1045
1048
1066
1111
1142
1158
1162
1179
1185
1198
1206
1210
1222
1230
1238
1246
1259
1262
1289
1298
1325
1326
1327
1328
1329
1339
1362
1372
1376
1387
1390
1414
1416
1428
1451
1482
1487
1495
1510
1512
1513
1514
1534
1535
1546
1594
1612
Contract Date Years Employed
5/5/2010
1/9/1900
8/6/2004
1/14/1900
9/21/2008
1/10/1900
1/20/2011
1/8/1900
4/2/2001
1/18/1900
6/24/2005
1/13/1900
7/19/2011
1/7/1900
12/19/2002
1/16/1900
11/8/2000
1/18/1900
12/21/2010
1/8/1900
3/3/2011
1/8/1900
1/17/2010
1/9/1900
2/14/2008
1/11/1900
4/29/2000
1/19/1900
9/28/2000
1/18/1900
9/16/2010
1/8/1900
6/1/2002
1/16/1900
10/22/2008
1/10/1900
6/16/2003
1/15/1900
4/15/2010
1/9/1900
12/24/2006
1/12/1900
8/23/2010
1/8/1900
10/7/2010
1/8/1900
12/13/2007
1/11/1900
9/28/2000
1/18/1900
3/9/2011
1/8/1900
6/11/2004
1/14/1900
3/16/2009
1/10/1900
10/10/2011
1/7/1900
5/17/2001
1/17/1900
11/8/2005
1/13/1900
7/1/2001
1/17/1900
5/28/2009
1/9/1900
10/24/2004
1/14/1900
5/2/2011
1/8/1900
11/27/2003
1/15/1900
5/6/2009
1/10/1900
12/27/2000
1/18/1900
6/29/2008
1/10/1900
9/29/2006
1/12/1900
8/7/2007
1/11/1900
5/12/2010
1/8/1900
6/10/2004
1/14/1900
8/9/2006
1/12/1900
1/15/2000
1/19/1900
10/4/2006
1/12/1900
10/24/2007
1/11/1900
9/28/2006
1/12/1900
3/19/2000
1/19/1900
6/14/2000
1/18/1900
7/17/2000
1/18/1900
3/4/2001
1/18/1900
Books in Print
15
14
2
11
15
7
4
13
11
1
8
1
11
3
1
1
12
4
5
12
1
2
4
8
14
6
12
1
5
1
8
3
13
6
11
1
8
11
9
13
9
6
13
10
8
15
14
15
6
7
14
13
Books Sold
316237
344944
255650
83399
16892
283588
43921
231823
140462
225503
214442
295514
43062
197522
496293
213766
365614
307741
380640
159593
269278
491853
188565
366628
115011
102016
142362
237358
66937
325510
56188
189282
326649
55193
304729
202863
69750
493759
16274
130656
459115
58989
251418
481840
446184
170817
341694
20501
282843
339998
387860
277507
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Sell Price
3.90
5.99
7.99
10.99
2.99
10.99
5.99
3.99
2.99
2.99
10.99
12.99
23.99
10.99
23.99
23.99
9.99
5.99
7.99
3.99
3.99
12.99
2.99
23.99
2.99
2.99
9.99
9.99
9.99
7.99
10.99
10.99
5.99
9.99
2.99
2.99
3.99
5.99
2.99
10.99
23.99
12.99
9.99
10.99
2.99
12.99
2.99
10.99
7.99
12.99
12.99
15.99
1619
1620
1622
1622
1647
1664
1676
1680
1693
1699
1701
1704
1747
1755
1755
1773
1773
1799
1803
1804
1816
1817
1818
1843
1862
1863
1871
1872
1902
1911
1914
1915
1925
1928
1929
1932
1939
1950
1951
1952
1958
prepared by
Nicky
10/20/2000
9/23/2009
2/25/2008
12/15/2006
6/22/2003
3/23/2008
6/22/2010
1/8/2007
6/7/2000
8/1/2000
6/23/2001
11/24/2011
3/2/2011
1/15/2012
9/29/2004
6/13/2004
12/27/2009
8/4/2002
7/1/2006
3/30/2003
11/29/2001
12/22/2005
5/5/2011
5/3/2003
11/21/2004
9/22/2007
10/1/2008
11/24/2000
10/18/2001
4/29/2007
7/26/2007
9/16/2003
2/8/2006
4/27/2009
12/3/2008
1/7/2012
12/27/2009
3/2/2008
9/26/2002
2/13/2000
6/8/2003
6/30/2018
1/18/1900
1/9/1900
1/11/1900
1/12/1900
1/15/1900
1/11/1900
1/8/1900
1/12/1900
1/18/1900
1/18/1900
1/17/1900
1/7/1900
1/8/1900
1/7/1900
1/14/1900
1/14/1900
1/9/1900
1/16/1900
1/12/1900
1/16/1900
1/17/1900
1/13/1900
1/8/1900
1/16/1900
1/14/1900
1/11/1900
1/10/1900
1/18/1900
1/17/1900
1/12/1900
1/11/1900
1/15/1900
1/13/1900
1/10/1900
1/10/1900
1/7/1900
1/9/1900
1/11/1900
1/16/1900
1/19/1900
1/15/1900
7
4
10
5
9
8
6
15
3
1
12
8
5
5
1
7
12
10
13
14
12
7
8
11
12
14
8
7
9
11
14
3
14
1
9
12
8
5
13
8
12
459194
384719
69105
469732
357692
439448
222954
68935
79116
329506
207992
96684
70283
478985
433029
156940
494065
89636
113217
53003
346024
494672
192681
86193
404443
128447
454064
234903
424493
14412
35987
53061
74256
230678
9483
274491
283414
133993
292451
194145
490910
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
15.99
7.99
3.99
9.99
12.99
5.99
9.99
2.99
5.99
7.99
15.99
9.99
3.99
2.99
15.99
2.99
7.99
7.99
5.99
10.99
2.99
2.99
3.99
15.99
12.99
12.99
10.99
2.99
2.99
12.99
2.99
2.99
7.99
5.99
2.99
12.99
9.99
5.99
9.99
7.99
3.99
by Author
Commission Rate:
Income Earned
3%
Grades
Emp ID
1
2
3
4
5
6
7
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fran
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Results
84
57
68
71
63
75
43
=VLOOKUP(Value,Dat
a,Column,Range?)
Grade
0
40
50
60
70
80
Fail
E
D
C
B
A
Tax Rate
Emp ID
E1001
E1002
E1003
E1004
E1005
E1006
E1007
Salary
$18,201.00
$100,000.00
$68,000.00
$191,000.00
$92,000.00
$75,000.00
$94,300.00
Threshold
$18,200.00
$90,000.00
$37,000.00
$180,000.00
$90,000.00
$37,000.00
$90,000.00
Base
$0.00
$20,797.00
$3,572.00
$54,097.00
$20,797.00
$3,572.00
$20,797.00
Per Dollar
$0.19
$0.37
$0.33
$0.45
$0.37
$0.33
$0.37
Tax
$0.19
$24,497.00
$13,647.00
$59,047.00
$21,537.00
$15,922.00
$22,388.00
Threshold
Base
Per Dollar over
$0.00
$0.00
$0.00
$18,201.00
$0.00
$0.19
$37,001.00
$3,572.00
$0.33
$90,001.00
$20,797.00
$0.37
$180,001.00
$54,097.00
$0.45
Invoice Data
Invoice Number
10048315510008
10048315510017
10048315510026
10048315510035
10048315510044
10048315510047
10048315510053
10048315510062
10048315510063
10048315510065
10048315510069
10048315510071
10048315510080
10048315510104
10048315510115
10048315510124
10048315510133
10048315510137
10048315510142
Client Number
CR28344
CR26993
CR39614
CR55886
CR50569
CR12587
CR42419
CR53950
CR19690
CR20133
CR30611
CR44868
CR38427
CR17303
CR24073
CR41635
CR18901
CR25983
CR23755
Amount
$2,700.00
$697.27
$359.11
$784.44
$392.70
$549.00
$404.66
$366.23
$280.00
$1,942.00
$568.50
$688.79
$322.89
$1,095.00
$392.09
$610.37
$399.92
$1,007.00
$135.34
Invoice Date
2/8/1919
2/12/1919
2/15/1919
2/17/1919
3/7/1919
3/11/1919
2/1/2019
2/18/2019
2/21/2019
2/24/2019
3/2/2019
3/3/2019
3/4/2019
3/9/2019
3/10/2019
3/12/2019
3/17/2019
3/17/2019
3/19/2019
Payment Ref
Paid Date
Paid Amount
Still Owing
$2,700.00
$697.27
$359.11
$784.44
$392.70
$549.00
$404.66
$366.23
$280.00
$1,942.00
$568.50
$688.79
$322.89
$1,095.00
$392.09
$610.37
$399.92
$1,007.00
$135.34
Payment Data
Invoice Number
10048315510008
10048315510017
10048315510026
10048315510035
10048315510044
10048315510053
10048315510062
10048315510071
10048315510080
10048315510115
10048315510124
10048315510133
10048315510142
Payment Reference
BP43546008
BP43554017
BP43536026
BP43550035
BP43547044
BP43541053
BP43548062
BP43537071
BP43536080
BP43553115
BP43549124
BP43543133
BP43524142
Instruction
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
Date
Amount Time
3/22/2019 $2,700.00 12:45:38
3/30/2019 $497.27 21:25:20
3/12/2019 $359.11 22:11:17
3/26/2019 $784.44 20:16:32
3/23/2019 $392.70 21:23:49
3/17/2019 $404.66
8:10:54
3/24/2019 $366.23 14:03:00
3/13/2019 $688.79
3:45:04
3/12/2019 $322.89 12:18:28
3/29/2019 $392.09
7:48:27
3/25/2019 $610.37 10:26:20
3/19/2019 $399.92 14:44:19
2/28/2019 $135.34 20:36:00
Sales Record
Date
6/20/2013
6/20/2013
6/20/2013
6/20/2013
TOTAL:
Part Number
1243
1245
1249
1271
Product
Vendor
Unit Price
Units
3
1
2
10
Total
$0.00
$0.00
$0.00
$0.00
$0.00
Part Number
1251
1257
1269
1248
1260
1243
1252
1264
1250
1256
1261
1270
1247
1254
1265
1272
1268
1246
1253
1271
1273
1245
1262
1267
1266
1258
1263
1255
1244
1259
1249
Product
Mouse
Keyboard
Memory Board
Hard Drive
Hard Drive
Keyboard
Monitor
Monitor
System Unit
System Unit
Mouse
Keyboard
CD ROM Drive
Hard Drive
Memory Board
System Unit
Mouse
Memory Board
Memory Board
Memory Board
Memory Board
Monitor
Monitor
Monitor
Mouse
Keyboard
CD ROM Drive
Monitor
Mouse
CD ROM Drive
System Unit
Vendor
Ace
Circular
Circular
Circular
Circular
Diamond
Diamond
Diamond
Diamond
Diamond
Letter C
Letter C
Letter C
Letter C
Nickel
Nickel
Pile
Pile
Pile
Pile
Pile
Pile
Pile
Pile
Quick Parts
Quick Parts
Quick Parts
Quick Parts
Treetop
Treetop
Treetop
Units On-Hand
160
48
45
60
17
100
40
110
100
148
32
10
20
40
89
13
20
300
112
2
85
400
56
64
35
36
18
43
218
5
60
Unit Price
$19.00
$29.00
$59.00
$99.00
$99.00
$29.00
$129.00
$129.00
$199.00
$199.00
$19.00
$29.00
$39.00
$99.00
$59.00
$199.00
$19.00
$59.00
$59.00
$59.00
$59.00
$129.00
$129.00
$129.00
$19.00
$29.00
$39.00
$129.00
$19.00
$39.00
$199.00
Sales Summary
Year
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
TOTAL
Book Sales
55085
68534
81890
85825
90422
99282
101943
106210
110054
110943
111484
118299
118443
122586
123095
137633
135128
161817
1938673
Music Sales
67755
93206
113827
113289
120261
129067
139662
135949
161779
145335
150503
145508
145685
166717
155100
198192
189179
233016
2604030
Total
124841
163742
197720
201118
212688
230355
243612
244167
273842
258288
263998
265819
266141
291317
280210
337841
326324
396851
4578874
Sales 200
250000
200000
150000
100000
50000
0
2001 2002 2003 2004 2005 2006 2007
Book Sales
Sales 2001-2018
2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018
Book Sales
Music Sales
Excel Assignment 2019 Semester 1
Student Name:
Student Number:
TO PROCEED YOU MUST ENABLE MACROS AND ENTER YOUR STUDENT NUMBER WHEN PROMPTED
ASSIGNMENT OVERVIEW
The Blues Brothers guitar Company make three types of guitar which it sells to wholesalers.
The three models of guitar made are:
- Tenor Electric
- Bass Electric
- Bass Solid Body
The Company has used a traditional product costing system with direct labour hours as the only cost driver
overhead application, but has asked you to develop a full activity based costing method solution for prod
costing and profitablity measurement.
For full instructions please download the Instruction sheet.
PLEASE NOTE:
Do not use rounding functions unless specifically instructed
Do not change the values or structure of the workbook in any way, i.e. do not move/add/remove sheets, c
Only the contents of the light blue cells should be changed and these should ALL contain formulas or functi
Formulas in/using the Labour Costs, Inventory and BOM should work regardless of sort order, other workshe
Assignment 2019 Semester 1
MACROS AND ENTER YOUR STUDENT NUMBER WHEN PROMPTED
e three types of guitar which it sells to wholesalers.
oduct costing system with direct labour hours as the only cost driver for
ou to develop a full activity based costing method solution for product
e Instruction sheet.
ecifically instructed
f the workbook in any way, i.e. do not move/add/remove sheets, columns or rows.
should be changed and these should ALL contain formulas or functions
entory and BOM should work regardless of sort order, other worksheets remain fixed
M
M@QU@R1E2017
Customer Invoice
Product
E-Tec USB 2 Drive
Lenolo Tab 2
Reflux A4 paper White 500
Laser Jet Toner Cartridge Black
Price
$19.99
$196.98
$24.99
$194.00
Totals
Add
Subtract
Multiply
Divide
Exponent
+
*
/
^
Quantity
Sub Tot
Discount %
45
12
28
3
$899.55
$2,363.76
$699.72
$582.00
5%
10%
5%
5%
Discount Amt
Total
44.98
236.38
34.99
29.10
854.57
2127.38
664.73
552.90
4199.59
4199.59
=A1+A3
=sum(A1,A3)
Sales Ledger 2016
Name
Prentice
Chirillo
Wong
Baker
Richards
Hite
de Moret
McWilliams
Howard
Silvis
Morris
Lewis
Jefferson
Patrino
Trowns-Hale
Nguyen
Johnsen
OBrien
Tantalo
Parker
TOTALS
Q1
23100
22700
20900
15900
25000
24110
25600
22600
22700
23300
23800
54826
31200
50224
15500
30900
14815
24500
20900
15900
Q2
13100
24110
22600
22700
34000
54812
48752
58445
48648
24600
27700
59224
23100
17300
19000
24400
13100
25600
22600
22700
Q3
16800
48752
20140
17600
21000
15200
30300
16800
36855
21380
12600
24100
17700
15200
30300
16800
11580
22000
20140
17600
Q4
37422
39566
22185
12334
38443
28464
18443
20454
29430
42204
17790
23667
34210
27330
34482
29843
15590
38843
29466
39003
Total
90422
135128
85825
68534
118443
122586
123095
118299
137633
111484
81890
161817
106210
110054
99282
101943
55085
110943
93106
95203
508475
606491
432847
579169
2126982
Average
22605.5
33782
21456.25
17133.5
29610.75
30646.5
30773.75
29574.75
34408.25
27871
20472.5
40454.25
26552.5
27513.5
24820.5
25485.75
13771.25
27735.75
23276.5
23800.75
Max
37422
48752
22600
22700
38443
54812
48752
58445
48648
42204
27700
59224
34210
50224
34482
30900
15590
38843
29466
39003
Min Commission
13100
3616.88
22700
5405.12
20140
3433
12334
2741.36
21000
4737.72
15200
4903.44
18443
4923.8
16800
4731.96
22700
5505.32
21380
4459.36
12600
3275.6
23667
6472.68
17700
4248.4
15200
4402.16
15500
3971.28
16800
4077.72
11580
2203.4
22000
4437.72
20140
3724.24
15900
3808.12
85079.28
Commission rate:
4%
Summary Data 2016
Total Sales
Average Sales
Total Commission
2126982
$26,587.28
$85,079.28
Bonus
Emp ID
1
2
3
4
5
6
7
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fran
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Sales
$120,000.00
$70,000.00
$78,000.00
$45,000.00
$89,000.00
$88,000.00
$80,000.00
5%
Bonus
$6,000.00
$3,500.00
$3,900.00
$2,250.00
$4,450.00
$4,400.00
$4,000.00
Book Sales by Author
Author ID
1755
1932
1704
1328
1048
1818
1387
1325
1158
1747
1035
1142
1262
1206
1259
1676
1487
1006
1238
1162
1773
1939
1620
1372
1414
1928
1327
1929
1222
1871
1024
1428
1664
1950
1622
1179
1289
1514
1863
1482
1914
1911
1680
1246
1622
1513
1451
Contract Date Years Employed
1/15/2012
7.32
1/7/2012
7.34
11/24/2011
7.46
10/10/2011
7.58
7/19/2011
7.81
5/5/2011
8.01
5/2/2011
8.02
3/9/2011
8.17
3/3/2011
8.19
3/2/2011
8.19
1/20/2011
8.31
12/21/2010
8.39
10/7/2010
8.59
9/16/2010
8.65
8/23/2010
8.71
6/22/2010
8.88
5/12/2010
8.99
5/5/2010
9.01
4/15/2010
9.07
1/17/2010
9.31
12/27/2009
9.37
12/27/2009
9.37
9/23/2009
9.63
5/28/2009
9.95
5/6/2009
10.01
4/27/2009
10.04
3/16/2009
10.15
12/3/2008
10.44
10/22/2008
10.55
10/1/2008
10.61
9/21/2008
10.64
6/29/2008
10.86
3/23/2008
11.13
3/2/2008
11.19
2/25/2008
11.21
2/14/2008
11.24
12/13/2007
11.41
10/24/2007
11.54
9/22/2007
11.63
8/7/2007
11.76
7/26/2007
11.79
4/29/2007
12.03
1/8/2007
12.34
12/24/2006
12.38
12/15/2006
12.40
10/4/2006
12.60
9/29/2006
12.61
Books in Print
5
12
8
5
4
8
11
6
8
5
11
1
4
1
2
6
6
15
12
1
12
8
4
13
8
1
1
9
4
8
2
9
8
5
10
11
8
14
14
9
14
11
15
1
5
15
13
Books Sold
478985
274491
96684
66937
43921
192681
304729
102016
214442
70283
83399
225503
188565
213766
491853
222954
58989
316237
159593
295514
494065
283414
384719
326649
69750
230678
237358
9483
307741
454064
255650
16274
439448
133993
69105
43062
366628
341694
128447
459115
35987
14412
68935
269278
469732
170817
130656
1534
1510
1803
1925
1817
1339
1045
1862
1376
1755
1021
1773
1326
1495
1390
1915
1647
1230
1958
1843
1804
1066
1951
1799
1210
1816
1902
1362
1701
1329
1042
1612
1416
1872
1111
1619
1198
1298
1699
1594
1546
1693
1185
1535
1952
1512
2222
9/28/2006
8/9/2006
7/1/2006
2/8/2006
12/22/2005
11/8/2005
6/24/2005
11/21/2004
10/24/2004
9/29/2004
8/6/2004
6/13/2004
6/11/2004
6/10/2004
11/27/2003
9/16/2003
6/22/2003
6/16/2003
6/8/2003
5/3/2003
3/30/2003
12/19/2002
9/26/2002
8/4/2002
6/1/2002
11/29/2001
10/18/2001
7/1/2001
6/23/2001
5/17/2001
4/2/2001
3/4/2001
12/27/2000
11/24/2000
11/8/2000
10/20/2000
9/28/2000
9/28/2000
8/1/2000
7/17/2000
6/14/2000
6/7/2000
4/29/2000
3/19/2000
2/13/2000
1/15/2000
6/30/2018
12.62
12.75
12.86
13.26
13.38
13.51
13.88
14.47
14.54
14.61
14.76
14.91
14.91
14.92
15.45
15.65
15.88
15.90
15.92
16.02
16.11
16.39
16.62
16.77
16.94
17.45
17.56
17.86
17.88
17.98
18.11
18.18
18.37
18.46
18.51
18.56
18.62
18.62
18.78
18.81
18.91
18.93
19.03
19.14
19.24
19.32
0.86
15
10
13
14
7
8
7
12
6
1
14
7
12
13
1
3
9
5
12
11
14
13
13
10
12
12
9
3
12
1
15
13
11
7
11
7
1
14
1
14
7
3
3
6
8
8
5
20501
481840
113217
74256
494672
56188
283588
404443
55193
433029
344944
156940
142362
251418
202863
53061
357692
380640
490910
86193
53003
231823
292451
89636
365614
346024
424493
189282
207992
325510
16892
277507
493759
234903
140462
459194
496293
115011
329506
387860
339998
79116
197522
282843
194145
446184
555555
Sell Price
2.99
12.99
9.99
9.99
5.99
3.99
2.99
2.99
10.99
3.99
10.99
2.99
2.99
23.99
12.99
9.99
12.99
3.99
3.99
12.99
7.99
9.99
7.99
5.99
3.99
5.99
9.99
2.99
5.99
10.99
7.99
2.99
5.99
5.99
3.99
23.99
23.99
2.99
12.99
23.99
2.99
12.99
2.99
3.99
9.99
12.99
10.99
Income Earned
1432165.15
3565638.09
965873.16
668700.63
263086.79
768797.19
911139.71
305027.84
2356717.58
280429.17
916555.01
674253.97
563809.35
5128246.34
6389170.47
2227310.46
766267.11
1261785.63
636776.07
3838726.86
3947579.35
2831305.86
3073904.81
1956627.51
278302.5
1381761.22
2371206.42
28354.17
1843368.59
4990163.36
2042643.5
48659.26
2632293.52
802618.07
275728.95
1033057.38
8795405.72
1021665.06
1668526.53
11014168.85
107601.13
187211.88
206115.65
1074419.22
4692622.68
2218912.83
1435909.44
Total Income Earned:
Total Books Sold:
Average Sell Price:
Average No Books in Print:
First Contract Date:
Create Named
Ranges using Name
Box and Create
10.99
10.99
5.99
7.99
2.99
10.99
10.99
12.99
9.99
15.99
5.99
2.99
9.99
9.99
2.99
2.99
12.99
7.99
3.99
15.99
10.99
3.99
9.99
7.99
9.99
2.99
2.99
10.99
15.99
7.99
2.99
15.99
5.99
2.99
2.99
15.99
23.99
2.99
7.99
12.99
12.99
5.99
10.99
7.99
7.99
2.99
3.99
225305.99
5295421.6
678169.83
593305.44
1479069.28
617506.12
3116632.12
5253714.57
551378.07
6924133.71
2066214.56
469250.6
1422196.38
2511665.82
606560.37
158652.39
4646419.08
3041313.6
1958730.9
1378226.07
582502.97
924973.77
2921585.49
716191.64
3652483.86
1034611.76
1269234.07
2080209.18
3325792.08
2600824.9
50507.08
4437336.93
2957616.41
702359.97
419981.38
7342512.06
11906069.07
343882.89
2632752.94
5038301.4
4416574.02
473904.84
2170766.78
2259915.57
1551218.55
1334090.16
2216664.45
eate Named
nges using Name
x and Create
208237340.8
22769224
9.00
8.26
1/15/2000
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fran
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Gender
F
M
F
F
F
M
F
Department
IT
Marketing
Finance
IT
Finance
Finance
Marketing
Salary
$120,000.00
$70,000.00
$78,000.00
$45,000.00
$89,000.00
$88,000.00
$80,000.00
Number of Staff in Finance
Number of Staff who earn over 80K
3
4
Total Salaries in Finance
Total Salaries in IT
Total Salaries in Marketing
$
$
$
Female Salary Breakdow
Finance
167,000
165,000
80,000
Challenge: Change the SUMIFS to get total salaries for FEMALES in each department
male Salary Breakdown
Finance
IT
Marketing
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fred
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
Attendance
100
65
80
80
65
100
100
Results
84
57
68
71
63
75
52
Grade
Pass
Fail
Pass
Pass
Pass
Pass
Fail
60 - 100
0 - 59
Logical
Functions
=IF
=OR
Pass
Fail
Logical
Functions
=IF
=OR
Last
First
DECKER
BARRY
COLE
CHAFFEE
DONNELL
FERN
ELLIS
CHUNG
FLANDERS
CLARK
BOLLER
CAROL
CLARK
CLARKE
COMUNTZIS
Erica
Angela
Daniel
Adam
Susan
Elizabeth
Janet
Bob
Sabrina
John
Mary
Elizabeth
Anna
Joe
Mark
KPI
100000
120000
120000
100000
80000
120000
120000
120000
100000
100000
120000
150000
100000
120000
120000
Sales KPI Met Y/N
85736
135647
61085
137491
91386
136424
154849
143610
119338
176077
120440
119887
91684
136519
147034
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Bonus
8573.6
4069.41
6108.5
4124.73
2741.58
4092.72
4645.47
4308.3
3580.14
5282.31
3613.2
11988.7
9168.4
4095.57
4411.02
Bonus Rates
KPI Met
KPI Not Met
10%
3%
Invoice # Invoice Amt Invoice Date
30245
30246
30247
30248
30249
30250
30251
30252
30253
30254
30255
30256
30257
30258
30259
$1,460.00
$7,338.00
$3,910.00
$7,045.00
$4,733.00
$8,756.00
$8,282.00
$4,455.00
$816.00
$2,987.00
$8,717.00
$6,537.00
$4,758.00
$558.00
$1,649.00
12/7/2017
12/15/2017
12/16/2017
12/24/2017
12/24/2017
1/6/2018
1/13/2018
1/13/2018
1/17/2018
1/26/2018
1/26/2018
1/27/2018
1/30/2018
1/31/2018
2/2/2018
Due Date
Paid Date
12/21/2017
12/29/2017
12/30/2017
1/7/2018
1/7/2018
1/20/2018
1/27/2018
1/27/2018
1/31/2018
2/9/2018
2/9/2018
2/10/2018
2/13/2018
2/14/2018
2/16/2018
12/8/2017
12/17/2017
1/5/2018
1/5/2018
1/25/2018
1/14/2018
2/21/2018
2/21/2018
2/6/2018
3/5/2018
2/18/2018
2/24/2018
3/5/2018
2/21/2018
2/27/2018
Clients who pay before the due Date are elligible for a 10% discount
Clients who pay more than 14 days after the Due Date pay a $100 late
penalty
Days After Extension Early Discount
-13
-12
6
-2
18
-6
25
25
6
24
9
14
20
7
11
Y
Y
ble for a 10% discount
ue Date pay a $100 late
146
733.8
0
704.5
0
875.6
0
0
0
0
0
0
0
0
0
Adjusted Amt
$1,314.00
$6,604.20
$4,010.00
$6,340.50
$4,833.00
$7,880.40
$8,282.00
$4,555.00
$916.00
$3,087.00
$8,817.00
$6,637.00
$4,758.00
$658.00
$1,749.00
Book Sales by Author
Author ID
1006
1021
1024
1035
1042
1045
1048
1066
1111
1142
1158
1162
1179
1185
1198
1206
1210
1222
1230
1238
1246
1259
1262
1289
1298
1325
1326
1327
1328
1329
1339
1362
1372
1376
1387
1390
1414
1416
1428
1451
1482
1487
1495
1510
1512
1513
1514
1534
1535
1546
1594
1612
Contract Date
5/5/2010
8/6/2004
9/21/2008
1/20/2011
4/2/2001
6/24/2005
7/19/2011
12/19/2002
11/8/2000
12/21/2010
3/3/2011
1/17/2010
2/14/2008
4/29/2000
9/28/2000
9/16/2010
6/1/2002
10/22/2008
6/16/2003
4/15/2010
12/24/2006
8/23/2010
10/7/2010
12/13/2007
9/28/2000
3/9/2011
6/11/2004
3/16/2009
10/10/2011
5/17/2001
11/8/2005
7/1/2001
5/28/2009
10/24/2004
5/2/2011
11/27/2003
5/6/2009
12/27/2000
6/29/2008
9/29/2006
8/7/2007
5/12/2010
6/10/2004
8/9/2006
1/15/2000
10/4/2006
10/24/2007
9/28/2006
3/19/2000
6/14/2000
7/17/2000
3/4/2001
Years Employed
1/9/1900
1/14/1900
1/10/1900
1/8/1900
1/18/1900
1/13/1900
1/7/1900
1/16/1900
1/18/1900
1/8/1900
1/8/1900
1/9/1900
1/11/1900
1/19/1900
1/18/1900
1/8/1900
1/16/1900
1/10/1900
1/15/1900
1/9/1900
1/12/1900
1/8/1900
1/8/1900
1/11/1900
1/18/1900
1/8/1900
1/14/1900
1/10/1900
1/7/1900
1/17/1900
1/13/1900
1/17/1900
1/9/1900
1/14/1900
1/8/1900
1/15/1900
1/10/1900
1/18/1900
1/10/1900
1/12/1900
1/11/1900
1/8/1900
1/14/1900
1/12/1900
1/19/1900
1/12/1900
1/11/1900
1/12/1900
1/19/1900
1/18/1900
1/18/1900
1/18/1900
Books in Print
15
14
2
11
15
7
4
13
11
1
8
1
11
3
1
1
12
4
5
12
1
2
4
8
14
6
12
1
5
1
8
3
13
6
11
1
8
11
9
13
9
6
13
10
8
15
14
15
6
7
14
13
Books Sold
316237
344944
255650
83399
16892
283588
43921
231823
140462
225503
214442
295514
43062
197522
496293
213766
365614
307741
380640
159593
269278
491853
188565
366628
115011
102016
142362
237358
66937
325510
56188
189282
326649
55193
304729
202863
69750
493759
16274
130656
459115
58989
251418
481840
446184
170817
341694
20501
282843
339998
387860
277507
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Sell Price
3.90
5.99
7.99
10.99
2.99
10.99
5.99
3.99
2.99
2.99
10.99
12.99
23.99
10.99
23.99
23.99
9.99
5.99
7.99
3.99
3.99
12.99
2.99
23.99
2.99
2.99
9.99
9.99
9.99
7.99
10.99
10.99
5.99
9.99
2.99
2.99
3.99
5.99
2.99
10.99
23.99
12.99
9.99
10.99
2.99
12.99
2.99
10.99
7.99
12.99
12.99
15.99
1619
1620
1622
1622
1647
1664
1676
1680
1693
1699
1701
1704
1747
1755
1755
1773
1773
1799
1803
1804
1816
1817
1818
1843
1862
1863
1871
1872
1902
1911
1914
1915
1925
1928
1929
1932
1939
1950
1951
1952
1958
prepared by
Nicky
10/20/2000
9/23/2009
2/25/2008
12/15/2006
6/22/2003
3/23/2008
6/22/2010
1/8/2007
6/7/2000
8/1/2000
6/23/2001
11/24/2011
3/2/2011
1/15/2012
9/29/2004
6/13/2004
12/27/2009
8/4/2002
7/1/2006
3/30/2003
11/29/2001
12/22/2005
5/5/2011
5/3/2003
11/21/2004
9/22/2007
10/1/2008
11/24/2000
10/18/2001
4/29/2007
7/26/2007
9/16/2003
2/8/2006
4/27/2009
12/3/2008
1/7/2012
12/27/2009
3/2/2008
9/26/2002
2/13/2000
6/8/2003
6/30/2018
1/18/1900
1/9/1900
1/11/1900
1/12/1900
1/15/1900
1/11/1900
1/8/1900
1/12/1900
1/18/1900
1/18/1900
1/17/1900
1/7/1900
1/8/1900
1/7/1900
1/14/1900
1/14/1900
1/9/1900
1/16/1900
1/12/1900
1/16/1900
1/17/1900
1/13/1900
1/8/1900
1/16/1900
1/14/1900
1/11/1900
1/10/1900
1/18/1900
1/17/1900
1/12/1900
1/11/1900
1/15/1900
1/13/1900
1/10/1900
1/10/1900
1/7/1900
1/9/1900
1/11/1900
1/16/1900
1/19/1900
1/15/1900
7
4
10
5
9
8
6
15
3
1
12
8
5
5
1
7
12
10
13
14
12
7
8
11
12
14
8
7
9
11
14
3
14
1
9
12
8
5
13
8
12
459194
384719
69105
469732
357692
439448
222954
68935
79116
329506
207992
96684
70283
478985
433029
156940
494065
89636
113217
53003
346024
494672
192681
86193
404443
128447
454064
234903
424493
14412
35987
53061
74256
230678
9483
274491
283414
133993
292451
194145
490910
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
15.99
7.99
3.99
9.99
12.99
5.99
9.99
2.99
5.99
7.99
15.99
9.99
3.99
2.99
15.99
2.99
7.99
7.99
5.99
10.99
2.99
2.99
3.99
15.99
12.99
12.99
10.99
2.99
2.99
12.99
2.99
2.99
7.99
5.99
2.99
12.99
9.99
5.99
9.99
7.99
3.99
y Author
Commission Rate:
Income Earned
1233324.3
2066214.56
2042643.5
916555.01
50507.08
3116632.12
263086.79
924973.77
419981.38
674253.97
2356717.58
3838726.86
1033057.38
2170766.78
11906069.07
5128246.34
3652483.86
1843368.59
3041313.6
636776.07
1074419.22
6389170.47
563809.35
8795405.72
343882.89
305027.84
1422196.38
2371206.42
668700.63
2600824.9
617506.12
2080209.18
1956627.51
551378.07
911139.71
606560.37
278302.5
2957616.41
48659.26
1435909.44
11014168.85
766267.11
2511665.82
5295421.6
1334090.16
2218912.83
1021665.06
225305.99
2259915.57
4416574.02
5038301.4
4437336.93
Commission
36999.729
61986.4368
61279.305
27496.6503
1515.2124
93498.9636
7892.6037
27749.2131
12599.4414
20227.6191
70701.5274
115161.8058
30991.7214
65123.0034
357182.0721
153847.3902
109574.5158
55301.0577
91239.408
19103.2821
32232.5766
191675.1141
16914.2805
263862.1716
10316.4867
9150.8352
42665.8914
71136.1926
20061.0189
78024.747
18525.1836
62406.2754
58698.8253
16541.3421
27334.1913
18196.8111
8349.075
88728.4923
1459.7778
43077.2832
330425.0655
22988.0133
75349.9746
158862.648
40022.7048
66567.3849
30649.9518
6759.1797
67797.4671
132497.2206
151149.042
133120.1079
3%
7342512.06
3073904.81
275728.95
4692622.68
4646419.08
2632293.52
2227310.46
206115.65
473904.84
2632752.94
3325792.08
965873.16
280429.17
1432165.15
6924133.71
469250.6
3947579.35
716191.64
678169.83
582502.97
1034611.76
1479069.28
768797.19
1378226.07
5253714.57
1668526.53
4990163.36
702359.97
1269234.07
187211.88
107601.13
158652.39
593305.44
1381761.22
28354.17
3565638.09
2831305.86
802618.07
2921585.49
1551218.55
1958730.9
220275.3618
92217.1443
8271.8685
140778.6804
139392.5724
78968.8056
66819.3138
6183.4695
14217.1452
78982.5882
99773.7624
28976.1948
8412.8751
42964.9545
207724.0113
14077.518
118427.3805
21485.7492
20345.0949
17475.0891
31038.3528
44372.0784
23063.9157
41346.7821
157611.4371
50055.7959
149704.9008
21070.7991
38077.0221
5616.3564
3228.0339
4759.5717
17799.1632
41452.8366
850.6251
106969.1427
84939.1758
24078.5421
87647.5647
46536.5565
58761.927
Grades
Emp ID
1
2
3
4
5
6
7
First Name
Julie
Sam
Andrea
Annabelle
Cindy
Andy
Fran
Surname
Fennell
Viney
Ball
Lewis
Thompson
Smith
Carpenter
=VLOOKUP(Value,D
ata,Column,Range
Results
84
57
68
71
63
75
43
Grade
A
D
C
B
C
B
E
0
40
50
60
70
80
Fail
E
D
C
B
A
Tax Rate
Emp ID
E1001
E1002
E1003
E1004
E1005
E1006
E1007
Salary
$18,201.00
$100,000.00
$68,000.00
$191,000.00
$92,000.00
$75,000.00
$94,300.00
Threshold
$18,200.00
$90,000.00
$37,000.00
$180,000.00
$90,000.00
$37,000.00
$90,000.00
Base
$0.00
$20,797.00
$3,572.00
$54,097.00
$20,797.00
$3,572.00
$20,797.00
Per Dollar
$0.19
$0.37
$0.33
$0.45
$0.37
$0.33
$0.37
Tax
$0.19
$24,497.00
$13,647.00
$59,047.00
$21,537.00
$15,922.00
$22,388.00
Threshold
Base
Per Dollar over
$0.00
$0.00
$0.00
$18,201.00
$0.00
$0.19
$37,001.00
$3,572.00
$0.33
$90,001.00
$20,797.00
$0.37
$180,001.00
$54,097.00
$0.45
Invoice Data
Invoice Number
10048315510008
10048315510017
10048315510026
10048315510035
10048315510044
10048315510047
10048315510053
10048315510062
10048315510063
10048315510065
10048315510069
10048315510071
10048315510080
10048315510104
10048315510115
10048315510124
10048315510133
10048315510137
10048315510142
Client Number
CR28344
CR26993
CR39614
CR55886
CR50569
CR12587
CR42419
CR53950
CR19690
CR20133
CR30611
CR44868
CR38427
CR17303
CR24073
CR41635
CR18901
CR25983
CR23755
Amount
$2,700.00
$697.27
$359.11
$784.44
$392.70
$549.00
$404.66
$366.23
$280.00
$1,942.00
$568.50
$688.79
$322.89
$1,095.00
$392.09
$610.37
$399.92
$1,007.00
$135.34
Invoice Date
2/8/1919
2/12/1919
2/15/1919
2/17/1919
3/7/1919
3/11/1919
2/1/2019
2/18/2019
2/21/2019
2/24/2019
3/2/2019
3/3/2019
3/4/2019
3/9/2019
3/10/2019
3/12/2019
3/17/2019
3/17/2019
3/19/2019
Payment Ref
BP43546008
BP43554017
BP43536026
BP43550035
BP43547044
#N/A
BP43541053
BP43548062
#N/A
#N/A
#N/A
BP43537071
BP43536080
#N/A
BP43553115
BP43549124
BP43543133
#N/A
BP43524142
Paid Date
Paid Amount
3/22/2019
$2,700.00
3/30/2019
$497.27
3/12/2019
$359.11
3/26/2019
$784.44
3/23/2019
$392.70
#N/A
$0.00
3/17/2019
$404.66
3/24/2019
$366.23
#N/A
$0.00
#N/A
$0.00
#N/A
$0.00
3/13/2019
$688.79
3/12/2019
$322.89
#N/A
$0.00
3/29/2019
$392.09
3/25/2019
$610.37
3/19/2019
$399.92
#N/A
$0.00
2/28/2019
$135.34
Still Owing
$0.00
$200.00
$0.00
$0.00
$0.00
$549.00
$0.00
$0.00
$280.00
$1,942.00
$568.50
$0.00
$0.00
$1,095.00
$0.00
$0.00
$0.00
$1,007.00
$0.00
Payment Data
Invoice Number
10048315510008
10048315510017
10048315510026
10048315510035
10048315510044
10048315510053
10048315510062
10048315510071
10048315510080
10048315510115
10048315510124
10048315510133
10048315510142
Payment Reference
BP43546008
BP43554017
BP43536026
BP43550035
BP43547044
BP43541053
BP43548062
BP43537071
BP43536080
BP43553115
BP43549124
BP43543133
BP43524142
Instruction
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
PAYMENT
Date
Amount Time
3/22/2019 $2,700.00 12:45:38
3/30/2019
$497.27 21:25:20
3/12/2019
$359.11 22:11:17
3/26/2019
$784.44 20:16:32
3/23/2019
$392.70 21:23:49
3/17/2019
$404.66
8:10:54
3/24/2019
$366.23 14:03:00
3/13/2019
$688.79
3:45:04
3/12/2019
$322.89 12:18:28
3/29/2019
$392.09
7:48:27
3/25/2019
$610.37 10:26:20
3/19/2019
$399.92 14:44:19
2/28/2019
$135.34 20:36:00
Sales Record
Date
6/20/2013
6/20/2013
6/20/2013
6/20/2013
TOTAL:
Part Number
1243
1245
1249
1271
Product
Keyboard
Monitor
System Unit
Memory Board
Vendor
Diamond
Pile
Treetop
Pile
Unit Price
$29.00
$129.00
$199.00
$59.00
Units
3
1
2
10
Total
$87.00
$129.00
$398.00
$590.00
$1,204.00
Part Number
1251
1257
1269
1248
1260
1243
1252
1264
1250
1256
1261
1270
1247
1254
1265
1272
1268
1246
1253
1271
1273
1245
1262
1267
1266
1258
1263
1255
1244
1259
1249
Product
Mouse
Keyboard
Memory Board
Hard Drive
Hard Drive
Keyboard
Monitor
Monitor
System Unit
System Unit
Mouse
Keyboard
CD ROM Drive
Hard Drive
Memory Board
System Unit
Mouse
Memory Board
Memory Board
Memory Board
Memory Board
Monitor
Monitor
Monitor
Mouse
Keyboard
CD ROM Drive
Monitor
Mouse
CD ROM Drive
System Unit
Vendor
Ace
Circular
Circular
Circular
Circular
Diamond
Diamond
Diamond
Diamond
Diamond
Letter C
Letter C
Letter C
Letter C
Nickel
Nickel
Pile
Pile
Pile
Pile
Pile
Pile
Pile
Pile
Quick Parts
Quick Parts
Quick Parts
Quick Parts
Treetop
Treetop
Treetop
Units On-Hand
160
48
45
60
17
100
40
110
100
148
32
10
20
40
89
13
20
300
112
2
85
400
56
64
35
36
18
43
218
5
60
Unit Price
$19.00
$29.00
$59.00
$99.00
$99.00
$29.00
$129.00
$129.00
$199.00
$199.00
$19.00
$29.00
$39.00
$99.00
$59.00
$199.00
$19.00
$59.00
$59.00
$59.00
$59.00
$129.00
$129.00
$129.00
$19.00
$29.00
$39.00
$129.00
$19.00
$39.00
$199.00
Sales Summary
Year
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
TOTAL
Book Sales
55085
68534
81890
85825
90422
99282
101943
106210
110054
110943
111484
118299
118443
122586
123095
137633
135128
161817
1938673
Music Sales
67755
93206
113827
113289
120261
129067
139662
135949
161779
145335
150503
145508
145685
166717
155100
198192
189179
233016
2604030
Total
124841
163742
197720
201118
212688
230355
243612
244167
273842
258288
263998
265819
266141
291317
280210
337841
326324
396851
4578874
250000
200000
150000
100000
50000
0
2001 2002 2003 2004
Sales 2001-2018
2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018
Book Sales
Music Sales
Purchase answer to see full
attachment