Project Direction

• The submission format will be a single EXCEL file using the template provided on Canvas, with yellow boxes filled in for the calculations and questions on the bottom of the spreadsheet answered
• Any other submission format is unacceptable
• All entries in the EXCEL file need to be linked, meaning that if one were to change any value in a green cell, the corresponding new solution would automatically be updated
• This implies that there should not be any typed-in numbers in the yellow boxes on your submission, and in general, green cells should not be changed
• There is no need to compute any calculations outside of the yellow and green boxes provided. If you do so, label them.

Note that SIPMATH creates 2 additional tabs in EXCEL. This is normal -- do not worry about them, and include them in your submission.

PROJECT DETAILS: A company is considering purchasing new equipment

• New equipment costs $150,000
• The firm will borrow $120,000 and finance (fund themselves) the remaining $30,000
• The firm will repay the loan in 5 equal annual amounts at a 10% annual rate compounded annually
• Depreciate the equipment using a 7-year MACRS, with the understanding that you will sell the equipment for Salvage at the end of 5 years
• Salvage value at the end of 5 years is $30,000 in today's dollars
• Additional expected revenue (actual dollars) as a result of purchasing this equipment would be $50,000 in year 1, going up by $2,000 each of the 5 years
• For the simulation, you will use a Normal distribution for these cash flows, with a standard deviation of $5,000 for each year (actual dollars)
• Year 1 will have a mean of $50,000 and std deviation of $5,000; year 2 will have a mean of $52,000 and std deviation of $5,000, and so on. These cash flows are in actual dollars
• Run the simulation 100,000 times
• O&M costs are expected to be $15,000 (actual dollars) per year
• Federal tax rate is 21% (assume 0% state tax rate)
• An initial Working Capital investment of $16,000 is required, and the following years will require additional Working Capital at the general inflation rate (4%). Working Capital will be recovered at the end of the project.
• Your MARR (or market interest rate i) is 11%. Note that this rate is different from the 10% rate you are charged for borrowing

NOTE: You can complete this assignment using actual dollar analysis only (using the cells C33:H33). In other words, you DO NOT have to fill in cells C34:H34 or cell C37 if you don't want to. They are just provided as a way to double-check your solution, should you desire.

• ANSWER THE QUESTIONS ON THE BOTTOM OF THE "TEMPLATE -- DISCRETE SOLUTION" TAB BY WRITING DIRECTLY IN THE SPACE PROVIDED.
• LEAVE THE EXCEL SPREADSHEET THE WAY YOU HAD IT AFTER RUNNING THE SIMULATION.

QUESTIONS

1. Using the point estimates for revenue (not the simulation results), what is the NPV of the project?
2. Do you recommend this project based on the NPV using the point estimates? Why or why not? Answer in 1 sentence or less.
3. What is the IRR of the project using point estimates (not the simulation results)
4. Assume revenues for years 1-4 remain at $50k, $52k, $54k and $56k. What revenue is required in year 5 such that you are indifferent to the project?
5. Run the simulation 100,000 times. What is the probability of a positive NPV? (calculate it on the SIPMATH sheet and write down here)