The second big idea this course seeks to demonstrate is that your data-analysis results cannot and should not aim to eliminate all uncertainty. The two models should demonstrate to you in a practical, hands-on way the idea that your choice of business metric drives your choice of an optimal model. Your first model will focus on minimizing default risk, and your second on maximizing bank profits. In the Final Project (module 6) you will assume the role of a business data analyst for a bank, and develop two different predictive models to determine which applicants for credit cards should be accepted and which rejected. This course will prepare you to design and implement realistic predictive models based on data. We use Excel to do our calculations, and all math formulas are given as Excel Spreadsheets, but we do not attempt to cover Excel Macros, Visual Basic, Pivot Tables, or other intermediate-to-advanced Excel functionality. The function returns an array of predicted y values for the x values in R3 based on the model determined by the values in R1 and R2.Important: The focus of this course is on math - specifically, data-analysis concepts and methods - not on Excel for its own sake. In this case, GROWTH(R1, R2, R3) is an array function where R1 and R2 are as described above and R3 is an array of x values. GROWTH can also be used to predict more than one value. Which is the same result we obtained earlier using the Regression data analysis tool. E.g., based on the data from Example 1, we have: For R1 = the array containing the y values of the observed data and R2 = the array containing the x values of the observed data, GROWTH(R1, R2, x) = EXP( a) * EXP( b)^ x where EXP( a) and EXP( b) are as defined from the LOGEST output described above (or alternatively from the Regression data analysis). GROWTH is the exponential counterpart to the linear regression function TREND described in Method of Least Squares.
For Example 1 the output for LOGEST(B6:B16, A6:A16, TRUE, TRUE) is as in Figure 4.įigure 4 – LOGEST output for data in Example 1 LOGEST doesn’t supply any labels and so you will need to enter these manually.Įssentially LOGEST is simply LINEST using the mapping described above for transforming an exponent model into a linear model. Once again you need to highlight a 5 × 2 area and enter the array function =LOGEST(R1, R2, TRUE, TRUE), where R1 = the array of observed values for y (not ln y) and R2 is the array of observed values for x, and then press Ctrl-Shft-Enter. LOGEST is the exponential counterpart to the linear regression function LINEST described in Testing the Slope of the Regression Line. We can get the same result using Excel’s GROWTH function, as described below.Įxcel Functions: Excel supplies two functions for exponential regression, namely GROWTH and LOGEST. Thus if we want the y value corresponding to x = 26, using the above model we get ŷ =14.05∙(1.016) 26 = 21.35. We can also create a chart showing the relationship between x and ln y and use Linear Trendline to show the linear regression line (see Figure 3).Īs usual we can use the formula y = 14.05∙(1.016) x described above for prediction. We can also see the relationship between x and y by creating a scatter chart for the original data and choosing Layout > Analysis|Trendline in Excel and then selecting the Exponential Trendline option. The table in Figure 2 shows that the model is a good fit and the relationship between ln y and x is given byĪpplying e to both sides of the equation yields We now use the Regression data analysis tool to model the relationship between ln y and x.įigure 2 – Regression data analysis for x vs. The table on the right side of Figure 1 shows ln y (the natural log of y) instead of y. Clearly, any such model can be expressed as an exponential regression model of form y = αe βxby setting α = e δ.Įxample 1: Determine whether the data on the left side of Figure 1 fits with an exponential model.įigure 1 – Data for Example 1 and log transform Observation: A model of the form ln y = βx + δ is referred to as a log-level regression model. e β, we note that an increase in x of 1 unit results in y being multiplied by e β.This equation has the form of a linear regression model (where I have added an error term ε): Taking the natural log (see Exponentials and Logs) of both sides of the equation, we have the following equivalent equation: In particular, we consider the following exponential model: Sometimes linear regression can be used with relationships that are not inherently linear, but can be made to be linear after a transformation.