Required information

Chapter $8$: $($Algo$)$ Applying Excel $[$LO$8-2,$ LO$8-3,$ LO$8-4]$

The Chapter $8$ Form worksheet shown below recreates the Review Problem in the text.

Chapter $8$: $($Algo$)$ Applying Excel: Exercise $($Part $2$ of $2)$

Requirement $2$:

The company just hired a new marketing manager who insists unit sales can be dramatically increased by dropping the selling price from $\backslash (\backslash$$ $8\backslash )$ to $\backslash (\backslash$$ $7\backslash ).$ The marketing manager would like to use the following projections in the budget:

$\backslash$begin$\{$tabular$\}\{|$c$|$c$|$c$|$c$|$c$|$c$|$c$|\}$

$\backslash$hline & $\backslash$multicolumn$\{4\}\{|$c$|\}\{$Year $2$ Quarter$\}$ & $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Year $3$ Quarter$\}\backslash \backslash$

$\backslash$hline Data & $1$ & $2$ & $3$ & $4$ & $1$ & $2\backslash \backslash$

$\backslash$hline Budgeted unit sales & $45,000$ & $70,000$ & $120,000$ & $75,000$ & $80,000$ & $90,000\backslash \backslash$

$\backslash$hline Selling price per unit & $\backslash$$$7$ & & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

$\backslash$begin$\{$tabular$\}\{|$c$|$c$|$c$|$c$|$c$|$c$|$c$|$c$|$c$|\}$

$\backslash$hline & A & & B & C & D & E & F & G $\backslash \backslash$

$\backslash$hline $1$ & Chapter $8$: Applying Excel & & & & & & & $\backslash \backslash$

$\backslash$hline $2$ & & & & & & & & $\backslash \backslash$

$\backslash$hline $3$ & Data & & & & & & Year $3$ Quarter & $\backslash \backslash$

$\backslash$hline $4$ & & & $1$ & $2$ & $3$ & $4$ & $1$ & $2\backslash \backslash$

$\backslash$hline $5$ & Budgeted unit sales & & $45,000$ & $70,000$ & $120,000$ & $75,000$ & $80,000$ & $90,000\backslash \backslash$

$\backslash$hline $6$ & & & & & & & & $\backslash \backslash$

$\backslash$hline $7$ & $-$ Selling price per unit & $\backslash$$ & $7$ & per unit & & & & $\backslash \backslash$

$\backslash$hline $8$ & $-$ Accounts receivable, beginning balance & $\backslash$$ & $65,000$ & & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

A B C D E F G $1$ Chapter $8$: Applying Excel $23$ Data Year $3$ Quarter $41234125$ Budgeted unit sales $45,00070,000120,00075,00080,00090,00067-$ Selling price per unit $ $7$ per unit $8-$ Accounts receivable, beginning balance $ $65,0009-$ Sales collected in the quarter sales are made $75\%10-$ Sales collected in the quarter after sales are made $25\%11-$ Desired ending finished goods inventory is $30\%$ of the budgeted es of the n $12-$ Finished goods inventory, beginning $12,000$ units $13-$ Raw materials required to produce one unit $5$ pounds $14-$ Desired ending inventory of raw materials is $10\%$ of the next quart duction ne $15-$ Raw materials inventory, beginning $23,000$ pounds $16-$ Raw material costs $ $0\mathrm{.}80$ per pound $17-$ Raw materials purchases are paid $60\%$ in the quarter the ases are m $18$ and $40\%$ in the quarter foll purchase $19-$ Accounts payable for raw materials, beginning balance $81,500$

a$.$ What are the total expected cash collections tor the year under this revised budget?

Expected cash collections for the year

b$.$ What is the total required production for the year under this revised budget?

c$.$ What is the total cost of raw materials to be purchased for the year under this revised budget?

Total cost of raw materials to be purchased for the

year

d$.$ What are the total expected cash disbursements for raw materials for the year under this revised budget?

Total expected cash disbursements for raw materials for the

year