Sunday, 26 January 2014

A Simple and Free Forecasting Model for Your Small Business





If you run a small business, then you know how important it is to have an idea of what your monthly profits or sales may be, so you can plan ahead and better schedule your growth.

Thankfully, this is possible with tools such as forecasting. A business forecast studies the behavior of important business indicators like sales and expenses over long periods of time to arrive at a predictive model for future performance in those areas.

[Related Article: Modelling Important Business Decisions with Game Theory]

The validity of forecast models is guaranteed by the mathematical nature of business processes; your business can be modeled as a mathematical system receiving inputs (factors of production) and producing output (sales and profits).

Accuracy of forecast models is guaranteed by the fact that business performance fluctuates between reasonably fixed high and low points. 

These fluctuations may be seasonal (such as sales highs during holidays or profit lows in the first quarter of a new year), cyclic (consisting of alternating highs and lows), or reactive (consisting of nearly-random small changes). 

In any case, these fluctuations can be reasonably predicted over the long term using certain mathematical systems called forecast models. Software companies have successfully packaged these models into forecasting software that accurately predict key business performance indicators.

The problem is that many of these models are very complicated, and may not be suitable for relatively simple business processes – like those of small businesses. As a result of the complex nature of many of these programs, they are also very expensive. 

This post focuses on a simple and free forecast model for small businesses

Introducing the Mean Adjustment Prediction Model (MAPM) for sequential business data: 

With this model you can predict new monthly values of data given a substantial set of historical data by performing a simple calculation on a spreadsheet (e.g. MS Excel)


Read on to discover the actual equation for the model, as well as the code (formula) for EXCEL, so you can use this at home or in the office.



This forecast model is very simple and only requires that you obtain the mean (average) of the values of data you are interested in, and add an adjustment factor.

It is remarkable to note that this model does not sacrifice accuracy on account of its simplicity. For a detailed background of this model (open only if you are mathematically inclined), see Mean Adjustment Prediction.

For a simpler analysis, consider the following:



The MAPM is especially recommended for slightly-varying non-constant data, such as small business sales and profits.



Notice the high accuracy of the model (ranging between 87% and 120% for the example above). In the experiment shown above, MAPM was used to guess random sales figures ranging between $7,000 and $9,000 with very impressive results. Accuracy values in excess of 100% indicate an optimistic prediction (greater than the actual result).

Tip: I recommend you use MAPM in a spreadsheet to quickly calculate a prediction for applications such as planning and budgeting.

To use MAPM with Excel, you can use the following formula:



=AVERAGE(B3:G3) + 0.5*(G3 - B3)*(1 - 1/N)

B3 = first value in sequence
G3 = last value in sequence
N = number of values in sequence

I understand it’s a little technical, but I assure you it works great every time – and of course, it’s free. So by all means get started with MAPM in Excel and let me know if you need any assistance! Thanks for reading!