Tennis Specialty Pro-Shop Analysis	Option 1	Option 2	Cumulative total option 1	Cumulative total option 2	Which option do your recommend, 1 or 2 based on NPV, IRR, RI and Payback results? Type your answer in the space below:
Year	Cash Flow	Cash Flow
Initial Investment - year 0	$ (10,200,000)	$ (4,000,000)
1	900,000	695,000
2	2,500,000	1,950,000
3	3,900,000	2,500,000
4	5,500,000	2,800,000
5	6,000,000	2,900,000
6	6,000,000	3,000,000
			Your embedded (floating) chart for Option 1 NPV below this line:
Recommended Hurdle Rate	24%
Hurdle Rate Sensitivity:	Option 1 - NPV	Option 2 - NPV
Internal Rate of Return
ROI
Payback

The Tennis Equipment division is considering starting its fin line of specialy tent, shops in selected areas of the country. The endeavor would require significant upfith, capital, but according to the marketing group, it could reap substantial rewards, Tut options are under consideration, as follows: Option 1- Build three new shops on empty lots in three different locations, all or which are close to tennis facilities. Option 2- Establish three shops located in existing tennis club facilities. Table 6.5 summarizes the projected cash flows, excluding any effects of financing, for the two options over a six-year period.

The investment for the first option is the most significant, requiring the construction of new buildings, but has the lowest operating costs because it involves no significant rent or royalty-sharing agreements, as does the second option. The first option also has a higher earning potential with a larger customer base. Your task in these steps is to complete the financial analysis for each option. Complete the following: 1. Open the workbook named Tennis.xlsx located in the Chapter 6 folder, and then save the file as Tennis Option Analysis.xlsx. 2. Create a hurdle rate sensitivity table in cells Al5:C25 that calculates the NPV for each option between +/- 5% of the hurdle rate. Be certain that if the hurdle rate in cell B13 is modified, the sensitivity table will automatically update to reflect the changed rate. 3. In cells B26:C26, calculate the corresponding internal rate of return for each option. Format the cells as percentages. 4. Calculate the return on investment for each option in cells B28:C28. Format the cells as percentages.

5,. In cells D3.0%, calculate the cumulative income tincluding the initial investment, Chapter 6 for each year in option & (remember that option I must include year 0, Write the ormula so that it can be copied both down the column and across the row to caleute The cumulative income for option 2. Provide appropriate labels in cells D2 F2. 6. In cells B29, C29, enter the payback period (that is, the year number in which the investment is recouped) for each option. Optional challenge: Write the necessary formulas to automatically calculate the vear number in which the cumulative total income exceeds SO. 4. Highlight in yellow the income data for the option you would recommend. To the right of the cash flow values, include a sentence about why you are making this recommendation and highlight it in yellow. 8. Create an XY Scatter chart for your selected option that displays the functional rela- rionship of the hurdle rates to the NPV values. Size the chart, and place it next to the hurdle rate analysis. Give the chart an appropriate title and name the axes accordingly. TROUBLESHOOTING: To modify the XY Scatter chart to include chart titles and axis titles, use the Chart Elements button. Select the appropriate propertv vou want to include. You can modify the chart elements using the CHART TOOLS FORMAT tab. 9. Format your worksheet as needed to convey the information clearly and neatly. 10. Save and close the Tennis Option Analysis.xlsx workbook.