Each student will develop a 15% spreadsheet to analyze the target price of a firm. This spreadsheet will estimate a target buy price for a firm in order to yield an average annual rate of return of 15%. Use the above example template and check figures to complete this spreadsheet. The Nike numbers are provided as check figures so that you can program your spreadsheet correctly. Once you have programmed your spreadsheet correctly, you may use your spreadsheet for other firms. I am interested in the formulas that you develop for the blue cells. If your formulas are correct, you should get the same results (blue cells) as the Nike check figures.

D D E B 1 2 Nike NKE 3 4 Price $106.43 5 EPS $1.60 6 Current P/E 66.52 7 Current Year 2020 8 Projected Growth 16.0% 9 Average P/E 24 10 Dividend Payout 23.0% 11 Target Price $44.12 12 E(Rate of Return) 5.3% 13 Price