Cost Spread Allocation

SAMEERS

New Member
Joined
Jul 8, 2020
Messages
8
Office Version
  1. 2019
Hello All
Request help with formula on Cost Spread between specific dates of Months. The dates & actions are dynamic in nature but able to derive the overall action cost. not able to spread the cost in Months

My Cost Savings is available in
Column
AE2, this needs to be bifurcated Month on Month basis the Column T & Column S in conjunction with Column AG 2 - AO2 to populate the cost Savings under Column AG3 - AO3


Your help will be highly appreciated to complete my annual project

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
E.IDEMETWAENCALOBCountryAnualized CTC $USDHourly Rate $ USD W HrsL% LCTC $ Hourly Loaded Rate $ USD Weekly Loaded CTC $USD MLCTCAction TypeAction DateStart DateEnd DateAction Duration (WKs)Interm Furlough Working Period
( Wks)
Action
(Saved Weeks )
% Week's SavedAPL%Effective Loaded Rate by Action TypeHour reduction per week (in %)Saved HrsBase Salary/Wage/ Rate reduction (in %)Weekly Salary SavingsAction Period SavingsAverage Monthly Savings30-Apr-2031-May-2030-Jun-2031-Jul-2031-Aug-2030-Sep-2031-Oct-2030-Nov-2031-Dec-20
51620396ABCPYGFPPENNUS
41600
20
40
18.00%
49088
24
944
4091
Furlough - continuous
20-Apr-20
27-Apr-20
4-Jul-20
14014100%18%240%00%944128385664?????????


 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
Welcome to MrExcel!

Do you mean like this?

Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1E.IDEMETWAENCALOBCountryAnualized CTC $USDHourly Rate $ USDW HrsL%LCTC $Hourly Loaded Rate $ USDWeekly Loaded CTC $USDMLCTCAction TypeAction DateStart DateEnd DateAction Duration (WKs)Interm Furlough Working PeriodAction% Week's SavedAPL%Effective Loaded Rate by Action TypeHour reduction per week (in %)Saved HrsBase Salary/Wage/ Rate reduction (in %)Weekly Salary SavingsAction Period SavingsAverage Monthly Savings30-Apr-2031-May-2030-Jun-2031-Jul-2031-Aug-2030-Sep-2031-Oct-2030-Nov-2031-Dec-20
2( Wks)(Saved Weeks )
351620396ABCPYGFPPENNUS41600204018.00%49088249444091Furlough - continuous20-Apr-2020-Apr-202004-Jul-202014014100%18%240%00%9441283856641858.131585236.552635067.6316675.68421100000
Sheet5
Cell Formulas
RangeFormula
AG3:AO3AG3=(MAX(-1,MIN(AG1,$T3)-MAX(AG1-DAY(AG1)+1,$S3))+1)/($T3-$S3+1)*$AE3


I assumed that the dates in AG1:AO1 were the month end dates, and you wanted to see how much of the saving went in each month.
 

SAMEERS

New Member
Joined
Jul 8, 2020
Messages
8
Office Version
  1. 2019
Hi Eric - Wonderful thank you, 2 More Questions, Rather 1 is a Question & 1 is a different Scenario where i need help to add another formula in this one

1. I understand that with the formula we're trying to derive the days & distribute the $ Value for the Precise days of the month to pro rate the $'s, but what i do not understand is why do we have +1 & -1 embedded in the formula, what is the relevance here?

