Formula to calculate employee cost when total salary is given and industry average per employee is given

[Waulk]

Hi,

Please help with formula or a method to calculate per employee job level salary cost for different departments when we only have is total salary cost employee for entire dept. We are also given average salary for each level of employee.

Below are the details

Average salary per Job level per month

JL 1 - 200 $
JL 2 - 300 $
JL 3 - 400 $
JL 4 - 550 $
JL 5 - 700 $
JL 6 - 900 $

Total actual cost for a department - 20000 $

Below are the actual headcount given

JL 1 - 35
JL 2 - 25
JL 3 - 15
JL 4 - 7
JL 5 - 2
JL 6 - 1

We need to find per employee salary cost with reference to total cost given @ 20 K USD and with reference to average salary cost given per job level.

Please help.

Thanks

 

[GraH]

Dangerous to work with unknown parameters and averages without knowing deviations.
Maybe like this? Since you know the total and based on averages to have a theoretical total which is the either above/below the given total. Then you aply this delta based on headcount.
Not very scientific, and most likely there are better approaches. I just don't know them.

Book1
	B	C	D	E	F	G	H	I	J
1	Level	Avrg Pay	HeadCount	Correction on Total Cost	New Total	New Avrg
2	JL 1	$ 200	35	$ -2.738	$ 4.262	$ 122		Total on Avrg	$ 26.650
3	JL 2	$ 300	25	$ -1.956	$ 5.544	$ 222		Known Total	$ 20.000
4	JL 3	$ 400	15	$ -1.174	$ 4.826	$ 322		Delta	$ -6.650
5	JL 4	$ 550	7	$ -548	$ 3.302	$ 472
6	JL 5	$ 700	2	$ -156	$ 1.244	$ 622
7	JL 6	$ 900	1	$ -78	$ 822	$ 822
8			85	$ -6.650	$ 20.000
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=D2/$D$8*$J$4
F2:F7	F2	=C2*D2+E2
G2:G7	G2	=F2/D2
J2	J2	=SUMPRODUCT(C2:C7,D2:D7)
J4	J4	=J3-J2
D8:F8	D8	=SUM(D2:D7)

 

[Waulk]

Dangerous to work with unknown parameters and averages without knowing deviations.
Maybe like this? Since you know the total and based on averages to have a theoretical total which is the either above/below the given total. Then you aply this delta based on headcount.
Not very scientific, and most likely there are better approaches. I just don't know them.

Book1
	B	C	D	E	F	G	H	I	J
1	Level	Avrg Pay	HeadCount	Correction on Total Cost	New Total	New Avrg
2	JL 1	$ 200	35	$ -2.738	$ 4.262	$ 122		Total on Avrg	$ 26.650
3	JL 2	$ 300	25	$ -1.956	$ 5.544	$ 222		Known Total	$ 20.000
4	JL 3	$ 400	15	$ -1.174	$ 4.826	$ 322		Delta	$ -6.650
5	JL 4	$ 550	7	$ -548	$ 3.302	$ 472
6	JL 5	$ 700	2	$ -156	$ 1.244	$ 622
7	JL 6	$ 900	1	$ -78	$ 822	$ 822
8			85	$ -6.650	$ 20.000
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=D2/$D$8*$J$4
F2:F7	F2	=C2*D2+E2
G2:G7	G2	=F2/D2
J2	J2	=SUMPRODUCT(C2:C7,D2:D7)
J4	J4	=J3-J2
D8:F8	D8	=SUM(D2:D7)

Thanks a lot for your response and this seems to be a very good approach with the data we have.