 # 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/

## Files

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

## Files

• 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!