please explain what calculations you used for each cell so I can recreate in excel. Thank you
The production department of Zan Corporation has submitted the following forecast of units to be produced by quarter for the upcoming fiscal year: 1st Quarter 5,400 2nd 3rd 4th Quarter Quarter Quarter 8,400 7,400 6,400 Units to be produced In addition, 6,400 grams of raw materials inventory is on hand at the start of the 1st quarter and the beginning accounts payable for the 1st quarter is $3,280. Each unit requires 8.40 grams of raw material that costs $1.40 per gram. Management desires to end each quarter with an inventory of raw materials equal to 30% of the following quarter's production needs. The desired ending inventory for the 4th quarter is 8,400 grams. Management plans to pay for 50% of raw material purchases in the quarter acquired and 50% in the following quarter. Each unit requires 0.30 direct labour-hours and direct labourers are paid $10.70 per hour. Required: 1. Prepare the company's direct materials purchases budget and schedule of expected cash disbursements for materials for the upcoming fiscal year. Answer is complete and correct. Rectangular. Shu Required production in units of finished goods Units of raw materials needed per unit of finished goods Units of raw materials needed to meet production Add: Desired units of ending raw materials inventory Total units of raw materials needed Less: Units of beginning raw materials inventory Units of raw materials to be purchased Unit cost of raw materials Cost of raw materials to be purchased Zan Corporation Direct Materials Budget 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 5,400 8,400 7,400 6.400 8.40 8.40 8.40 8.40 45,360 70,560 62,160 53,760 21,168 18,648 16,128 8,400 66,528 89,208 78.288 62.160 6.400 21,168 18,648 16,128 60,128 68,040 59,640 46,032 $ 1.40 $ 1.40$ 1.40$ 1.40 $ $ 84,179 $ 95,256 $ 83.496 $ 64,445 $ Year 27,600 8.40 231,840 8,400 240,240 6.400 233,840 1.40 327,376 lo > Answer is complete but not entirely correct. Schedule of Expected Cash Disbursements for Materials Beginning accounts payable 3,280 1st Quarter purchases 42,090 $ 42,090 2nd Quarter purchases 47,628 $ 47.628 3rd Quarter purchases 41,748$ 4th Quarter purchases Total cash disbursements for materials $ 45,370 $ 89,718 $ 89,376 $ $ 3,280 84,180 95,256 41,748 83,496 32,222 X 32,222 X 73,970 $ 298,434 2. Prepare the company's direct labour budget for the upcoming fiscal year, assuming that the direct labour workforce is adjusted each quarter to match the number of hours required to produce the forecast number of units produced. Answer is not complete. Zan Corporation Direct Labour Budget 1st 2nd Quarter Quarter 3rd Quarter 4th Quarter Year Required production in units Direct labour-hours per unit Total direct labour-hours needed Direct labour cost per hour Total direct labour cost 0 0 0 0 0 $ 0 $ 0 $ 0 $ 0 $ 0
