90.2K

Verified Solution

Link Copied!

Demand for a new product is estimated as 50,000 units next year (year 2) and is expected to grow at 5% per year for the next 10 years (years 2 to 11). The management needs to decide the capacity of a new factory to be built for this product. Following data have been collected:

Size	Annual capacity (units)	Building cost	Annual operating cost
Small	50,000	$800,000	$20,000
Medium	60,000	$900,000	$25,000
Large	70,000	$1,000,000	$30,000

The factory with take 1 year to build, so it will be ready for production in year 2 (now is the beginning of year 1). The selling price of product will be $5 per unit and variable cost will be $2 per unit. Any excess demand will be lost (assume at no cost). If demand is less than capacity, the factory will produce enough to meet the demand. Assume all cash flows occur at the end of the year, including the building cost (will occur at the end of year 1).

a. Create below a spreadsheet model of this problem. Assuming a small size (50,000 annual capacity) factory is built but make your model flexible so it can also work for other capacities. Use vlookup to pickup the building cost and annual operating cost associated with the annual capacity. Calculate the NPV of this project using discount rate of 7%.

b. Use one-way data table to determine which capacity has the highest NPV.

Please include the formulas used in excel.

Answer & Explanation Solved by verified expert

See Answer