Cashflow Modelling - allocating single unit sales to the correct month

[EnglishDan]

Dear All,

Thanks in advance for looking at this.

I'm looking to create a dynamic cashflow that can accurately reflect the revenue generated from individual unit sales.

So far, I've managed to capture the total revenue generated and spread it over the corresponding months based on the number of days it takes to complete each transaction. I've also managed to allocate the number of days in each month, the mechanics of which are explained here Excel Formula to Allocate an Amount into Monthly Columns - Excel University.

Looking at cell H12, the formula for this is:

=H22*((MAX(H$1-$E22,0)-MAX(EOMONTH(H$1,-1)-$E22,0))-(MAX(H$1-$F22,0)-MAX(EOMONTH(H$1,-1)-$F22,0))+(EOMONTH(H$1,0)=EOMONTH($E22,0)))

However, there is an issue that needs resolving / additional functionality that needs to be built in.

Additional Functionality:

The cashflow needs to reflect when each unit is sold and allocate revenue from each sale in its entirety to the appropriate month (opposed to spreading the revenue over multiple months for one unit sale). The first sale should occur from the starting month (C12:C16) + the number of days it takes to sell the first unit (C22:C26). Then for the remaining cashflow to reflect whole unit sales only.

I look forward to getting your thoughts.

Daniel
I look forward to getting your feedback.

DanielI