Please provide detailed explanation and formulas used to calculate the answers along with the excel sheet cell references:
NOTE: Incorporate the Unproductive/unbilled factor mentioned on the 'Actual Sheet' for calculating the Labour Commercial and Residential Variance
B C E F G Parameters - budget 1 2 Quarters 1 2 3 4 Year 4,160 2,900 1,260 5,060 3,530 1,530 4,460 3,110 1,350 5,360 3,740 1,620 19,040 13,280 5,760 $ 37 $ 48 $ 37 $ 48 $ 37 $ 48 $ 37 $ 48 $ 37 48 $ 23 23 23 23 0.25 0.25 0.25 3 4 Sales ( hours): 5 Budgeted total sales in hours 6 Budgeted commercial sales in hours 7 Budgeted residential sales in hours 8 Selling price ($/hr): 9 Selling price per hour (commercial) 10 Selling price per hour (residential) 11 12 Direct labour expense ( $/hr): 13 14 Cleaning Supply usage ( litre / labour hour): 15 Commercial 16 Residential 17 18 Cleaning Supply expense ($/litre)): 19 20 Fixed overhead: 21 Receptionist 22 Depreciation 23 Gas 24 Office Supplies 25 26 27 28 0.25 0.15 0.15 0.15 0.15 $ 5.50 $ 5.50 $ 5.50 $ 5.50 6,500 9,125 7,625 1,125 6,500 9,125 7,625 1,125 6,500 9,125 7,625 6,500 9,125 7,625 1,125 26,000 36,500 30,500 4,500 1,125 Parameters - actual Quarters 2 1 3 4 Year Sales ( hours): Actual total sales Actual sales - commercial Actual sales - residential Selling price ( $/hr): Actual selling price - commercial Actual sellling price - residential 4,020 2,810 1,210 5,220 3,650 1,570 4,120 2,880 1,240 5,320 3,720 1,600 18,680 13,060 5,620 $ $ $ 41 $ 47 $ 41 $ 47 $ 41 47 41 S 47 $ 41 47 U Direct labour expense ($/hr): 22 22 22 22 Actual Cleaning Supply usage ( litre / labour hour): Commercial Residential 0.22 0.11 0.22 0.11 0.22 0.11 0.22 0.11 Actual Cleaning Supply expense ($/litre)): 5 5 5 5 Actual Fixed overhead: Receptionist Depreciation Gas Office Supplies 6,010 9,125 7,300 990 5,770 9,125 7,940 950 5,915 9,125 7,450 975 6,325 9,125 7,275 1,215 24,020 36,500 29,965 4,130 21,482 Total no. of hours actually paid to employees Breakdown: Commercial Residential Actual hours worked Direct labour cost 3,232 1,392 4,623 101,706 4,198 1,806 6,003 132,066 3,312 1,426 4,738 104,236 4,278 1,840 6,118 134,596 15,019 6,463 21,482 472,604 Unproductive ( unbilled) factor 15% 15% 15% 15% 15% not be able to make any changes to its values. You are required to link data from this spreadsheet and complete the inputs in the spreadsheets above ( whose tabs are highlighted in yellow). 4.On the input data spreadsheet, cells K33:033 represent unproductive / unbilled factor. The percentage reflects hours over and above revenue generating hours. These are hours where re-work had to be done, but clients would not be billed for. This factor is to be included in the direct labour budget, but not in the revenue budget.Assume that there is no impact on direct materials on account of these hours. 5. Ensure that there are no plugged values in cells IF statements are used where appropriate A B C D E H L F G Labour - Commercial 1 2 3 Actual Hours - AH 4 Actual Rate -AR 5 Actual Hours - AH Standard Rate -SR Standard Hours - SH Standard Rate - SR 6 7 Rate Variance Efficiency Variance 8 Total Variance 9 10 11 Explanation for Efficiency variance: 12 13 14 Explanation for rate variance: 15 16 17 18 19 Labour - Residential 20 21 22. Actual Hours-AH Flexible Budget Actual HoureAH Var Analysis - Static and Flex Material variances Labour variances Standard Haus SEL Input D A B D E F G H K 19 20 Labour - Residential 21 22 Actual Hours - AH 23 Actual Rate -AR 24 Actual Hours - AH Standard Rate -SR Standard Hours - SH Standard Rate - SR 25 Rate Variance Efficiency Variance 26 27 28 Total Variance 29 30 31 Explanation for Efficiency variance: 32 33 34 Explanation for rate variance: 35 36 37 38 39 Total un Flexible Budget Var Analysis - Static and Flex Material variances Labour variances Input D B C E F G Parameters - budget 1 2 Quarters 1 2 3 4 Year 4,160 2,900 1,260 5,060 3,530 1,530 4,460 3,110 1,350 5,360 3,740 1,620 19,040 13,280 5,760 $ 37 $ 48 $ 37 $ 48 $ 37 $ 48 $ 37 $ 48 $ 37 48 $ 23 23 23 23 0.25 0.25 0.25 3 4 Sales ( hours): 5 Budgeted total sales in hours 6 Budgeted commercial sales in hours 7 Budgeted residential sales in hours 8 Selling price ($/hr): 9 Selling price per hour (commercial) 10 Selling price per hour (residential) 11 12 Direct labour expense ( $/hr): 13 14 Cleaning Supply usage ( litre / labour hour): 15 Commercial 16 Residential 17 18 Cleaning Supply expense ($/litre)): 19 20 Fixed overhead: 21 Receptionist 22 Depreciation 23 Gas 24 Office Supplies 25 26 27 28 0.25 0.15 0.15 0.15 0.15 $ 5.50 $ 5.50 $ 5.50 $ 5.50 6,500 9,125 7,625 1,125 6,500 9,125 7,625 1,125 6,500 9,125 7,625 6,500 9,125 7,625 1,125 26,000 36,500 30,500 4,500 1,125 Parameters - actual Quarters 2 1 3 4 Year Sales ( hours): Actual total sales Actual sales - commercial Actual sales - residential Selling price ( $/hr): Actual selling price - commercial Actual sellling price - residential 4,020 2,810 1,210 5,220 3,650 1,570 4,120 2,880 1,240 5,320 3,720 1,600 18,680 13,060 5,620 $ $ $ 41 $ 47 $ 41 $ 47 $ 41 47 41 S 47 $ 41 47 U Direct labour expense ($/hr): 22 22 22 22 Actual Cleaning Supply usage ( litre / labour hour): Commercial Residential 0.22 0.11 0.22 0.11 0.22 0.11 0.22 0.11 Actual Cleaning Supply expense ($/litre)): 5 5 5 5 Actual Fixed overhead: Receptionist Depreciation Gas Office Supplies 6,010 9,125 7,300 990 5,770 9,125 7,940 950 5,915 9,125 7,450 975 6,325 9,125 7,275 1,215 24,020 36,500 29,965 4,130 21,482 Total no. of hours actually paid to employees Breakdown: Commercial Residential Actual hours worked Direct labour cost 3,232 1,392 4,623 101,706 4,198 1,806 6,003 132,066 3,312 1,426 4,738 104,236 4,278 1,840 6,118 134,596 15,019 6,463 21,482 472,604 Unproductive ( unbilled) factor 15% 15% 15% 15% 15% not be able to make any changes to its values. You are required to link data from this spreadsheet and complete the inputs in the spreadsheets above ( whose tabs are highlighted in yellow). 4.On the input data spreadsheet, cells K33:033 represent unproductive / unbilled factor. The percentage reflects hours over and above revenue generating hours. These are hours where re-work had to be done, but clients would not be billed for. This factor is to be included in the direct labour budget, but not in the revenue budget.Assume that there is no impact on direct materials on account of these hours. 5. Ensure that there are no plugged values in cells IF statements are used where appropriate A B C D E H L F G Labour - Commercial 1 2 3 Actual Hours - AH 4 Actual Rate -AR 5 Actual Hours - AH Standard Rate -SR Standard Hours - SH Standard Rate - SR 6 7 Rate Variance Efficiency Variance 8 Total Variance 9 10 11 Explanation for Efficiency variance: 12 13 14 Explanation for rate variance: 15 16 17 18 19 Labour - Residential 20 21 22. Actual Hours-AH Flexible Budget Actual HoureAH Var Analysis - Static and Flex Material variances Labour variances Standard Haus SEL Input D A B D E F G H K 19 20 Labour - Residential 21 22 Actual Hours - AH 23 Actual Rate -AR 24 Actual Hours - AH Standard Rate -SR Standard Hours - SH Standard Rate - SR 25 Rate Variance Efficiency Variance 26 27 28 Total Variance 29 30 31 Explanation for Efficiency variance: 32 33 34 Explanation for rate variance: 35 36 37 38 39 Total un Flexible Budget Var Analysis - Static and Flex Material variances Labour variances Input D
