Business Forecasting With Microsoft Excel

All businesses must plan for the future if they want to stay in business and prosper. Nobody really knows what the future will bring but sitting back and accepting what it may bring without trying to at least prepare for it, will set a course to failure.

Data forecasting enables a business to try and see the shape of things to come by taking past data and predicting future performance. Businesses need to know how much income will be generated and what costs will be involved. Microsoft Excel gives us a number of tools to help us predict the future, for example, we can forecast future trends based on historical data or we could create scenarios of Best Case, Worst Case and 'In The Middle' Case of income and costs and see what effect variables in the business model have on the organisation. We can also use Excel tools to help set our business targets.

There are a number of formula based forecasting tools. The FORECAST function. This function uses mathematical analysis called linear regression, to find relationships between existing data pairs and apply these relationships to extrapolate data where only part of the data exists. For example: if we have 6 years worth of annual sales, we may want to predict sales in year 7 based on the previous 6 years. The syntax of the function is as follows:

= FORECAST(x, known_y's ,known_x's)

Where X is the single data point for which you want to predict a Y value. In the above example x=7 for year 7 . The known x's are a range (B6:G6) containing 1,2,3,4,5,6 representing the past 6 years . Known_y's is the dependant range of numerical data and in this example is the cell range(B7:G7) containing the 6 historical annual sales. Therefore sales in year 7 can be forecasted by:

=FORECAST(7,B7:G7,B6:G6)

The TREND function works in a similar way to FORECAST but instead of calculating a single Y value as above, it will extrapolate multiple Y values by calculating a linear trend (a best-fit straight line) using an array formula.
Expanding on the above example, we wish to calculate sales for years 7 -12 based on the sales of years 1 -6. The syntax of the function is as follows:

=TREND(known_y's,known_x's,new_x's)

Known Y's & Known X's are similar to the FORECAST function above. The additional argument of new X's are a cell range (H6:M6) with new X values for which you want TREND to calculate corresponding y-values (in this example 7,8,9,10,11,12). Because we will be using an array formula, all the cell for the predicted sales (H7:M7) should be selected and the following formula entered:

=TREND(B7:G7,B6:G6,H6:M6)

As this is an array formula, pressing CTRL+SHIFT+ENTER.
The GROWTH function will perform an exponential (a curved line that rises or falls at increasingly higher rates) growth calculation also to extrapolate multiple Y values.
The syntax is similar to the TREND Function:

=GROWTH(known_y's,known_x's,new_x's)

Business forecasting is a tricky affair. However, using historical data and the tools above will give businesses some insight to what might lie ahead