I have a figure for YTD Sales and am trying to calculate estimated annualized
sales off of this. And, this will change monthly as sales managers update
their YTD sales to actual for the prior month end. Mathematically, the
formula would be: YTD Sales + ((YTD Sales/# whole months passed) (# whole
months remaining in year)).

Any suggestions?

"Kelli" wrote:
I have a figure for YTD Sales and am trying to
calculate estimated annualized sales off of this.

Assuming a calendar year, if A1 contains YTD sales and A2 contains YTD date,
then:

=12*A1/month(A2)

If YTD is based on a fiscal year that is different from the calendar year,
replace month(A2) with A2, where A2 contains the fiscal month number
corresponding to the YTD sales.


----- original message -----

"Kelli" wrote:
I have a figure for YTD Sales and am trying to calculate estimated annualized
sales off of this. And, this will change monthly as sales managers update
their YTD sales to actual for the prior month end. Mathematically, the
formula would be: YTD Sales + ((YTD Sales/# whole months passed) (# whole
months remaining in year)).

Any suggestions?

You have not given us any clue as to how we know the number of month's of
data that has been entered.
a) if this is based on the current date; let N moths have passed then your
formula
=ytd +(ytd/N)*(12-N) which is the same as 12*ytd/N
If A1 holds the ytd figure then 12*A1/(MONTH(TODAY())-1) will work provided
the data is added the day after the last day of the completed month

b) maybe we could count how many entries have been made
Suppose the value are entered in cells A1:A12 on a monthly basis
The we could use 12*SUM(A1:A12)/COUNT(A1:A12)
Of if A1:A12 has a running total such than the last one is the YTD value
12*MAX(A1:A12)/COUNT(A1:A12)

Give us a bit more info so we can better help
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Kelli" wrote in message
...
I have a figure for YTD Sales and am trying to calculate estimated
annualized
sales off of this. And, this will change monthly as sales managers update
their YTD sales to actual for the prior month end. Mathematically, the
formula would be: YTD Sales + ((YTD Sales/# whole months passed) (# whole
months remaining in year)).

Any suggestions?

Joe's reply worked, so did Bernard's option a. So, to both of you, THANK
YOU. You've saved me a bunch of time!

To annualize sales for the remainder of the year based on YTD sales, you can use the formula you mentioned: YTD Sales + ((YTD Sales/# whole months passed) (# whole months remaining in year)).

Here are the steps to follow:

1. Determine the number of whole months that have passed in the year so far. For example, if it is currently August, then 7 whole months have passed.
2. Calculate the average monthly sales for the months that have passed by dividing the YTD sales by the number of whole months passed. For example, if the YTD sales are $70,000 and 7 whole months have passed, then the average monthly sales would be $10,000 ($70,000/7).
3. Determine the number of whole months remaining in the year. For example, if it is currently August, then there are 5 whole months remaining.
4. Multiply the average monthly sales by the number of whole months remaining in the year. For example, if the average monthly sales are $10,000 and there are 5 whole months remaining, then the estimated sales for the remainder of the year would be $50,000.
5. Add the YTD sales to the estimated sales for the remainder of the year to get the annualized sales figure. For example, if the YTD sales are $70,000 and the estimated sales for the remainder of the year are $50,000, then the annualized sales figure would be $120,000.

Remember to update the YTD sales figure and repeat these steps each month as sales managers update their YTD sales to actual for the prior month end.