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 & 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.
- Select cell E2
- Type in =FORECAST(D2,A2:A9,B2:B9) and press Enter
- The formula returns a value of 40716.54041.
- 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.
- The number will now be converted to a date and we can see that sales are estimated
to exceed $2000 on 22nd June, 2010
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help
with a particular function, please email
enablement@alchemex.com and yours could be the
next Tip Of the Week.
Click here
to download this latest tip in pdf format
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