Spreading Revenue

Hi guys,

I feel I can crack most problems with Excel, but this one has got me beat, I am not even sure that it is possible. But if it is, I am sure you guys will know the best solution. There may be too many variables to consider.
Please refer to the attached file.

Column “E” is the amount of revenue that I have collected this month.

I need to spread this revenue equally between column G to AP, taking into account the billing period (Columns C & D):

So, if the period (C & D) falls between 01/09/2020 to 30/09/2020 this is nice and simple, all revenue should be collected in column O (for September).

For entries such as on row 5 (period from 01/10/2020 to 31/12/2020) this should be spread equally at a third for Oct, a third for Nov and a third for Dec. (P,Q & R)

For entries such as on row 13 (for 1 year) this should be split equally at 1/12 for each month. But Pro Rata for the first and last month if necessary.

Each line should be spread equally (where possible). So, a £75 charge over 3 months should be £25 for each month. The number of days in each month should not make a difference to the spread. If there is a rounding this could be dealt with by taking the addition in the first month. Each charge recurs each month/quarter/year, and there should be no fluctuation in revenue between columns G to AP unless it is a new service or ceased service, or a change in value.

Closed periods – we are in September now, so any revenue collected for the period of August or sooner needs to be reflected in September onwards (all prior accounting periods are closed). So, line 150 should be reflected in September despite the date range being 01/02/2020 to 31/08/2020. Row 145 should show £1,196 in September and £598 in October. No revenue in August.

So, what is the magic formula that I should input into columns G to AP to automatically spread the revenue?

Thanks in advance to any genius who can master this one

Hopefully I have now managed to include the attachment.

Many thanks for taking the time to look at this for me. This is very helpful.