Pin Me

Microsoft Excel: Build A Model To Predict Sales Based On Multiple Regression

written by: Mr Excel•edited by: Michele McDonough•updated: 7/6/2011

Problem: You run an ice cream stand. After 10 days of sales, you discover that you either make a lot of money or nearly go broke. As you analyze sales, you feel that temperature and rain might be two important determining factors. On rainy or cool days, fewer people buy ice cream.

  • slide 1 of 2

    As shown in Fig. 399, you set up this table showing sales, temperature, and whether or not it rained.

    You want to determine the relationship between sales, temperature,

    and rainfall.

    Strategy: You need to do a multiple regression. After a multiple regression, you will have a formula that predicts sales like this: Y = m1x1 + m2x2 + b

    Sales = Temperature x M1 + Rain x M2 + b

    The LINEST function can return the values M1, M2, and b that best describe your sales model.

    1) LINEST is going to return three values. Select a range of three cells that are side by side, as shown in Fig. 400. The first argument is the range of known sales figures. The second argument is the range of temperatures and rainfall.

    2) Hit Ctrl+Shift+Enter to calculate the array formula. As shown in Fig. 401, enter a prediction formula in column D to see how well the formula describes sales.

    The results are so-so. The prediction in D6 is right on the mark.

    The predictions in D11 and D12 are off by $20 each – an error of 10 percent.

    3) The LINEST function can return additional statistics that will tell you how well the results match reality. To get the statistics, add a fourth argument of TRUE. Enter the function in a five-row range, as shown in Fig. 402.

    Personally, I only somewhat paid attention in statistics class. One of the key indicators that I look at is the R-Squared value. This ranges from 0 to 1, where 1 is a perfect match and 0 is a horrible match. The 0.88 value here confirms that the prediction model is pretty good, but not perfect.

    Additional Details: Regression models try to force actual results into a straight-line formula. The fact is that life may not fit in a straight-line formula. Since I created the spreadsheet, I know that the actual data in the ice cream model uses a formula of (Temperature – 50) x $2 if raining and (Temperature – 50) x $6 if not raining. In this example, Sally was correct that ice cream sales are dependent on rain and temperature, but even a powerful regression engine could not predict the absolutely correct formula.

    Summary: The LINEST function will automate the process of performing a least-squares method to fit a line to a series of actual sales using a multifactor regression.

    Functions Discussed: =LINEST()

    See all Microsoft Excel tips

  • slide 2 of 2


    Fig. 399Fig. 400Fig. 401Fig. 402Fig. 403