Data Year 2 Quarter Year 3 Quarter

1 2 3 4 1 2

Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000

Selling price per unit $12

Accounts receivable, beginning balance $65,000

Sales collected in the quarter sales are made 75%

Sales collected in the quarter after sales are made 25%

Desired ending finished goods inventory is 30% of budgeted unit sales of next QTR

Finished goods inventory, beginning 12,000

Raw materials required to produce one unit 5 pounds

Desired ending inventory of raw materials is 10% of next Qtr production needs

Raw materials inventory, beginning 23,000 pounds

Raw material costs $0.80 per pound

Raw materials purchases are paid 60% in Qtr purchases are made and 40% in Qtr following purchase

Accounts payable for raw materials, beginning balance $81,500

Direct labor cost per hour $15

Direct labor hour per unit 0.2

Variable MOH rate $2

Total fixed MOH $60,000

Variable S&A expense rate $1.80

Minimum cash balance $50,000

Annual Interest rate 12%

BASED on above info what is the EXCEL formulas for the missing info below to get correct answers?

Construct the raw materials purchases budget	Year 2 Quarter					Year 3 Quarter
	1	2	3	4	Year	1
Required production (units)	?	?	?	?	?	?
Raw materials required to produce one unit	?	?	?	?	?	?
Production needs (pounds)	?	?	?	?	?	?
Add desired ending inventory of raw materials (pounds)	?	?	?	?	?
Total needs (pounds)	?	?	?	?	?
Less beginning inventory of raw materials (pounds)	?	?	?	?	?
Raw materials to be purchased	?	?	?	?	?
Cost of raw materials per pound	?	?	?	?	?
Cost of raw materials to be purchased	?	?	?	?	?

Construct the schedule of expected cash payments	Year 2 Quarter
	1	2	3	4	Year
Accounts payable, beginning balance	?				?
First-quarter purchases	?	?			?
Second-quarter purchases		?	?		?
Third-quarter purchases			?	?	?
Fourth-quarter purchases				?	?
Total cash disbursements	?	?	?	?	?