Have you ever been in this situation? The lead negotiator for the district says that they need those calculations for the cost of a 1% raise and the cost of one day of salary. Negotiations are starting in 10 minutes. You get that frozen look on your face because this is the first time you are hearing about this request. Sorry, they say. Just bring in the calculations when you have them.
There go your plans for this morning.
These are calculations that you’ll be asked for so routinely that you are best served to include them along with your periodic financial reporting. Pull personnel data as of the same date, and distribute to upper management along with the financial report.
Both calculations can be derived from one Excel download, using pivot tables. If you don’t know how to use pivot tables, learn fast here.
The downloaded file needs to contain the following columns:
- Employee number
- Employee Name (probably 2 columns with last name and first name)
- Job name or number
- Full time equivalency (FTE)
- Annual number of days for that job (full number of days, not actual number for an employee who started late)
- Bargaining Unit
- Columns with each component of the account string in a separate column
- Annualized salary cost (as if the person filling the position had been there all year, even if they started late)
- Add-ons such as masters stipend
- Columns showing employer’s cost of benefits such as PERS, STRS, Social Security, Medicare, Health Plan, Dental Plan and so on
Add the following columns, if they are not included in the download: total salary (base plus add-ons), total benefit costs without health and welfare (and without any other flat-rate benefits such as life insurance), total cost without H&W, Cost of 1% (total cost without H&W x 0.01), Cost of Once Day (total cost without H&W divided by number of days).
The Cost of One Percent
Using a pivot table, display the cost of 1% as a table by fund and bargaining unit.
The Cost of One Day
Using a pivot table, display the cost of one day as a table by fund and bargaining unit.
Determine whether you want to include or exclude vacancies and use that assumption consistently.
The same data download can be used to calculate the average teacher cost, the average new teacher cost (if start date is part of the file) as well as numerous other ad hoc analyses as requested. Use it for the J-90 and the SARC. Use it to reconcile FTE’s at each reporting period. Use it to project retirements (if birthdate is part of the file).
The hardest part of this is to work with your IT department to get exactly the download that you need quickly and accurately. When you are adept at pivot tables, you don’t need a separate download for each calculation. In fact one “personnel download” should be created so that it contains all the columns you need for all of the analyses you are asked for.
And, once you get into the routine of generating the file and the pivot tables you’ll realize that doing the same thing over and over again lends itself nicely to the use of a macro and delegation to junior staff. Done and done.
FiscalShare 