Today’s blog is a continuation of a series of articles discussing the need for salary coverage reports for managing personnel at research intensive institutions. In the first blog, dated April 2, 2015 we outlined the issues involved with monitoring salary coverage. In the second blog, dated April 27, 2015 we discussed the variables used in combining information in order to create reports for verifying salary coverage. In today’s blog we take a closer look at an example of a report format that works well for salary coverage analysis.
How to Create Salary Coverage Reports
Salary coverage reports are typically created manually with spread sheets or via a more automated process using a grant management software solution. In both cases, the end result are salary coverage report formats that allow grant, financial, and research administrators to review the current and future salary coverage for individual and groups of scientists and their associated research teams. Figure 1 shows a sample report format for reviewing an employee’s salary coverage.
Figure 1. Sample Report for Reviewing an Employee’s Salary Coverage
The sample report in Figure 1 presents the salary coverage information over a six month period for a hypothetical employee, Dr. Albert Jones. The report is divided into the three sections: a header at the top, followed by information regarding active funding by month in the middle, and information regarding pending projects at the bottom.
Salary Coverage Report Header
The report header includes the following information:
- Effective date (07/01/2015)
- Name of the employee (Dr. Albert Jones)
- Employee’s Annual Salary ($238,008.00) as of the effective date
- Employee’s Monthly Salary ($19,834.00) as of the effective dateThe employee’s monthly salary defines the institution’s monthly obligation for the employee and is used for comparison purposes in the lower sections of the report.
The effective date defines the starting point for the analysis.
Active Funding by Month
The active funding by month is presented in rows and columns in the middle section of the report. The rows present information per funding source. The bottom three rows of this section display the:
- Total amount of the columns in the row
- Percent of the monthly obligation covered by the total amount of the columns in the row
- Amount short of the monthly obligation in the row
We will discuss the last two rows in more detail as they are key to salary coverage analysis.
The columns in the active funding section display the:
- Unique identifying number for each active fund or project
- Unique identifying number for projects covered by cost sharing
- Date when the amount and percent of salary covered by the funding source starts
- Date when the amount and percent of salary covered by the funding source ends
- Percent of salary covered by the funding source
- Monthly amount provided by the funding source
While in most cases, 12 monthly amount columns are used, for the sake of simplicity, Figure 1 includes only six monthly amount columns. The month of the effective date is the first monthly amount column.The month of the column is displayed in the column header. Monthly columns dated between the start and end dates contain the amount of salary provided by the funding source listed in the first column. Monthly columns dated after the end date contain blank values (highlighted in red) and provide a visual indicator of gaps in future funding for the employee’s salary. This visual indicator allows the user of the report to quickly discern when funding is needed for an employee.The bottom two rows of this section provide information regarding how much funding is being provided and how much is needed in the future. The % Covered row shows the percentage of the monthly obligation that is provided. When an employee’s salary is fully covered, the value in this row is 100. Additional funding is needed for any month with a value less than 100. The $ Short row shows the amount of the monthly obligation that is not met and is needed to provided complete coverage to meet an employee’s salary requirements. This number can be quite large when there are funding shortages for highly compensated employees.
At most research institutions, alleviating funding shortages requires the acquisition of new funding via sponsored projects. And because acquiring new sponsored project funding requires time for proposal development, submission, and receipt of funding, the need for advanced notice, i.e. salary coverage monitoring is an essential part of grant management. While there is no guarantee that pending projects will be funded, it is often helpful to include them in the salary coverage reports as a way of monitoring the efforts that have already been made to acquire additional funding.
Pending projects are listed in the bottom section of the example report shown in Figure 1. The layout of this section is like that of the Active Funding section except that the:
- Unique identifying project id is replaced with the sponsor code and a unique proposal number
- Proposed budget period start and end dates are used as the start and end dates.
- Amount provided by the funding source is calculated by multiplying the committed effort on the proposal by the monthly salary.
The bottom row of this section is the sum of the percent covered by Active Funding and Pending Projects.
Interpretation of Salary Coverage Reports
The format of the report shown in Figure 1 leads to a quick realization of the situation with respect to salary coverage for Dr. Albert Jones. Unless new measures are taken, there may not be funding available to fully support his salary starting in September of 2015. Even if the two proposals which have been submitted are funded, there still may not be adequate funding starting in November of 2015. At most institutions, this would be considered a precarious situation commanding immediate action such as a renewed focus on submitting additional proposals, collaboration with other investigators, and others.
In the next blog of this series we will discuss how other factors such as no cost extensions, cost sharing, budget availability, etc. may affect salary coverage analysis and the situation with Dr. Jones.