Revenue Allocation Based on the Number of Weeks ,Days Months etc

C

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
97
Office Version
  1. 365
Platform
  1. Windows
Dear Excel Legends,

Just need some help in allocating Revenue across Dates Based on weeks months etc. On the spreadsheet below , I have calculated Revenue in column N, which is based on Number of HRS, Frequency and then Support Ratio if 1:1, then its 100% 1:2 = 0.5 and 2:1 = Double. Now checking if there is a possibility that based on Weekly or fortnightly or Monthly Revenue can be allocated across from Column O under the dates?.
Any help would be greatly appreciated.
CP Budget Workbook3.xlsx
FGHIJKLMNOPQRS
1
2
3SupportSignedService Agreement
4HoursRatioFrequencySAStart DateEnd DateWeeklyHR RateRevenue5/07/202412/07/202419/07/202426/07/20242/08/2024
531:1WeeklyY19/09/202318/09/202452.00120.5518,805.80
621:1WeeklyY8/09/20237/09/202452.00125.0013,000.00
751:1FortnightlyY8/09/20237/09/202426.00125.0016,250.00
851:1FortnightlyY8/09/20237/09/202426.00125.0016,250.00
921:1FortnightlyY8/09/20237/09/202426.00
1031:1FortnightlyY8/09/20237/09/202426.00
1121:2WeeklyN17/11/202316/11/202452.00
1221:1WeeklyY4/08/20233/08/202452.00
1331:1WeeklyN3/10/20232/10/202452.00
1451:1FortnightlyN3/10/20232/10/202426.00
1531:1WeeklyY9/02/20248/02/202552.00
1631:1WeeklyY9/02/20248/02/202552.00
1731:1WeeklyY6/09/20235/09/202452.00
1831:1WeeklyY6/09/20235/09/202452.00
CAS SUPPORT
Cell Formulas
RangeFormula
M5M5=120.55
N5:N8N5=SWITCH(G5,$BP$6,$BQ$6*M5*F5,$BP$7,$BQ$7*M5*F5,$BP$8,$BQ$8*M5*F5)*L5
L5:L18L5=SWITCH(H5,$BP$1,$BQ$1,$BP$2,$BQ$2,$BP$3,$BQ$3,$BP$4,$BQ$4,$BP$5,$BQ$5)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I:ICell Value="N"textNO
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
You have these cell references in a formula, yet you don't provide that range of cells.
$BP$1,$BQ$1,$BP$2,$BQ$2,$BP$3,$BQ$3,$BP$4,$BQ$4,$BP$5,$BQ$5
Also, please provide some expected results so we can work toward your desired outcome.
 
Upvote 0
awoohaw said:
You have these cell references in a formula, yet you don't provide that range of cells.
$BP$1,$BQ$1,$BP$2,$BQ$2,$BP$3,$BQ$3,$BP$4,$BQ$4,$BP$5,$BQ$5
Also, please provide some expected results so we can work toward your desired outcome.
Click to expand...
We have a revenue stream which is based on Weekly revenue using the Source sheet I needed to allocate the revenue on Weekly basis between 05 July 2024 to 30 June 2025 for number of clients who may different contract end dates and after 1st July they would have CPI increase, so that was the whole purpose of source sheet and then I wanted to add on to the Main Budget by month so I can present it to the Board soon. But as XL2BB only can copy up to 300 cells so I couldn't include anything further.
On another one Just wondering if there is a way for another revenue stream, we can allocate revenue in Colmn N across weeks based on criteria in Column H or L. So for example if Colum H is weekly then from Column it should allocate across to 52 weeks, if its fortnightly then it should jump a week and allocate revenue on 19 July etc. The revenue should also be timed by criteria based in Column G where 1:1 =1, 1:2=0.5 & 2:1 = Double time.
CP Budget Workbook3.xlsx
FGHIJKLMNOPQRST
1
2
3SupportSignedService Agreement
4HoursRatioFrequencySAStart DateEnd DateWeeklyHR RateRevenue5/07/202412/07/202419/07/202426/07/20242/08/20249/08/2024
531:1WeeklyY19/09/202318/09/202452.00120.5518,805.80
621:1WeeklyY8/09/20237/09/202452.00125.0013,000.00
751:1FortnightlyY8/09/20237/09/202426.00125.0016,250.00
851:1FortnightlyY8/09/20237/09/202426.00125.0016,250.00
921:1FortnightlyY8/09/20237/09/202426.00
1031:1FortnightlyY8/09/20237/09/202426.00
1121:2WeeklyN17/11/202316/11/202452.00
1221:1WeeklyY4/08/20233/08/202452.00
1331:1WeeklyN3/10/20232/10/202452.00
1451:1FortnightlyN3/10/20232/10/202426.00
1531:1WeeklyY9/02/20248/02/202552.00
1631:1WeeklyY9/02/20248/02/202552.00
1731:1WeeklyY6/09/20235/09/202452.00
1831:1WeeklyY6/09/20235/09/202452.00
CAS SUPPORT
Cell Formulas
RangeFormula
M5M5=120.55
N5:N8N5=SWITCH(G5,$BP$6,$BQ$6*M5*F5,$BP$7,$BQ$7*M5*F5,$BP$8,$BQ$8*M5*F5)*L5
L5:L18L5=SWITCH(H5,$BP$1,$BQ$1,$BP$2,$BQ$2,$BP$3,$BQ$3,$BP$4,$BQ$4,$BP$5,$BQ$5)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I:ICell Value="N"textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Revenue Allocation by Week
Replies
3
Views
809
ChetanPuri
C
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
485
amolvijay
A
T
  • Solved
Automatically generate dates for specific days of the week on any given month
Replies
6
Views
294
Peter_SSs
Peter_SSs
P
  • Question Question
calculating attendance percentage based on the number of days in a week worked
2
Replies
10
Views
1K
Plasmech
P
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

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