Parameters - actual Quarters 3 1 2 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,950 1,210 5,220 3,650 1,570 4,440 3,150 1,240 4,400 2,800 1,600 18,080 12,460 5,620 40 40 40 40 40 49 49 49 49 49 Direct labour expense ( $/hr): 23 23 23 23 Actual Cleaning Supply usage ( litre / labour hour): Commercial Residential 0.22 0.22 0.22 0.22 0.12 0.12 0.11 0.11 Actual Cleaning Supply expense ( $/litre)): 5 5 5 Actual Fixed overhead: Receptionist Depreciation Gas Office Supplies 6,010 9,250 7,300 990 5,770 9,250 7,940 950 5,915 9,250 7,450 975 6,325 9,250 7,275 1,215 24,020 37,000 29,965 4,130 20,896 Total no. of hours actually paid to employees Breakdown: Commercial Residential Actual hours worked Direct labour cost 3,393 1,392 4,784 1,10,032 4,198 1,806 6,003 1,38,069 3,623 1,426 5,049 1,16,116 3,220 1,840 5,060 1,16,380 14,433 6,463 20,896 4,80,597 Unproductive (unbilled) factor 15% 15% 15% 15% 15% Parameters - budget Quarters 1 2 3 4 Year 4,160 2,950 1,260 5,110 3,050 1,530 4,560 3,250 1,400 5,375 3,550 1,635 19,205 13,380 5,825 $ 38 38 $ 48 S 38 $ 48 $ 38 $ 48 $ 38 $ 48 $ S 48 22 22 22 22 Sales ( hours): - Budgeted total sales in hours 5 Budgeted commercial sales in hours Budgeted residential sales in hours Selling price ( $/hr): Selling price per hour (commercial) O Selling price per hour (residential) 1 2 Direct labour expense ($/hr): 3 4 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 0.24 0.25 0.25 0.24 0.15 0.15 0.15 0.15 $ 5.50 $ 5.50 $ 5.50 $ 5.50 6750 6750 9250 27000 9250 6750 9250 8250 1500 6750 9250 8250 1500 8250 8250 37000 33000 6000 1500 1500 26 27 1. Prepare the following Budget > Static Budget > Actual Budget > Flexible Budget > Var Analysis - Static and Flex > Material variances > Labour variances > Assumptions 2.On assumptions sheet, 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. Parameters - actual Quarters 3 1 2 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,950 1,210 5,220 3,650 1,570 4,440 3,150 1,240 4,400 2,800 1,600 18,080 12,460 5,620 40 40 40 40 40 49 49 49 49 49 Direct labour expense ( $/hr): 23 23 23 23 Actual Cleaning Supply usage ( litre / labour hour): Commercial Residential 0.22 0.22 0.22 0.22 0.12 0.12 0.11 0.11 Actual Cleaning Supply expense ( $/litre)): 5 5 5 Actual Fixed overhead: Receptionist Depreciation Gas Office Supplies 6,010 9,250 7,300 990 5,770 9,250 7,940 950 5,915 9,250 7,450 975 6,325 9,250 7,275 1,215 24,020 37,000 29,965 4,130 20,896 Total no. of hours actually paid to employees Breakdown: Commercial Residential Actual hours worked Direct labour cost 3,393 1,392 4,784 1,10,032 4,198 1,806 6,003 1,38,069 3,623 1,426 5,049 1,16,116 3,220 1,840 5,060 1,16,380 14,433 6,463 20,896 4,80,597 Unproductive (unbilled) factor 15% 15% 15% 15% 15% Parameters - budget Quarters 1 2 3 4 Year 4,160 2,950 1,260 5,110 3,050 1,530 4,560 3,250 1,400 5,375 3,550 1,635 19,205 13,380 5,825 $ 38 38 $ 48 S 38 $ 48 $ 38 $ 48 $ 38 $ 48 $ S 48 22 22 22 22 Sales ( hours): - Budgeted total sales in hours 5 Budgeted commercial sales in hours Budgeted residential sales in hours Selling price ( $/hr): Selling price per hour (commercial) O Selling price per hour (residential) 1 2 Direct labour expense ($/hr): 3 4 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 0.24 0.25 0.25 0.24 0.15 0.15 0.15 0.15 $ 5.50 $ 5.50 $ 5.50 $ 5.50 6750 6750 9250 27000 9250 6750 9250 8250 1500 6750 9250 8250 1500 8250 8250 37000 33000 6000 1500 1500 26 27 1. Prepare the following Budget > Static Budget > Actual Budget > Flexible Budget > Var Analysis - Static and Flex > Material variances > Labour variances > Assumptions 2.On assumptions sheet, 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