annualize sales for remainder of year
in [Functions]
|
Prev: Question about syntax for conditional formatting (RESOLVED)
Next: Way do do search/replace on beginning/end of cell w/o functions?
From: Kelli on 22 Mar 2010 14:56 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?
From: Joe User on 22 Mar 2010 15:36 "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?
From: Bernard Liengme on 22 Mar 2010 15:34 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" <Kelli(a)discussions.microsoft.com> wrote in message news:D9AC3735-FEC0-43B7-9878-0F91FA1D39DE(a)microsoft.com... > 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?
From: Kelli on 23 Mar 2010 12:48
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! |