I just want the formula to use for the bottom four questions.

23 AVERAGEIF will permit you to select data by year and average the four quarterly results for each year. 24 25 Industry Ford EPS 26 Capacity Ford EPS Outliers 27 Utilization Removed 28 1990 72 0.93 $ 0.93 29 1991 63 (2.40) $ (2.40) 30 1992 71 (0.73) $ (0.73) 31 1993 2.10 2.10 32 1994 57 4.44 S 4.44 33 1995 3.33 3.33 34 1996 4.00 4.00 35 1997 36 5.54 5.54 36 1998 34 4.72 4.72 37 1999 90 5.29 5.29 38 2000 4.36 5 4.36 39 2001 31 (2.96) 40 2002 35 0.15 $ 0.15 41 2003 35 1.61 $ 1.61 42 2004 31 2.10 2.10 43 2005 1.47 1.47 44 2006 71 (1.44) $ (1.44) 45 2007 (0.22) $ (0.22) 46 2008 57 (3.13) $ (3.13 47 2009 14 (0.25) 48 2010 1.98 1.98 49 2011 59 1.86 1.86 50 2012 76 1.49 $ 1.49 51 2013 30 3.61 3.61 52 2014 32 1.45 1.45 53 2015 36 1.90 1.90 54 2016 1.75 1.75 55 2017 82 1.59 1.59 56 57 The following graph displays the relationship between capacity utilization and Ford EPS.56 57 The following graph displays the relationship between capacity utilization and Ford EPS. 58 59 EXHIBIT: RELATIONSHIP BETWEEN UTILIZATION AND FORD EPS EXHIBIT: RELATIONSHIP BETWEEN UTILIZATION AND FORD EPS OUTLIERS REMOVED $6.00 $5.00 $400 y = 0.1953x - 13.517 R= = 0.5721 $3.00 y = 0.1395x- 9.214 $200 T6860 = 24 $100 Ford EPSs S- ...... . .. 20 40 80 20 100 80 100 $ (1.00) $(2.00) $13.00) $(4.00) Automobile Industry Capacity Utilization Automobile Industry Capacity Utiization 60 19 Source: Marianne Wolk & Richard Horwitz 62 63 You will use Excel regression functions to analyze the relationship between capacity utilization and Ford EPS. 64 In the yellow cell immediately below use the SLOPE function to determine the slope of the regression of Ford's EPS (column D) to automobile industry capacity utilization (column C) using the full data from 1990 to 2017. 65 66 This would mean that for 10 percent decline in capacity utiization Ford's EPS will decline by $0.00. 67 The R-squared (R') of a regression is a measure of the quality of fit. 68 A perfect fit has a R-squared (R2) of 1. If there is no statistical relationship the R-squared (R2) is 0. 69 In the yellow cell immediately below use the RSQ function to determine the R-squared (R") of the regression of Ford's EPS to automobile industry capacity utilization. 70 71 The relationship is statistically significant but weak. 72 You can see that the slope and R-square are the same values that are calculated by Excel in the above graph. 73 We have identified two years of data that are potential outliers, circled in red on the graph above. 74 These two outliers are related to known unique events, the tech bubble bursting at the turn of the century and the 2008 financial crisis. 75 An outlier is a data point that differs significantly from other observations. 76 You are going to redo the regression eliminating these two outliers. 77 Notice that we have replaced the outliers with blanks in column D and that Excel functions generally ignore blank values. 78 In the yellow cell immediately below use the SLOPE function to determine the slope of the regression eliminating outliers of Ford's EPS (column E) to automobile industry capacity utilization (column C) using the full data from 1990 to 2017 79 80 In the yellow cell immediately below use the RSQ function to determine the R-squared (R") of the regression eliminating outlier data points of Ford's EPS to automobile industry capacity utilization. 81 82 Removing outliers makes the relationship significantly stronger. 83 The data excluding these two outliers are graphed above on the right and you can see how much better the fit is. 84 This would mean that for 10 percent decline in capacity utiization Ford's EPS will decline by $0.00. 85 This is approximately equivalent to the average Ford EPS of the history, making this sensitivity very significant