# Cashflow Modelling - allocating single unit sales to the correct month

#### EnglishDan

##### New Member
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.

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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Replies
4
Views
350
Replies
0
Views
513
Replies
12
Views
306
Replies
0
Views
55
Replies
78
Views
2K

1,127,425
Messages
5,624,730
Members
416,046
Latest member
Elliottj2121

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back