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^{2}, 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^{2} 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^{2} can
take on values between 0 and 1. Values closer to 1 indicate a stronger
relationship. Larger values of R^{2} result in more reliable
forecasts. We interpret that R^{2} 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^{2} 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^{2} 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.