excel assignment just need to fill the blanks
Untitled Spreadsheet Instructions Group Number5Excel 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 correct, full marks will be awarded, where a good attempt has been made but the answer is incorrect some marks may still be awarded (at the markers discretion). Please follow all instructions and check your work carefully.Allocated Mark Student Name Student IDContribution %Mark 12100567Sweta GURUNGSection A - The following questions are to be answered on the Direct Material Sheet 11700190Rajeeb SAPKOTA1Calculate 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 12001605Khusbu TAMANG2Calculate Total Job cost (Multiply number of units to total DM cost per unit of each job)1 3In row 18 Calculate the Total Direct Material Cost0.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 percentages1Calculate Labour cost before Super- column E (Multiply Working hours to Direct labour rate per hour)Consider absolute referencing1 IMPORTANT: 2Calculate 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.3Calculate Total Direct Labour cost (Column G)1 4Using "SUMIFS" function, calculate each job Direct Labour Cost (Column K)2.5 5Cell K11: calculate Total Direct Labour Cost1 6Using "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 1Calculate Cost per unit of activity driver (dividing Budgeted Activity Cost by Budgeted Activity Driver) Cells : F6 to F94 2Calculate "Allocated MOH" ( Multiply Actual activity by Cost per unit of activity drivers from the last step)2 3Calculate "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 number4Calculate "Total allocated MOH" Cell: K191 Section D - The following questions are to be answered on the Production Report Sheet 1Calculate "Total Direct Material cost per each Job" by linking the cells to "Section A-Direct Material" sheet- Column C1 2Calculate "Total Direct Labour cost per each Job" by linking the cells to "Section B-Direct Labour" sheet- Column D1 3Calculate "Total Allocated MOH per each Job" by linking the cells to "Section C-MOH" sheet- Column E1 4Column F: Find total cost of Each Job1 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.5Column 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 6Row 9: Calculate Total DM, DL and MOH in production3 7Cell F11: Find Total Production cost2 Section E - The following questions are to be answered on the Pricing and Margin Sheet 1Populate "Job Cost per unit" , Column C by linking cells to "Production Report" Sheet1 2Calculate "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 3Calculate "Gross Margin per unit", column E (deducting selling price and job cost per unit1 4Calculate "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 5Calculate "Profit per unit of the job", column G ( Deducting Gross margin and selling &Admin expenses)2 TOTAL400 Student details ID numberNameGroup numberLectureEmailNameID numberGroup number ERROR:#NAME?1Charisse Mae Alyssa RESTAURO12100354 12001879Muhammad
[email protected] HAMZA12001879ERROR:#NAME? 12100354Charisse Mae Alyssa
[email protected] Mae Alyssa RESTAURO12100354ERROR:#NAME? 12100977Kaushal
[email protected] KARKI12100977ERROR:#NAME?ERROR:#NAME? 12100856Shiva
[email protected] KARKI12100856ERROR:#NAME?ERROR:#NAME? 12200273Hong Duc
[email protected] Duc TRAN12200273ERROR:#NAME?ERROR:#NAME? 12000267Sanjaya
[email protected] GAUTAM12000267ERROR:#NAME?ERROR:#NAME? 12100803Roberta SILVA
[email protected] SILVA QUAGLIO12100803ERROR:#NAME?ERROR:#NAME? 12100985Muhammad
[email protected] ALI12100985ERROR:#NAME?ERROR:#NAME? 11902187Muhammad
[email protected] HASEEB11902187ERROR:#NAME?ERROR:#NAME? 12002075Aditya Kumar
[email protected] Kumar SINGH12002075ERROR:#NAME?ERROR:#NAME? 12100567Sweta
[email protected] GURUNG12100567ERROR:#NAME?ERROR:#NAME? 11700190Rajeeb
[email protected] SAPKOTA11700190ERROR:#NAME?ERROR:#NAME? 12001605Khusbu
[email protected] TAMANG12001605ERROR:#NAME?ERROR:#NAME?
[email protected]:#NAME?
[email protected]:#NAME?
[email protected]:#NAME?
[email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] Section A- Direct Material Material CodeUnitCost per unit of material Job NumberNumber of unitsMaterial CodeUnitCost per unit of material Job NumberNumber of units PVCKilogram$22.27SYD10017,480PVCKilogram$6.10SYD1001520 METKilogram$16.06SYD20211,742METKilogram$4.40SYD2021021 WODMeter$31.76SYD2068,326WODMeter$9SYD206724 MEL30017,710MEL3001540 MEL2084,082MEL208355 Job NumberMaterial needed per unit of JobCost of material per unit of jobTotal Job cost Job NumberMaterial needed per unit of JobCost of material per unit of jobTotal 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 SYD100254915SYD100572$111.33$112.42$63.51$287.26$436,627.60 SYD202348430SYD2027124$155.86$192.72$127.02$475.60$485,582.50 SYD206494211SYD2061061.5$222.65$96.36$47.63$366.64$265,449.17 MEL300396338MEL300895$178.12$144.54$158.78$481.44$741,409.90 MEL208155653MEL208387$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 Labour %Super9% Labour rate per hour$28 Job NumberEmployee IDWorking hoursLabour cost before SuperSuperTotal Direct Labour costDirect Labour costNumber of labours involved Working hours SYD10010026728SYD10028 SYD202100271560SYD20260 SYD206100301638SYD20663 MEL300100322132MEL30082 MEL30010033936MEL20836 SYD10010034754Total DL cost:29 SYD20610035111843 SYD2021003652020 SYD20610037163863 SYD10010038202878 MEL2081002691035 SYD20210027101439 SYD10010030234090 MEL30010032257499 MEL30010033228888 MEL20810034241893 SYD20210035221085 MEL30010036213282 SYD2021003793636 MEL20810038254898 SYD10010026221085 SYD20610027140454 MEL20810030223686 MEL30010032182070 SYD20210033195075 SYD20610034218484 MEL20810035239292 SYD20210036239292 MEL30010037119646 SYD10010038226287 SYD20610026254898 MEL30010027140454 MEL30010030122247 SYD10010032119646 SYD20210033236691 MEL20810030135252 SYD20210032218484 MEL30010033106641 SYD2021003493636 MEL20810035213282 Section C-MOH ActivityActivity driverBudgeted Activity CostBudgeted Activity DriverCost per unit of activity driverActivityActivity driverBudgeted Activity CostBudgeted Activity DriverCost per unit of activity driver Machine Set upNumber of setup$600,00050,000Machine Set upNumber of setup$60,000250024 Material HandlingNumber of material removal$500,000200,000Material HandlingNumber of material removal$50,000100005 InspectionNumber of inspections$350,000100,000InspectionNumber of inspections$35,00050007 Product designDesign hours$280,0008,000Product designDesign hours$28,00040070 Actual usageAllocated MOHActual usage Number of setupNumber of material removalNumber of inspectionsDesign hoursNumber of setupNumber of material removalNumber of inspectionsDesign hoursTotal allocated MOH- per JobNumber of setupNumber of material removalNumber of inspectionsDesign hours SYD1007,50052,50021,000700SYD10060025001200100 SYD20210,00035,70016,800350SYD202800170096050 SYD2065,62565,10010,500840SYD2064503100600120 MEL3004,37544,10031,500560MEL3003502100180080 MEL2085,00016,8008,400420MEL20840080048060 Total Allocated MOH: Section D- Production Report Total Direct MaterialTotal Direct LabourAllocated MOHTotal 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 unitSelling PriceGross Margin per unitSelling and Admin CostProfit per unit of the job SYD100 SYD202 SYD206 MEL300 MEL208