Excel Assignment # $4$

Spring

Vice President for Sales and Marketing Sam Totter is trying to plan for the coming year in

terms of production needs to meet the sales demand. He is also trying to determine ways in

which the company$$s profits might be increased in the coming year.

Instructions $($Do all parts$)$:

Northern Illinois Manufacturing markets a simple water control and timer that it mass$-$

produces. During the past year, the company sold $701,000$ units at an average selling price

of $4\mathrm{.}25$ per unit. The variable expenses were $$2,039,910$ and the fixed expenses were

$$693,340.$

$1.$ What is the product$$s contribution margin ratio? $($Round to nearest whole

percentage.$)$

$2.$ What is the company$$s break$-$even point in units and in dollars for this product?

$3.$ What is the margin of safety, both in dollars and as a ratio? $($Round to nearest

whole percentage.$)$

$4.$ If management wanted to increase its net operating income from this product by

$10\%,$ how many additional units would have to be sold to reach this income

level?

$5.$ If sales increase by $60,000$ units and the cost behaviors do not change, how

much will net operating income increase on this product?

$6.$ Northern Illinois$$ management believes that increased advertising would

increase unit sales by $10\%.$ If management wants to increase its operating

income by $$50,000,$ how much could the company spend on additional

advertising to reach its goal?

$7.$ Using last year$$s data as the starting point, assume the company can increase

sales by $5\%$ by just continuing its current marketing program. What would the

company$$s net operating income be next year using the DOL?

All parts should be completed, with your work shown, on an Excel spreadsheet. Reference

the seven parts with the identifying numbers on your Excel spreadsheet. Upload the Excel

spreadsheet to Canvas.