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

C

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
60
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
Revenue Allocation by Week
Replies
3
Views
254
ChetanPuri
C
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
258
amolvijay
A
P
  • Question
calculating attendance percentage based on the number of days in a week worked
2
Replies
10
Views
470
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
1K
phiero21
P
A
  • Question
How to populate daily sales dynamically based on the week-ending dates and daily % breakdown
Replies
0
Views
136
Adit
A

Forum statistics

Threads
1,215,069
Messages
6,122,956
Members
449,096
Latest member
Anshu121

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