Assume the following about a 6-year project: Mean Std Dev Sales Growth 4% 3% COGS/Sales 50% 8% Fixed Cost $2,700 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 unce

Assume the following about a 6-year project:

Mean Std Dev

Sales Growth 4% 3%

COGS/Sales 50% 8%

Fixed Cost $2,700

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 8%.
• Fixed costs will be $2,700 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 MACRS schedule given in the Excel upload file.
• The equipment will have 0 salvage value by the end of the project.
• The cost of capital facing the firm is 12%.

Tasks:

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

2. Simulate the NPV 500 times using a data table.

3. Graph the frequency distribution of the NPV estimates; calculate the mean NPV and standard deviation (using sample, not population, calculations).

4. What is the probability that the NPV is positive? What is your recommendation for the project? Why?

***Please show all work in excel***

rtain 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 8%. Fixed costs will be $2,700 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 MACRS schedule given in the Excel upload file. The equipment will have 0 salvage value by the end of the project. The cost of capital facing the firm is 12%. Tasks: Calculate the NPV of the project while capturing the uncertainty in sales and costs of goods sold. Simulate the NPV 500 times using a data table. Graph the frequency distribution of the NPV estimates; calculate the mean NPV and standard deviation (using sample, not population, calculations). What is the probability that the NPV is positive? What is your recommendation for the project? Why? ***Please show all work in excell***