Question

Chicago Furniture Company produces combination desk and chair sets for the elementary schools in the Midwest. As the second quarter is progressing it is important for the controller to complete a budget for the third quarter. The sales department manager has provided the following forecast.

July	8,000 desk combos
August	8,700 desk combos
September	7,600 desk combos
October	8,700 desk combos
November	8,800 desk combos

• In order to ensure Just-in-Time (JIT) deliveries are maintained in accordance with the needs of the schools Chicago Furniture Company has a standing policy that the inventory at the end of each month must be equal to 40% of the following month’s forecasted sales. On July 1st there will be 3,200 desk combos in inventory.
• The building of each desk combo requires 12 board feet of pine planks which cost $0.70 per foot. In order to maintain proper inventory for building the desk combos the department must have 30% of the next month’s production requirements.

Using Microsoft Excel, create a spreadsheet for the production and material purchases budget for the 3rd Quarter.

Answer 1

Production budget:
Particulars	July	Aug	Sep	3rd Quarter
Sales in units	8,000	8,700	7,600	24,300
Add
Closing inventory	3,480	3,040	3,480	3,480
	8700*40%	7600*40%	8700*40%
Total needs	11,480	11,740	11,080	27,780
Less
Opening inventory	3,200	3,480	3,040	3,200
Production	8,280	8,260	8,040	24,580
Raw materials budget	July	Aug	Sep	3rd Quarter
Units of raw materials needed for production	99,360	99,120	96,480	2,94,960
Add
Closing inventory	29,736	28,944	31,464	31,464
	99120*30%	96480*30%	104880*30%
Total need	1,29,096	1,28,064	1,27,944	3,26,424
Less opening invetory	29,808	29,736	28,944	29,808
Purchases	99,288	98,328	99,000	2,96,616
X cost per unit	0.7	0.7	0.7	0.7
Purchase dollars	69,501.60	68,829.60	69,300.00	2,07,631.20