(Solve in Excel and Show all Work)

4. Sega, Inc. is considering expanding its operations into computer-based lacrosse games. Sega feels that there is a 3-year life associated with the project, and it will initially involve an investment of $100,000. It also believes that there is a 60% chance of success and a cash flow of $100,000 in year 1 and a 40% chance of failure and a $10,000 cash flow in year 1. If the project fails in year 1, there is a 60% chance that it will produce cash flows of only $10,000 in years 2 and 3. There is also a 40% chance that it will really fail and Sega will earn nothing in year 2 and get out of this line of business, with the project terminating and no cash flow occurring in year 3. If conversely, this project succeeds in the first year, then cash flows in the second year are expected to be $200,000, $175,000, or $150,000 with probabilities of 30%, 50%, and 20%, respectively. Finally, the cash flows in the third and final year of operation are expected to be either $30,000 more or $20,000 less than they were in year 2, with an equal chance of occurrence.

a. Construct the decision tree representing the possible outcomes.

b. Determine the joint probability of each possible sequence of events. Show your work.

c. Determine the IRR of each possible sequence of events.

d. What is the expected IRR for this project? Show your work.

e. What is the standard deviation of the IRR for this project? Show your work.