Revenue Allocation by Week

[ChetanPuri]

Hello Excel Community,

I need some help with Revenue allocation per week. Attached is a screenshot what I am trying to achieve but so far it has been very frustrating. As you will notice that un Column N row 2 & 3 I have 2 dates one for the Start of the Financial year and another one reporting date i.e. 09 Feb 2024. Similarly in column I 10 & J 10, I have 2 dates which are the starting & end dates for a contract.

I want the formula to do the following:
- Look at the Start Date in Column I and if its less than the Financial Reporting Date 01 July 2023 then it defaults to 01 July 2023 to calculate the revenue based on column N under Plan Amount.
- if the End date is less than the Reporting Date in my case 09 Feb 2024, then it evenly calculates and allocate revenue between 01 July 2023 and End Date in Column J. For example Column O 11 to column AK it stops but it only calculates 1, 626 instead of 2,846.72. (148,937.36/52)
-In another Scenario, if Start Date is greater than the Financial Year Start Date i.e 1. 01 July 2023 then the formula should calculate using the Start date giving in column I, also if the end date in Column J is greater than the reporting date i.e. 09 Feb 2024, then it should divide the revenue by 52 weeks and evenly distribute to the reporting week. For example $ 167k in Column n should be allocated across from 12 Dec to 09 FeB 2024 for 3,216.43. The revenue must be divided by 52 weeks.

I understand these are very complex scenarios, but I would appreciate any help.

Many thanks,
Regards,
Chetan

 

[kvsrinivasamurthy]

In O11 copied to full range.

=IF(AND(O$10<=MIN($K11,$N$3),(O$10+6)>=MAX($J11,$N$2)),($N11/52)*((MEDIAN(MIN($K11,$N$3),O$10,O$10+6)-MEDIAN(MAX($J11,$N$2)-1,O$10-1,O$10+6))/7),"")

 

[kvsrinivasamurthy]

Better formula. In O11and copied to full range.

=IFERROR(($N11/52)*((1/(1/(MEDIAN(MIN($K11,$N$3),O$10-1,O$10+6)-MEDIAN(MAX($J11,$N$2)-1,O$10-1,O$10+6))))/7),"")

 

[ChetanPuri]

Better formula. In O11and copied to full range.

=IFERROR(($N11/52)*((1/(1/(MEDIAN(MIN($K11,$N$3),O$10-1,O$10+6)-MEDIAN(MAX($J11,$N$2)-1,O$10-1,O$10+6))))/7),"")

Thank you Mate, you are a legend. Also thanks for answering my other queries,. Many thanks, regards, Chetan