How to create flexible budget using this information:
Parameters - budget Quarters 1 2 3 4 total Sales ( hours): Budgeted total sales in hours 6,100 6,300 5,800 5,600 23,800 Budgeted commercial sales in hours 4,000 4,200 4,000 3,500 15,700 Budgeted residential sales in hours 2,100 2,100 1,800 2,100 8,100 Selling rate ( $/hr): Selling price per hour (commercial) $39 $39 $39 $39 $39 Selling price per hour (residential) $42 $42 $42 $42 $42 Direct labour expense ( $/hr): S 26 26 $ 26 26 $ 26 Cleaning Supply usage ( litre / labour hour): Commercial 0.28 0.28 0.28 0.28 0.28 Residential 0.17 0.17 0.17 0.17 0.17 Cleaning Supply expense ( $/litre)): 5.70 $ 5.70 $ 5.70 $ 5.70 $ 5.70 Fixed overhead: Receptionist 6,000 6,000 $ 6,000 $ 6,000 $ 24,000 Depreciation 8,700 $ 8,700 $ 8,700 8,700 $ 34,800 Gas 6,000 $ 6,000 $ 6,000 $ 6,000 $ 24,000 Maintenance 1,200 $ 1,200 $ 1,200 $ 1,200 4,800Parameters - actual Quarters 1 2 3 4 Year Quarter over quarter Sales ( hours): change> > > Actual total sales 5,950 6,650 5,700 5,900 24,200 Actual sales - commercial 3,700 4,300 3,900 3,900 15,800 Actual sales - residential 2,250 2,350 1,800 2,000 8,400 Selling rate ($/hr): Actual selling price - commercial $38 $38 $38 $38 $38 Actual sellling price - residential $46 $46 $46 $46 $46 Direct labour expense ($/hr): $ 28 $ $ 28 $ 28 $ 28 Actual Cleaning Supply usage ( litre / labour hour): Commercial 0.27 0.27 0.27 0.27 0.27 Residential 0.15 0.15 0.15 0.15 0.15 Actual Cleaning Supply expense ( $/litre)): $ 5.65 $ 5.65 $ 5.65 $ 5.65 $ 5.65 Actual Fixed overhead: Receptionist $ 6,200 $ 6,400 $ 5,900 $ 5,800 $ 24,300 Depreciation $ 8,500 $ 8,500 $ 8,500 8,500 $ 34,000 Gas 8,300 7,800 $ 8,100 $ 8,300 $ 32,500 Maintenance 1,300 1,000 1,200 1,000 $ 4,500 Total no. of hours actually paid to employees Breakdown: Commercial 4,218 4,902 4,446 4,446 $ 18,012 Residential 2,565 2,679 2,052 2,280 $ 9,576 Actual hours worked 6,783 7,581 6,498 6,726 27,588 Direct labour cost 189,924 212,268 181,944 188,328 772,464 Unproductive ( unbilled ) factor 14% 14% 14% 14% 14%Flexible budget for the period Jan - Dec '20 Quarters 2 3 4 Year Sales budget Sales Commercial Sales residential Total sales Direct material budget Variable costs commercial Variable costs residential Total variable costs Labour budget Commercial Residential Total labour Fixed Overhead: Receptionist $6,000 $6,000 $6,000 $6,000 $24,000 Depreciation $8,700 $8,700 $8,700 $8,700 $34,800 Gas $6,000 $6,000 $6,000 $6,000 $24,000 Maintenance $1,200 $1,200 $1,200 $1,200 $4,800 Total $21,900 $21,900 $21,900 $21,900 $87,600
