Summing Sales over Time with Monthly Price Increases

Hi all,

I have customers that pay an amount every month. After a certain number of months, the amount increases by a certain amount. I am trying to build out a model that will project out my monthly sales as I add new customers each month. But I am finding I need to build out a huge waterfall to do it. Is there a simpler way anyone is aware of – perhaps one that will calculate each’s month’s sales in one row?

Without the increases, a basic sumproduct would work. Perhaps a separate formula could be added in for just the price increases? Or perhaps there's a better way to go about this?

I have attached a model to show what I mean.


Any help would be greatly appreciated!

I believe I am making progress with this. I believe I can make a sumproduct formula for the base prices without the increases, and then a second sumproduct for only the price increases.

The only issue I’m having is that I would need to set the starting point for the count before the price increase takes effect not from the start of the model but from the start of the appropriate time period.

I have included an updated spreadsheet, in case it’s helpful!

i'll do it with tables in one column
=SUMPRODUCT(_t[amount],--(_t[mînth]<=[@month]),(1+INT(([@month]-_t[mînth]+1)/_t[tim])*_t[inc]))

Thanks, but I'm not quite sure you understood my question. Perhaps I didn't explain it correctly. I am trying to replicate Line 20 of the attached document (without creating the waterfall above it). I tried my best to do so in lines 26 + 27 (which I could combine into one formula), but Line 27 isn't perfect because the counts are starting from Column D rather than from the Column associated with the applicable month. Does that make sense? It's the only thing I need to adjust (assuming you agree with my approach).

Thanks again!

My apologies for the continued posting but I figured I'd try one more time... I am attempting to project out monthly amounts, with the amounts for each individual month able to increase by a certain amount after a certain number of months.

I can do this with a waterfall (Line 20 of the attachment) but, given the size of the model, I’d like to avoid that.

I am not super-knowledgeable with this stuff, but I can get close by using a sumproduct (Line 23). But it’s not exactly correct because for each individual month, the number of months thereafter that triggers the increase starts counting
from the very first month instead of the month which contains the original amount. I hope that makes sense.

I've simplified the attached model from above to hopefully make more clear what I'm after.

Any help would be appreciated!

Thanks

This is working.Pl see file.
In D26 then drag across.

Please Login or Register  to view this content.

Thank you very much!