Working on a revenue forecast in excel with opportunities that have two key variables: 1) Start Year, and 2) Ramp (in years). I need the value of the opportunity (i.e. $1) to be assigned into the appropriate year and distributed equally based on the ramp up (i.e. $0.50 assigned to 2014 and $0.50 in 2015). Tried the following formula:
=IF(AND(O$2-(O$3-G$5)<=$G5,O$3>=$F5),$I5/$G5,0)
Where O$2 is the number of the year (1 – 5), O$3 is the actual year 2013 – 2017), G$5 is the number of years to Ramp input (i.e. 2), F$5 is the start year input (i.e. 2014), I$5 is the Revenue Value.
The problem I’m having is that $0.50 will also show up in 2016 (instead of just 2014 and 2015) because O$2 (Year number 4) minus O$3 (2016) less than G$5 (2014) equals 2 and is equal to G$5 Ramp input (2).
Correctly Assigning Sales Revenue by Year
-
-
-
Re: Correctly Assigning Sales Revenue by Year
If you can add an attachment I'll have a look, as I'm struggling with where all the figures go.
-
Re: Correctly Assigning Sales Revenue by Year
Thanks! Attached an example to the original posting.
-
Re: Correctly Assigning Sales Revenue by Year
Here you go. Changed the thinking process of the formula a bit but it works.
-
Re: Correctly Assigning Sales Revenue by Year
Thanks a million!
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!