Question

Managerial Accounting: Operating budgets 1. Abe Toys, LLC manufactures and distributes a number or products to retailers. One of these products, Playclay, requires three pounds of materials A135 in the manufacture of each unit. The company is now planning raw materials needs for the third quarter - July, August, and September. Peak sales of Playclay occur in the third quarter of each year. To keep production and shipments moving smoothly, the company has the following inventory requirements: a. The finished goods inventory on hand at the end of each month must be equal to 5,000 units plus 30% of the next month's sales. The finished goods inventory on June 30 is budgeted to be 17,000 units. b. The raw materials inventory on hand at the end of the month must be equal to one-half of the following month's production needs for raw materials. The raw materials inventory on June 30 for material A135 is budgeted to be 64,500 pounds. The cost per pound of raw material is $5.00. c. The company maintains no work in process inventories and the selling price per unit of Playclay is $39.50. A sales budget for Playclay for the last six months of the year follows: July August September October November December Budgeted Sales in Units 40,000 50.000 70.000 35,000 | 20,000 10.000 Required: a. Prepare a sales budget for Playclay for the months of July, August, and September, and for the quarter in total. b. Prepare a production budget (finished goods) for Playclay for the months of July, August, and September, and for the quarter in total. c. Prepare a direct materials budget showing the quantity of material A135 to be purchased and total direct materials purchased cost for July, August, and September and for the quarter in total.

Need completed in excel format

Answer 1

a) Sales budget

	July	August	September	Total
Sales	40000	50000	70000	160000

b) Production budget

	July	August	September	Total
Sales	40000	50000	70000	160000
+ closing stock	20000	26000	15500	61500
- opening stock	-17000	-20000	-26000	-63000
production	43000	56000	59500	158500

working notes

	July	August	September	October	November
Sales	40000	50000	70000	35000	20000
+ closing stock	20000	26000	15500	11000	8000
- opening stock	-17000	-20000	-26000	-15500	-11000
production	43000	56000	59500	30500	17000

c) Direct material budget

	July	August	September	total
production (in pounds)	129000	168000	178500	475500
+ closing inventory	84000	89250	45750	219000
- opening inventory	-64500	-84000	-89250	-237750
raw material required	148500	173250	135000	456750
cost($)	742500	866250	675000	2283750

notes

	July	August	September	October
production (in pounds)	129000	168000	178500	91500
+ closing inventory	84000	89250	45750	25500
- opening inventory	-64500	-84000	-89250	-45750
raw material required	148500	173250	135000	71250
cost($)	742500	866250	675000	356250