Vanderbilt University is currently evaluating a football facility project. This facility renovation would be the biggest renovation to their stadium in nearly 40 years. The Chancellor of the university said the plan shows Vanderbilt's commitment to athletes to give them the tools and environment to grow, "Bold action requires inspiration and imagination, and I am pleased to see the spirit underlying Vandy United come to life in these renderings." In addition the McGugin Center will be renovated with square footage expanded to house football operations, a new academic center, football locker room and lounge area, weight room and a doubled training room for all sports. This includes a new indoor practice facility adjacent to the building with a 120-yard artificial turf field. In total, it is expected these renovations will cost approximately $325 million dollars, and will increase stadium capacity to 56,000. The hope is these renovations will lead to an improved team and greater community support, leading to an improvement to the financial situation of the team in the long run. That is, the team felt that the stadium renovation would ultimately lead to a positive return on their investment.

As a financial analyst, you have been asked by Vanderbilt to examine their 2022 numbers and conduct a sensitivity analysis, projecting out from 2023-2032 (hint: use 2022 as year zero numbers). Once your analysis is complete, the team expects to receive a copy of your calculations, as well as a brief write up summarizing your findings as an excel document.

Here is the information you will need for your analysis:

2022 Average attendance: 34,258

2022 Average ticket prices: $65

2022 Average spending per fan per game: $17.50

Home games per year: 7

Optimistic projection: Attendance will be at arena capacity (56,000) in 2023, and then stay at capacity each year for the next 10 years. Average ticket prices will increase by 10% each year, and per game average fan spending will increase by 4% each year. On the expense side, per game costs will amount to $652,500 and increase by 0.35% each year (hint: this would equate to a growth rate of 1.0035). For discounting, use a rate of 5.25%.

Pessimistic projection: The attendance will start at the 2022 average of 34,258, but will decline each year by one percent. Ticket prices will also start at the 2022 average of $65, but will decline in average prices by 2% each year. Overall fan spending is expected to remain at the 2022 average of $17.50, without an increase. The discount rate during this time period is 6%. Additionally, the expenses per home game are expected to be $700,000 and increase by 0.35% each year.

Using the above information, (1) calculate the total yearly revenues for the team for the next ten years in each scenario. Next, after taking into account the per game expenses, (2) project the expected net cash flows for each scenario (total revenues - total costs). Then, using these projected net cash flows, calculate the following for each of the two scenarios:

1. Non-Adjusted payback period
2. Discounted payback period
3. Net Present value
4. Internal Rate of Return
5. Modified Internal Rate of Return