Cost Spread Allocation

[SAMEERS]

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

E.ID

EM

ET

WA

EN

CA

LOB

Country

Anualized CTC $USD

Hourly Rate $ USD

W Hrs

L%

LCTC $

Hourly Loaded Rate $ USD

Weekly Loaded CTC $USD

MLCTC

Action Type

Action Date

Start Date

End Date

Action Duration (WKs)

Interm Furlough Working Period ( Wks)

Action (Saved Weeks )

% Week's Saved

APL%

Effective Loaded Rate by Action Type

Hour reduction per week (in %)

Saved Hrs

Base Salary/Wage/ Rate reduction (in %)

Weekly Salary Savings

Action Period Savings

Average Monthly Savings

30-Apr-20

31-May-20

30-Jun-20

31-Jul-20

31-Aug-20

30-Sep-20

31-Oct-20

30-Nov-20

31-Dec-20

51620396

ABC

P

Y

GF

PPE

NN

US

41600​

20​

40

18.00%​

49088​

24​

944​

4091​

Furlough - continuous

20-Apr-20​

27-Apr-20​

4-Jul-20​

14

0

14

100%

18%

24

0%

0

0%

944

12838

5664

?

?

?

?

?

?

?

?

?

 

[Eric W]

Welcome to MrExcel!

Do you mean like this?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	E.ID	EM	ET	WA	EN	CA	LOB	Country	Anualized CTC $USD	Hourly Rate $ USD	W Hrs	L%	LCTC $	Hourly Loaded Rate $ USD	Weekly Loaded CTC $USD	MLCTC	Action Type	Action Date	Start Date	End Date	Action Duration (WKs)	Interm Furlough Working Period	Action	% Week's Saved	APL%	Effective Loaded Rate by Action Type	Hour reduction per week (in %)	Saved Hrs	Base Salary/Wage/ Rate reduction (in %)	Weekly Salary Savings	Action Period Savings	Average Monthly Savings	30-Apr-20	31-May-20	30-Jun-20	31-Jul-20	31-Aug-20	30-Sep-20	31-Oct-20	30-Nov-20	31-Dec-20
2																						( Wks)	(Saved Weeks )
3	51620396	ABC	P	Y	GF	PPE	NN	US	41600	20	40	18.00%	49088	24	944	4091	Furlough - continuous	20-Apr-20	20-Apr-2020	04-Jul-2020	14	0	14	100%	18%	24	0%	0	0%	944	12838	5664	1858.13158	5236.55263	5067.6316	675.684211	0	0	0	0	0
Sheet5

Cell Formulas
Range	Formula
AG3:AO3	AG3	=(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]

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]

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]

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​

​

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

EC

EM

EN

WA

EN

CA

LOB

Country

Anualized CTC $USD

Hourly Rate $ USD

W Hrs

Loading %

LCTC $

Hourly Loaded Rate $ USD

Weekly Loaded CTC $USD

MLCTC $

Action Type

Action Date

Start Date

End Date

Action Duration (WKs)

Intermitent Furlough Working Period ( Wks)

Action (Saved Weeks )

% Week's Saved

Action Period Loading

Effective Loaded Rate by Action Type

Hour reduction per week (in %)

Saved Hrs

Base Salary/WASe/ Rate reduction (in %)

Weekly Salary Savings

Action Period Savings

AverASe Monthly Savings

30-Apr-20

31-May-20

30-Jun-20

31-Jul-20

31-Aug-20

30-Sep-20

31-Oct-20

30-Nov-20

31-Dec-20

31-Jan-21

28-Feb-21

31-Mar-21

FY 20

Validation

123

ABC

Salaried - Exempt

H1 B

GF

1​

A

USA

160115

77​

40

22.70%​

196461​

94​

3778​

16372​

Involuntary

3-Apr-20​

3-Apr-20​

3-Apr-20​

0

0

30

100%

23%

94

0%

0

0%

3778

114854

22669

9440

10628

11334

12994

14652

16253

19563

22819

29425

39561

54507

114854

356032

FALSE​

321

CBA

Salaried - Exempt

H1 B

