Correctly Assigning Sales Revenue by Year

  • 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).


    forum.ozgrid.com/index.php?attachment/49908/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!