Question: I am recording monthly sales amounts and there seems to be a constant, upward trend, though the increase each month isn’t always the same amount. Can I use Excel to tell me when I’ll hit a certain sales amount?
Answer: Yes, with the FORECAST function
Process (Excel 2003, 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’ll 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’ll use the FORECAST function to determine on what date sales will exceed $2000.
1. Select cell E2
2. Type in =FORECAST(D2,A2:A9,B2:B9) and press Enter
3. The formula returns a value of 40716.54041.
4. 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.
5. The number will now be converted to a date and we can see that sales are estimated to exceed $2000 on 22nd June, 2010
