Companies that can accurately forecast sales can successfully adjust future production levels, resource allocation and marketing strategies to match the level of anticipated sales. These actions help to optimize operations and maximize profits. A regression model forecasts the value of a dependent variable -- in this case, sales -- based upon an independent variable. An Excel spreadsheet can easily handle this type of equation.

Data Gathering

Decide upon an independent variable. For example, suppose your company produces a product with sales that tie closely to changes in the price of oil. Your experience is that sales rise when the price of oil rises. To set up the regression, create a spreadsheet column for your annual sales over some number of previous years. Create a second column showing the percentage change in the year-over-year average price of oil in each of the sales years. To proceed, you will need the Excel Analysis ToolPak, which you can load for free by selecting "Add-ins" on the "Options" menu.

Running the Regression

Choose "Regression" from the "Data Analysis" item on the "Data" menu. Mark the range of the independent variable as the X-axis and that of the dependent variable as the Y-axis. Give a cell range for the output and mark the boxes for residuals. When you press "OK," Excel will compute the linear regression and display the results in your output range. The regression represents a straight line with a slope that best fits the data. Excel displays several statistics to help you interpret the strength of the correlation between the two variables.

Interpreting the Results

The R-squared statistic indicates how well the independent variable forecasts sales. In this example, the R-squared of oil versus sales is 89.9, which is the percent of product sales explained by the percentage change in the price of oil. Any number above 85 indicates a strong relationship. The Y-intercept, in this example 380,000, shows the amount of product you would sell if the price of oil remained unchanged. The correlation coefficient, in this case 15,000, indicates that a 1 percent increase in the price of oil would drive sales up by 15,000 units.

Using the Results

The value of the linear regression depends on how well you can forecast the independent variable. For example, you might pay oil industry analysts for a private forecast that predicts a 6 percent increase in the price of oil over the next year. Multiply the correlation coefficient by 6, and add the result -- 90,000 -- to your Y-intercept amount of 380,000. The answer, 470,000, is the number of units you would likely sell if the price of oil rose 6 percent. You can use this prediction to prepare your production schedule for the upcoming year. You can also run the regression using different oil price movements to predict a best- and worst-case outcome. Of course, these are just predictions, and surprises are always possible. You can also run regressions with multiple independent variables, if appropriate.