Forecasting With Excel

Your client, Dave’s BBQ, a local independent restaurant, is interested in determining the effect on sales revenue of certain advertising strategies. Dave has weekly data on advertising dollars spent as well as sales revenue from the restaurant and has come to you, his CPA, to help him determine the link between the two. It certainly seems reasonable that advertising, at least in part, drives sales revenue, but you need to measure the strength of that relationship and then use the information to help forecast sales revenue for future periods.

Creating financial forecasts from historical data can be daunting for managers. However, several tools in Excel are readily accessible and easy to use to help with forecasting revenues and costs. The use of regression analysis can significantly enhance the accuracy of your financial forecasts and make the budgeting process more efficient. This article guides you through the process of using one of these tools, linear regression. The purpose of this article is to demonstrate how to use Excel for forecasting—not to teach forecasting or regression analysis. The discussion below assumes a basic understanding of linear regression (for more details, see “Further Reading.”).

The screenshots demonstrate the tools in Excel 2003. The tools function much the same way in Excel 2007, but accessing them is slightly different. See the sidebar “Accessing Regression Tools in Excel 2007” for directions. To follow the analysis of Dave’s BBQ, download the illustration file here.

PLOT THE DOTS
Linear regression is a statistical tool that can help determine whether the link between a measure of activity (x) and an output measure, such as cost or revenue (y), is sufficient to be used to forecast that output measure. The x is generally referred to as the independent variable and the y as the dependent variable. In the case of Dave’s BBQ, we analyze weekly sales revenue (the dependent variable) versus advertising dollars (the measure of activity or independent variable). Scanning the data in Exhibit 1, it appears there is a positive correlation between advertising dollars and weekly sales revenue. In other words, higher levels of advertising seem to be associated with greater sales revenue. To confirm this assessment and generate forecasts, we will use a graphical approach to regression analysis.

Begin by highlighting cells B1:C21 where the advertising costs and sales revenue data exist. With those cells highlighted, start Excel’s Chart Wizard by selecting Insert, Chart from the menu bar as illustrated in Exhibit 2. In Step 1 of the Chart Wizard select XY (Scatter) from the Standard Types list as shown in Exhibit 3, then select Next. In Step 2 of the Chart Wizard you should see a thumbnail of the chart being created (see Exhibit 4 below). It should be noted that Excel assumes the first column of data selected represents the independent variable (x), while the second column of data represents the dependent variable (y); in this case x and y represent advertising and sales revenue, respectively.

Select Next again to move on to Step 3 of the Chart Wizard shown in Exhibit 5. Here you can specify the Chart Options including title, labeling the axes, and other aesthetic elements. After completing those tasks as desired, select Next. In Step 4 of the Chart Wizard, select the location of the chart. I normally leave the default to include the chart as an object in the current worksheet tab and select Finish as illustrated in Exhibit 6. Your worksheet should now look similar to Exhibit 7 with the completed chart and data all in a single worksheet.

Visually we can confirm our initial assessment of a connection between advertising and sales revenue. More advertising leads to more sales revenue. However, it would be nice to measure the strength of that relationship and determine the extent to which we can rely on that relationship to forecast sales revenue. Linear regression can measure and specify the relationship.

CONNECT THE DOTS
Now that the chart is created, we can include regression analysis directly in the chart. Right-click on any data point in the chart and select Add Trendline as shown in Exhibit 8. Leave the Type as Linear and move to the Options tab. Select Display equation on chart and Display R-squared value on chart, and click OK as illustrated in Exhibit 9. The completed analysis is shown in Exhibit 10.

R², called the coefficient of determination, is a measure of the degree to which changes in the independent variable (advertising) are associated with changes in the dependent variable (sales revenue). Note: While R² is one of the more ubiquitous measures to evaluate the forecasting model’s effectiveness, it is by no means the only measure. You are encouraged to become familiar with additional regression diagnostics. R² can take on values between 0 and 1. Values closer to 1 indicate a stronger relationship. Larger values of R² result in more reliable forecasts. We interpret that R² for Dave’s BBQ as 69.95% of the variation in sales revenue is associated with variation in advertising. The remaining 30% of variation in sales revenue is presumably due to random fluctuations, weather, pricing, quality of service, or other factors.

The trend line in the chart represents the forecast of sales revenue based on advertising using the following equation: sales revenue (y) = (35.202 × advertising (x)) + 21,792. In other words, there is a baseline of $21,792 sales revenue plus $35.20 of additional revenue for each $1 of advertising.

