1/14/2010

Instructions for Generating ARRA Linked Accts

After installing the Excel Add-in, while you are in Excel, click on Tools | QDB GLPPP FPM ADHOC reporting. After logging in, using your QDB login and password, you will see the following Custom Reports – Main Menu. Make sure the version date is Jan. 14, 2010 or later. Select “ARRA Linked Accts by Hierarchy Code” - push Next.

On the hierarchy screen that follows, indicate the 4 digit code for your Organization, Division, Subdivision or Department. Leave an asterisk “*” in all the other fields. Click Finish.

Note: the enhancement to this screen now allows you to put an asterisk in all fields of hierarchy code and indicate a fund number in order to get all the accounts across campus that may be linked to your fund.

When you run it for your department, you will get the following report which is a complete list of ARRA funds linked to your accounts. If you are a recipient of ARRA funding from another department, the fund owner is also listed in this report. You will need to submit your information to the home department prior to the specified departmental deadline so that they have enough time to compile the information and enter in the mandatory ARRA Report Submission Website.

Instructions for Generating ARRA – FTE Report

In Excel, click on Tools, QDB GLPPP FPM ADHOC reporting. Choose the “ARRA – FTE Report” on the Custom Reports - Main Menu. Make sure “Organizational” is selected towards the bottom and push Next.

On the following screen, enter your Department FS code. If your ARRA funds are linked to other accounts in your organization, you can indicate “*” in the Department box and select a higher hierarchy level such as Subdivision, Division or Organization, to get at once, all payroll data for that fund in the selected hierarchy level.

Note: the enhancement to this screen now allows you to indicate * in all the fields in order to get all payroll for your fund to which you have access in the DACCS system. This functionality was primarily added for EFM.

In order to run all your payroll reports as a batch, on the following screen click on “Load”. It will either load all your FAUs or provide you a list of sheets to select from. In this case, select the sheet called: “ARRA – by Hierarchy Code”. Once you see that your FAUs are loaded in the batch sheet, push Next.


You can also run your reports one by one by entering the ARRA fund number in the FAU screen and pushing “Next”. You can either indicate the Account and CC as well, or leave “*” for Account and CC to get payroll data on all accounts/cc linked to this fund, that fall under the hierarchy level you chose earlier and for which you have EDB Inquiry in the DACSS system.

The feature for projecting one additional month is built into this tool. Depending on the departmental deadline for a given quarter, in relation to the release date of payroll for the last month of that quarter, you may or may not need to include a projection for the last month of that quarter. Meaning if the departmental deadline is several days after the release of payroll ledgers for the last month of that quarter, then you can use actual payroll data for all 3 months and not use one month projection. Otherwise, you do.

Since ARRA reporting is quarterly, on the following Date screen, either indicate the 1st 2 months of the quarter and mark the “Include 1 Month Projection” check box or if actual payroll data is available for all 3 months, indicate the “From” and “To” dates to include all 3 months. For Date Source, leave the default at “Pay Period Ending (earned)” to only get what is relevant and earned in that quarter, regardless of retroactive and other adjustments.

On the screen that follows click on the “Finish” button at the bottom of the screen.

You will get the following report. In order to assist with ARRA FTE calculation, column O, the highlighted FTE column is automatically calculated using the rules listed below.

1. For rows with DOS codes that are excluded from ERS, the "FTE" column O will be blank.

2. For rows which have a "Rate Adj. Indicator" meaning they are a retro pay for salary increase, the "FTE" column O will be blank.

3. If pay is hourly, therefore column N shows “H” - in the "FTE" column, we will see the result of number of hours paid, divided by 174.

4. If the pay is %, therefore column N shows “%” - in the "FTE" column, we will see the percentage of pay, equal to the value in column M. This applies to summer ninth DOS codes as well (ACR, ACT, etc.)

If you had selected “Include 1 Month Projection”, the row showing the projected month would have been highlighted in yellow. In the above example, we did not include projection.

After reviewing the payroll entries to make sure the entries and projections make sense to you, click on “2” in the upper left corner of your spreadsheet as shown below.

The report will roll up and get summarized as shown below:

For each person, select the “Labor Category” and “Created or Retained”, by clicking on the subtotal line in those columns and using the drop down boxes, as shown in the above print-screen. (If you need to see the detail rows again, click on “3” in the upper left corner of your spreadsheet).

At this point, summarize and tally your Quarterly Average FTE figures by “Labor Category” and “Created versus Retained”, for the whole fund, including Quarterly Average FTEs on other department accounts linked to your fund. Enter these final Quarterly Average FTE amounts in the mandatory ARRA Report Submission Website. Keep a copy of your Excel file for future reference.

Special instructions for calculating Quarterly Average FTE for 9-month faculty (Does not apply to School of Medicine and Dentistry):

For calculating Quarterly Average FTE for summer ninths, DOS codes ACR, ACT, etc., the built-in formula in col. P that divides col. O by 3, is correct.

However, for REG and FEP DOS codes of 9-month faculty, the formula in col. P should be: divide FTE (Col. O) by 3, then multiply by 75%.

For faculty who have ACR, as well as REG & FEP DOS codes on ARRA funds, each row should have the appropriate formula.

1