Either reference another cell or enter a formula into each of the cells marked with a $?$

Data and Budgetary Assumptions

$$ Change per quarter $0\mathrm{.}0\%$ change per quarter

Year $1,$ Quarter Year $2,$ Quarter

Quarters $>123412$

$$ Budgeted unit sales $??????$

$$ Selling price per unit $$15\mathrm{.}50$

$$ Accounts receivable, beginning balance $0\mathrm{.}00$

$$ Sales collected in the quarter sales are made $30\%$

$$ Sales collected in the quarter after sales are made $70\%$

$$ Desired ending finished goods inventory is $20\%$ of the budgeted unit sales of the next quarter

$$ Finished goods inventory, beginning $0$ units

$$ Raw materials required to produce one unit $5\mathrm{.}0$ pounds

$$ Desired ending inventory of raw materials is $10\%$ of the next quarter's production needs

$$ Raw materials inventory, beginning $0$ pounds

$$ Raw material costs $$0\mathrm{.}75$ per pound

$$ Raw materials purchases are paid $30\%$ in the quarter the purchases are made

and $70\%$ in the quarter following purchase

$$ Accounts payable for raw materials, beginning balance $$-$

$$ Direct Labor Hours per Unit $0\mathrm{.}350$

$$ Cost per Direct Labor Hour $$22\mathrm{.}00$

$$ Fixed Overhead Costs $$600,000$ per year

$$ Variable Overhead Costs per Direct Labor Hour $$3\mathrm{.}25$

$$ Annual Depreciation $$100,000$

$$ Selling & Admin. Expenses per year $$250,000$

$$ Beginning Cash balance $$65,000$

$*$ Bonds, issued $1/1/$Year$1$ $$1,000,000$ Face amount

$$ Annual Interest on Bonds, Interest is paid semi$-$annually $10\mathrm{.}0\%$ annual face rate $($coupon rate$)$

Either reference another cell or enter a formula into each of the cells marked with a $?$

Budget Schedules

Construct the sales budget Year $1,$ Quarter Year $1,$ Totals Year $2$ Quarter

$123412$

Budgeted unit sales $30,00033,60037,63242,148143,38047,20652,870$

Selling price per unit $$15\mathrm{.}50$ $$15\mathrm{.}50$ $$15\mathrm{.}50$ $$15\mathrm{.}50$ $$15\mathrm{.}50$ $$15\mathrm{.}50$ $$15\mathrm{.}50$

Total sales $$465,000\mathrm{.}00$ $$520,800\mathrm{.}00$ $$583,296\mathrm{.}00$ $$653,291\mathrm{.}52$ $$2,222,387\mathrm{.}52731,687819,489$

Construct the schedule of expected cash collections Year $1,$ Quarter

$1234$ Year

Beginning balance accounts receivable $139,500139,500$

First$-$quarter sales $325,500156,240481,740$

Second$-$quarter sales $364,560174,989539,549$

Third$-$quarter sales $408,307195,987604,295$

Fourth$-$quarter sales $457,304457,304$

Total cash collections $465,000520,800583,296653,2922,222,388$

Construct the production budget Year $1,$ Quarter Year $2$ Quarter

$1234$ Year $12$

Budgeted unit sales $30,00033,60037,63242,148143,38047,20652,870$

Add desired ending finished goods inventory $6,7207,5268,4$