ESB forecast

Target · May 12th, 2002, 04:42 AM

Hello
I want to do a forecast for 4 different scenarios for one employee to calculate his END OF SERVICE BENEFIT (ESB) as per this input assuming that his basic salary is $10000 , the year is equal 52 weeks .

Service (Pro-rata) Basic Pay
Up to 5 years 3 weeks
Next 5 years 4 weeks
From 10 to 20 years 5 weeks
Exceeding 20 years 6 weeks

Thanx

PaddyD · May 12th, 2002, 06:44 AM

Target,

The following formula should work:

If you've got salary in field A6, and years service in B6 (with years service represented as 5, 10.2 etc), use:

=IF(B6<5,((A6/52)*3),IF(AND(B6>=5,B6<10),((A6/52)*4),IF(AND(B6>=10,B6<20),((A6/52)*5),IF(B6>=20,((A6/52)*6),"Check the year's service entered"))))

No doubt there are more elegant ways to do it...
Paddy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 12th, 2002, 04:42 AM	#1
Target New Member Join Date: Apr 2002 Posts: 31	Hello I want to do a forecast for 4 different scenarios for one employee to calculate his END OF SERVICE BENEFIT (ESB) as per this input assuming that his basic salary is $10000 , the year is equal 52 weeks . Service (Pro-rata) Basic Pay Up to 5 years 3 weeks Next 5 years 4 weeks From 10 to 20 years 5 weeks Exceeding 20 years 6 weeks Thanx

May 12th, 2002, 06:44 AM	#2
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	Target, The following formula should work: If you've got salary in field A6, and years service in B6 (with years service represented as 5, 10.2 etc), use: =IF(B6<5,((A6/52)3),IF(AND(B6>=5,B6<10),((A6/52)4),IF(AND(B6>=10,B6<20),((A6/52)5),IF(B6>=20,((A6/52)6),"Check the year's service entered")))) No doubt there are more elegant ways to do it... Paddy