GF

2​

C

USA

24149​

12​

40

24.00%​

29945​

14​

576​

2495​

RIF

19-Mar-20​

31-Mar-20​

31-Mar-20​

0

0

30

100%

24%

14

0%

0

0%

576

17506

3455

1439

1620

1728

1981

2233

2477

2982

3478

4485

6030

8308

17506

54266

FALSE​

444

SSS

Salaried - Exempt

Citizen

GF

3​

D

USA

100000

48​

40

16.70%​

116700​

56​

2244​

9725​

Furlough - continuous

15-Apr-20​

15-Apr-20​

15-Sep-20​

31

0

31

100%

17%

56

0%

0

0%

2244

68673

13465

7135

13824

13378

13824

13824

6689

0

0

0

0

0

0

68673

TRUE​

​

 

[Eric W]

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]

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

EC

EM

EN

WA

EN

CA

LOB

Country

Anualized CTC $USD

Hourly Rate $ USD

W Hrs

Loading %

LCTC $

Hourly Loaded Rate $ USD

Weekly Loaded CTC $USD

MLCTC $

Action Type

Action Date

Start Date

End Date

Action Duration (WKs)

Intermitent Furlough Working Period ( Wks)

Action (Saved Weeks )

% Week's Saved

Action Period Loading

Effective Loaded Rate by Action Type

Hour reduction per week (in %)

Saved Hrs

Base Salary/WASe/ Rate reduction (in %)

Weekly Salary Savings

Action Period Savings

AverASe Monthly Savings

30-Apr-20

31-May-20

30-Jun-20

31-Jul-20

31-Aug-20

30-Sep-20

31-Oct-20

30-Nov-20

31-Dec-20

31-Jan-21

28-Feb-21

31-Mar-21

FY 20

Validation

123

ABC

Salaried - Exempt

H1 B

GF

1​

A

USA

160115

77​

40

22.70%​

196461​

94​

3778​

16372​

Involuntary

3-Apr-20​

3-Apr-20​

3-Apr-20​

0

0

30

100%

23%

94

0%

0

0%

3778

114854

22669

9440

10628

11334

12994

14652

16253

19563

22819

29425

39561

54507

114854

356032

FALSE​

321

CBA

Salaried - Exempt

H1 B

GF

2​

C

USA

24149​

12​

40

24.00%​

29945​

14​

576​

2495​

RIF

19-Mar-20​

31-Mar-20​

31-Mar-20​

0

0

30

100%

24%

14

0%

0

0%

576

17506

3455

1439

1620

1728

1981

2233

2477

2982

3478

4485

6030

8308

17506

54266

FALSE​

444

SSS

Salaried - Exempt

Citizen

GF

3​

D

USA

100000

48​

40

16.70%​

116700​

56​

2244​

9725​

Furlough - continuous

15-Apr-20​

15-Apr-20​

15-Sep-20​

31

0

31

100%

17%

56

0%

0

0%

2244

68673

13465

7135

13824

13378

13824

13824

6689

0

0

0

0

0

0

68673

