Untitled Spreadsheet
Instructions
Group Number 5 Excel Assignment - Trimester 1-2021
The instructions below explain what calculation is required for each cell.
All cells with a blue background require a calculation (except for the ledger account names).
Do NOT just type in the answer as this will get 0 marks. Where a sensible formula has been used
and the answer is co
ect, full marks will be awarded, where a good attempt has been made but the
answer is inco
ect some marks may still be awarded (at the markers discretion).
Please follow all instructions and check your work carefully. Allocated Mark
Student Name Student ID Contribution % Mark
XXXXXXXXXX Sweta GURUNG Section A - The following questions are to be answered on the Direct Material Sheet
XXXXXXXXXX Rajeeb SAPKOTA 1 Calculate the Cost of material per unit of job (Multiply Cost per unit of material to Material needed per unit of Job for each job) 2.5
XXXXXXXXXX Khusbu TAMANG 2 Calculate Total Job cost (Multiply number of units to total DM cost per unit of each job) 1
3 In row 18 Calculate the Total Direct Material Cost 0.5
Section B - The following questions are to be answered on the Direct Labour Sheet
Check group member names and student numbers and add contribution percentages 1 Calculate Labour cost before Super- column E (Multiply Working hours to Direct labour rate per hour)Consider absolute referencing 1
IMPORTANT: 2 Calculate Super cost column F (Multiply super rate to labour cost before super)- Consider applying absolute referencing-Use "Round function to have no decimal points) 1
This file should be opened in "EXCEL" only. 3 Calculate Total Direct Labour cost (Column G) 1
4 Using "SUMIFS" function, calculate each job Direct Labour Cost (Column K) 2.5
5 Cell K11: calculate Total Direct Labour Cost 1
6 Using "COUNTIFS" function, calculate each Number of labours involved (Column L) 2.5
Section C - The following questions are to be answered on the MOH Sheet
1 Calculate Cost per unit of activity driver (dividing Budgeted Activity Cost by Budgeted Activity Driver) Cells : F6 to F9 4
2 Calculate "Allocated MOH" ( Multiply Actual activity by Cost per unit of activity drivers from the last step) 2
3 Calculate "Total allocated MOH per each job" 1
The students in the wrong group or with no contribution will receive ZERO mark So this is your own responsibility to check your group number 4 Calculate "Total allocated MOH" Cell: K19 1
Section D - The following questions are to be answered on the Production Report Sheet
1 Calculate "Total Direct Material cost per each Job" by linking the cells to "Section A-Direct Material" sheet- Column C 1
2 Calculate "Total Direct Labour cost per each Job" by linking the cells to "Section B-Direct Labour" sheet- Column D 1
3 Calculate "Total Allocated MOH per each Job" by linking the cells to "Section C-MOH" sheet- Column E 1
4 Column F: Find total cost of Each Job 1
Follow the steps and complete Section A to E. The cells with the this cell's colour are required to be completed. All the rest of cells are locked. 5 Column G: Find cost of Each Job per unit (Divide Total cost of each job by number of units per job)- Use "Round function to have 2 decimal points) 1
6 Row 9: Calculate Total DM, DL and MOH in production 3
7 Cell F11: Find Total Production cost 2
Section E - The following questions are to be answered on the Pricing and Margin Sheet
1 Populate "Job Cost per unit" , Column C by linking cells to "Production Report" Sheet 1
2 Calculate "Selling Price" for each Job by applying the mark up to the product cost, column D-Use "Round function to have 2 decimal points) 2.5
3 Calculate "Gross Margin per unit", column E (deducting selling price and job cost per unit 1
4 Calculate "Selling and Admin Cost" column F (Multiply Job cost per unit to the % provided in Cell G2)- consider absolute referencing-Use "Round function to have 2 decimal points) 2.5
5 Calculate "Profit per unit of the job", column G ( Deducting Gross margin and selling &Admin expenses) 2
TOTAL 40 0
Student details
ID number Name Group number Lecture Email Name ID number Group number ERROR:#NAME? 1 Charisse Mae Alyssa RESTAURO XXXXXXXXXX
XXXXXXXXXX Muhammad HAMZA 1 LA XXXXXXXXXX Muhammad HAMZA XXXXXXXXXX ERROR:#NAME?
XXXXXXXXXX Charisse Mae Alyssa RESTAURO 1 LA XXXXXXXXXX Charisse Mae Alyssa RESTAURO XXXXXXXXXX ERROR:#NAME?
XXXXXXXXXX Kaushal KARKI 2 LA XXXXXXXXXX Kaushal KARKI XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Shiva KARKI 2 LA XXXXXXXXXX Shiva KARKI XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Hong Duc TRAN 3 LA XXXXXXXXXX Hong Duc TRAN XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Sanjaya GAUTAM 3 LA XXXXXXXXXX Sanjaya GAUTAM XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Roberta SILVA QUAGLIO 3 LA XXXXXXXXXX Roberta SILVA QUAGLIO XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Muhammad ALI 4 LA XXXXXXXXXX Muhammad ALI XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Muhammad HASEEB 4 LA XXXXXXXXXX Muhammad HASEEB XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Aditya Kumar SINGH 4 LA XXXXXXXXXX Aditya Kumar SINGH XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Sweta GURUNG 5 LA XXXXXXXXXX Sweta GURUNG XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Rajeeb SAPKOTA 5 LA XXXXXXXXXX Rajeeb SAPKOTA XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
XXXXXXXXXX Khusbu TAMANG 5 LA XXXXXXXXXX Khusbu TAMANG XXXXXXXXXX ERROR:#NAME? ERROR:#NAME?
LA @students.koi.edu.au 0 0 ERROR:#NAME?
LA @students.koi.edu.au 0 0 ERROR:#NAME?
LA @students.koi.edu.au 0 0 ERROR:#NAME?
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LA @students.koi.edu.au 0 0
LB @students.koi.edu.au 0 0
LB @students.koi.edu.au 0 0
LB @students.koi.edu.au 0 0
LB @students.koi.edu.au 0 0
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
XXXXXXXXXX LB XXXXXXXXXX 0 XXXXXXXXXX
B @students.koi.edu.au
B @students.koi.edu.au
B @students.koi.edu.au
B @students.koi.edu.au
Section A- Direct Material
Material Code Unit Cost per unit of material Job Number Number of units Material Code Unit Cost per unit of material Job Number Number of units
PVC Kilogram $22.27 SYD100 17,480 PVC Kilogram $6.10 SYD100 1520
MET Kilogram $16.06 SYD202 11,742 MET Kilogram $4.40 SYD202 1021
WOD Meter $31.76 SYD206 8,326 WOD Meter $9 SYD206 724
MEL300 17,710 MEL300 1540
MEL208 4,082 MEL208 355
Job Number Material needed per unit of Job Cost of material per unit of job Total Job cost Job Number Material needed per unit of Job Cost of material per unit of job Total Job cost
PVC (Kg) MET (Kg) WOD (Meter) PVC (Kg) MET (Kg) WOD (Meter) Total PVC (Kg) MET (Kg) WOD (Meter) PVC (Kg) MET (Kg) WOD (Meter) Total
SYD100 25 49 15 SYD100 5 7 2 $111.33 $112.42 $63.51 $287.26 $436,627.60
SYD202 34 84 30 SYD202 7 12 4 $155.86 $192.72 $127.02 $475.60 $485,582.50
SYD206 49 42 11 SYD206 10 6 1.5 $222.65 $96.36 $47.63 $366.64 $265,449.17
MEL300 39 63 38 MEL300 8 9 5 $178.12 $144.54 $158.78 $481.44 $741,409.90
MEL208 15 56 53 MEL208 3 8 7 $66.80 $128.48 $222.29 $417.56 $148,233.80
Total Direct Material Cost: Total Direct Material Cost: $2,077,302.97
Section B-Direct Labou
%Super 9%
Labour rate per hour $28
Job Number Employee ID Working hours Labour cost before Super Super Total Direct Labour cost Direct Labour cost Number of labours involved Working hours
SYD100 10026 728 SYD100 28
SYD202 10027 1560 SYD202 60
SYD206 10030 1638 SYD206 63
MEL300 10032 2132 MEL300 82
MEL300 10033 936 MEL208 36
SYD100 10034 754 Total DL cost: 29
SYD206 10035 1118 43
SYD202 10036 520 20
SYD206 10037 1638 63
SYD100 10038 2028 78
MEL208 10026 910 35
SYD202 10027 1014 39
SYD100 10030 2340 90
MEL300 10032 2574 99
MEL300 10033 2288 88
MEL208 10034 2418 93
SYD202 10035 2210 85
MEL300 10036 2132 82
SYD202 10037 936 36
MEL208 10038 2548 98
SYD100 10026 2210 85
SYD206 10027 1404 54
MEL208 10030 2236 86
MEL300 10032 1820 70
SYD202 10033 1950 75
SYD206 10034 2184 84
MEL208 10035 2392 92
SYD202 10036 2392 92
MEL300 10037 1196 46
SYD100 10038 2262 87
SYD206 10026 2548 98
MEL300 10027 1404 54
MEL300 10030 1222 47
SYD100 10032 1196 46
SYD202 10033 2366 91
MEL208 10030 1352 52
SYD202 10032 2184 84
MEL300 10033 1066 41
SYD202 10034 936 36
MEL208 10035 2132 82
Section C-MOH
Activity Activity driver Budgeted Activity Cost Budgeted Activity Driver Cost per unit of activity driver Activity Activity driver Budgeted Activity Cost Budgeted Activity Driver Cost per unit of activity drive
Machine Set up Number of setup $600,000 50,000 Machine Set up Number of setup $60,000 2500 24
Material Handling Number of material removal $500,000 200,000 Material Handling Number of material removal $50,000 10000 5
Inspection Number of inspections $350,000 100,000 Inspection Number of inspections $35,000 5000 7
Product design Design hours $280,000 8,000 Product design Design hours $28,000 400 70
Actual usage Allocated MOH Actual usage
Number of setup Number of material removal Number of inspections Design hours Number of setup Number of material removal Number of inspections Design hours Total allocated MOH- per Job Number of setup Number of material removal Number of inspections Design hours
SYD100 7,500 52,500 21,000 700 SYD100 600 2500 1200 100
SYD202 10,000 35,700 16,800 350 SYD202 800 1700 960 50
SYD206 5,625 65,100 10,500 840 SYD206 450 3100 600 120
MEL300 4,375 44,100 31,500 560 MEL300 350 2100 1800 80
MEL208 5,000 16,800 8,400 420 MEL208 400 800 480 60
Total Allocated MOH:
Section D- Production Report
Total Direct Material Total Direct Labour Allocated MOH Total Job Cost Job Cost per unit
SYD100
SYD202
SYD206
MEL300
MEL208
Total
Total Production Cost:
Section E-Pricing and Margin
%Mark up: 28%
%Selling and Admin Expenses per each dollar of unit job cost: 9%
Job Cost per unit Selling Price Gross Margin per unit Selling and Admin Cost Profit per unit of the jo
SYD100
SYD202
SYD206
MEL300
MEL208