Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Excel Formula Help - using the FORECAST function

Today's Microsoft Excel tip will give you a quick and easy way to create forecasts for any of your simple modelling needs. In our example, we will look at some sales figures for a 12 month period.

As of month 6, we have our accumulated figures and would like to know the potential total for the full 12 month period. This is a pretty simple formula so can be used for creating quick projections. Our numbers and tables are below:

As can be seen above, we have already started entering our formula with =FORECAST. Excel has immediately provided us with notes on the required syntax, which are x - the total number of months we would like to forecast to; known_y's - the array of cells containing sales figures to date; known_x's - the array of cells containing the month numbers. Our final formula in this case should read: =FORECAST(F6,C3:H3,C2:H2) as seen in the example here:

When we hit return, we get our 12 month number.

For more help and advice for creating quick modelling formulas to help your business, contact our experts. You can also find more details on the FORECAST function by checking out the Microsoft Excel Help pages here.