How to Build Accurate Sales Forecasts in Excel Using Linear and Multivariate Regression

Mathematical modeling transforms real‑world problems into mathematical language. Excel, with its functions and visualization, is a convenient tool for beginners and practitioners.

Case Study 1: Predicting Product Sales

Assume a small retailer wants to forecast sales for the next six months based on twelve months of historical data.

1. Data preparation

Create a table with month (A column) and sales (B column).

2. Choose model: time‑series analysis

Use a linear regression to capture the trend. In Excel, open Data Analysis → Regression.

3. Build model: linear regression

Add a scatter plot, insert a linear trend line, and display the equation and R².

The resulting equation is y = 1.8427x + 14.273 , where y is sales, x is month, and R² = 0.9.

4. Forecast future sales

Apply the equation to months 13‑18 to obtain predicted values, e.g., for month 13: y = 1.8427 × 13 + 14.273.

In Excel you can copy the formula =1.8 * A2 + 12 to the future rows.

5. Validate the model

Compute residuals (actual – predicted) and check that they are small and show no systematic bias.

Case Study 2: Multivariate Regression (Price, Promotion, Seasonality)

Sales may be affected by price, promotion, and seasonality. The data includes month, sales, price, and a promotion flag.

1. Data preprocessing

Convert the “promotion” column to numeric values (1 for Yes, 0 for No) using the IF function:

<code>=IF(D2="是", 1, 0)</code>

2. Perform multivariate regression

Use Data Analysis → Regression, set sales as the dependent variable and price and promotion as independent variables.

The regression yields the equation Sales = -0.5 × Price + 8 × Promotion + 10 .

3. Interpret results

Price : a 1‑unit decrease in price raises sales by 0.5 k units.

Promotion : a promotion adds 8 k units to sales.

4. Use the model for prediction

For a future month with price 90 and promotion active, the forecast is Sales = -0.5 × 90 + 8 × 1 + 10 = 45 (k units).

5. Model evaluation and improvement

Increase sample size or add factors such as seasonality, product rating, etc. Excel’s “Forecast” and “Solver” tools can further refine predictions.

Other common Excel modeling tools

Solver : linear programming for production optimization.

Goal Seek : find input values that achieve a target output.

PivotTables : quickly summarize and explore data.

These examples demonstrate how Excel provides a powerful yet easy‑to‑use environment for practical mathematical modeling.