formula for the revenue row that evenly spreads each months bookings

**Thor222** · 04-29-2021, 09:58 PM

How do I automate this? I have bookings, revenue and ramp time.

Lets say ramp time = 6 months (input cell) - i.e. bookings are spread evenly over this amount of time
Bookings = 10K per month
Revenue should equal 10K/6 for the next 6 months ...so in month 1 its just 1.67K but in month 2 it would be 3.33K (i.e. 1/6th of bookings from month 1 plus 1/6th of month 2 and so on until month 6 when you have 10K).

I think there's an offset formula somewhere in here because I essentially need to reference by ramp time cell to tell revenue how many months to spread each month of bookings over.

Any ideas?

**Gregb11** · 04-29-2021, 10:26 PM

Please read the yellow banner at the top of the page. Showing expected results and where you want the formulas will be helpful.

**Thor222** · 04-29-2021, 10:43 PM

Attached. Need a formula for the revenue row that evenly spreads each months bookings (with can vary by month) over the next x number of months. But its a waterfall so it also needs to pick up 1/6th of the current month if that makes sense. The attached is a very simplified example but I need a dynamic formula. Maybe I need to add multiple rows and sum them up but was hoping to avoid this.

**D13L** · 04-29-2021, 10:59 PM

Hi Thor222,

I'm not really sure what you're looking for. My assumption is:
B6 = 10000/6
C6 = 20000/6
D6 = 30000/6
etc...

if this is so please try:

Please Login or Register  to view this content.

Edited: What will happen if it reaches above the 6 months ? The next 6 months would tally to 20,000.

**Gregb11** · 04-29-2021, 11:07 PM

Maybe this in B6?
=MIN(B$4/$B$1,B$4)
And then this in the rest (C6:M6)
=MIN(B6+$B$4/$B$1,B$4)

**Thor222** · 04-29-2021, 11:17 PM

That doesn't work because bookings are not going to be a flat 10K per month in reality. Think about this way...

You can have any amount of new bookings in a given month. You need to recognize that months bookings evenly over the ramp period.

Lets just say ramp period is 6 months. So if in month 1 you book 10K you will recognize 10k/6 but then if you book 20K in month 2 you need to pick up another 1/6th of 10K from month 1 + 1/6th of 20K in month 2. And then in month 7 those bookings will start rolling off since you've fully recognized the booked revenue.

Does that make more sense?

**Phuocam** · 04-29-2021, 11:39 PM

=IF(COLUMNS($B$6:B6)>$B$1,"Value?",SUM($B$4:B4)/$B$1)

Change "Value?" to the value you expect

**Thor222** · 04-29-2021, 11:45 PM

Huh?! Is my explanation of what I'm trying to accomplish not clear? I thought I was pretty clear but if there are questions about my goal please let me know.

Appreciate the attempt but this doesn't seem to make sense.

**Thor222** · 04-29-2021, 11:46 PM

What do you mean by value I expect?

**D13L** · 04-30-2021, 12:11 AM

Hi Thor,

I think I understood what you are looking for. Please Try:

Please Login or Register  to view this content.

Set at B6 or B7 and drag across

Hope this is what you are looking for.

EDITED: Added excel sheet as reference. Changed the bookings value because it becomes confusing. See attached if this is what you are looking for. --12:23 AM

EDITED: This accounts for changing months. The formula above locks it to just 6 months. --12:28 AM

Please Login or Register  to view this content.

**Thor222** · 04-30-2021, 12:31 AM

Thanks so much, getting much closer, knew there was an offset in there somewhere!

One issue I'm having is with your references to G4. I really need to make the ramp period dynamic so that I can change the number in B1. This seems to break your formula. Is there a way to use another offset rather than setting G4 as a fixed point?

Another issue I'm having is that some of your cell locks seem off. Does this need to slide? In reality my database will go on for multiple years. Why are you locking B4?

Thanks!

**D13L** · 04-30-2021, 12:33 AM

Hi thor,

Please check the 2nd code. I edited the post and recognized that it was locked to 6 months. The code below changeswhen you update $B$1

**Thor222** · 04-30-2021, 12:36 AM

Yeah that helps but you still have the G4 concept which seems problematic, right?

