Question

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	？	？	？	？	？

Answer 1

PRODUCTION BUDGET
			Q1	Q2	Q3	Q4	Year	Q1	Q2
Budgeted Sales Units		40,000	60,000	1,00,000	50,000	2,50,000	70,000	80,000
Add: Desired Ending Finished inventory	18,000	30,000	15,000	21,000	21,000	24,000
Total Needs		58,000	90,000	1,15,000	71,000	2,71,000	94,000
Less: Beginning Finished Inventory	12,000	18,000	30,000	15,000	12,000	21,000
Required Production in units	46,000	72,000	85,000	56,000	2,59,000	73,000
LABOUR COST BUDGETS
			QUARTER-1	QUARTER-2	QUARTER-3	QUARTER-4	Total
Units to be produced		46,000	72,000	85,000	56,000	2,59,000
Labour required per unit	0.2	0.2	0.2	0.2	0.2
Labour hours		9200	14400	17000	11200	51800
Labuor Rate per hour	15	15	15	15	15
Budgeted Labour cost in $	1,38,000	2,16,000	2,55,000	1,68,000	7,77,000