(LO 9-6) This exhibit provides an example of a sensitivity analysis that might be performed by financial analysts. In their estimation, the two most important inputs to determine the estimated stock price is the cost of capital and sales growth in the future. To perform sensitivity analysis, they vary sales growth from 1 to 10 percent (as shown in the columns) and the cost of capital from 4 to 14 percent (as shown in the rows). The colored columns are the estimated stock prices based on the changing input parameters. When combined with conditional formatting, the estimated stock price can also be evaluated in a visual way.

Required:
1. As the cost of capital increases, what happens to the estimated stock price?
2. As the sales growth decreases, what happens to the estimated stock price?
3. If the sales growth is 4 percent, and the cost of capital is 14 percent, what is the estimated stock price?
4. If the sales growth is 10 percent, and the cost of capital is 10 percent, what is the estimated stock price?
5. Which is more impactful on stock price, a 1 percent increase in sales growth or a 1 percent increase in cost of capital?
6. How does conditional formatting help you to visualize the impact of these two important inputs on estimated stock price?

Horizon Period Sales Growth 1% 2% 3% 4% 5% 6% 7% 4% 156.64 161.90 167.31 172.87 178.60 184.48 190.54 8% 196.76 9% 10% 203.15 209.71 Cost of Capital 5% 118.33 122.07 125.91 129.86 6% 95.35 98.18 101.09 104.08 133.93 138.11 142.41 107.16 110.33 113.58 146.82 151.36 156.02 116.92 120.35 123.87 7% 80.03 82.26 84.56 86.92 89.34 91.83 94.40 97.03 99.73 102.50 8% 69.09 70.90 72.76 74.67 76.63 78.65 80.73 82.86 85.04 87.29 9% 60.88 62.38 63.92 65.50 67.12 68.79 70.50 72.26 74.06 75.92 10% 54.50 55.76 57.05 58.37 59.73 61.13 62.57 64.04 65.55 67.10 11% 49.40 50.46 51.56 52.68 53.84 55.02 56.24 57.49 58.77 60.09 12% 45.22 46.14 47.07 48.04 49.02 50.04 51.08 52.15 53.24 54.37 13% 41.75 42.53 43.34 44.17 45.02 45.89 46.79 47.71 48.66 49.62 14% 38.80 39.49 40.19 40.90 41.64 42.40 43.18 43.97 44.79 45.63