Assume the following about a 6 year project: Mean Std Dev Sales Growth COGS/Sales 8% 53% 3% 8% Fixed Cost NWC as % of Sales Tax Rate Cost of Capital $2,700 10% 25% 10% Sales for the first year will be S10,500, but the sales in subsequent years are uncertain. Estimated sales growth is assumed to be normally distributed with a mean of 8% 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 53% and a standard deviation of 8%. Fixed costs will be S2,700 per year. The project will require an initial investment in net working capital of $500. Beginning at year l, 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 S12,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 10%. Assignment a) Set up a spreadsheet that will calculate the NPV of the project while capturing the uncertainty in sales and costs of goods sold. b) Simulate the NPV 1000 times using a data table. c) Graph the frequency distribution of the NPV estimates; calculate the mean NPV and standard deviation (using sample, not population, calculations). d) What is the probability that the NPV is positive? Assume the following about a 6 year project: Mean Std Dev Sales Growth COGS/Sales 8% 53% 3% 8% Fixed Cost NWC as % of Sales Tax Rate Cost of Capital $2,700 10% 25% 10% Sales for the first year will be S10,500, but the sales in subsequent years are uncertain. Estimated sales growth is assumed to be normally distributed with a mean of 8% 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 53% and a standard deviation of 8%. Fixed costs will be S2,700 per year. The project will require an initial investment in net working capital of $500. Beginning at year l, 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 S12,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 10%. Assignment a) Set up a spreadsheet that will calculate the NPV of the project while capturing the uncertainty in sales and costs of goods sold. b) Simulate the NPV 1000 times using a data table. c) Graph the frequency distribution of the NPV estimates; calculate the mean NPV and standard deviation (using sample, not population, calculations). d) What is the probability that the NPV is positive
