11/24/99
Excel PAR PIVOT DOCUMENTATION
IMPORTANT MESSAGE:
This tool automates the calculation of percentage of TIME PAID according to payroll data. PAR certification is not merely a verification of payroll records but is an after-the-fact certification of ALL EFFORT expended on Contracts & Grants. This includes both direct charge effort as well as cost shared effort. Cost shared effort as it applies here is defined as effort devoted to a particular sponsored project but not charged directly to that project e.g. charged to 19900 or any other unrestricted funds.
In addition, this Excel tool is in “TEST MODE” at this time. It is being rolled out to the School of Medicine for further testing by users. Please review your findings carefully as described below and if you find mistakes or ambiguities, notify Neda Navab X4-1916 immediately.
When is the Best Time to Generate a PAR Pivot Report?
Campus PARs get distributed about 45 days after the end of the quarter. Wait another 2 weeks before running the PAR Pivot report, in order to capture 2 months worth of adjustments. For example for the quarter July to Sept, run it after November payroll has been loaded onto the QDB. This information is available on the “Welcome” screen of the QDB GL/PPP program, in the box labeled: “Most Recent Closed PPP”.
How to Generate the PAR Pivot Report
Using QDB GL/PPP Program, in Excel, under “Tools” on the menu bar, click on QDB GL/PPP. Under Payroll Expense Distribution, click on “Organizational Unit” and click “Next”. Select “Department” as “Unit Type” and enter your 4-digit department code and click “Next”. Leave the next FAU screen at default with “*” in all fields and click “Next”. Choose the “From” and “To” months for the quarter that you are reporting for. For “Date Source” choose “Pay Period Ending (earned)” and click on “Next”. On the next screen, under “Standard Reports”, click on “PAR” and then click on “Finish”. Excel will create 2 sheets for you. The first one is the detail data. You will be asked if you want to print the PAR population list.
If you answer yes, Excel will print it for you and will then take you to the sheet which contains the Pivot table report and is labeled “PAR-Pivot”.
If you answer no, Excel will still take you to the sheet which contains the Pivot table report and is labeled “PAR-Pivot”.
If you decide to print the PAR population list later, go to the sheet with detail data, select the rows that have a “Y” in the “Need PAR?” column and are sorted at the top of the sheet. Highlight these rows through column V and click on “File”, “Print”. At the bottom left side of the “Print pop up screen” click on “Selection” and then click “OK”. If you don’t select “Selection” you will print your entire department payroll for 3 months!!!!
By comparing this list to the PAR population list you received from Campus, you will notice differences. This is due to the fact that for employees paid on accounts across departments, the EDB system changes the “home department”. These employees will appear on the department’s PAR population list to which they were assigned at the time Campus PARs were processed. So there is a chance the employee that appears on your Excel PAR population list, may appear on another department’s Campus PAR population list. To assure that a Campus PAR was in fact generated but was just sent to another department, send an e-mail to Olga Sanders with the names of all the missing employees. Olga will verify the information for you.
Please note that the built-in criteria for creating the Excel PAR population list is based on fund numbers as explained below. There is also built-in criteria for including or excluding DOS codes.
PAR Population List – Based on built in criteria for fund numbers and fund codes
Anybody who is paid on the following Federal Funds, needs to have a PAR completed:
Funds in the range of 21000 to 33999 or funds with “Fund Flow Type” = 2 or 3 regardless of fund number.
(Exceptions: the following funds are disregarded: funds 26555 and 26570, 23490 through 23499, 23462 through 23469, 24853 through 24872).
Grouping for “Sponsored Projects” versus “All Other Institutional Activities”
All Federal, State, Local and Private Contract & Grant (C&G) funds are classified as “Sponsored Projects”.
State C&G funds are 20600 through 20999 and 18200 through 18999, excluding 18888.
Local C&G funds are 77001 through 77086 and 77097 through 77149.
Private C&G funds are 57000 through 59999 and 78000 through 79999.
All other funds are classified as “All Other Institutional Activities”.
PAR DOS Codes – Built in Criteria
See attached for DOS codes that are included or excluded in the Excel PAR Pivot program.
Comparing Excel PARs to Campus PARs
On the “PAR-Pivot” sheet in Excel, click on the arrow in cell B1 to see a list of all your employees. Click on the first employee for who you have a campus PAR. Compare the percentages. For most employees there will be a perfect match and that is fine. If you see a difference, it could be due to the following reasons:
Reasons for differences between Campus PAR and PAR Pivot
1.Multiple Departments
For employees paid on accounts across departments, your original query which was based on your departments accounts only, will not show other departments account, but the Campus PAR will show all accounts. For these individuals, in order to get information on all accounts across departments, run the following query:
Using QDB GL/PPP Program, in Excel, under “Tools” on the menu bar, click on QDB GL/PPP. Under Payroll Expense Distribution, click on “Employee” and click “Next”. Type the name of the person, click on “#” sign, select the right individual and click “Next”. Leave the next FAU screen at default with “*” in all fields and click “Next”. Choose the “From” and “To” months for the quarter that you are reporting for. For “Date Source” choose “Pay Period Ending (earned)” and click on “Next”. On the next screen, under “Standard Reports”, click on PAR then click “Finish”. Excel will create 2 sheets for you. The first one is the detail data.
Check the percentages on the sheet labeled “PAR-Pivot” to see if it matches the Campus PAR. If it still doesn’t match, maybe the difference is due to:
2.NIH Salary Cap
The campus PAR percentages are calculated based on gross earnings which need to be adjusted for faculty salary exceeding the NIH cap. The Excel PAR Pivot percentages are calculated based on percentage of time which is the appropriate calculation for faculty salary exceeding NIH cap. However, additional review may be necessary to determine the actual effort expended per fund.
To be sure that the difference in the two PARs is only due to this factor, you need to slightly redesign the pivot table as follows:
When you run a pivot table in Excel, the Pivot Table toolbar appears on your screen and it looks like this:
If you don’t see this toolbar then you can choose the Toolbar command from the View menu and choose PivotTable.
Select a cell in the pivot table, and then click on the first button on the left of this toolbar to go back to the drawing board or layout screen.
The following screen will pop up.
Remove “Sum of Time” button and replace by “Gross Earnings”. Double click on “Sum or Gross Earnings”, click on ”Options”, click on arrow next to “Show Data As” box and select “% of column”, click “OK”, then “Finish”. If you get the same as the Campus PAR, you can be assured that this explanation applies and the Pivot PAR are the correct percentages.
(REMEMBER TO CLICK THE UNDO BUTTON TO GO BACK TO HOW YOUR PIVOT ORIGINALLY WAS: BASED ON “SUM OF TIME”)
If you don’t get the same as the Campus PAR, it might be due to:
3.Adjustments
The Campus PARs do not reflect adjustments made after the quarter for the quarter, but the Excel PAR Pivot does. The easiest way to find out if this is the cause of difference between the 2 PARs, on the pivot table report, double click on “100%”. The rows included in the 100% calculation for that individual get copied from the main detail spreadsheet and a new sheet gets created just for that person which you can use as a “scratch paper” and then delete. Look in the “Led YM” column. Any rows with Led YM beyond the quarter you are reporting on will cause a difference. To verify that, try deleting this(these) line(s) in your “scratch paper” and recalculate the percentage of effort. If the difference is due to this factor, you can be assured that the Excel PAR Pivot is giving you the correct percentages. If not, the difference might be due to:
4. Partial Retro Adjustments
If within the quarter (i.e. in June for April), you do a partial retro adjustment, the Campus PAR will credit the entire original amount which you put on the Upay and debit the partial amount on the new fund. The Excel PAR program debits and credits in equal amounts which is the correct way. If your discrepancy is due to this factor, you can be assured that the Excel PAR percentages are correct.
5. DOS codes
We were not able to figure out exactly which DOS codes are included or excluded in the Campus PAR system. So we reviewed all DOS codes used by School of Medicine and tried to guess and put in the Excel program what made sense. (See attached list of excluded/included DOS codes).
By testing two entire departments it appears that our guess about DOS codes are correct. However, we have not tested the whole school. So if you encounter discrepancies that cannot be explained by the previous factors, take a look at the DOS codes and inform us immediately so we can look into it and if necessary modify the DOS codes built into the program.
6. Shift Differential DOS codes
The Campus PAR calculates percentages based on gross earnings. The Excel Pivot program calculates percentages based on number of hours, converted to percentage of effort. Therefore, shift differential as well as premium overtime payments cause a discrepancy between the Campus PAR and the Excel PAR. Since PAR is effort reporting, percentages calculated based on number of hours are correct. Therefore in this scenario, percentages in the Excel program are correct. To be sure that the difference in the two PARs is only due to this factor, follow the instructions in the boxed section of “2. NIH Salary Cap”.
Note: for rare instances where an employee gets paid both on a percentage basis and hourly basis, the Excel program, converts the hours to percentages to prevent problems due to this factor.
Acknowledgments
Special thanks to:
Tom Kaner, CFO, Department of Pathology for coming up with the great idea of using Excel features to manipulate payroll data on QDB to facilitate calculation of percentages for PAR verification and designing the base pivot table.
Mike Hassanvand, programmer, Dean’s Office for building in all the necessary criteria and automating the pivot table to make it as easy as possible for the users.
Olga Sanders, Extramural Fund Management, for guiding us throughout this process.
Mike Anthony, Lynne Yorita, Rochelle Caballero, Albert Glover and Blakely Pallock for providing feedback and insight.
Documentation prepared by: Neda Navab
Dean’s Office, School of Medicine
1