New SPA Excel Spreadsheet
User Guidelines
Overview: To enhance efficiency and financial compliance, UNMC has designed a spreadsheet that supports the preparation of UNMC’s internal routing and budget forms. The User Guidelines outline steps for navigating the new spreadsheet.
UNMC policy requires that routing and budget forms accompany applications submitted through Sponsored Programs Administration (SPA) for institutional approval and signature. If you have questions, please call SPA at 559-7456.
General Instructions: SPA offers these tips for working with the interactive Excel form. Software
• Excel 95 or a more recent version is required to use the form. Security
• When opening the spreadsheet, an error message stating, “Macros in this
workbook are disabled because the security level is high . . .”, may appear. Click OK in the dialog box. Follow the steps on the Security page to adjust macro security settings:
1. Select “Tools” from the menu bar.
2. Choose “Options” from the drop-down menu. 3. From the Options box, click on the “Security” tab. 4. Click on the “Macro Security” button.
5. Select a medium security setting. 6. Click “OK.”
7. For the security change to take effect, close and re-open the file. • If you do not receive an error message, move to the Welcome page. Getting Started
• Information on the Welcome page introduces users to the components of the spreadsheet.
• Should you need to start over, click the “Reset Form” button. This action clears entered information on the spreadsheet pages.
• Use the “NEXT” button to move forward through the pages. Using the “NEXT” button activates formulas and reveals hidden pages, allowing the spreadsheet to function properly.
• Once you have read the Welcome page, click the “NEXT” button to move to the
View page
o As you become familiar with the form, you may choose to skip the
Welcome page and go directly to the View page by clicking “NEXT.”
o You may skip both pages (Welcome and View) and go directly to the
SPAdministration Routing Page
UNMC Information• Principal Investigator: Enter the Principal Investigator’s (PI) name. • Faculty Member?: Check the appropriate box, “Y” for Yes or “N” for No. • Phone: Enter the PI’s UNMC phone number.
• Zip: Enter the PI’s UNMC zip code.
• Department: Enter the PI’s UNMC department.
• Contact Person #1 & #2: Enter the name(s) of the departmental administrator and/or support staff who will be the main contact(s) for this proposal.
• Phone: Enter the UNMC phone number(s) of the contact person(s). • Zip: Enter the UNMC zip code(s) of the contact person(s).
Proposal Information
• Proposal Title: Enter the proposal title as stated on the sponsor’s application. • Sponsor: Enter the name of the sponsor.
• Deadline Date: Enter the sponsor’s application deadline. Indicate whether the deadline is a “Postmark” or “Receipt” date by checking the appropriate box. • Address #1 and Address #2: Enter the address(es) for the sponsor.
• Phone #1 and Phone #2: Enter the phone number(s) for the sponsor.
• Mailing Instructions: Check the correct box for mailing the proposal. Include a UNMC chargeback number.
• Number of Proposal Requested by the Sponsor: Enter the number of proposal copies to be submitted to the sponsor.
• Proposal Type: Check the correct box for the proposal type. For
Non-Competitive and Non-Competitive Renewals include the WBS# of the active award. • Funding Category: Check the correct box for the funding category. If applicable,
• Project Type: Check the correct box(es) for the project type.
• Special Considerations: Check all boxes that apply to the project proposal. Research Compliance
• Human Subjects Approval: If the proposal uses human subjects, check “Yes.” If the study does not require the use of human subjects, check “No.”
• If yes: If you checked “Yes” for Human Subjects Approval, check the correct approval sites.
• IRB Approval Date: Enter the IRB approval dates for the proposal. • IRB #: Enter the IRB approval numbers.
• Pending: Check the appropriate boxes for pending IRB approval.
• Animal Subjects Approval: If the proposal uses animals, check “Yes.” If the study does not require the use of animals, check “No.”
• If yes: If you checked “Yes” for Animal Subjects Approval, check the correct approval sites.
• IACUC Approval Date: Enter the IACUC approval date for the proposal. • IACUC #: Enter the IACUC approval number.
• Pending: Check the appropriate boxes for pending IACUC approval.
• Biosafety Approval: If the proposal requires biosafety approval, check “Yes.” If the study does not require biosafety approval, check “No.”
• If yes: If you checked “Yes” for Biosafety Approval, check if the approval is “Exempt” or “Pending.”
• IBC Approval Date: Enter the IBC approval date for the proposal. • IBC #: Enter the IBC approval number.
Other Considerations
Principal Investigator Certification
• After reading the certification statements and proofing the completed routing form, the principal investigator must sign to document approval.
Click the “NEXT” button to go to the next page.
Start Budget Page
• Signatures Required for Approval: Enter the names of the signing officials, including appropriate deans and chairs. If the form does not allow enough space, include a separate page for signatures.
• Budget Prepared By: Enter your name.
• # of Periods in Project: Enter the number of budget periods for this project. Typically budget periods are 12 months. Entering data into this field creates blank personnel pages and composite budgets for each budget period of the proposal.
• Project Period: Enter beginning and end dates for the entire proposal. Use the format MM/DD/YY. For example a five-year project might be entered as 07/01/03 – 06/30/08.
• Subcontracts: Enter the number of different organizations receiving
subcontracts from this project. Click the “More information on subcontracts” button for specific information on NIH and FDP subcontracts.
• Inflation Factors: Enter inflation factors for personnel, subcontracts, and other costs. Currently, NIH will fund a 3% increase for all direct costs.
• Will this project have personnel costs? Click “Yes” or “No.”
• Is this an NIH grant?: If this proposal is NIH or NIH pass-through, check “Yes.” If not, check “No.” Data entry in this field activates NIH salary cap calculations within the spreadsheet.
• Enter the current NIH salary cap: This cell defaults to the current NIH cap. The user can override this amount should the cap change.
Funding Information Page
• Source of Funding: Check the circle that represents the original source of funds. Federal pass-through projects should be marked as federal dollars. Federal policies and procedures (e.g., cost accounting standards) apply to pass through funds.
• F&A Costs are based on: Check the appropriate circle.
o Modified Total Direct Costs (MTDC) apply to all federal dollars including federal pass-through dollars. The spreadsheet will automatically subtract from Total Direct Costs, those costs exempt from F&A. Exempt costs include equipment, tuition and fees, patient care costs, as well as, dollars over the first $25,000 of subcontracts.
o Total Direct Costs (TDC) apply to industry, foundation, and state awards. No costs are subtracted from the budget to determine the base.
• If Federal, does this qualify as a major project? Check “Yes” or “No.” Click the “More information on major projects” for the federal description and
examples of major projects.
• Enter the F&A rate: Enter the appropriate F&A rate for this proposal. • If requesting an F&A waiver: Complete the linked form and submit to SPA
prior to submitting the proposal.
• Will this project generate program income? Check “Yes” or “No.” If yes, a new data entry box will appear. Enter the amount of expected program income for each year of the project.
• Cost Sharing: If this budget contains cost sharing, select the appropriate type from the drop down list. If this proposal does not contain cost sharing, select “None.” UNMC discourages voluntary cost sharing.
Click the “NEXT” button to go to the next page.
Subcontracts Page
• The Subcontract Summary Budget (highlighted in yellow) will be automatically calculated as the budgets are completed for each individual subcontract.
• To allow you to clear information within an individual subcontract, the form offers a separate “Reset” button for each subcontract budget.
• Subcontract 1
o Name: Enter the legal name of the organization receiving this subcontract. o Principal Investigator: Enter the name of the PI at the subcontract
organization.
o Prior Year(s): If this budget is being calculated in the middle of a multi-year project, enter the total dollars awarded through subcontracts in previous years. This information is necessary for the F&A base of the total project to be properly calculated.
o Year 1 Budget: Enter the dollar amount for each budget category in Year one. Typically, these amounts are provided by the subcontract
organization.
Based on the inflation factor entered on the Funding Information
Page, future years’ budgets will automatically calculate.
Benefit rates may vary among organizations. Request documentation of this rate from the subcontract organization.
o Year 1 Cost Sharing: If cost sharing occurs with this organization, enter those costs in the appropriate categories.
o F&A Basis: Enter the dollar amount on which the subcontractee’s F&A rate will apply. Subcontract organizations may calculate F&A based on MTDC, TDC or salaries, wages, and fringe benefits. Organizations should document their calculation method.
o F&A Rate: Enter the F&A rate for the subcontractee. Each organization has different F&A rates. Request documentation of this rate from the subcontract organization (e.g. federally negotiated rate agreement). o F&A Costs: If an “F&A Basis” number and an “F&A Rate” number
were entered on the form, the spreadsheet will automatically calculate the “F&A costs.”
o Grand Total: This field is automatically calculated.
o Comments: Use the comments section to clarify the method you used to calculate subcontract base (e.g. MTDC, TDC and Salaries, Wages, and Fringe Benefits). Sponsored Programs Accounting uses the explanation for their records.
• Subcontract 2-15: If the project has more than one subcontract organization, repeat the previous steps for each subcontract organization on the project.
Personnel Instructions Page
• Study the dialog boxes on the Personnel Instructions Page for a quick overview on how to use the Personnel Page or follow the step by step guidelines outlined below.
Click the “NEXT” button to go to the next page.
Personnel Page
• Fringe Benefits: Determine whether you will be using the same benefit rate for all personnel on the budget. University policy requires that benefits be calculated at 28%. If the “Different rates” button is selected, the rates must be entered separately for each individual. For example, budget only FICA expenses (7.7%) for graduate students.
• Enter Rate: If you select the “Same rate for each” button, enter 28%. If you select “Different rates,” enter the appropriate rate for each person in the column labeled “% Benefits.”
• Name: Enter the full name of each UNMC project participant.
• Role: Select or enter the appropriate role (e.g., Co-Investigator). The spreadsheet has a drop-down menu or the user can enter a role directly into the cell.
• DEPT. #: Enter the appropriate four-digit departmental number for individual personnel. Departmental numbers help SPA in determining whether appropriate signatures have been obtained and in assigning grant numbers.
• UNMC Salary: Enter the current salary for the personnel listed.
• % Benefits: The percentage of benefits is automatically calculated from
previously entered data. If you have selected “Different rates,” enter manually in this cell.
• % Effort: Enter the percent effort for the personnel being charged to the grant. • # Months: In the next three columns, enter the number of months that will fall
into each fiscal year during the budget period.
• Annualized Salary, Salary Requested, Benefits and Total: No data entry is required for these fields. These fields are automatically calculated based on the UNMC salary, percent effort columns and inflation factor.
• Cost Sharing % Effort: To determine whether cost sharing is required, consult sponsor guidelines and check the “% Required” column on this spreadsheet. Example of required cost share: Salary over the NIH salary cap.
• Enter Cost Share Benefit Rate: If you are cost sharing salary, enter the appropriate fringe benefit rate for cost sharing on this proposal. Fringe benefit rates are typically 28%. Any portion of a person’s salary that is cost shared must also cost share the proportional benefits.
• Source of Cost Sharing: When cost sharing funds are used, a company center source must be identified. External dollars are rarely used for cost share. Before an external sponsor’s funds can be used as cost share, approval must be obtained in writing from the sponsor. Federal regulations restrict cost share from other sponsored projects. A WBS number beginning with 34, 35 or 36 is an
inappropriate source of cost sharing. Personnel Year 2 - Year 5
• Fringe Benefits: The spreadsheet is programmed to automatically calculate salary and benefits for Year 2 and beyond by applying the previously entered inflation factor. Users can override existing data and insert new information. • Reset Button: Unlike the previous reset buttons (which erase data), the reset
buttons in this section replace modified formulas with the default formulas. Click the “NEXT” button to go to the next page.
UNMC Other Costs Page
• Other costs are defined as Operating, Consultants, Supplies, Travel, Equipment and Government Aid.
o Government Aid includes patient care costs, tuition and fees
• Based on the inflation factor selected on the Funding Information Page, the spreadsheet will automatically calculate future year totals. Calculations can be manually overridden.
• Determine whether cost share will be a component of Other Costs. Enter in appropriate line item(s) of the budget.
• F&A Basis: Based on your selection of MTDC or TDC, the spreadsheet will automatically subtract appropriate line items to establish a base for calculating F&A.
• F&A Rate: The current rates for UNMC are as follows: Federal
o Federal rate = 47%
o Other Sponsored Projects = 26% o Off-campus rate = 26% o Training Programs = 8% Non-Federal o Industry rate = 26% o State – Varies o Foundation – Varies Consult guidelines
• F&A Costs: This field is calculated automatically.
o Clarifications: UNMC’s federally negotiated rate F&A rate is based on a Modified Total Direct Costs (MTDC) base. Costs exempt from F&A charges are:
Equipment
Patient Care Costs Tuition and Fees
Subcontracts over the first $25,000
• Grand total: The form automatically calculates the total costs for each year of the project.
• If the budget requires adjustments to the grand total, click the
appropriate “Adjust Year 1(-5)” buttons. If you select the “Adjust Year” button, enter an explanation in the comments section. Comments will appear on a summary page at the end of the spreadsheet.
Click the “NEXT” button to go to the next page.
SPAdministration Budget Summary Form
• The SPAdministration Budget Summary is comprised of a summary budget, yearly budgets and comments summary page.
• The spreadsheet automatically summarizes the previously entered information on these pages.
• Yearly budgets should be printed and routed for signature along with application materials. To print the routing form, summary budget, yearly budgets and comments summary pages, click the “Print Forms” button.