2 The second request is the $ Value is derived in Column AE from a certain action, here is a scenario where Start Date & End Dates are the same & we need under this condition & only this condition we have the entire AE value Prorated across the Months till ENd ( The $ Value is nothing but the entire Yrs Salary Saved

Request your help on this one too

Thanks
Greatly Appreciate!!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
1. There are 3 +1s in the formula, with 2 purposes. In this part: AG1-DAY(AG1)+1, it is used to find the 1st day of the month. If AG1 has the last day of the month, if you subtract out the day number, you get the last day of the last month, so add 1 to get the first day of the current month. EOMONTH(AG1,-1)+1 would have also worked.

The bulk of the formula is based on this formula: =MAX(0,MIN(ed1,ed2)-MAX(sd1,sd2)) which calculates the number of overlapping days in 2 date ranges, if one range is defined by start date1 (sd1), and end date1 (ed1), and the other range is (sd2,ed2). In this formula, I use S1 and T1 as one range, and the start/end day of the current month for the other. If the 2 ranges do not overlap, then it will create a negative number, so the MAX(0, part ensures that non-overlapping ranges come up with a 0 answer. For your purposes though, there is a snag. If you subtract January 2 - January 1 to get the number of days, you'll get 1. But this leaves out one of the end points. If you want to include the end point, you need to add 1 to the range. This is the reason for both of the other +1's in the formula.

As stated before, the MAX(0, part ensures that the overlapping range result is always non-negative. But if the range does not overlap, and we get 0, then we add 1 to that, we'll get 1, and the non-overlapping months will have something in them when they shouldn't. So I changed it to MAX(-1, and when I add 1, I get the proper 0.

2. Try:

AG3: =IF($T3=$S3,DAY(AG1)/($AO1-$AG1+DAY($AG1)),(MAX(-1,MIN(AG1,$T3)-MAX(AG1-DAY(AG1)+1,$S3))+1)/($T3-$S3+1))*$AE3

Instead of having the dates the same in S3/T3, you could set S1 to 4/1 and T1 to 12/31, and you'd get the same answer with the original formula.
 

SAMEERS

New Member
Joined
Jul 8, 2020
Messages
8
Office Version
  1. 2019

ADVERTISEMENT

1. There are 3 +1s in the formula, with 2 purposes. In this part: AG1-DAY(AG1)+1, it is used to find the 1st day of the month. If AG1 has the last day of the month, if you subtract out the day number, you get the last day of the last month, so add 1 to get the first day of the current month. EOMONTH(AG1,-1)+1 would have also worked.

The bulk of the formula is based on this formula: =MAX(0,MIN(ed1,ed2)-MAX(sd1,sd2)) which calculates the number of overlapping days in 2 date ranges, if one range is defined by start date1 (sd1), and end date1 (ed1), and the other range is (sd2,ed2). In this formula, I use S1 and T1 as one range, and the start/end day of the current month for the other. If the 2 ranges do not overlap, then it will create a negative number, so the MAX(0, part ensures that non-overlapping ranges come up with a 0 answer. For your purposes though, there is a snag. If you subtract January 2 - January 1 to get the number of days, you'll get 1. But this leaves out one of the end points. If you want to include the end point, you need to add 1 to the range. This is the reason for both of the other +1's in the formula.

As stated before, the MAX(0, part ensures that the overlapping range result is always non-negative. But if the range does not overlap, and we get 0, then we add 1 to that, we'll get 1, and the non-overlapping months will have something in them when they shouldn't. So I changed it to MAX(-1, and when I add 1, I get the proper 0.

2. Try:

AG3: =IF($T3=$S3,DAY(AG1)/($AO1-$AG1+DAY($AG1)),(MAX(-1,MIN(AG1,$T3)-MAX(AG1-DAY(AG1)+1,$S3))+1)/($T3-$S3+1))*$AE3

Instead of having the dates the same in S3/T3, you could set S1 to 4/1 and T1 to 12/31, and you'd get the same answer with the original formula.


Hi Eric - highly appreciate the help & really thank you from core.
I tried the 2nd Formula & it works for 90% of the Conditions, but does not work ( Prorate the savings for the Yr & evenly spreads for the remainder of the Months of FY, Please see this below, there are 3 Conditions, 1 & 2 are exactly the same but the distribution is not matching the AE Savings but for the 3 condition it does & formula used here is

=IF($T3=$S3,DAY(AG1)/($AO1-$AG1+DAY($AG1)),(MAX(-1,MIN(AG1,$T3)-MAX(AG1-DAY(AG1)+1,$S3))+1)/($T3-$S3+1))*$AE3

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
ECEMENWAENCALOBCountryAnualized CTC $USDHourly Rate $ USDW HrsLoading % LCTC $ Hourly Loaded Rate $ USD Weekly Loaded CTC $USD MLCTC $Action TypeAction DateStart DateEnd DateAction Duration (WKs)Intermitent Furlough Working Period
( Wks)
Action
(Saved Weeks )
% Week's SavedAction Period LoadingEffective Loaded Rate by Action TypeHour reduction per week (in %)Saved HrsBase Salary/WASe/ Rate reduction (in %)Weekly Salary SavingsAction Period SavingsAverASe Monthly Savings30-Apr-2031-May-2030-Jun-2031-Jul-2031-Aug-2030-Sep-2031-Oct-2030-Nov-2031-Dec-2031-Jan-2128-Feb-2131-Mar-21FY 20Validation
123ABCSalaried - ExemptH1 BGF
1
AUSA160115
77
40
22.70%
196461
94
3778
16372
Involuntary
3-Apr-20
3-Apr-20
3-Apr-20
0030100%23%940%00%377811485422669944010628113341299414652162531956322819294253956154507114854356032
FALSE
321CBASalaried - ExemptH1 BGF
2
CUSA
24149
12
40
24.00%
29945
14
576
2495
RIF
19-Mar-20
31-Mar-20
31-Mar-20
0030100%24%140%00%576175063455143916201728198122332477298234784485603083081750654266
FALSE
444SSSSalaried - ExemptCitizenGF
3
DUSA100000
48
40
16.70%
116700
56
2244
9725
Furlough - continuous
15-Apr-20
15-Apr-20
15-Sep-20
31031100%17%560%00%22446867313465713513824133781382413824668900000068673
TRUE
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
That's because you've added some more columns. Try:

=IF($T3=$S3,DAY(AG1)/($AR1-$AG1+DAY($AG1)),(MAX(-1,MIN(AG1,$T3)-MAX(AG1-DAY(AG1)+1,$S3))+1)/($T3-$S3+1))*$AE3

When S3 <> T3, the denominator of the function is derived from S3 and T3. When S3 = T3, the denominator is determined from the first and the last month, AG1 and AR1 in this case.
 

SAMEERS

New Member
Joined
Jul 8, 2020
Messages
8
Office Version
  1. 2019

ADVERTISEMENT

That's because you've added some more columns. Try:

=IF($T3=$S3,DAY(AG1)/($AR1-$AG1+DAY($AG1)),(MAX(-1,MIN(AG1,$T3)-MAX(AG1-DAY(AG1)+1,$S3))+1)/($T3-$S3+1))*$AE3

When S3 <> T3, the denominator of the function is derived from S3 and T3. When S3 = T3, the denominator is determined from the first and the last month, AG1 and AR1 in this case.
Dear Eric

Really appreciate the efforts you've put in to teach a student (myself) i must thank you more & enough & i wish if you were in Toronto i would have personally met you & thanked you.

I tried this formula as well but if you see it still doesn't match up & prorate the saving MoM from Colum AE, Please see below have highlighted with color

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
ECEMENWAENCALOBCountryAnualized CTC $USDHourly Rate $ USDW HrsLoading % LCTC $ Hourly Loaded Rate $ USD Weekly Loaded CTC $USD MLCTC $Action TypeAction DateStart DateEnd DateAction Duration (WKs)Intermitent Furlough Working Period
( Wks)
Action
(Saved Weeks )
% Week's SavedAction Period LoadingEffective Loaded Rate by Action TypeHour reduction per week (in %)Saved HrsBase Salary/WASe/ Rate reduction (in %)Weekly Salary SavingsAction Period SavingsAverASe Monthly Savings30-Apr-2031-May-2030-Jun-2031-Jul-2031-Aug-2030-Sep-2031-Oct-2030-Nov-2031-Dec-2031-Jan-2128-Feb-2131-Mar-21FY 20Validation
123ABCSalaried - ExemptH1 BGF
1
AUSA160115
77
40
22.70%
196461
94
3778
16372
Involuntary
3-Apr-20
3-Apr-20
3-Apr-20
0030100%23%940%00%377811485422669944010628113341299414652162531956322819294253956154507114854356032
FALSE
321CBASalaried - ExemptH1 BGF
2
CUSA
24149
12
40
24.00%
29945
14
576
2495
RIF
19-Mar-20
31-Mar-20
31-Mar-20
0030100%24%140%00%576175063455143916201728198122332477298234784485603083081750654266
FALSE
444SSSSalaried - ExemptCitizenGF
3
DUSA100000
48
40
16.70%
116700
56
2244
9725
Furlough - continuous
15-Apr-20
15-Apr-20
15-Sep-20
31031100%17%560%00%22446867313465713513824133781382413824668900000068673
TRUE
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
Looks like I should have tested on more rows! The formula is lacking some $ signs to lock the date ranges to the top row. Here's an updated version:

Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
1ECEMENWAENCALOBCountryAnualized CTC $USDHourly Rate $ USDW HrsLoading %LCTC $Hourly Loaded Rate $ USDWeekly Loaded CTC $USDMLCTC $Action TypeAction DateStart DateEnd DateAction Duration (WKs)Intermitent Furlough Working PeriodAction% Week's SavedAction Period LoadingEffective Loaded Rate by Action TypeHour reduction per week (in %)Saved HrsBase Salary/WASe/ Rate reduction (in %)Weekly Salary SavingsAction Period SavingsAverASe Monthly Savings30-Apr-2031-May-2030-Jun-2031-Jul-2031-Aug-2030-Sep-2031-Oct-2030-Nov-2031-Dec-2031-Jan-2128-Feb-2131-Mar-21FY 20Validation
2( Wks)(Saved Weeks )
3123ABCSalaried - ExemptH1 BGF1AUSA160115774022.70%19646194377816372Involuntary3-Apr-203-Apr3-Apr0030100%23%940%00%3778114854226699440.054799754.72339440.05489754.72339754.72339440.05489754.72339440.05489754.72339754.72338810.71789754.7233114854TRUE
4321CBASalaried - ExemptH1 BGF2CUSA24149124024.00%29945145762495RIF19-Mar-2031-Mar31-Mar0030100%24%140%00%5761750634551438.849321486.8111438.84931486.8111486.8111438.84931486.8111438.84931486.8111486.8111342.9261486.81117506TRUE
5444SSSSalaried - ExemptCitizenGF3DUSA100000484016.70%1167005622449725Furlough - continuous15-Apr-2015-Apr15-Sep31031100%17%560%00%224468673134657134.8571413823.78613377.85713823.78613823.7866688.928600000068673TRUE
Sheet1
Cell Formulas
RangeFormula
AG3:AR5AG3=IF($T3=$S3,DAY(AG$1)/($AR$1-$AG$1+DAY($AG$1)),(MAX(-1,MIN(AG$1,$T3)-MAX(AG$1-DAY(AG$1)+1,$S3))+1)/($T3-$S3+1))*$AE3
AS3:AS5AS3=SUM(AG3:AR3)
AT3:AT5AT3=AE3=AS3
 

SAMEERS

New Member
Joined
Jul 8, 2020
Messages
8
Office Version
  1. 2019
Looks like I should have tested on more rows! The formula is lacking some $ signs to lock the date ranges to the top row. Here's an updated version:

Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
1ECEMENWAENCALOBCountryAnualized CTC $USDHourly Rate $ USDW HrsLoading %LCTC $Hourly Loaded Rate $ USDWeekly Loaded CTC $USDMLCTC $Action TypeAction DateStart DateEnd DateAction Duration (WKs)Intermitent Furlough Working PeriodAction% Week's SavedAction Period LoadingEffective Loaded Rate by Action TypeHour reduction per week (in %)Saved HrsBase Salary/WASe/ Rate reduction (in %)Weekly Salary SavingsAction Period SavingsAverASe Monthly Savings30-Apr-2031-May-2030-Jun-2031-Jul-2031-Aug-2030-Sep-2031-Oct-2030-Nov-2031-Dec-2031-Jan-2128-Feb-2131-Mar-21FY 20Validation
2( Wks)(Saved Weeks )
3123ABCSalaried - ExemptH1 BGF1AUSA160115774022.70%19646194377816372Involuntary3-Apr-203-Apr3-Apr0030100%23%940%00%3778114854226699440.054799754.72339440.05489754.72339754.72339440.05489754.72339440.05489754.72339754.72338810.71789754.7233114854TRUE
4321CBASalaried - ExemptH1 BGF2CUSA24149124024.00%29945145762495RIF19-Mar-2031-Mar31-Mar0030100%24%140%00%5761750634551438.849321486.8111438.84931486.8111486.8111438.84931486.8111438.84931486.8111486.8111342.9261486.81117506TRUE
5444SSSSalaried - ExemptCitizenGF3DUSA100000484016.70%1167005622449725Furlough - continuous15-Apr-2015-Apr15-Sep31031100%17%560%00%224468673134657134.8571413823.78613377.85713823.78613823.7866688.928600000068673TRUE
Sheet1
Cell Formulas
RangeFormula
AG3:AR5AG3=IF($T3=$S3,DAY(AG$1)/($AR$1-$AG$1+DAY($AG$1)),(MAX(-1,MIN(AG$1,$T3)-MAX(AG$1-DAY(AG$1)+1,$S3))+1)/($T3-$S3+1))*$AE3
AS3:AS5AS3=SUM(AG3:AR3)
AT3:AT5AT3=AE3=AS3


Hi Eric - Thank you so very very much, your help is highly appreciated. I do not have words to express but you're a true "Guru"
Thank a ton!!

Cheers!!
SAM
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

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
Top