**D13L** · 04-30-2021, 12:38 AM

Hi Thor,

I've re-read your post. Please include giving a sample which include multiple years.

Also, does the sum carry over to the previous year, or does it go back to square one at one point?

Edited: Locking B4 has a reason. Imagine as 3 month your range varies as you drag B:C, B:D, B:E. As you hit column F, code tells B:E and offset by 1 so it becomes C:F.

**Thor222** · 04-30-2021, 12:41 AM

Something is off with your formula but can't put my finger on it yet. The multiple years shouldn't matter you should just be able to copy the same formula over regardless of the year. There is no reset at year end or anything.

**D13L** · 04-30-2021, 12:43 AM

Is it this code you use:

Please Login or Register  to view this content.

**AliGW** · 04-30-2021, 12:46 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have done it for you today.)

**Thor222** · 04-30-2021, 12:47 AM

Okay this should make it more clear what I'm trying to do. Row 10 is the result I'm trying to get (without creating all those rows and manual formulas above obviously)

**Thor222** · 04-30-2021, 12:49 AM

Thanks...any ideas on potential formulas?

**D13L** · 04-30-2021, 12:51 AM

Hi Thor,

It looked like you used the Old code please see POST#10 and POST#16.

Please try that code.

**Thor222** · 04-30-2021, 12:53 AM

No I tried those as well, it doesn't work. I just used your workbook as a starting point but wasn't illustrating your formula. Look at row 10 and the rows I created above to understand the concept because your formulas are not accomplishing exactly what I intend to do. I think the file I uploaded should make it more clear...

**AliGW** · 04-30-2021, 12:54 AM

FYI This is not CODE, it's a FORMULA. Code is the term used for VBA, M Code, etc.

**kvsrinivasamurthy** · 04-30-2021, 12:55 AM

Pl see file.
In B7 then copied across

=SUM(OFFSET(B$4,0,0,1,-MIN(COLUMNS($B$4:B$4),$B$1)))/$B$1

**Thor222** · 04-30-2021, 12:55 AM

Value add...

**D13L** · 04-30-2021, 12:57 AM

I apologize AliGW. Guess I'm still not familiar with Excel Jargon. Sorry for the confusion

Also Thor222. You're right I didn't really analyze your post and excel file and jumped to conclusion. You're request will take me a while or I may not be able to solve. Hopefully someone more experienced in the forum can help.

**Thor222** · 04-30-2021, 12:58 AM

AMAZING - you win, thanks a bunch!

**AliGW** · 04-30-2021, 01:06 AM

Thor222 - please acknowledge that you have understood the note about thread titles.

**Thor222** · 04-30-2021, 01:10 AM

AliGW - please acknowledge that you have added zero value

**AliGW** · 04-30-2021, 01:13 AM

Thor222 - I am a moderator with a job to do. You need to take note of the information I gave you.

I suggest you read the forum rules. A title such as 'OFFSET' is vague and useless. If you continue to ignore our rules you will find yourself in danger of being banned from here, so please do not ignore moderation notes.

**kvsrinivasamurthy** · 04-30-2021, 01:15 AM

Pl see file same formula in previous post I have used in this file in row 12 and I am getting result of Row 14.
You need Row 10. In row 10 after 4 months calculation is suddenly changing Why. What is the criteria.
In column F why you are not adding $F$4/$B$1 (stopped at $E$4/$B$1).

**Thor222** · 04-30-2021, 01:24 AM

No I just stopped adding new rows because I thought I had made my point. It was just a manual way of illustrating what my question was but in reality it would continue on with the same pattern. Your formula is working. In F9 you would need to type F4/B1 and then you'll see it still ties to your formula.

Thanks for your help. Knew it had to be an offset formula but the concept of min b1 / b1 eluded me! Thanks again!

**kvsrinivasamurthy** · 04-30-2021, 03:27 AM

Thanks for feedback and rep.

formula for the revenue row that evenly spreads each months bookings

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: Offset

Re: formula for the revenue row that evenly spreads each months bookings

Re: Offset

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Re: formula for the revenue row that evenly spreads each months bookings

Thread Information

Users Browsing this Thread

Similar Threads

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions