I am building an implementation model and I have most of it done, the revenue table looks like this:
REVENUE TABLE
Basically the table will show the calculated run rate based on the start date.
But, what I want to do on top of this is add a ramp.
RAMP TABLE
So the table should look like this:
REVENUE TABLE WITH RAMP APPLIES
So what I am trying to do is have a draggable formula but it needs to apply the 25% of the ramp table to the start date even though that column varies.
REVENUE TABLE
Opportunity | Start | Apr | May | Jun | Jul | Aug |
Opp 1 | May | 0 | 10,000 | 10,000 | 10,000 | 10,000 |
Opp 2 | Apr | 5,000 | 5,000 | 5,000 | 5,000 | 5,000 |
Basically the table will show the calculated run rate based on the start date.
But, what I want to do on top of this is add a ramp.
RAMP TABLE
1st month | 2nd month | 3rd month | 4th month | 5th month |
25% | 50% | 75% | 100% | 100% |
So the table should look like this:
REVENUE TABLE WITH RAMP APPLIES
Opportunity | Start | Apr | May | Jun | Jul | Aug |
Opp 1 | May | 0 | 2,500 (10,000 x 25%) | 5,000 (10,000 x 50%) | 7,500 (10,000x75%) | 10,000 (10,000 x100%) |
Opp 2 | Apr | 1,250 (5,000 x 25%) | 2,500 (5,000 x 50%) | 3,750 (5,000 x 75%) | 5,000 (5,000 x 100%) | 5,000 (5,000 x 100% |
So what I am trying to do is have a draggable formula but it needs to apply the 25% of the ramp table to the start date even though that column varies.