One is sometimes called upon to make sales forecasts or verify those made by the sales and marketing departments. One of the better approaches for doing this is to extend the past history of sales volume forward into the periods being projected. Though this method of prediction is like trying to drive a car by looking in the rear view mirror, it is still one of the best tools available, as long as it is supplemented by detailed conversations with the sales staff to see what is really happening in the marketplace.

Advertisement

There are two formulas provided by Excel that result in forecast information:

The first, and simplest, is the TREND command. This one superimposes a trend line on an existing set of time-sequenced data points to arrive at an expected sales level for a specified future period.

To illustrate the command, we return once again to the income statement shown earlier in my previous post [Financial Statement Proportional and Ratio Analysis with Excel Spreadsheet]. We will use a new worksheet within the same spreadsheet, called Trend, and reference in it all of the monthly sales figures from the previous income statement. This is shown in below figure, along with a graph that shows the added trend line.

Trend Analysis

In the above worksheet, we already know all sales data points from January through August, and want to calculate a trend line that extends an additional month to give us a prediction for September sales. Accordingly, in the table of months and historical sales figures noted in the Trend worksheet, there is an additional cell next to the “August” sales period. In that cell we enter the following formula:

TREND(B4:B11,A4:A11,A12)

 

Though it looks complicated, this is a relatively simple command. The trend line is based on the data points contained in cells B4 through B11 for the date ranges contained in cells A4 through A11. The date for the period to be forecast is noted in cell A12. The formula generates a number that is the extension of the trend shown by the previous data elements and will deposit this number in the B12 cell.

Another way to state the formula is to ignore the dates and just ask for the next number in sequence. The formula, based on the previous example, looks like this:

TREND(B4:B11, ,{9})

 

Under this variation, we are using the same set of data points, but ignoring the dates (hence the two commas in the formula with no data in between), and a number in brackets which represents the trend for the ninth number in the sequence of data elements. Since the original set of data only included eight data elements, this will be the next revenue figure after the last month of actual data. If the requested trend were for the month of December, the number in brackets would change to {12}, since this would represent the twelfth data point in the series.  However, to add a trend line overlay to the presented data, click on the completed graph, move the cursor to the revenue line on the chart, and press the right mouse button. Then click on the Add Trend line option, and pick from six available types of trend lines that can be added to the graph.

 

The second type of forecasting tool provided by Excel is REGRESSION ANALYSIS. This is a powerful tool for determining the trend line that best fits a disparate set of data, and is most useful when dealing with a set of numbers that are widely scattered, and show no apparent pattern.

In essence, the method determines the trend line that minimizes the sum of all squared errors between all data points and the line. Rather than delve into the formula for this method, it is easiest to plot the data elements and proceed immediately to a graph, on which Excel will superimpose a regression trend line.

In the above figure, the second half of the presentation includes the regression analysis. In it, we have plotted twenty data items for twenty periods that are wildly different from each other, and have no apparent pattern. The first step in the analysis is to create a graph. Then use the same steps just described for the TREND analysis to add a trend line to the chart. The result is shown in the bottom half of the above figure, where we find that there is a slight upward trend line to the data used to compile the regression trend line.

Of the two methods presented, the TREND formula is of the most use, for one will find that most data being analyzed in the financial arena has a lengthy and steady trend line of data. Only for the most unusual analyses, involving wildly disparate data items, will the regression analysis be necessary.

 

The following list shows some of the trend lines that you may consider and try when measuring a company’s operations using the TREND formula in excel spreadsheet:

  1. Trend of amount of utilized storage space. This trend can spotlight many contributing problems, such as obsolete inventory, returned goods, scrapped parts, and excess finished goods.
  2. Trend of cost of distribution channels. This analysis should include the net margins earned on each sales channel.
  3. Trend of cost of freight. A company may extend into new geographical regions without considering the cost of shipping product into those areas. This analysis should be conducted by region to spot such problems.
  4. Trend of cost of sales calls. In particular, one should compare the relationship between the sales received from high-volume customers and the cost of making sales calls to them. It is common to find a few high-maintenance customers who are not worth the sales effort from a cost-benefit perspective.
  5. Trend of design cycle iterations required. Increases in the number of design iterations indicate serious problems in the design process, while a drop in the trend indicates good design management (though it can also indicate that an insufficient number of product reviews are being made).
  6. Trend of direct labor rates. This can be used in comparison to a market survey to see if company pay rates are varying from those offered by other firms. After a layoff, it can change suddenly if people whose pay varies substantially from the mean have been laid off.
  7. Trend of engineering change notices issued. A jump in the number of change orders can signal the presence of quality problems, as well as a likely increase in inventory, as some components are rendered obsolete.
  8. Trend of gross margins. Investigate margins by both product and volume.
  9. Trend of inventory, bill of material, and labor routing accuracy. These three items require very high levels of accuracy in order to operate a production planning system. Any drop in these trends will likely result in production snafus.
  10. Trend of number of product options per product family. Changes in this trend can refer to a deliberate attempt at product proliferation as a marketing strategy (if it increases) or a rationalization of engineering tasks (if it declines).
  11. Trend of overhead capitalization. If the amount of overhead shifted into a capital account, such as inventory, is changing, this may signal a deliberate attempt by management to alter the reported level of earnings.
  12. Trend of pricing. Track the sensitivity of sales volume to changes in pricing.
  13. Trend of product returns. A sudden change in this trend can signal a quality problem in the product, or that the distribution pipeline is full.
  14. Trend of ratio of overhead to production labor. Overhead can balloon rapidly, and this is a prime early indicator of the problem.
  15. Trend of return on shareholders’ equity and return on assets. A significant decline in these measures signals reduced cash flow, more difficult borrowing covenants, and a host of other problems, such as reduced margins and increased expenses.
  16. Trend of sales quotas. This trend can be compared to actual sales per salesperson to see if the quotas are unrealistically high or low.
  17. Trend of sales volume. Examine sales volume by both territory and product.
  18. Trend of utilized plant capacity. This is a good indicator of the need for additional production shifts, increased maintenance, or more facilities.