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 S &A expense budget	Year 2 Quarter
	1	2	3	4	Year
Budgeted sales in units	?	?	?	?	?
Variable S&A expense rate	?	?	?	?	?
Total variable S&A expenses	?	?	?	?	?
Total fixed S&A expenses	100,000	100,000	100,000	100,000	400,000
Total S&A expenses	?	?	?	?	?
Less depreciation	100,000	100,000	100,000	100,000	400,000
Cash disbursements for S&A expenses	?	?	?	?	?

Income Statement Budget

Cost per unit	Quanitity			Cost			Total
Direct materials	?	pounds		?	per pound		?
Direct labor	?	hours		?	per hour		?
Manufacturing overhead	?	hours		?	per hour		?
Unit product cost							?