GENERATING FORECASTS USING =FORECAST
Now that we have completed analyzing the data, we can use one of the functions to predict sales revenue based on advertising. Create a forecast in any blank cell (for example, cell F2) by selecting that cell and typing =forecast and then using Ctrl+A to bring up the Function Arguments dialog box for the linear regression forecast function. Exhibit 11 shows the completed dialog box where we specify that cell E2 will contain a future value for x (advertising), the Known_y’s (actual sales revenue) in C2:C21, and Known_x’s (actual advertising dollars) in B2:B21. Click OK. As shown in Exhibit 12, insert some future advertising costs (for example, $200) and the value in cell F2 adjusts the forecast for sales revenue ($28,832 = (35.202 × 200) + 21,792) based on the linear regression equation noted earlier. We can insert various values for advertising in cell E2 and instantly see the predicted sales revenue.

OTHER USES OF LINEAR REGRESSION
While this article focuses on using linear regression tools in Excel for forecasting revenue, linear regression can also help model a variety of business situations. Regression can be used to establish relationships between costs and activity to improve the management of costs. A thorough understanding of the company’s cost functions is imperative for effective cost control.

Regression analysis could also be used to gain an understanding of the price elasticity of your company’s products. Understanding how changes in prices affect changes in sales volume can be critical to maximizing revenue for the organization.

Regression can also be used for time-series analysis. For example, suppose you wanted to analyze daily closing stock prices for your company over a period of one year. You would obtain a list of all the closing prices for the stock each day for the past year and list them in chronological order. Regression analysis could be used to identify patterns in the series of data.

CAUTIONS IN USING LINEAR REGRESSION
Users should be aware of several cautions before relying on regression analysis for forecasting. First, the analyst must be sure that an economically feasible relationship exists between the dependent and independent variables. R² is meaningful only if the relationship between the measures is economically plausible. In the case of Dave’s BBQ, it seems realistic that advertising and sales revenue are economically linked.

Second, the results of the analysis are only as good as the historical data. Errors in the data or use of too few data points may skew the analysis and generate unreliable forecasts of future measures. The forecasts are valid only within the relevant range of historical data. In other words, Dave’s forecasts of sales revenue are reliable only for a range of advertising of approximately $125-$300. As advertising spending moves further outside that range, the forecasts’ reliability diminishes without reanalyzing the data and including that new information.

Third, Dave’s BBQ uses what is known as simple, linear regression. The “simple” part refers to establishing the relationship between one dependent variable (sales revenue) and one independent variable (advertising dollars). There are several variations to regression analysis such as multiple linear regression whereby a dependent variable is associated with more than one independent variable. For example, revenue for Dave’s BBQ may be a function of advertising dollars as well as day of the week, month or other factors. Care must be taken to evaluate multiple explanatory variables to ensure unbiased forecasts. Use of additional, economically feasible, independent variables can increase R² and improve the reliability of forecasts.

Fourth, the “linear” part refers to an underlying assumption that a straight line can reasonably explain the association between sales and advertising. In numerous situations, however, variables have nonlinear (curvilinear) relationships. Consideration of those issues is beyond the scope of this article. Many times, a simple plot of the data suggests a nonlinear relationship, and the use of variants of linear regression may be beneficial.

A variety of excellent resources are available in print and on the Internet that describe in much greater detail the ins and outs of using linear regression analysis (and its variations) for financial forecasting. Cost and managerial accounting books typically include discussion on applying linear regression to financial modeling problems.

James A. Weisel, CPA, DBA, CMA, is a professor of accountancy at the School of Business, Georgia Gwinnett College, Lawrenceville, Ga. His e-mail address is jweisel@ggc.usg.edu.

Further Reading
Numerous books are available on the subject of applying linear regression (and its variants) to solving business problems such as Applied Regression Modeling: A Business Approach, by Iain Pardoe, Wiley, 2006. Additional, nontechnical examples of applied regression analysis can be found in Freakonomics, by Steven D. Levitt and Stephen J. Dubner, HarperCollins, 2006.

Accessing Regression Tools in Excel 2007
The tools described in this article are accessible in Excel 2007 as follows:

To insert an XY (Scatter) Chart begin by selecting the two columns of data, select the Insert ribbon and select Scatter with Only Markers from the Charts menu. To modify the aesthetics of the chart, click inside the chart to activate the Chart Tools ribbon and select the Layout tab. Various options to alter the titles, gridlines, etc., appear in the menu. To add a trendline, the regression equation, and R2 to the chart, right-click on any data point in the chart and select Add Trendline… and select the appropriate elements from the Trendline Options dialog box.

The =forecast function works identically in Excel 2007 and Excel 2003. Select any blank cell (such as F2) and type = forecast and then use Ctrl+A to bring up the Function Arguments dialog box for the linear regression forecast function.