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 production budget	Year 2 Quarter					Year 3 Quarter
	1	2	3	4	Year	1	2
Budgeted unit sales	?	?	?	?	?	?	?
Add desired finished goods inventory	?	?	?	?	?	?
Total needs	?	?	?	?	?	?
Less beginning inventory	?	?	?	?	?	?
Required production	?	?	?	?	?	?

Construct the direct labor budget	Year 2 Quarter
	1	2	3	4	Year
Required production (units)		?	?
Direct labor-hours per unit
Total direct labor-hours needed
Direct labor cost per hour
Total direct labor cost