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

C

ChetanPuri

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
694
ChetanPuri
C
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
452
amolvijay
A
P
  • Question Question
calculating attendance percentage based on the number of days in a week worked
2
Replies
10
Views
997
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
A
  • Question Question
How to populate daily sales dynamically based on the week-ending dates and daily % breakdown
Replies
0
Views
256
Adit
A

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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