I am trying to figure out how to utilize the given inflation rate in this project and am looking for some assistance. The given information is provided in the Stock Project pdf and the Additional Info png. My current work is provided in the excel workbook.

\fIE 305 Fall 2015 Stock Project For this project you will conduct a detailed analysis of a personal investment choice. Assume that you have $10,000 to invest, and that you invest this $10,000 on January 1, 2016. Assume also that you will not need this money before 3 years from now. Your goal is to decide whether to buy stock in Amazon or in Duke Energy Corporation (a large investorowned electric utility). For the purpose of this project, you must choose one or the other (or neither) - you cannot diversify the investment of this $10,000. Because you are adopting a value-based buy-andhold strategy, if you buy stock in one of these companies now, you will hold it for the next three years. The following provides more detailed information and requirements for the project. The project is due BY NOON on Friday, December 4 th. This project is a group project, and will consist of 2-3 students per group. Groups can consist of students from different sections in the course. You should base your investment decision on publicly available financial data from both companies, including stock prices, from January 1, 2012 through October 31, 2015. Use the following links to determine historical prices: o https://beta.finance.yahoo.com/quote/DUK o https://beta.finance.yahoo.com/quote/AMZN When determining historical prices, use the links above and select \"Historical Data.\" Set Date Range should be monthly, and the dates should range from 1/31/2012 to 10/31/2015. Once you hit \"Apply,\" you'll see historical prices (and dividends, if any) pop up for each stock. You will use CLOSING PRICES (not Open, High or Low) and you will use a quarterly analysis approach (meaning you are interested in the closing stock prices at the end of each quarter). So, the first closing price of interest will be March 31st, 2012; the second will be June 30th, 2012; and so on, with the last closing price of interest occurring on September 30th, 2015. That means there are a total of 15 prices over 15 quarters that you will use to project future stock prices. o To check your work, closing stock price for Duke on 6/30/15 was $74.22 per share, and for Amazon was $536.15 per share. You will use the 15 data points for each stock, and apply trend models to project future stock prices, and to estimate future dividends (if any) based on historical data. A regression equation using EXCEL should be used to project future stock prices. You will find it helpful to approach this project from a quarterly standpoint. Use EXCEL, and have time periods ranging from 0-12 across the top of columns. Time period 0 is when you invest your $10,000; at the end of time period 12 is when you will sell your stock for a projected price that you will determine. You may earn dividends during this 3-year period as well, depending on historical analysis of each stock. Assume the average annual inflation rate is 1.5% and will remain at 1.5% for the next 3 years. Assume your inflation-free MARR' is 6%. Note that the applicable interest rate to be used in the project is compounded quarterly. Assume your federal income tax rate is 35% and state income tax rate is 5%. NOTE: For individuals, long-term capital gains and qualified dividends (those received after one year) are taxed by the federal government at 15%. For the first year though, they are taxed as ordinary income at 35%. The state of Iowa does not provide any dividend or capital gains tax breaks. For purposes of this project, it is acceptable to purchase fractions of shares of stock, so that your entire $10,000 is invested. Dividends are paid on a \"per share\" basis. They will not be reinvested but will be considered revenue for the quarter they are earned. Project Deliverables. In the next week or two, you will be provided with a list of questions that can be answered once your analysis is complete. You can expect those questions will include: A recommendation for which stock to invest in (if any) and why. Projected stock prices of Amazon and Duke on December 31st, 2018. Estimates for projected dividends received from Amazon and Duke each year. Provide after-tax cash flows in years 2016, 2017 and 2018. IRR for both Duke and Amazon Other Information: Please use the discussion board for general questions. 0 Nov 1 2 Feb 3 May TIME PERIOD (QUARTER) 5 6 7 Feb May Aug 4 Aug Nov 8 9 Nov 10 May Feb 11 Aug 12 Nov AMAZON price (buy) 521.78 invest $ 10,000 shares 19.16 price (sell) 1059.19 Projected dividends Buy & Sell Dividends Taxes NET 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 $ (10,000) $ (10,000) 0.83 0.83 0.85 0.85 0.85 0.85 0.87 0.87 0.87 0.87 0.89 $ (10,000) 106.70 40.81 $ (10,000) 65.89 106.70 40.81 65.89 Combined Tax rate year 1 Combined Tax rate year 2&3 109.27 41.80 67.47 38.2500% 19.2500% 109.27 109.27 41.80 41.80 67.47 67.47 266.72 109.27 41.80 67.47 111.84 42.78 69.06 111.84 111.84 42.78 42.78 69.06 69.06 273.07 111.84 42.78 69.06 114.41 43.76 70.65 Year 2 Year 3 Federal Income Tax = State Income Tax = Inflation Free MARR = Avg Annual Inflation = Tax rate (gains) State Fed DUKE price (buy) 77.79 invest $ 10,000 shares 128.55 price (sell) 81.06 Projected dividends Buy & Sell Dividends Taxes NET NET(YEARLY) Year 1 5% 5% 5% 35% 15% 15% 1.50% 1.50% 1.50% 6% 6% 6% 21.60 22.11 22.63 143.62 63.02 64.48 165.22 85.13 87.11 Inflation MARR' State Tax 0 Fed Tax $ 20,912.82 Total Tax 0 ANALYSES $ 2,100.72 $ 18,812.10 Rate of Return (quarter) Amazon Duke Rate of Return (annual) Amazon Duke Inflation (annual) 0.89 IRR' 10,736.04 Amazon 114.41 Duke 43.76 70.65 279.42 819.22 1 2 3 64.29 67.78 65.69 DUK 231.90 233.30 232.89 AMZN 35% 5% 6% 1.50% Interest rate used should Compounded quarterly Stock Historical Quarterly Trend 700.00 600.00 500.00 f(x) = 203.0065572292 exp( 0.0590057222 x ) R = 0.7922619832 400.00 300.00 200.00 100.00 0.00 f(x) = 63.4124218655 x^0.0736863774 R = 0.5193448259 1 2 3 4 5 DUK 4 68.74 5 75.20 265.50 6 71.00 253.81 301.22 6 7 8 Power (DUK) 7 71.73 364.03 8 70.62 358.69 9 AMZN 9 74.49 304.13 10 72.13 312.99 10 uld ly nd 10 11 12 13 14 15 Exponential (AMZN) 11 82.15 305.46 12 87.14 354.53 13 77.57 421.78 14 74.22 536.15 15 71.47 625.90 Start Value 16 77.79 521.78 End Value 28 81.06 1059.19