How do I calculate standardised costs for centralised data management and security (obfuscating sensitive data)

This article explains how to implement a solution where we are unable to share actual salaries with our data consumers but need an approximation to actual in order for the data consumers to provide input and review

The solution is to create standardized costing to give spend estimates for scenarios and one way to implement this is via lookups: we can use lookups to store average employee costs by e.g. grade/location/department

This article assumes that the actual cost data per employee is stored in OrgVue, this is then used to generate standardized costs and the actual data is then hidden or removed.

In this example the standardized cost is dependent on the combination of grade and department. OrgVue is used to generate an average Total Payroll Cost by Grade and Department, these averages are then exported to Excel and re-added to OrgVue as a separate lookup table.

  • Create a new property in your OrgVue employee dataset which concatenates the properties on which the standardized cost is dependent e.g. for a concatenation of Grade and Department use the following expression in the property value box:
node.format("{grade} - {department}")


In the OrgVue dataset expand the property pane by clicking the double chevrons in the bottom middle of the screen, click ‘Add Property’ and enter the following values (or equivalent).

If the use case calls for more properties to influence the standardized cost then simply add these to the expression above.

 

  • A bar chart is used to generate average Total Payroll Cost by Grade and Department

  • These values are copied to the clipboard – click the clipboard icon in the chart controls and then press Ctrl + C

 

  • Open up an new Excel worksheet and press Ctrl + V to paste the chart data

 

  • Upload columns A and B to a lookup table in OrgVue with ‘Grade-Dept’ as the ID field. 

 

  • Back in your main dataset set a lookup from ‘Grade-Dept’ to your new lookup dataset.

  • You can now delete or hide the actual costs in the people dataset and use the lookup to give a standardised cost for each person dependent on their department and grade.

This article was authored by Miranda Copps from the OrgVue Consulting team

Have more questions? Submit a request

Comments