Question

MATERIAL 311: Materials per Finished Component: 9 feet Cost per foot: $2.00 Inventory at March 31: 69,000 feet Material No. 208 is sometimes in short supply. Therefore, the East Division requires that enough of the material be on hand at the end of each month to provide for 50% of the following month's production needs. Material No. 311 is easier to get, so only one-third of the following month's production needs must be on hand at the end of each month. Direct Labor: The East Division has three departments through which the components must past before they are completed. Information relating to direct labor in these departments is given below: SHAPING DEPARTMENT: Direct Labor-Hours per Finished Component: .25 Cost per Direct Labor hour: $18.00 ASSEMBLY DEPARTMENT: Direct Labor-Hours per Finished Component: .70 Cost per Direct Labor hour: $16.00

I would like the excel part of 311 and the schedule of cash payments to be done and possibly with the work shown thank you!

Answer 1

There is some problem in data provided, as Total production for Quarter is given as 94200 which is not equal to 22800+37400+51000

I have taken monthly production units given by you.

Direct Material Budget
	April	May	June	Quarter	July
Production Required	22800	37400	51000	111200	45000
Raw Material Per unit	9	9	9		9
Raw Material for Production	205200	336600	459000	1000800	405000
Add : Closing Stock Required	168300	229500	202500
Total Material Required	373500	566100	661500	1601100
Less : Opening Stock	69000	168300	229500
Material to be Purchased	304500	397800	432000	1134300
Rate per unit	$              2.00	$              2.00	$              2.00
Cost of Material	$      6,09,000	$      7,95,600	$      8,64,000	$   22,68,600
Schedule of Cash Payments
	April	May	June	Quarter
Accounts Payable	$      3,51,200			$      3,51,200
April	$      3,04,500	$      3,04,500		$      6,09,000
May		$      3,97,800	$      3,97,800	$      7,95,600
June			$      4,32,000	$      4,32,000
Total	$      6,55,700	$      7,02,300	$      8,29,800	$   21,87,800

Calculations

Direct Material Budget
	April	May	June
Add : Closing Stock Required	=336600*50%	=459000*50%	=405000*50%
Schedule of Cash Payments
	April	May	June
Accounts Payable	351200
April	=609000*1/2	=609000*1/2
May		=795600*1/2	=795600*1/2
June			=864000*1/2

Beginning Stock is closing stock for previous month