Excel - formula to project revenue

Asked By Need formula help, Excel 2007, Thanks on 13-Feb-10 07:27 PM
Hello, is there a formula I can use that will project revenue in real time
based on total sales at any given time.  Basically, take my sales to date,
consider how many days into a calendar year we are at presently and make a
prediction to where the sales will be at the end of the calendar year based
on sales to date.  So, say I am 50 days into the calendar year and my sales
are 500,000.  Presently, I am averaging 10,000 per day and at this rate my
sales for the year will be 3,650,000.  Is there a formula that will take the
revenue from cell1 and provide the annual projected revenue in cell2?  I hope
I am making sense.  Thanks in advance to anyone that can assist.




Joe User replied to Need formula help, Excel 2007, Thanks on 13-Feb-10 08:29 PM
Yes, you are.  But whether or not it is a good way to project year-end
revenue depends on the nature of the business.  Usually not.  Oh well, "give
the customer what he wants".... :-)



Assume that "cell1" is A1, with the YTD revenue.  And assume that A2
contains the date of the YTD revenue in A1.  Then put the following into

=round((date(1+year(today()),1,1)-date(year(today()),1,1))
*A1/(1+A2-date(year(today()),1,1)),0)

Note:  You could replace
do not want to account for the potential leap day.

You could replace "1+A2" or just A2 with TODAY() if you always project
revenue on, respectively, the day after or the day of the date corresponding
to the YTD revenue.


----- original message -----
Dana DeLouis replied to Need formula help, Excel 2007, Thanks on 13-Feb-10 09:28 PM
Another option that provides a linear guess might be:
(Adjust as necessary)

=FORECAST(DATEVALUE("12/31/2010"),B1:B2,A1:A2)

3640000


A1: 1/1/2010
A2: 2/20/2010

B1: 0
B2: 500000

HTH
Dana DeLouis