Question

Project 2: Flexible Budget and Budget Report (10 points) Upload your file to Moodle by the deadline specified. Do NOT email your filel XYZ Company estimates that 240 000 direct labor hours will be worked during 2018 in the Painting Department. Based on this estimate the following budget was developed for manufacturing overhead costs. Variable Overhead Costs Indirect labor $72,000 Indirect materials 48.000 Repairs 36,000 Utilities 26,400 Lubricants 9,600 $ 192,000 Fixed Overhead Costs Supervision $75,000 Depreciation 30,000 Insurance 12,000 Rent 9,000 Property taxes 6,000 $132.000 It is estimated that direct labor hours worked each month will range from 16,000 to 24,000 hours. During January, 20,000 direct labor hours were worked and the following overhead costs were incurred. Variable Overhead Costs Indirect labor $6,300 Indirect materials 3,800 Repairs 2,700 Utilities 1,900 Lubricants 830 $15,530 Fixed Overhead Costs Supervision $6,250 Depreciation 2,500 Insurance 1,000 Rent 750 Property taxes 500 $11,000 Instructions for Project 2: 1. Using Excel, prepare a monthly flexible manufacturing overhead budget for each increment of 2,000 direct labor hours over the relevant range for the year ending December 31, 2020. Refer to Illustration 23.13 on page 23-10 for an example. 2. Using Excel, prepare a manufacturing overhead budget report for January in good form. Refer to Illustration 23.10 on page 23-8 as a guide.

Answer 1

Q.1 Flexible Budget :

XYZ Company

Flexible Manufacturing Overhead Budget Report
Variable Overhead	Per Labor Hour	Labor Hours
		16,000	18,000	20,000	22,000	24,000
Indirect Labour	0.30	4,800	5,400	6,000	6,600	7,200
Indirect Material	0.20	3,200	3,600	4,000	4,400	4,800
Repairs	0.15	2,400	2,700	3,000	3,300	3,600
Utilities	0.11	1,760	1,980	2,200	2,420	2,640
Lubricants	0.04	640	720	800	880	960
Total Variable Oveheads		12,800	14,400	16,000	17,600	19,200
Fixed Overheads
Super Vision		6,250	6,250	6,250	6,250	6,250
Depreciation		2,500	2,500	2,500	2,500	2,500
Insurance		1,000	1,000	1,000	1,000	1,000
Rent		750	750	750	750	750
Property Tax		500	500	500	500	500
Total Fixed Overhead		11,000	11,000	11,000	11,000	11,000
Total Variable & Fixed Overhead		23,800	25,400	27,000	28,600	30,200

Q.2 January Budget Report :

XYZ company :

January Manufacturing Overhead budget Report
Variable Overhead	Budget	Actual	Variance
Indirect Labour	6,000	6,300	(300)
Indirect Material	4,000	3,800	200
Repairs	3,000	2,700	300
Utilities	2,200	1,900	300
Lubricants	800	830	(30)
Total Variable Oveheads	16,000	15,530	470
Fixed Overheads
Super Vision	6,250	6,250	-
Depreciation	2,500	2,500	-
Insurance	1,000	1,000	-
Rent	750	750	-
Property Tax	500	500	-
Total Fixed Overhead	11,000	11,000	-
Total Variable & Fixed Overhead	27,000	26,530	470