Forecasting and Business Analysis

Estimating the Unknown Parameters in a Simple Regression Model This Excel template shows a simple example of how the unknown parameters are estimate in a simple regression. These parameters are b0 (intercept) and b1 (slope). Students are not expected to be able to calculate these by hand and for most problems a computer would always be used to estimate the parameters. The calculations and demonstration here is simply to provided a greater level of understanding. Using the ToolPak and Linest function are also explained.

Y

X

e1

e2

e3

e4

e5

e6

Ordinary Least Squares The method used to estimate the unknown parameters is called ordinary least squares or OLS. The diagram illustrates that the 6 observed values and the line of best fit for these points. The two unknown parameters for this line are b0 and b1. We can find these parameters by minimising the errors between each point and the line. These are labelled as e1 to e6. The errors are squared first. So the parameters are found by minimising the squared errors. This can be shown as And the values of b0 and b1 can be found from the partial derivates i.e. The method is shown on the "Estimating the Model" tab.

Estimate the relationship between advertisement responses and size You are analysing the responses from your companies latest advertising campaign. You wish to estimate the number of responses that you receive on average for each column centimetre of advertisement. You hypothesis that there is a positive relationship between the size of the advertisement and the number of responses. The relationship is shown on the scattergram. The line of best fit is also shown. You can adjust the line by changing the b0 & b1 parameters below.

Advertisment Size (Column Centimetres)

Number of Responses

Find the Line of best fit Adjust the bo and b1 value to find the best fit. The errors (e) above are the difference between the observed response and the model response. Try to minimise the squared error.

Y=b0+b1(size)

Finding the line of best fit by minimising the sum of squared errors While you can find the line of best fit by trial and error you can also minimise the squared errors by formula. This is shown on the "OLS Calculations" tab. To make the trial and error (iteration) method quicker, you can use solver to find the minimum. Use solver as shown below to minimise the squared errors. Check the results are the same as OLS.

Using Solver to minimise the error You can open solver from the Tools menu. If Solver does not appear in the menu you may have to click the appropriate tick box in Tools Add-ins. In the solver screen notice that you are setting the target cell J17 to a minimum. Thus you minimise the sum of squared erros in J17. You do this by changing the values in H18 and H19. These are the two parameters b0 and b1. Use the solve button to find the parameter values. If you ask it to "Keep Solver Solution" the new parameter estimates will become the parameter values (the same as for OLS).

Finding the line of best using the OLS formulae This shows the application of the OLS formulae for estimating the parameters

The formulae are applied to the data using the table and the formulae to the left. Note that these OLS results are the same as for the iterative (trial and error) approach.

Estimate the relationship between advertisement responses and size You are analysing the responses from your companies latest advertising campaign. You wish to estimate the number of responses that you receive on average for each column centimetre of advertisement. You hypothesis that there is a positive relationship between the size of the advertisement and the number of responses. This relationship can be tested using regression analysis. The analysis can be performed using the Analysis ToolPak.

Excel Regression Using Data Analysis The graphic on the left, shows the regression dialog box. To find this use the Tools - Data Analysis menu and select regression from the list of methods. The most important inputs to this dialog box are Y Range - which is the cells that refer to the dependent variable X Range - which is a contiguous set of cells that refer to one or more independent variable (up to 16 variables can be chosen) Labels - which needs to be ticked if the first row of the data is a label Output options - you need to select to put the output in a specified range of the same worksheet OR on a new worksheet ply (or tab) or in a new workbook. Residuals - you may choose to select from a range of residual options. Normal Probability - you may choose to have a normal probability plot.

The Output On the left you can see the output from the data analysis. Notice that the Coefficients are the same as for the other methods of analysis. These are the parameter estimates of the intercept and the slope. All of the other details are relevant statistics. This will be discussed at a later stage.

Estimate the relationship between advertisement responses and size You are analysing the responses from your companies latest advertising campaign. You wish to estimate the number of responses that you receive on average for each column centimetre of advertisement. You hypothesis that there is a positive relationship between the size of the advertisement and the number of responses. This relationship can be tested using regression analysis. The analysis can be performed using the the LINEST function.

Details of the LINEST Function from the Excel Help file LINEST(known_y's,known_x's,const,stats) Fits a straight line to your data and returns an array that describes that line. The accuracy of the line depends on the degree of scattering in the data you provide. The more linear the data, the more accurate the LINEST model. LINEST uses the method of least squares for determining the best fit for the data. The known_x's, const, and stats arguments are optional. If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable. If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable. The array known_x's can include one or more sets of variables. If you use only one variable, known_y's and known_x's can be shaped differently. If you use more than one variable, known_y's must be a vector (a range with a height or width of 1). If you omit known_x's, LINEST uses the values {1,2,3,...} in an array the same size as known_y's. If const is FALSE, the constant term b equals zero. If const is TRUE or omitted, the constant term will be estimated. If stats is FALSE or omitted, LINEST returns only the slope and y-intercept. If stats is TRUE, LINEST returns the additional values: Standard error for each coefficient Standard error for the constant b Coefficient of determination (r-squared) Standard error for the y-estimate F-statistic Degrees of freedom Regression sum of squares Residual sum of squares

LINEST is an Array function in EXCEL to produce regression results. The advantage of this over the Analysis ToolPak approach is that like all functions the results will change as values in the data set change. The function is applied below and you can read about at the bottom of the sheet. To use Linest you must use an array. This is a group of cells which cannot change. In this case the arrays will be the X's and Y's and the results. To input an array you must use <Ctrl> <Shift> and <Enter> together rather than just <Enter>. See the further example to your right.

Time for you to try this for yourself Use the fx command to enter the Linest Command in cells O21 for the data in cells 03 to P8. Function should be LINEST(O3:O8,P3:P8,TRUE,TRUE). Now highlight cells O21 to P25. Click the cursor in the Equation Edit Bar while the cells are still highlighted. Now press <Ctrl><Shift><Enter>. You should get an answer like those in cells O14 to P18. A version with labels is to the right of that.

The yellow highlighted cells are the results from the LINEST function. In this case only the two unknown parameters are shown. The example to the right shows all of the statistics as well. Note the "squiggly" brackets around the formula. This indicates it is entered as an array.