Kinston Pachaging is considering expanding its capacity by purchasing a new machine, the XC-450. The cost is $2.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 $5 million this year. As with Kinston's existing products, the cost of goods for the 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. The firm expects receivables from the new sales to be 15% of revenues and the payables to be 10% of the cost of goods sold. Kinston's marginal tax rate is 35%. No salvage value is assumed for the machine.

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.

Please make an excel for the base, downside and upside case. 10 Year sheet

BASE CASE

Incr. Sales

COGS @ 70%

Incr. fixed costs

Depreciation

EBIT

Tax @ 35%

NI

Capital Expenditure

WC

Depreciation add-back

Net Cash Flow

NPV @ 10%

IRR

Incr. A/R

Incr. Inv

Incr A/P

Total incr. WC

BASE CASE