Cashflow Modelling - allocating single unit sales to the correct month

EnglishDan

New Member
Joined
Nov 25, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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)))

Snapshot of Cashflow V3.png


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.

Snapshot of Cashflow V4.png


I look forward to getting your thoughts.

Daniel
I look forward to getting your feedback.

DanielI
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top