Excel Isn’t Dead: How to Use the Venerable Spreadsheet for Forecasting

Operations managers are often the people with the best understanding of demand for products and services, and how much it would cost to deliver those items. Yet, many of them don’t have the skills to deliver a forecast to the level that senior management requires. Finance professionals can offer this analysis, but they often are not close enough to the operations to understand them sufficiently.

Because there is an art, a give-and-take, to budgeting, there is a need for objective forecasting. By this, I mean something that can be constructed simply such that if anyone follows the same process they will get the same figures.

This needs to be a mechanical, objective process. This way, analysts may prepare this data in moments without feeling emotionally attached to the output. Furthermore, operational managers can review the trend and state where future numbers are wrong, and all they need to do is explain the variation, i.e., undertake incremental budgeting.

There is no need for disagreements or confrontation: Both parties may work together as a team.

Using TREND for forecasting

Now the scene is set for having a simple, mechanical approach for budgeting. Using my sample Excel file (click to download), let’s take a look at ways Excel can do this for you.

Imagine I have some historical data:

The data are from September 2012 to July 2017, and I want to extrapolate them until the end of 2020. (I want 2020 foresight!)

There are several functions that can help, with one of the simplest being TREND.

TREND(known_y’s,known_x’s,new_x’s,[constant]) assumes that there is a relationship between variables x (independent variable — here, the dates) and y (dependent variable — the sales), through a formula y = βx + c, i.e., the equation of a straight line (β is the gradient of the line and c is the y-intercept).

Before you disregard linear regression, bear in mind many nonlinear relationships can become linear ones by taking logarithms of the variables. However, this won’t always work.

Here, time is our independent variable (x), and sales is our dependent variable (y). We only specify the constant if we want to force c into the equation (not common — it will usually be left blank).

Similarly, it’s preferable to leave constant blank in the TREND function. For example:

  • 1
  • 2
  • 3
  • Next page

Related Articles

New technology tools are affecting many elements of the human endeavor,...
When executed successfully, multi-participant budgeting can ultimately make the...
The retired CFO of Walmart shares his experiences with capital allocation and...
The CFO expects the financial planning and analysis team to work closely with...