TRUE​

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	EC	EM	EN	WA	EN	CA	LOB	Country	Anualized CTC $USD	Hourly Rate $ USD	W Hrs	Loading %	LCTC $	Hourly Loaded Rate $ USD	Weekly Loaded CTC $USD	MLCTC $	Action Type	Action Date	Start Date	End Date	Action Duration (WKs)	Intermitent Furlough Working Period	Action	% Week's Saved	Action Period Loading	Effective Loaded Rate by Action Type	Hour reduction per week (in %)	Saved Hrs	Base Salary/WASe/ Rate reduction (in %)	Weekly Salary Savings	Action Period Savings	AverASe Monthly Savings	30-Apr-20	31-May-20	30-Jun-20	31-Jul-20	31-Aug-20	30-Sep-20	31-Oct-20	30-Nov-20	31-Dec-20	31-Jan-21	28-Feb-21	31-Mar-21	FY 20	Validation
2																						( Wks)	(Saved Weeks )
3	123	ABC	Salaried - Exempt	H1 B	GF	1	A	USA	160115	77	40	22.70%	196461	94	3778	16372	Involuntary	3-Apr-20	3-Apr	3-Apr	0	0	30	100%	23%	94	0%	0	0%	3778	114854	22669	9440.05479	9754.7233	9440.0548	9754.7233	9754.7233	9440.0548	9754.7233	9440.0548	9754.7233	9754.7233	8810.7178	9754.7233	114854	TRUE
4	321	CBA	Salaried - Exempt	H1 B	GF	2	C	USA	24149	12	40	24.00%	29945	14	576	2495	RIF	19-Mar-20	31-Mar	31-Mar	0	0	30	100%	24%	14	0%	0	0%	576	17506	3455	1438.84932	1486.811	1438.8493	1486.811	1486.811	1438.8493	1486.811	1438.8493	1486.811	1486.811	1342.926	1486.811	17506	TRUE
5	444	SSS	Salaried - Exempt	Citizen	GF	3	D	USA	100000	48	40	16.70%	116700	56	2244	9725	Furlough - continuous	15-Apr-20	15-Apr	15-Sep	31	0	31	100%	17%	56	0%	0	0%	2244	68673	13465	7134.85714	13823.786	13377.857	13823.786	13823.786	6688.9286	0	0	0	0	0	0	68673	TRUE
Sheet1

Cell Formulas
Range	Formula
AG3:AR5	AG3	=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:AS5	AS3	=SUM(AG3:AR3)
AT3:AT5	AT3	=AE3=AS3

 

[SAMEERS]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	EC	EM	EN	WA	EN	CA	LOB	Country	Anualized CTC $USD	Hourly Rate $ USD	W Hrs	Loading %	LCTC $	Hourly Loaded Rate $ USD	Weekly Loaded CTC $USD	MLCTC $	Action Type	Action Date	Start Date	End Date	Action Duration (WKs)	Intermitent Furlough Working Period	Action	% Week's Saved	Action Period Loading	Effective Loaded Rate by Action Type	Hour reduction per week (in %)	Saved Hrs	Base Salary/WASe/ Rate reduction (in %)	Weekly Salary Savings	Action Period Savings	AverASe Monthly Savings	30-Apr-20	31-May-20	30-Jun-20	31-Jul-20	31-Aug-20	30-Sep-20	31-Oct-20	30-Nov-20	31-Dec-20	31-Jan-21	28-Feb-21	31-Mar-21	FY 20	Validation
2																						( Wks)	(Saved Weeks )
3	123	ABC	Salaried - Exempt	H1 B	GF	1	A	USA	160115	77	40	22.70%	196461	94	3778	16372	Involuntary	3-Apr-20	3-Apr	3-Apr	0	0	30	100%	23%	94	0%	0	0%	3778	114854	22669	9440.05479	9754.7233	9440.0548	9754.7233	9754.7233	9440.0548	9754.7233	9440.0548	9754.7233	9754.7233	8810.7178	9754.7233	114854	TRUE
4	321	CBA	Salaried - Exempt	H1 B	GF	2	C	USA	24149	12	40	24.00%	29945	14	576	2495	RIF	19-Mar-20	31-Mar	31-Mar	0	0	30	100%	24%	14	0%	0	0%	576	17506	3455	1438.84932	1486.811	1438.8493	1486.811	1486.811	1438.8493	1486.811	1438.8493	1486.811	1486.811	1342.926	1486.811	17506	TRUE
5	444	SSS	Salaried - Exempt	Citizen	GF	3	D	USA	100000	48	40	16.70%	116700	56	2244	9725	Furlough - continuous	15-Apr-20	15-Apr	15-Sep	31	0	31	100%	17%	56	0%	0	0%	2244	68673	13465	7134.85714	13823.786	13377.857	13823.786	13823.786	6688.9286	0	0	0	0	0	0	68673	TRUE
Sheet1

Cell Formulas
Range	Formula
AG3:AR5	AG3	=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:AS5	AS3	=SUM(AG3:AR3)
AT3:AT5	AT3	=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

 

[Eric W]

I'm glad it works for you! Thanks for the update.