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
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!