Formula/function to evenly distribute revenue over a variable number of months??

New Contributor

Hello friends - I am looking for advice on how to program a revenue recognition schedule. The idea is pretty simple but I'm stumped on the solution. I need to be able to enter the # of months over which I want to recognize revenue and have it be even distributed horizontally over that many months. Let's say I have $12,000 to recognize. I want to be able to enter "6" as the # of months, and then have $2,000 populate over 6 months. Or if I enter "12" for the # of months, it will populate $1,000 over 12 months. I've attached simple screenshots to show what outcome I am looking for. Any/all assistance appreciated. Thank you!Screen Shot 2020-04-10 at 10.44.39 PM.pngScreen Shot 2020-04-10 at 3.47.27 PM.png

5 Replies

@swilkenburn That would be like in the attached workbook.

 

 

 

@Riny_van_Eekelen  Yes- that is exactly what I was looking for. I can't thank you enough!

@Riny_van_Eekelen 

 

Hi, in a similar situation. Trying to figure out a single dynamic formula that will allow me to split up the values from Column A (say a revenue contract value) across columns B-Z with various start and end dates. like this

 

Contract ValueStart DateEnd Date Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20
$1,100,00009/06/2009/06/23              
$50,00009/06/20NA              
$375,00005/28/2005/28/21              
$172,47502/03/2102/03/22              

@John_Bloggs650 This is slightly different from the original post. Best to start a new thread.

 

Anyway, which Excel version are you on and how would you split the examples if you had to do it manually? For instance, the first one covers three years (i.e. 36 months) but you mention to want to split across columns B:Z (25 columns). And the second one. How would you threat a contract with no end date?

Just started one here: https://techcommunity.microsoft.com/t5/excel/dynamic-formula-that-splits-distributes-revenue-a-value...

Im using Office 365. sorry the columns would be A: CA.

 

Have been playing around with this formula but the days in each month are variable so 30 doesn't necessarily work, with the total falling slightly short.
=($E2<=H$1)*(H$1<=$F2)*($D2/(($F2-$E2)/30))

Trying to do something similar to this:

https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png