Assume the following about a 6-year project:

Mean Std Dev

Sales Growth 4% 3%

COGS/Sales 50% 10%

Fixed Cost $1,500

NWC as % of Sales 10%

Tax Rate 25%

Cost of Capital 12%

• Sales for the first year will be $10,500, but the sales in subsequent years are uncertain. Estimated sales growth is assumed to be normally distributed with a mean of 4% and a standard deviation of 3%. Costs of goods Sold (COGS) each year are uncertain as well but are assumed to be a percentage of sales. COGS as a percentage of sales is assumed to be distributed normally with a mean of 50% and a standard deviation of 10%.
• Fixed costs will be $1,500 per year.
• The project will require an initial investment in net working capital of $500. Beginning at year 1, NWC is 10% of sales. The entire NWC investment (across all years) will be recovered at the end of the project.
• To operate the project, a new piece of equipment must be purchased at a cost of $12,000. The equipment will be depreciated using the straight-line approach (depreciate over 6 years, no salvage value).
• The equipment will have 0 salvage value by the end of the project.
• The cost of capital facing the firm is 12%.

Tasks:

a) Calculate the NPV of the project while capturing the uncertainty in sales and costs of goods sold.

b) Simulate the NPV 500 times using a data table.

c) Graph the frequency distribution of the NPV estimates; calculate the mean NPV and standard deviation.

d) What is the probability that the NPV is positive? What is your recommendation for the project? Why?

Please show formulas on excel used.