CAPEX Problem Instructions

Using the following assumptions, Calculate the NPV and IRR for a Base Case, a Downside Case, and an Upside Case

Note that the worksheet has spaces for each case, set up $($hopefully$)$ in print friendly format.

Assumptions:

Kinston Pachaging is considering expanding its capacity by purchasing a new machine, the $xC-450.$ The cost is $$2\mathrm{.}75$ million.

Unfortunately, installing this machine will take several months and will partially disrupt current production. The firm has just

completed a $$50,000$ feasibility study to analyze the decision to buy the XC$-750,$ resulting in the following estimates:

Marketing:

Once the XC$-750$ is operational next year, the extra capacity is expected to generate $$10$ million per year in additional sales

which will continue for the ten$-$year life of the machine.

Operations:

The disruption caused by the installation will decrease sales by $$4$ million this year. As with Kinston's existing products,

the cost of goods for the producted produced by the XC$-750$ is expected to be $70\%$ of their sale price. The increased production

will also require increased inventory on hand of $$1$ million during the life of the project, including year $0.$

Human Resources:

The expansion will require additional sales and adminstrative personnel at a cost of $$2$ million per year.

Accounting:

The XC$-750$ will be depreciated via the straight line method over the ten$-$year life of the machine.

Consistent with current experience, the firm expects receivables to be $15\%$ of revenue and payables to be $10\%$ of cost of goods sold

on ANY incermental business associated with this project $($positive or negative$).$

Kinston's marginal tax rate is $35\%.$ No salvage value is assumed for the machine.

COO's instructions:

While the base case assumptions seem reasonable, she believes that actual sales could range from $$8$ million to $$12$ million.

Therefore she has asked that you determine both the NPV and IRR for not only the base case, but also for a downside and upside.

I need an excul sheet for base,d downside, and upside.