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?

Review Problem: Budget Schedules
Construct the sales budget	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	?	?	?	?	?	?
Selling price per unit	?	?	?	?	?	?
Total sales	?	?	?	?	?	?
Construct the schedule of expected cash collections	Year 2 Quarter
	1	2	3	4	Year
Accounts receivable, beginning balance	?				?
First-quarter sales	?	?			?
Second-quarter sales		?	?		?
Third-quarter sales			?	?	?
Fourth-quarter sales				?	?
Total cash collections	?	?	?	?	?

Answer 1