I need someone to look over my sheet 6.BOPM calculations... something isn't right and I can't figure out where I am going wrong. All the work is done, just need a proofread.

Analyzing Historical Market Risk and Valuing a Call Option for Bob Evans. We will continue with the same company from the first Excel project, Bob Evans Farms Inc. (BOBE) Follow all directions for all six steps below and answer all questions. Type all answers in the spaces provided on this instructions tab. Navigate to Yahoo! Finance to begin gathering data. Step 1: Gather the most recent 61 monthly stock prices for the Stock Market (S&P 500 ETF) and BOBE. a. Start by entering the ticker 'SPY' on the home page on yahoo finance in the 'quote lookup' window. Then Click on Historical Data. b. Change the time period to September 1, 2011 through September 1, 2016 to capture 61 months of data. Set the frequency to 'monthly' and hit Apply. c. Click on 'Download Data', and open the .csv file. d. Copy the date column from your downloaded yahoo .csv into the first column (Column B) of the "2.Calc. Returns" worksheet. Start in cell B3. e. Copy the Adjusted Prices from the yahoo data and paste this data into Column C of the "2.Calc. Returns" worksheet. Start in C3. f. Download 61 months of data for BOBE and copy the data into Column D of the "2.Calc. Returns" worksheet. You need 61 observations to calculate 60 returns. The dates of the S&P 500 index data (SPY) and BOBE MUST BE THE SAME. Step 2: Work on the "2.Calc Returns" worksheet to find the various holding period returns. The price series can be converted into a return series by calculating Return t = (Pt - Pt-1 + Div)/Pt-1. However, when the information is downloaded using the Yahoo! Finance .csv file, the prices are automatically adjusted to include dividends. Hence the name, Adjusted Price. Therefore, it is only necessary to calculate the change in Price divided by the beginning monthly Price to calculate returns. Return t = (Pt - Pt-1)/Pt-1 a. Open the "2. Calc. Returns" tab of this spreadsheet below. b. Calculate all 60 Holding Period Returns for both SPY and BOBE by creating formulas in columns 'E' & 'F' Step 3: Create and interpret a Line Graph of the returns. Refer to the instructions on the "2. Calc. Returns" tab. Refer to the "3. Line Graph" worksheet to answer the following questions. Helpful Hint: Place cursor on a point in the graph to display values. a. In what month was the largest positive monthly return for the company in the past five years? Sep-13 b. What was the largest positive monthly return? 16.81% c. In what month was the largest negative monthly return for the company in the past five years? Mar-15 d. What was the largest negative monthly return? -20.50% e. Based on the line graph that you created, what observations can you make regarding the relationship between BOBE and the market over the past five years? Based on the line graph of SPY vs BOBE I can see that there is a positive correlation between these historical returns, meaning that SPY is a good benchmark for BOBE and that BOBE's stock price will react in similar ways to the changes in SPY index. BOBE has less volitility than SPY, smaller swings, and I also see a slight lag in the reaction of BOBE to the shifts in SPY. In other words, if the SPY becomes bearish I will expect that BOBE will soon follow in a bearish trend and vice versa if the trend becomes bullish. Step 4: Calculate the Variance and Standard Deviation for both 'SPY' and 'BOBE'. Refer to the instructions on the "4. Calc. Stats" tab. I have completed this step for you. You will use the standard deviation calculated in this step in order to value the call option in step 6. Although I have already coded this step, you should look over the formulas and steps that were used as an example of a simple way thet you can calculate these risk measures. This follows for step 5 as well: Step 5: Compare the calculated Beta with the reported Beta on Yahoo! Finance. Covariance is a statistical measure that caculates how two series move in relation to each other. The calculations for covariance are shown in column H of the '5.Calc Beta" spreadsheet. I have done all of the calculations for you in this tab, you need to interpret it... Answer the following questions referring to the "5.Calc. Beta" worksheet. a. What can you tell about 'BOBE' based upon its calculated beta? BOBE has a beta of 0.59, means that BOBE is less volitile than the market by 41%. This means that BOBE will be expected to underperform the SPY index by 41% in up markets and outperform SPY index by 41% during down markets. b. Look up BOBE's Beta on Yahoo!Finance using the following steps: 1. Enter the ticker symbol under quote lookup. 2. Click on Key Statistics under COMPANY on the left-hand side of the web page. The beta is on the right-hand side under Trading Information What is the reported Beta on Yahoo!? 0.36 What is the calculated Beta from this spreadsheet? 0.59 What factors can lead to these two betas being different? (you may need to do a little research for this) Many factors can contribute to beta calculations being different. One difference is utilizing monthly vs weekly closing prices. The major difference is that Yahoo calculates their beta based on different time frames, we are using 61mos/5yrs vs 36mos/3yrs. The next difference may come from how their monthly periods are calculated. Is it 36 months ago from today's date or the 36 months from the last completed month? Another question, are they using arithmetic returns or the log returns? Yahoo's help site only advises of a difference in time period, 36 months, than our 61 month period calculation. Step 6: Calculate the premium on a BOBE 30 Call using the Two-State Binomial Options Pricing Model and the Black-Scholes Model **All formulas that you need for this step are included in the lesson 4 material in Angel** a. On the "6.BOPM" tab, you will calculate the premium on a six-month Bob Evans call option with a strike price of $35 using both the risk-free approach and the risk-neutral approach under both continuous AND annual compounding I have entered the current spot price on BOBE as the closing price on Tuesday, September 6, 2016 I have also entered the strike price and the borrowing rate You must fill in all cells highlighted in yellow, using formulas where appropriate b. On the "7.BSOPM" tab, you will calculate the premium on a six-month Bob Evans call option with a strike price of $35 using the Black-Scholes Model I have entered the current spot price on BOBE as the closing price on Tuesday, September 6, 2016 I have also entered the strike price and the borrowing rate You must fill in all cells highlighted in yellow, using formulas where appropriate **Hint: Instead of using the Z-Tables for N(d1) & N(d2), Excel has a function for this, NORMSDIST. Excel is more accurate because it does not round.** c. Look up the premium on a BOBE 35 call with an expiration of March 17, 2017 on Yahoo!Finance using the following steps: 1. Enter the ticker symbol under quote lookup. 2. Click on Options. 3. Directly above the option quotes is a drop-down menu with dates. Click on March 17, 2017 The BOBE 35 calls are listed underneath The 'last' price is the price at which the last trade was made The 'bid' price is the highest price that a buyer is willing to pay The 'ask' price is the lowest price that a seller is wiling to accept **Keep in mind that the trading volume of options is very small compared to common shares** What is the 'last' price reported on the 35 calls? 4.24 What is the calculated premium from this spreadsheet using the risk-free aproach? 5.702230469 What is the calculated premium from this spreadsheet using the risk-neutral aproach? 4.5531375221 What is the calculated premium from this spreadsheet using the Black-Scholes Model? 5.1817346816 What factors contribute to the difference between the listed premium and the calculated premiums? (you may need to do a little research for this) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 Date 9/1/2016 8/1/2016 7/1/2016 6/1/2016 5/2/2016 4/1/2016 3/1/2016 2/1/2016 1/4/2016 12/1/2015 11/2/2015 10/1/2015 9/1/2015 8/3/2015 7/1/2015 6/1/2015 5/1/2015 4/1/2015 3/2/2015 2/2/2015 1/2/2015 12/1/2014 11/3/2014 10/1/2014 9/2/2014 8/1/2014 7/1/2014 6/2/2014 5/1/2014 4/1/2014 3/3/2014 2/3/2014 1/2/2014 12/2/2013 11/1/2013 10/1/2013 9/3/2013 8/1/2013 7/1/2013 6/3/2013 5/1/2013 4/1/2013 3/1/2013 2/1/2013 1/2/2013 12/3/2012 11/1/2012 10/1/2012 9/4/2012 8/1/2012 7/2/2012 6/1/2012 5/1/2012 4/2/2012 3/1/2012 2/1/2012 1/3/2012 12/1/2011 11/1/2011 10/3/2011 9/1/2011 Holding Period Return (Market) SPY 0.00% 0.12% 3.65% 0.35% 1.70% 0.39% 6.73% -0.08% -4.98% -1.73% 0.37% 8.51% -2.55% -6.10% 2.26% -2.03% 1.29% 0.98% -1.57% 5.62% -2.96% -0.25% 2.75% 2.36% -1.38% 3.95% -1.34% 2.06% 2.32% 0.70% 0.83% 4.55% -3.52% 2.59% 2.96% 4.63% 3.16% -3.00% 5.17% -1.33% 2.36% 1.92% 3.80% 1.28% 5.12% 0.89% 0.57% -1.82% 2.54% 2.51% 1.18% 4.06% -6.01% -0.67% 3.22% 4.34% 4.64% 1.04% -0.41% 10.91% Holding Period Return (BOBE) BOBE -2.44% 12.45% -3.08% -14.27% -2.04% -2.46% 8.81% 5.66% 5.38% -2.51% -7.12% -0.18% -3.40% -9.48% -2.21% 11.15% 7.48% -7.00% -20.50% 3.92% 10.14% -5.85% 11.92% 3.19% 9.82% -8.61% -5.07% 12.04% -4.06% -6.32% -2.67% 2.97% -0.67% -8.99% -2.09% -0.31% 16.81% -2.93% 8.17% 1.78% 7.14% 1.69% 5.43% -8.06% 10.12% 6.66% -0.22% -2.71% -0.53% 2.83% -4.18% -0.84% 6.67% 1.38% 3.20% 4.22% 5.28% 0.18% 2.57% 15.36% 2.Calc. Returns Instructions for Inputing Data 1. First, paste the dates from the 61 most recent monthly returns and the 'SPY' adjusted close prices from Yahoo Finance. 2. Second, paste the adjusted prices from the 'BOBE' data. 3. Compute the Holding Period Returns for both the market and 'BOBE'. NOTE: The dates of the company information in column D and the S&P 500 data must match. Instructions for the Graphing Step 1. Hide columns C & D by highlighting both columns, then right click and select "hide" 2. Highlight columns B, E, & F. Begin with Row 2 to capture the labels. 3. With the rows highlighted, go to the "Insert" tab at the top of your Excel workbook and select a "Line Chart" in the "Charts" section 4. Select the "Line with Markers" chart 5. Select the chart that you have just created and CUT and paste it onto the tab labeled "3.Line Graph." Then enlarge the graph to be large enough for you to better see the differences between to two lines 6. Label the graph "SPY vs. BOBE" 0.2 0.15 0.1 0.05 0 -0.05 -0.1 -0.15 -0.2 -0.25 Holding Period SPY vs. BOBE Holding Period Return (Market) SPY Holding Period Return (BOBE) BOBE 4.Calc. Stats. Date Aug-16 Jul-16 Jun-16 May-16 Apr-16 Mar-16 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Apr-15 Mar-15 Feb-15 Jan-15 Dec-14 Nov-14 Oct-14 Sep-14 Aug-14 Jul-14 Jun-14 May-14 Apr-14 Mar-14 Feb-14 Jan-14 Dec-13 Nov-13 Oct-13 Sep-13 Aug-13 Jul-13 Jun-13 May-13 Apr-13 Mar-13 Feb-13 Jan-13 Dec-12 Nov-12 Oct-12 Sep-12 Aug-12 Jul-12 Jun-12 May-12 Apr-12 Mar-12 Feb-12 Jan-12 Dec-11 Nov-11 Oct-11 Sep-11 Sums: SPY Returns 0.00% 0.12% 3.65% 0.35% 1.70% 0.39% 6.73% -0.08% -4.98% -1.73% 0.37% 8.51% -2.55% -6.10% 2.26% -2.03% 1.29% 0.98% -1.57% 5.62% -2.96% -0.25% 2.75% 2.36% -1.38% 3.95% -1.34% 2.06% 2.32% 0.70% 0.83% 4.55% -3.52% 2.59% 2.96% 4.63% 3.16% -3.00% 5.17% -1.33% 2.36% 1.92% 3.80% 1.28% 5.12% 0.89% 0.57% -1.82% 2.54% 2.51% 1.18% 4.06% -6.01% -0.67% 3.22% 4.34% 4.64% 1.04% -0.41% 10.91% 78.62% Monthly E(R): Variance: Standard Deviation: BOBE Returns -2.44% 12.45% -3.08% -14.27% -2.04% -2.46% 8.81% 5.66% 5.38% -2.51% -7.12% -0.18% -3.40% -9.48% -2.21% 11.15% 7.48% -7.00% -20.50% 3.92% 10.14% -5.85% 11.92% 3.19% 9.82% -8.61% -5.07% 12.04% -4.06% -6.32% -2.67% 2.97% -0.67% -8.99% -2.09% -0.31% 16.81% -2.93% 8.17% 1.78% 7.14% 1.69% 5.43% -8.06% 10.12% 6.66% -0.22% -2.71% -0.53% 2.83% -4.18% -0.84% 6.67% 1.38% 3.20% 4.22% 5.28% 0.18% 2.57% 15.36% 63.60% Variance SPY (RM - E(RM)) (RM - E(RM))2 -0.01306 0.00017 -0.01191 0.00014 0.02337 0.00055 -0.00963 0.00009 0.00391 0.00002 -0.00916 0.00008 0.05416 0.00293 -0.01393 0.00019 -0.06289 0.00396 -0.03039 0.00092 -0.00945 0.00009 0.07196 0.00518 -0.03862 0.00149 -0.07405 0.00548 0.00949 0.00009 -0.03342 0.00112 -0.00025 0.00000 -0.00327 0.00001 -0.02881 0.00083 0.04310 0.00186 -0.04273 0.00183 -0.01564 0.00024 0.01437 0.00021 0.01045 0.00011 -0.02690 0.00072 0.02636 0.00069 -0.02654 0.00070 0.00754 0.00006 0.01010 0.00010 -0.00615 0.00004 -0.00481 0.00002 0.03241 0.00105 -0.04835 0.00234 0.01282 0.00016 0.01653 0.00027 0.03320 0.00110 0.01854 0.00034 -0.04310 0.00186 0.03857 0.00149 -0.02645 0.00070 0.01051 0.00011 0.00611 0.00004 0.02487 0.00062 -0.00035 0.00000 0.03809 0.00145 -0.00417 0.00002 -0.00744 0.00006 -0.03130 0.00098 0.01225 0.00015 0.01195 0.00014 -0.00127 0.00000 0.02748 0.00075 -0.07316 0.00535 -0.01978 0.00039 0.01906 0.00036 0.03030 0.00092 0.03327 0.00111 -0.00266 0.00001 -0.01717 0.00029 0.09604 0.00922 0.06123 SPY 1.31% 0.00104 3.22% Variance BOBE (RC - E(RC)) (RC - E(RC))2 -0.03499 0.00122 0.11393 0.01298 -0.04143 0.00172 -0.15334 0.02351 -0.03102 0.00096 -0.03523 0.00124 0.07749 0.00600 0.04604 0.00212 0.04320 0.00187 -0.03569 0.00127 -0.08179 0.00669 -0.01245 0.00015 -0.04463 0.00199 -0.10535 0.01110 -0.03274 0.00107 0.10087 0.01018 0.06420 0.00412 -0.08064 0.00650 -0.21559 0.04648 0.02861 0.00082 0.09081 0.00825 -0.06910 0.00477 0.10858 0.01179 0.02130 0.00045 0.08759 0.00767 -0.09669 0.00935 -0.06135 0.00376 0.10984 0.01206 -0.05117 0.00262 -0.07376 0.00544 -0.03730 0.00139 0.01905 0.00036 -0.01732 0.00030 -0.10054 0.01011 -0.03152 0.00099 -0.01374 0.00019 0.15746 0.02479 -0.03985 0.00159 0.07114 0.00506 0.00716 0.00005 0.06080 0.00370 0.00629 0.00004 0.04372 0.00191 -0.09124 0.00833 0.09064 0.00822 0.05600 0.00314 -0.01276 0.00016 -0.03769 0.00142 -0.01594 0.00025 0.01773 0.00031 -0.05239 0.00274 -0.01899 0.00036 0.05614 0.00315 0.00319 0.00001 0.02141 0.00046 0.03160 0.00100 0.04217 0.00178 -0.00881 0.00008 0.01514 0.00023 0.14298 0.02044 0.31075 BOBE 1.06% 0.00527 7.26% Instructions 1. Calculate the monthly expected returns for both 'SPY' and 'BOBE' (use the box at the bottom of this page) 2. Calculate columns E, F, H, & I to find the variance of 'SPY' and of 'BOBE' 3. Using the variance, calculate the standard deviation for both 'SPY' and 'BOBE' Date Aug-16 Jul-16 Jun-16 May-16 Apr-16 Mar-16 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Apr-15 Mar-15 Feb-15 Jan-15 Dec-14 Nov-14 Oct-14 Sep-14 Aug-14 Jul-14 Jun-14 May-14 Apr-14 Mar-14 Feb-14 Jan-14 Dec-13 Nov-13 Oct-13 Sep-13 Aug-13 Jul-13 Jun-13 May-13 Apr-13 Mar-13 Feb-13 Jan-13 Dec-12 Nov-12 Oct-12 Sep-12 Aug-12 Jul-12 Jun-12 May-12 Apr-12 Mar-12 Feb-12 Jan-12 Dec-11 Nov-11 Oct-11 Sep-11 Sums: SPY Returns 0.00% 0.12% 3.65% 0.35% 1.70% 0.39% 6.73% -0.08% -4.98% -1.73% 0.37% 8.51% -2.55% -6.10% 2.26% -2.03% 1.29% 0.98% -1.57% 5.62% -2.96% -0.25% 2.75% 2.36% -1.38% 3.95% -1.34% 2.06% 2.32% 0.70% 0.83% 4.55% -3.52% 2.59% 2.96% 4.63% 3.16% -3.00% 5.17% -1.33% 2.36% 1.92% 3.80% 1.28% 5.12% 0.89% 0.57% -1.82% 2.54% 2.51% 1.18% 4.06% -6.01% -0.67% 3.22% 4.34% 4.64% 1.04% -0.41% 10.91% 78.62% E(R): Variance and Covariance: Standard Deviation: BOBE Returns -2.44% 12.45% -3.08% -14.27% -2.04% -2.46% 8.81% 5.66% 5.38% -2.51% -7.12% -0.18% -3.40% -9.48% -2.21% 11.15% 7.48% -7.00% -20.50% 3.92% 10.14% -5.85% 11.92% 3.19% 9.82% -8.61% -5.07% 12.04% -4.06% -6.32% -2.67% 2.97% -0.67% -8.99% -2.09% -0.31% 16.81% -2.93% 8.17% 1.78% 7.14% 1.69% 5.43% -8.06% 10.12% 6.66% -0.22% -2.71% -0.53% 2.83% -4.18% -0.84% 6.67% 1.38% 3.20% 4.22% 5.28% 0.18% 2.57% 15.36% 63.60% Distance from Mean (RM - E(RM)) -0.01306 -0.01191 0.02337 -0.00963 0.00391 -0.00916 0.05416 -0.01393 -0.06289 -0.03039 -0.00945 0.07196 -0.03862 -0.07405 0.00949 -0.03342 -0.00025 -0.00327 -0.02881 0.04310 -0.04273 -0.01564 0.01437 0.01045 -0.02690 0.02636 -0.02654 0.00754 0.01010 -0.00615 -0.00481 0.03241 -0.04835 0.01282 0.01653 0.03320 0.01854 -0.04310 0.03857 -0.02645 0.01051 0.00611 0.02487 -0.00035 0.03809 -0.00417 -0.00744 -0.03130 0.01225 0.01195 -0.00127 0.02748 -0.07316 -0.01978 0.01906 0.03030 0.03327 -0.00266 -0.01717 0.09604 SPY 0.01310 0.00104 0.03221 Calculating Beta: Beta = Cov(BOBE,SPY)/Var(SPY) = 5.Calc. Beta Distance from Mean (RC - E(RC)) -0.03499 0.11393 -0.04143 -0.15334 -0.03102 -0.03523 0.07749 0.04604 0.04320 -0.03569 -0.08179 -0.01245 -0.04463 -0.10535 -0.03274 0.10087 0.06420 -0.08064 -0.21559 0.02861 0.09081 -0.06910 0.10858 0.02130 0.08759 -0.09669 -0.06135 0.10984 -0.05117 -0.07376 -0.03730 0.01905 -0.01732 -0.10054 -0.03152 -0.01374 0.15746 -0.03985 0.07114 0.00716 0.06080 0.00629 0.04372 -0.09124 0.09064 0.05600 -0.01276 -0.03769 -0.01594 0.01773 -0.05239 -0.01899 0.05614 0.00319 0.02141 0.03160 0.04217 -0.00881 0.01514 0.14298 Covariance(SPY,BOBE) (RM - E(RM))((RC - E(RC)) 0.00046 -0.00136 -0.00097 0.00148 -0.00012 0.00032 0.00420 -0.00064 -0.00272 0.00108 0.00077 -0.00090 0.00172 0.00780 -0.00031 -0.00337 -0.00002 0.00026 0.00621 0.00123 -0.00388 0.00108 0.00156 0.00022 -0.00236 -0.00255 0.00163 0.00083 -0.00052 0.00045 0.00018 0.00062 0.00084 -0.00129 -0.00052 -0.00046 0.00292 0.00172 0.00274 -0.00019 0.00064 0.00004 0.00109 0.00003 0.00345 -0.00023 0.00009 0.00118 -0.00020 0.00021 0.00007 -0.00052 -0.00411 -0.00006 0.00041 0.00096 0.00140 0.00002 -0.00026 0.01373 0.03612 BOBE 0.01060 0.00527 0.07257 0.59 Note: Beta measures the comovement of the company returns with the market. SPY & BOBE 0.00061 Two-State Binomial Option Pricing Model Six-Month BOBE 30 Call: Input Variables: Current Spot Price (S0) Strike Price (X) Time until Expiration (T) Volatility () Borrow Rate u d Continuous $39.53 $35.00 0.50 7.26% 3.75% 1.05 0.95 Annual $39.53 $35.00 0.5 7.26% 3.75% 1.05 0.95 41.61 37.55 6.61 2.55 1.00 36.8551 2.6749 41.61 33.25 6.61 0.00 0.79 32.6428 5.4453 0.85 5.64 5.80 0.67 4.42 4.34 Risk-Free Approach: uS0 dS0 Call Payoff if 'u' (Cu) Call Payoff if 'd' (Cd) Hedge Ratio (H) Amount Borrowed Call Premium (C0) Risk-Neutral Approach: Probability of 'u' (Pru) Expected Payoff Call Premium (C0) Black-Scholes Option Pricing Model Six-Month BOBE 30 Call: Input Variables: Current Spot Price (S0) Strike Price (X) Time until Expiration (T) Volatility () Borrow Rate $39.53 $35.00 0.50 7.26% 3.75% Black-Scholes: d1 d2 N(d1) N(d2) Call Premium (C0) 2.7628 2.7115 0.9971 0.9967 5.18