Share
Explore BrainMass

MBA-Level Finance Case Study EXCEL Problem

The Peach Company is thinking of building a new plant to put the peaches it grows into cans. The plant is expected to last for 20 years. Its initial cost is \$20 mln. This cost can be depreciated over the full 20-year of the plant using straight line depreciation. It will require a major renovation which will cost \$8 mln in real terms after 10 years. This cost of renovation can be depreciated (again using straight line depreciation) over the remaining 10 years of the plan's life. The land the plant is built on could be rented out for \$500 000 a year in nominal terms for twenty years. The salvage value of the plant at the end of the twenty years is \$3.5 mln in nominal terms. This salvage value is attributed to t he original expenditure on the plant for tax purposes. There is no salvage value with regard to the renovation.

The plant could be able to produce 50 mln cans of peaches a year. The price of a can of peaches is currently \$0.50. It is expected to grow at a rate of 3% per year in real terms for 6 years and then at 0% in real terms for the reminder of the plant's life. The firm expects to be able to sell all the cans of peaches it can produce. The peaches the firm puts in the cans are grown in the firm's own orchards. If the peaches were not canned they could be sold to supermarkets. The current price they could obtain per peach is \$0.1. This price is expected to grow at a rate of 2% in real terms for 5 years and then at 1% in real terms for the next five years and finally at 0% in real terms for the reminder of the plant's life. Each can requires 2.5 peaches to fill it. The raw materials for the cans currently cost \$0.05per can. These costs are expected to remain constant in real terms. The labor required to operate the plant costs a total of \$5 mln a year in real terms. Initially, the total working capital requirement is \$10mln and this is expected to remain constant in real terms.

The rate of inflation is expected to be 4% per year for the next four years and 3% per year for the remainder of the plant's life. The firm's total tax rate including local taxes is 38 per cent. The firm expects to make substantial profits on its other operations so that if it can offset any losses on the peach canning plant for tax purposes. Its opportunity cost of capital for projects of this type is 14% in nominal terms.

Construct two spreadsheets in EXCEL to find the NPV of the peach canning plant. One spreadsheet should be in nominal terms and the other should be in real terms. The value of the real and nominal
NPVs should be the same. Should the firm build the plant?

Solution Summary

The solution discusses the MBA-level finance case study in Excel.

\$2.19