Making Forecasts with the Forecast Function

If you are recording monthly sales amounts and there seems to be a constant upward trend, though the increase each month isn’t the same amount. You can use the FORECAST function to know when a certain sales amount has been hit.

Applies To: MS Excel 2007 and 2010

The FORECAST function can work whenever you have a set of data pairs: an x-value range (say, date) and a y-value range (say, value of sales for that date). The function uses a trend line on the y-value target amount (e.g. a sales amount target), then applies the same trend to the x-value range to work out the date that target will be reached. The values in the x-value range must be numbers, otherwise the function will return a #VALUE! Error. In this example, we will be using the following data table which lists sales dates from the end of the month, and the value of those sales for that month. We shall use the FORECAST function to determine on what date sales will exceed $2000.


tip1

1. Select cell E2 2. Type in =FORECAST(D2,A2:A9,B2:B9) and press Enter

tip2

2.The formula returns a value of 40716.54041

tip3

3. We can now format this number to return a date value. While cell E2 is still selected, press CTRL + 1. This will bring up the „Format Cells‟ window. On the „Number‟ tab, select the „Date‟ option in the left hand menu, then your desired date format on the right, the click OK

tip4

4. The number will now be converted to a date and we can see that sales are estimated to exceed $2000 on 22nd June, 2011

tip5

 

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email ” href=”mailto:enablement@alchemex.com”>enablement@alchemex.com and yours could be the next Tip of the Week.

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations.