Instructions for NIH Excel Budget Worksheet for Modular Grants
Before you use this spreadsheet for the first time, please make sure you take the following 3 steps.
1. Open Excel.
Click on the New Sheet Icon on the Tool bar. (The icon that looks like a blank piece of paper)
2. Make sure that “Transition Navigation Keys” is off.
Click “Tools”, “Options”, then click on the “Transition” tab.
There should not be a check mark in the “Transition Navigation Keys” box. If there is, click on the check mark to remove it.
3. Make sure that “Analysis ToolPak” is installed.
Click “Tools”, “Add-Ins”, “Analysis ToolPak”.
Open the template file named “nihmod.xls”. Click on the button that says “Enable Macros”
Save with a new name.
Click “File”, “Save As” and save the spreadsheet with a new name.
Don’t make any changes to “nihmod.xls” or you will lose the template file.
Make sure that the Protection is on.
Click “Tools”, “Protection”, “Protect sheet”(If you see “Unprotect sheet” here, it means that the worksheet is protected. Click outside of the menu box to get out of it with out unprotecting the sheet.) ”. In the “Protect worksheet for” box all three boxes should have a check mark. Then click “OK”.
When Protection is on, the tab key will take you only to the unprotected cells, i.e. the cells in which you will enter data.
Subcontractors and Collaborators
The first worksheet in the workbook is called “Subcontracts”. If you have subcontractors or collaborators in your project, enter the direct and indirect costs for each subcontractor on this sheet. This information will automatically be carried over to the yearly worksheets. If you do not have any subcontractors or collaborators, ignore this sheet and click on the tab “Year 1”.
Worksheet “Year 1”
Click on cell A1, then press the Tab key. This will take you to the first available cell.
Remember: When Protection is on, use the Tab key to move between the cells you can type in
If the Tab Key passes over a cell you should not type in it.
Cell D1 - enter the Indirect Cost Rate (Enter 54% as 54)
Cell L8 - enter the Principal Investigator’s name (Last Name, First Name, Middle Name)
Cell N13 - enter the start date of the project preceded by an apostrophe (example ‘07/01/99)
Cell P13 - enter the end date of the first budget period preceded by an apostrophe (example ‘06/30/00)
Personnel: Rows 19 though 47 (ENTER DATA IN ODD NUMBERED ROWS ONLY.)
Column B Fringe Benefit Rate: 29.5% is entered as 29.5
Column C Name
Column F Role on Project
Column H Appointment Type in Months (9 for Academic Year, 12 for Calendar Year)
Column J Percent effort of the person on the project.
(Enter the full effort of the person regardless of whether salary is requested or not.
Column L Base Salary (Do not include Practice Plan salary.)
If you need to manually alter a salary request in Column N Protection off, make the change and turn the Protection back on. You will need to manually alter the salary request if the salary requested does not equal the percent effort on the grant.
To Remove the Protection: Click “Tools”, “Protection”, “Unprotect Sheet”.
To Protect the worksheet: Click “Tools”, Protection”, “Protect sheet”. All three boxes in the “Protect worksheet for” box should be checked.
Consultants
Cell C52 - enter Consultant’s name
Cell D52 - enter amount
For additional Consultants use
Cell H52 - enter Consultant’s name
Cell L52 - enter amount
Use Row 53 for a third Consultant.
Equipment (items with a unit price of $5,000 or more and a useful life of 2 years or more.)
Cell C55 - enter Equipment name
Cell D55 - enter amount
Cell H55 - enter Equipment name
Cell L55 - enter amount
Enter additional pieces of Equipment in Rows 56 and 57.
Supplies
Cell C59 - enter Supply description
Cell D59 - enter amount
Cell H59 - enter Supply description
Cell L59 - enter amount
Use rows 60, 61, 62, 63 and 64 for additional supplies.
Travel
Cell C66 - enter Meeting Name or Destination
Cell D66 - enter amount
Patient Care Costs
Inpatient
Cell H67 - enter description
Cell R67 - enter amount
Outpatient
Cell H68 - enter description
Cell R68 - enter amount
Alterations and Renovations
Cell C70 - enter description
Cell D70 - enter amount
Other Expenses
Cell C72 - enter description
Cell D72 - enter amount
Cell H72 - enter description
Cell L72 - enter amount
Use rows 73, 74, 75, 76 and 77 for additional Other Expenses.
Cell F78 - enter the dollar amount of Participant Costs that were included in “Other Expenses”. This will allow the indirect costs to be calculated correctly.
Worksheets “Year 2”, “Year 3”, “Year 4”, and “Year 5”
For the Year 2 detailed budget, click on the worksheet “Year 2”
Cell D1 – Enter the Indirect Cost Rate for Year 2.
Cell D2 – Enter the percentage amount to increase salaries. (Enter 4.5% as 4.5)
Cell D3 - Enter “y” if you want a budget for Year 2.
All salary, fringe benefits, consultant, supplies, travel, patient care, alterations, other expenses will carry forward from the previous year.
Information on equipment does not carry forward. Also, the amount entered in Cell F78, the dollar amount of Participant Costs that were included in “Other Expenses” does not carry forward from year to year. This information must be entered in each year.
If you would like for all costs in a particular category (i.e. Travel or Supplies) to increase by the same amount over the previous year, indicate this percentage in column A next to that category.
To remove the contents of a cell (example: a nonrecurring cost), click on Edit, Clear, Contents. Use only the options of Edit, Clear, and Contents. If you use other options you will lose Formats, Notes and Formulas which are essential for the spreadsheet to work.
Other Sheets in the Workbook:
Total Page – This worksheet is the summary budget for all years of the project. It is automatically calculated, so no data entry is required, just print it.
EXTRA – The 398 yearly budget page fits only 8 entries for personnel. This page prints out up to seven more people. This is done automatically. Enter up to 15 people on the yearly budget, and the last seven will print out on this sheet.
IDC - This sheet automatically calculates the Indirect Costs for each year. To ensure that this page works correctly, please make sure that you enter Cells F78 and F81 (Participant Costs and Subcontracts) accurately. This page is not required by NIH, but may be attached to the Checklist page.
Modules – This sheet is a tool for the PI to go from a detailed 398 budget to a modular request. This form automatically calculates the options for the number of modules in each year, showing options for both rounding up and rounding down. It also allows the PI to see how his request will look if he or she adds one or more (well-justified) modules to any year. Please enter the number of modules that you will request for each year in row 30. Entering the number of modules here will enable the “IDC” and “JustificationData” sheets to calculate correctly.
JustificationData – This sheet summaries the data that is needed for the Budget Justification Page of a Modular Grant Request. The template for the Budget Justification Page in Microsoft Word can be found on SPA’s web site, in the same location where you found the Budget Spreadsheet. This word document is in the format suggested by NIH.
To Print:
Click on the worksheet tab “Year 1”. There are five Print buttons at the top of the Year 1 worksheet. Click on the button for the worksheet pages you would like to print.