In these complex and uncertain times, forecasting business performance is increasingly important. Today, more than ever, managers at all levels need to make intelligent predictions of future sales and profit trends as part of their overall business strategy. By forecasting sales six months, a year, or even three years down the road, managers can anticipate related needs such as employee acquisitions, warehouse space, and raw material requirements. Similarly, a profit forecast enables the planning of the future expansion of a company.
Business forecasting has been around for many years, and various methods have been developed-some more successful than others. The most common forecasting method is the qualitative “seat-of-the-pants” approach, in which a manager (or a group of managers) estimates future trends based on experience and knowledge of the market. This method, however, suffers from an inherent subjectivity and a short-term focus because many managers tend to extrapolate from recent experience and ignore the long-term trend. Other methods (such as averaging past results) are more objective but generally useful for forecasting only a few months in advance.
In business, it’s becoming increasingly popular to use a statistical tool called regression analysis to determine the relationship between one phenomenon that depends on another. For example, car sales might be dependent on interest rates, and units sold might be dependent on the amount spent on advertising. The dependent phenomenon is called the dependent variable or the y-value, and the phenomenon upon which it’s dependent is called the independent variable or the x-value. (Think of a chart or graph on which the independent variable is plotted along the horizontal [x] axis and the dependent variable is plotted along the vertical [y] axis.)
Given these variables, you can do two things with regression analysis:
- Determine the relationship between the known x- and y-values and use the results to calculate and visualize the overall trend of the data.
- Use the existing trend to forecast new y-values.
With linear data, the dependent variable is related to the independent variable by some constant factor. For example, you might find that car sales (the dependent variable) increase by one million units whenever interest rates (the independent variable) decrease by 1 percent. Similarly, you might find that division revenue (the dependent variable) increases by $100,000 for every $10,000 you spend on advertising (the independent variable).
You make these sorts of determinations by examining the trend underlying the current data you have for the dependent variable. In linear regression, you analyze the current trend by calculating the line of best-fit, or the trendline. This is a line through the data points for which the differences between the points above and below the line cancel each other out (more or less).
The easiest way to see the best-fit line is to use a chart. Note, however, that this works only if your data is plotted using an XY (scatter) chart. For example, Figure 1 shows a worksheet with quarterly sales figures plotted on an XY chart. Here, the quarterly sales are the dependent variable, and the period is the independent variable. (In this example, the independent variable is just time, represented, in this case, by fiscal quarters.)
The following steps show you how to add a trendline to a chart:
- Click the chart to select it.
- If more than one data series is plotted, click the series with which you want to work.
- Choose Layout, Trendline, Linear Trendline. Excel inserts the trendline.
Figure 2 shows the best-fit trendline added to the chart.
CAUTION
It’s important not to view the trendline values as somehow trying to predict or estimate the actual y-values (sales).The trendline simply gives you an overall picture of how the y-values change when the x-values change.
Figure 1 To see a trendline through your data, first make sure the data is plotted using an XY chart.
Figure 2 The quarterly sales chart with a best-fit trendline added.
Technorati Tags: plotting best fit, best fit trendline
Popularity: 13% [?]
If you liked this post, would you please buy me a twelve-ounce lattes for only $2
Recent Comments