Lokad News

Support » Here

Forecasting methods and formulas with Excel

By Guillaume Saint-Jacques, 2008-06-18

This guide explains elementary forecasting methods that can be readily applied into Microsoft Excel spreadsheets. This guide applies to managers and executive who need to anticipate customer demand. The theory is illustrated with Microsoft Excel. Advanced notes are available for software developer who would like to reproduce the theory into a custom application.

Benefits of forecasting

Forecasting can help you make the right decisions, and earn/save money. Here are a few examples.

• Define better sale strategies

If a product is declining, maybe it is a good idea to consider stop producing it. But maybe not : maybe it is just your sales that are declining, but not your competitor's ?

In this case, is there a chance that you can get your market share back ?

Forecasting techniques provide answers to these questions – vital questions to your business.

• Size your inventories optimally

Time is money. Room is money. So what you want to do is use all means at your disposal in order to reduce your stocks – without experiencing any shortages, of course.

How ? By forecasting !

How to make things easy : labels, comments, filenames

Over time, as you data accumulate, you will be more and more likely to get confused; to make mistakes. The solution ? Don't be messy : making good use of labels, comments and naming your files correctly can save you a lot of trouble.

• Always label your columns. Use the first row of each column to describe the data it contains.
• Different data, different columns. Do not put different numbers (for example, you costs and your sales), on the same column. It is incredibly likely to et you confused, and it makes computations and data handling more difficult.
• Give each file a clearly understandable name. It takes little effort and can speed things up by a great deal. It makes them easy to identify visually, and easier to find using the windows search function.
• Use Comments.

Even if you don't work with a lot of data, it is very easy to get confused, especially if you come back to data you have created long before. Excel has a great solution to offer : comments.

The usefulness of comments

Just do a right click on the cell you want to comment, and then select « insert comment ».

You can use them :

• To explain the content of a cell ( ex : unit cost according to Mr Doe's estimates)
• To leave warnings to future users of the sheet ( ex : I have a doubt about this calculation.. )

Getting Started : a simple forecasting example using trendlines

Let us now do our first forecast. In this part, we will be using this file : Example1.xls. You can download it if you want to do the steps again on your own. This data serves just as example.

Our Data :- in the first column, data about the unit costs of similar products. The unit cost reflects the quality of the product.- in the second one, data about how much have been sold.

What we want to know : If we sell another product, with a quality corresponding to a cost of $150/unit, how many units can we expect to sell ?

How we get there : Here, it is pretty simple. We want to find a simple mathematical relationship between unit cost and sales, and then use this relationship to do our forecast.

First, it is always a good idea to make Excel draw a graph, in order to take a look at the data. Your eyes are excellent tools that can help you identifying trends in a few seconds.

To do this, we select our data, then use Insert > Chart, and chose the XY(Scatter) option. Wen want to estimate sales as a function of quality, so we put the unit cost on the horizontal axis ant the sales on the vertical axes.

Now, we stop a few seconds and take a good look at what we see : the relationship seems to be increasing, and linear.

In order to have an idea of the exact form of the relation ship, we do a right click on the chart, and select the "Trendline" option.

Creating a trendline

Now, we have to select the relationship that seems to "fit" our data. here again, we use our eyes : In this case, the dots are almost in a straight line, so we use the "linear" setting. Later on, we will use other - more complex, but often more realistic - settings, like "exponential".

Our trendline has now appeared on the graph. Another right click allows us to display the exact form of the relationship : y = 102.4x - 191.64.

Understand : Number of unit sold = 102.4 times the unit cost - 191.64.

So, if we decide to produce at a $150 unit cost, we can expect to sell 102.4*150 - 191.64 = 15168 units.

A linear trendline

We have just done our first forecasting.

However, be careful : The software is always able to find a relationship between the two columns, even if this relationship is in reality very weak ! Here is how to quickly check for the robustness of the results :

1. First, always take a look at the chart. If you see that the dots are close to the trendline, at it is the case in our example above, then there is a good chance that the relationship is robust. However, if the dots seem to be located almost randomly and are in general quite far from the trendline, then you should be careful : the correlation is weak, and the estimated relationship should not be blindly trusted.

The dots are everywhere : no evident relationship, unreliable forecasts

The dots "make sense", and allow more reliable forcasting

1. After taking a look at the chart, you can use the CORREL function. In our example, the function would read : CORREL(A2:A83,B2:B83). If the result is close to 0, then the correlation is low, and the conclusion is : there is simply no real trend. If it is close to 1, then the correlation is strong, which is a good thing, since it increases the explanatory power of the relationship you found.

There are more subtle ways of making sure the correlation is high; we will come back to this later on.

Of course, these lasts steps can be automated : you don't have to write down the relationship, and then use you pocket calculator to do the computation. The Analysis Tool Pack is what you need !

Forecasting using the Analysis Tool Pack.

Before going further, you should check if you have the Excel ATP (Analysis Tool Pack) installed. For more information, see our Installing the Analysis Tool Pack section.

You might think that one has to be very lucky to have sales data that look as perfect as in our example, that show a nice, simple, linear relationship. You are right. Let us now see what excel has to offer to face more complicated situations, and more complicated data !

Going further : the example of exponential fitting

As you can imagine, it is not always likely that your data should follow a linear model. In fact, there are many reasons to believe that it should follow an exponential model : many things in the economy are driven by exponential equations : interest compounding computations are an omnipresent example of this.

Here is how to perform en exponential fitting :

1. Take look at your data. Draw a simple graph, and just look. If they follow an exponential evolution, they should look like this :

perfect exponential shape

This is the perfect case. Of course, the data will never exactly look like this. But if the dots seem to approximately follow this repartition, it should encourage you to consider exponential fitting.

Using trendlines

As in the #previous example, you can always draw a chart of your data, ask for a trendline, and choose « exponential » instead of linear.

And then, gather the displayed equation, as usual.

3) Luckily, you can also do all this directly, using the Analysis Tool Pack :Put all your data into a blank excel sheet, and go to Tools => Data Analysis

