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

?

?

?

?

?

?

?

?

?

 

[SAMEERS]

Hi Eric

RIF & Involuntary dates are same dates as Start & End Dates when the employee is terminated, therefore the salary paid is till last date & savings should reflect onwards of last date, which is not getting captured in the formula, how can we get this intact

 

[Eric W]

Could you show an example?

 

[SAMEERS]

Hi Ric

Please refer below, there are 2 Action Types

1. Furlough - The formula is capturing the savings with Start Date & End Date correctly
2. RIF ( Termination Date) - The formula is not capturing this correctly as for RIF Action the Start & End Date is the same

The employee was​

Furloughed​

between May 04, 2020 - Aug 13, 2020, then he was terminated on Aug 14, 2020
Formula used is =IF($U2=$T2,DAY(AH$1)/($AX$1-$AH$1+DAY($AH$1)),(MAX(-1,MIN(AH$1,$U2)-MAX(AH$1-DAY(AH$1)+1,$T2))+1)/($U2-$T2+1))*$AF2​

Condition 2 (RIF) should be capturing from Aug 14 - till the end

Please see e.g.​

SAP ID	Employee Name	Employee Type	Work Auth	Entity	Client Account	L0	LOB	Country	Anualized CTC $USD	Hourly Rate $ USD	Std Weekly Hrs	Loading %	Annual Loaded CTC $ USD	Hourly Loaded Rate $ USD	Weekly Loaded CTC $USD	Monthly Loaded CTC $USD	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/Wage/ Rate reduction (in %)	Weekly Salary Savings	Action Period Savings	Average Monthly Savings	30-Apr-20	31-May-20	30-Jun-20	AMJ'20	31-Jul-20	31-Aug-20	30-Sep-20	JAS'20	AMJ'20 & JAS'20 Combined	31-Oct-20	30-Nov-20	31-Dec-20	OND'20	AMJ'20 + JAS'20 + OND'20 Combined	31-Jan-21	28-Feb-21	31-Mar-21	JFM'21	FY '21
1	A	Salaried - Exempt	Citizen	1a	X	2.0​	2.01	USA	115000	55​	40	22.70%​	141105​	68​	2714​	11759​	Furlough - continuous	8-May-20	4-May-20	13-Aug-20	20	0	20	100%	9%	60	0%	0	0%	2411	48694	14463	0	13367	14322	27689	14799	6206	0	21005	48694	0	0	0	0	48694	0	0	0	0	48694
2	B	Salaried - Exempt	Citizen	1b	Y	2.0​	2.01	USA	115000	55​	40	22.70%​	141105​	68​	2714​	11759​	RIF	14-Aug-20	14-Aug-20	14-Aug-20	0	0	28	100%	23%	68	0%	0	0%	2714	75437	24599	6200	6407	6200	18808	6407	6407	6200	19014	37822	6407	6200	6407	19014	56836	6407	5787	6407	18601	75437

 

[SAMEERS]

Hi Eric

Please refer below, there are 2 Action Types

1. Furlough - The formula is capturing the savings with Start Date & End Date correctly
2. RIF ( Termination Date) - The formula is not capturing this correctly as for RIF Action the Start & End Date is the same

The employee was

Furloughed

between May 04, 2020 - Aug 13, 2020, then he was terminated on Aug 14, 2020
Formula used is =IF($U2=$T2,DAY(AH$1)/($AX$1-$AH$1+DAY($AH$1)),(MAX(-1,MIN(AH$1,$U2)-MAX(AH$1-DAY(AH$1)+1,$T2))+1)/($U2-$T2+1))*$AF2

Condition 2 (RIF) should be capturing from Aug 14 - till the end
Please see e.g.

SAP ID	Employee Name	Employee Type	Work Auth	Entity	Client Account	L0	LOB	Country	Anualized CTC $USD	Hourly Rate $ USD	Std Weekly Hrs	Loading %	Annual Loaded CTC $ USD	Hourly Loaded Rate $ USD	Weekly Loaded CTC $USD	Monthly Loaded CTC $USD	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/Wage/ Rate reduction (in %)	Weekly Salary Savings	Action Period Savings	Average Monthly Savings	30-Apr-20	31-May-20	30-Jun-20	AMJ'20	31-Jul-20	31-Aug-20	30-Sep-20	JAS'20	AMJ'20 & JAS'20 Combined	31-Oct-20	30-Nov-20	31-Dec-20	OND'20	AMJ'20 + JAS'20 + OND'20 Combined	31-Jan-21	28-Feb-21	31-Mar-21	JFM'21	FY '21
1	A	Salaried - Exempt	Citizen	1a	X	2.0	2.01	USA	115000	55	40	22.70%	141105	68	2714	11759	Furlough - continuous	8-May-20	4-May-20	13-Aug-20	20	0	20	100%	9%	60	0%	0	0%	2411	48694	14463	0	13367	14322	27689	14799	6206	0	21005	48694	0	0	0	0	48694	0	0	0	0	48694
2	B	Salaried - Exempt	Citizen	1b	Y	2.0	2.01	USA	115000	55	40	22.70%	141105	68	2714	11759	RIF	14-Aug-20	14-Aug-20	14-Aug-20	0	0	28	100%	23%	68	0%	0	0%	2714	75437	24599	6200	6407	6200	18808	6407	6407	6200	19014	37822	6407	6200	6407	19014	56836	6407	5787	6407	18601	75437

 

[Eric W]

Sorry I haven't replied sooner. This was a while back, and I had to remind myself how it works. For the RIF case, the first part of the code: $U2=$T2,DAY(AH$1)/($AX$1-$AH$1+DAY($AH$1)) specifically checks for when the start and end dates are the same. I thought we checked this originally. But can you manually calculate the values you want to see and put them in your sample sheet? I'm unclear as to what you want.