ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Read Me FIRST - Excel 90-4 Adapted Workbook
These instructions are from the National Sea Grant Office;
Instructions in blue font below are additional guidance
from Texas Sea Grant for
Full Proposal Budget Development
2
CLICK THE SHOW FUNCTIONS BUTTON FIRST
3
**see text below for details**
4
MAC USERS—Download / Open the Excel document. Click "Enable Macros" button when queried.
5
non-MAC USERS—Download / Open the Excel document. Save to desired drive / directory and Close the document. Open the document and macros will be functional. IF at any point you receive a message box / screen asking to Trust / Enable macros, reply YES — macros should then be functional.
6
Unlock Modifed Direct & H. Indirect Costs to set initial indirect cost rates for your budget; Lock when done so any change is purposeful.
7
Read Me for Excel 90-4 Adapted Workbook
8
This Excel 90-4 worksheet is adapted to better comply with budget categories found in the SF424RR budget form. While many categories are similar to the previously used 90-4 budget, there are new budget categories and differences in several data aggregations. It is not the intent of this Excel workbook to conduct all calculations needed to develop budgets for proposals. This workbook instead is meant to be a companion to other platforms, such as eSeaGrant that conduct the tedious calculations. The 90-4 Adapted budget is revised to more closely align to categories used in the SF424RR budget now required by use of eRA. The 90-4 Adapted displays specific budget data, not adequately displayed in the SF424RR form, needed by NSGO to effectively and efficiently manage project funding at the national level.
9
GENERAL INFORMATION
10
Macros—This Excel workbook contains macros. All macros function to expose or hide portions of the 90-4 budget form, lock or unlock specific cells, or reinstall formulas in cells that allow overwriting. No data are manipulated via a macro and none connect to any data outside of that contained in the Excel 90-4 budget document. If a calculation is found to be in error, the problem is in an embedded formula, not in the VBA macro code. In all instances of button use the cursor is returned the “Select Budget Type” dropdown (budget selection is not affected).
11
Protected Worksheet—The 90-4 Excel worksheet is protected, but without a password. All cells with yellow background color are available for data entry; white cells are locked (those outside of the budget form area however, are not). You can “unprotect” the worksheet but do so with caution. The worksheet has embedded formulas and macros that call on specific rows, columns, and cells. Adding or removing cells, rows, or columns, may cause macros to malfunction and/or formulas to provide incorrect calculations. It is recommended that you leave the worksheet in a “protected” state; work from a copy if you decide to alter the worksheet.
12
Dropdown Menus—are contained on a “hidden” Excel workbook tab. If for some reason you need to amend a dropdown list, you can “unhide” the tab. The only dropdown lists are for Budget Type and Sea Grant Program and should not need editing.
13
Fringe Table—mimics the "B. Fringe Benefits" section of the original 90-4 form where fringe rate detail can be added for institutional use. This tab pulls Key Person information from the budget form to the table, which can then be populated with fringe rate data for Key Persons as well as any notes of explanation. Data in this table are not used in any budget calculations.
14
NON-DESTRUCTIVE FUNCTION BUTTONS (BLUE and GREY)
15
SHOW / HIDE FUNCTIONS—This pair of buttons toggles between showing or hiding the series of command buttons as described below. It is recommended that you Click on HIDE FUNCTIONS prior to creating the PDF budget file that will be submitted to the funder. No data are affected by use of these buttons.
16
Select Budget Type: Lead Institution should click on SELECT BUDGET TYPE to activate dropdown menu to select "Primary Budget";
if have a sub-recipient, that institution should select from dropdown menu, "Subaward Budget".
17
Show Xtra Subs / Hide Xtra Subs—This pair of buttons toggles between showing or hiding 3 extra subaward lines in “F. Other Direct Costs, 5) Subawards.” Data are not affected by show/hide action.
18
Open / Close Key Persons—This pair of buttons opens Senior/Key Person rows for input of individual PI, co-PI, and other key persons on the project. Input values are summed in Row 11. CLOSE KEY PERSONS should be initiated prior to creating the PDF budget file that will be submitted to the funder. Currently set as Open, in order for you to add Co-PI staff. Note, if click on Close per guideline above, then only the Lead PI is displayed; we want to see all of your Co-PIs on the Full Proposal, therefore, PLEASE KEEP OPEN WHEN CREATING PDF. Have displayed rows 12-18 (lists Senior/Key Persons #1-7) and have hidden rows 19-23. Your proposal should typically not have more than 5 Lead PI and Co-PIs/Key Persons; if you do, unhide rows that are hidden. Sub-recipient Co-PIs should be listed on their own institution's Subaward Budget template.
19
Lock / Unlock Modified Direct & H. Indirect Costs—This pair of buttons toggles between locking (cannot edit) and unlocking (can edit) workbook Row 72 “Modified Direct Costs” and Rows 74-76 “H. Indirect Costs,” including indirect cost rates (SG% and Inst%). Cell color is yellow when unlocked, white when locked. Data already input are not affected by lock/unlock action. Unlock to set indirect cost rates and/or when Modified Direct or Indirect Cost totals need to be overwritten (see RESET YrX ModDir & IDC). Lock when done.
20
Freeze / UnFreeze Headers—This pair of buttons locks, or unlocks, the top 10 rows and leftmost 2 columns to keep row and column headers visible. Data are not affected by freeze/unfreeze action. Currently set as frozen, in order to display rows when scrolling down.
21
Open / Close Yr—These buttons open or close individual budget years. Data are not affected by open/close action. Currently set with 2 years displaying for budgeting the Full Proposal 2-year projects.
22
DESTRUCTIVE FUNCTION BUTTONS (RED)
23
RESET ENTIRE SHEET—Use of this button clears ALL data contained in the entire Excel worksheet for all budget years. All formulas are restored to their original state, and the worksheet is restored to its original condition (i.e., as if it were the first time opened for use). It is suggested that you save a copy before using this function. If you make a copy of this workbook for use with other proposals it is recommended that you "Reset Entire Sheet" before saving for reuse.
24
RESET Yr”X” ModDir & IDC—Use of this button replaces input data with the original formulas in those cells that can be unlocked when using the “Unlock Modified Direct & H. Indirect Costs” button. This function can be useful when there is the need to replace the original formulas an individual budget year. For instance, the amount of indirect cost is replaced with a specific dollar value in Yr2 to get the budget total to a desired value. Further budget work occurs, and it may be useful to replace the formula—click the “RESET Yr2 ModDir & IDC” button. Use of this button ONLY affects cells where formulas have been replaced by specific values (e.g., replacing the original formula in a cell containing the original formula has no affect). SG% and Inst% are not affected as they do not contain formulas.
25
NOTES (red triangles)
26
Other than Project Number and Project Period, all descriptors apply to each budget year in the project. Totals across budget years are automatically calculated and displayed in the TOTAL section (light blue cells).
27
PROJECT NUMBER—A unique project identification number given by the Sea Grant program and used for tracking in PIER and other database systems. LEAVE PROJECT NUMBER BLANK FOR FULL PROPOSALS.
28
PROJECT PERIOD FROM and TO—Start and end dates of the project length; includes all budget years.
29
BUDGET YEAR START and END—Start and end date of each individual budget year in the project; each budget year requests input of a start and end date.
30
NO. OF PERSONNEL—Total number of persons in A. Senior/Key and B. Other Personnel budget line categories. A. Senior/Key Person number auto-calculates as effort is entered.
31
MONTHS OF EFFORT—Total months of effort for all persons in the A. Senior/Key (auto-calculating) and B. Other Personnel categories.
32
REQUESTED SALARY—Total salaries—federal + non-federal.
33
FRINGE BENEFITS—Total fringe benefits—federal + non-federal.
34
SEA GRANT / FEDERAL FUNDS— Total salaries + fringe—federal funds only.
35
MATCH / NON-FEDERAL FUNDS— Total salaries + fringe—non-federal funds only.
36
C. PERMANENT EQUIPMENT—see Uniform Guidance for Federal Awards for details. This category is NOT included in Modified Direct Costs (e.g., it is not subject to indirect costs).
37
E. PARTICIPANT / TRAINEE SUPPORT COSTS—see Uniform Guidance for Federal Awards for details. This category is NOT included in Modified Direct Costs (e.g., it is not subject to indirect costs).
38
F. 5) SUBAWARDS: The default is 3 subaward options; 3 additional subawards can be accessed by clicking SHOW FUNCTIONS and Show Xtra Subs buttons.
39
FED SUB­—Total amount of federal funds in the subaward.
40
IDC APPLY—Amount of federal funds subject to indirect cost rate set in H. Indirect Costs budget section.
41
NON-FED SUB—Total amount of non-federal funds (match) in the subaward.
42
IDC APPLY—Amount of non-federal funds subject to indirect cost rate set in H. Indirect Costs budget section.
43
F. 8) TUITION NOT SUBJECT TO INDIRECT­—Total of tuition for supported students. This category is NOT included in Modified Direct Costs (e.g., it is not subject to indirect costs).
44
F. 9) OTHER NOT SUBJECT TO INDIRECT—A budget line that can include other items that are not subject to indirect costs. This category is NOT included in Modified Direct Costs (e.g., it is not subject to indirect costs).
45
MODIFIED DIRECT COSTS—The total of all budget costs that are subject to indirect costs. C. Permanent Equipment, E. Participant Support, F. 5) Subaward costs not subject to indirect, F. 8) Tuition, and F. 9) Other Not Subject to Indirect, are not included in this total.
46
H. INDIRECT COSTS—Indirect cost applied to the Modified Direct Cost total using the Indirect Cost Rates provided in H. 1), 2), and 3).
47
SG%—The indirect cost rate used by the Sea Grant program.
48
Inst%—The indirect cost rate used by the host institution for non-Sea Grant projects.
49
WAIVED $—The indirect costs not charged by the host institution for Sea Grant projects (e.g., Inst% - SG% = waived) can be claimed as match. If SG% = Inst% then there is no waived indirect costs to claim as match.
50
The above items are locked by default and can be accessed by clicking SHOW FUNCTIONS and Unlock Modified Direct & H. Indirect Costs buttons.
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100