Installing the Analysis Tool Pack (ATP)

The ATP is an add-in that comes with Microsoft Excel, but that is not always installed by default. In order to install it, one can proceed as follows :

1. Make sure you have your Office CD with you. Excel might require you to insert the CD in order to install the ATP files
2. Open an excel sheet, and go to Tools Menu, and then select Add-Ins. Check the first box of the window, labelled « Analysis ToolPack ».
3. Insert your Office CD if asked to do so by the software.
4. That's it ! Notice that your « Tools » menu now includes many more features, including a « Data Analysis » option. This is the one that we will use the most.

Using the Analysis Tool Pack (ATP)

.. In a linear setting

Now, let us come back to our linear. If your data « look » good (see above illustration), you can use the ATP to get a direct estimation of the functional form, without going through the « trendline »process.

Open your data sheet, then open the « tools » menu and select « Data Analysis ». A window shows up, asking what kind of analysis you want to perform. In our linear setting, what we want to select is « regression ».

Now you need to give excel two arguments : an « Y range » and an « X range ». The Y range is what you want to estimate (ie, your sales), and the X range is the data that you think can explain your sales (here, your unit cost). In our example (see example1.xls), our sales data are in column B, from row 3 to row 90, so you need to put « $B$3:$B$90 » as the Y range, and «$A$3:$A$90 » as the X range. When you are done, click « ok ».

A new sheet apperars, containing the « regression results ».

The Analysis ToolPack Output, in the case of an Ordinary Least Squares regression

What you care about the most is in the bottom of the sheet, in the « Coefficients » column. The intercept is the constant, and the « X variable » coefficient is the coefficient of X (here, your unit cost). Hence, we find the same equation we found using the « trendline » function. Sales = Intercept + Xcoefficient * unit costSales = -126 + 100 * unit cost

This sheet also contains a useful number, that gives you an information about how good your estimation is : the « R Square ». If it is close to 1, then your estimation is good,which means that the equation you found is a fairly good representation of your data. If it is close to 0, then the estimation is not good, and you should probably try another kind of fitting (see exponential fitting below).

This method is probably faster than the « trendline » techniques. However, it is a bit more technical and much less visual. So if you do not want to go through the trouble of plotting and eyeballing your date, make sure you at least check the « R square » value.

.. using exponential fitting

If the linear estimation does not go well (for instance if you obtain a low R-Squared, ie 0,1), you may want to use Exponential Fitting.

Launch the Analysis Tool Pack, as usual : Open your data sheet, then open the « tools » menu and select « Data Analysis ». A window shows up, asking what kind of analysis you want to perform.

In our exponential setting, what we want to select is « exponential ».

Notice that excel only asks you for one input range. Select the column that contains the data you want to forecast (here : unit cost), and pick a “smoothing factor”.

How do I know what model to choose ?

Note that you do not need to try every estimation method and then select the one that works best. Given the large number of estimation method, this is an almost impossible task without automation. If you want all models to be benchmarked against your data, you can consider sending them to Lokad. We have a powerful computer system that “tests” all models and selects only the ones that work best with the data of your business. (link : find out more about what Lokad has to offer).

However, a very powerful tool you have at your disposal that can enable you to select the proper model are your eyeballs : plot your data (see section 1), compare them to the following illustrations, and pick the model that “looks like” your data.

Getting your data ready

The Lokad Add-in for excel has many features that make data manipulation a lot easier.