Background:

Stockbridge Sprockets, Inc. $($SSI or the Company$)$ wants to bring an improved line of sprockets to the market. While their current sprocket machine can produce the new product, the Company wants to determine if it will be more cost effective to invest in a new machine that can produce the new product line more efficiently. They have hired you to do a financial analysis and make a recommendation as to whether they should purchase the new machine.

The Company has determined that their new product line will need to be upgraded in $5$ years, so the project to determine whether to buy the new machine will have a $5$ year time horizon.

Data:

$$ SSI paid $$100,000$ for the factory that the new product will be made in$.$ They made this purchase in $2016.$

$$ The Company spent $$5,000$ last year to secure the patent on the new line of sprockets.

$$ The cost of the new machine will be $$35,000.$ This will be depreciated straight$-$line to $0$ over the machine$$s estimated $7$ year useful life.

$$ The Company spent $$2,500$ on a trip to see a similar machine in use at a factory in Red Deer, Alberta last year.

$$ If the Company buys the new machine, it will sell its current machine to Mid$-$Montana Manufacturing, Inc., who has already made SSI an offer of $$7,594\mathrm{.}94.$ The current machine has already been fully depreciated, and will be sold at the beginning of the project $($Round the tax implication to $2$ decimal places$).$

$$ The efficiency of the new machine would allow more products to be produced each year, leading to additional revenue in year $1$ in the amount of $$34,000.$

$$ As word of the quality of the new product spreads in the marketplace, revenue will grow in Years $2$ & $3$ by $5\mathrm{.}5\%$ per year.

$$ In Years $4$ & $5,$ the market for the new product will become saturated, leading to a reduction in revenue by $4\%$ per year.

$$ Variable costs are estimated to be $72\%$ of revenue.

$$ Fixed production costs are estimated to be $$2,750$ per year.

$$ Additional Net Working Capital will be required at the beginning of the project in the amount of $$8,000.60\%$ of this will be recovered in the project$$s terminal year.

$$ At the end of the project, the company has estimated that it will be able to sell the machine it bought in Year $0$ for $$15,000.$

$$ The company$$s tax rate is $21\%$

$$ The Required Rate of Return on the project is $10\%.$

Base Case:

Use the data above to construct the Base Case analysis. Calculate NPV and IRR.

Scenario Analysis:

After you complete the Base Case, copy and paste it to the Case $2$ and Case $3$ tabs$.$

SSI has hired a consultant to review their estimates and assumptions regarding the project. They believe the company is being a bit optimistic, and has created a worst case $($Case $2)$ and best case $($Case $3)$ scenario.

Alternate Scenario $1($Worst Case$)$

In this scenario, change Revenue Growth in Yrs$.2$ & $3$ to $5\mathrm{.}2\%,$ Variable Costs as a $\%$ of Revenue to $72\mathrm{.}5\%,$ Year $1$ Revenue to $33,000.$ Note the new NPV and IRR.

Alternate Scenario $2($Best Case$)$

In this scenario, change Revenue Growth in Yrs$.2$ & $3$ to $5\mathrm{.}9\%,$ Variable Costs as a $\%$ of Revenue to $71\mathrm{.}5\%.$ Note the new NPV and IRR.

Recommendation

Based on the results from the $3$ Cases, make a recommendation as to whether SSI should purchase the new machine or not. Hint: the correct method can be found in Ch$.13.$

The consultant has assigned the following probabilities of each scenario occurring:

$$ Base Case: $50\%$

$$ Case $2$: $30\%$

$$ Case $3$: $20\%$

QUESTION $1$: What is the amount of the Year $5$ Total Cash Flow?:

QUESTION $2$: What is the amount of the annual depreciation?

QUESTION $3$: What is the Base Case NPV$?$

QUESTION $4$: Based on your entire analysis, should the project be accepted or rejected?