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

Waulk

New Member
Joined
Oct 25, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
613
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
BCDEFGHIJ
1LevelAvrg PayHeadCountCorrection on Total CostNew TotalNew Avrg
2JL 1$ 20035$ -2.738$ 4.262$ 122Total on Avrg$ 26.650
3JL 2$ 30025$ -1.956$ 5.544$ 222Known Total$ 20.000
4JL 3$ 40015$ -1.174$ 4.826$ 322Delta$ -6.650
5JL 4$ 5507$ -548$ 3.302$ 472
6JL 5$ 7002$ -156$ 1.244$ 622
7JL 6$ 9001$ -78$ 822$ 822
885$ -6.650$ 20.000
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=D2/$D$8*$J$4
F2:F7F2=C2*D2+E2
G2:G7G2=F2/D2
J2J2=SUMPRODUCT(C2:C7,D2:D7)
J4J4=J3-J2
D8:F8D8=SUM(D2:D7)
 

Waulk

New Member
Joined
Oct 25, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
BCDEFGHIJ
1LevelAvrg PayHeadCountCorrection on Total CostNew TotalNew Avrg
2JL 1$ 20035$ -2.738$ 4.262$ 122Total on Avrg$ 26.650
3JL 2$ 30025$ -1.956$ 5.544$ 222Known Total$ 20.000
4JL 3$ 40015$ -1.174$ 4.826$ 322Delta$ -6.650
5JL 4$ 5507$ -548$ 3.302$ 472
6JL 5$ 7002$ -156$ 1.244$ 622
7JL 6$ 9001$ -78$ 822$ 822
885$ -6.650$ 20.000
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=D2/$D$8*$J$4
F2:F7F2=C2*D2+E2
G2:G7G2=F2/D2
J2J2=SUMPRODUCT(C2:C7,D2:D7)
J4J4=J3-J2
D8:F8D8=SUM(D2:D7)
Thanks a lot for your response and this seems to be a very good approach with the data we have.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,823
Messages
5,544,538
Members
410,619
Latest member
gregor222
Top