ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3
Instructions for the Budget Table
4
5
The Budget Table is an Excel Workbook composed of several worksheets. Each worksheet has a Tab at the bottom
6
of the page: Funding Sources Summary, Project Master Sheet and Activities 1 through 5. There are 2 extra Tabs created just
7
for clarification: this Instructions page and the Rates and Match Info. You may click on each tab to move around the
8
workbook or you may use the hyperlinks (more on this below). The workbook may seem complex at first, but once
9
you have a chance to familiarize yourself with it, it should become an easy tool to track costs and deliverables.
10
The intent is that it will serve as a project management tool to evaluate progress and deliverables, as a
11
tool to manage costs, for the generation of invoices, for preparation of progress reports and eventually, to help with the
12
final report. As always, NPS program staff are available to answer questions and to work with project sponsors at any time.
13
14
How do I make this big Excel table work?
15
Answer:
The brunt of the effort is accomplished in Activity Tabs 1 through 5. Here is where most of the data entering
16
occurs. Match amounts may be roughly estimated and will be refined during PIP finalization,
17
pending project approval. Milestones/timeline - shade the "Quarter" cells in the "Project Master Sheet" tab that correspond
18
to when each Activity will be accomplished. Activity descriptions and products summarize information
19
developed in Section 3.1 of the application.
20
21
Everything else calculates automatically. There is even a place to check if the minimum match requirement has
22
been met - in the "Funding Sources Summary" sheet.
23
24
The following provides detailed information on how to use the workbook, per tab:
25
26
The Funding Sources Summary tab summarizes the NPS funds, the match and Federal contributions (if applicable)
27
per year. You will notice that all data entry cells in this worksheet are shaded pink. This is an indication that these cells
28
contain equations, link all Activities Tab, will calculate everything automatically and you don’t have (and shouldn’t) use them.
29
Bottom line – don’t touch this worksheet!!
30
31
The next tab is the Project Master Sheet. This worksheet also contains several pink cells that provide automatic
32
calculations. Do not enter anything in pink cells. But there are several things that do need to be entered:
33
34
1) Columns with the purple heading – enter the Activity Description, the Products (or deliverables) and the Parties
35
Responsible for Completing Activity (this refers to the entities that will help you implement the project and/or generate
36
match). The text in these columns should come from Section 3.1 in the application narrative table (you can abbreviate or summarize
37
the text).
38
Note – the column on the left in yellow contains a hyperlink to each activity. If you click on each "Activity number", the
39
hyperlink will take you to that activity. Conversely, if you click on each hyperlink “Back” found on each Activity
40
worksheet (upper left), that hyperlink will take you back to the Project Master Sheet. The hyperlinks make it easier
41
to move around the workbook.
42
43
2) Columns with the yellow heading – these cells are all shaded pink. Do not enter anything in these cells.
44
45
3) Another important feature on this worksheet is the information in the blue, green, orange and gray column headings:
46
the definition of the starting point of each year. The NPS program made a determination to use State Fiscal Year
47
because the information gathered this way will help us with contracting later. The State fiscal year starts on July 1st and
48
ends on June 30th. So first quarter is July-September, 2nd quarter is October-December and so on. Also note that,
49
although there are 4 years worth of budgeting available, most projects take 2 to 3.5 years to be implemented.
50
Note: Due to the timing of when federal funds are awarded to the state, projects generally do not start before
51
spring of the following year, after the Funding Announcement Year.
52
53
4) Columns with the blue, green, orange and gray headings depict the milestones/timelines per Activity and the total costs per
54
year, per activity. You will need to shade the white cells beneath these headings to depict the timeframe that each activity will
55
take to be implemented, when the activity will happen and if they are reoccurring. The existing gray cells in the "Project Master Sheet"
56
tab are examples and need to be modified to fit in the project timeline. Grey-in the cells for which quarters of each year you plan
57
to do each activity (if no activity that year/quarter, then leave white in background).
58
Total costs are calculated automatically (note the pink cells again). The quarters follow the state fiscal year, which starts on July 1st
59
and ends on June 30th. So first quarter is July-September, 2nd quarter is October-December and so on.
60
61
5) The minimum match requirement for the project is also automatically calculated: if you scroll down the "Funding Sources Summary" sheet.
62
tab, you will find the automatic calculations for the minimum match requirement. Total project cost
63
and minimum match information should be consistent with the information in the Application Section 1.0 - "Project Proposal Summary";
64
also use this automatic calculation to check if you are meeting the minimum match required.
65
66
For convenience, 5 activity tabs are provided. Similar activities should be combined into a single activity with multiple products.
67
Doing so helps keep the activities to a manageable number.
68
Note: You can use the hyperlinks to move easily between the Project Master Sheet and the Activities.
69
70
Activities worksheets (all the subsequent worksheets): Almost all the data entry happens on the Activities worksheets.
71
For each activity, decisions need to be made about costs, products and who pays for what. The first column (the budget line
72
items) captures most of the usual expenditures associated with NPS projects. The list is very comprehensive and does
73
not imply that all line items need to be used. The intent of creating such a long list is to ensure that all possible project
74
expenditures are included in the budget. Please include unit costs as there may be federal funding limitations and
75
regulations that apply to what is fundable or to maximum reimbursable rates. You may edit the titles for certain line
76
items to address your project needs. For example, if your project does not include purchasing equipment, you may edit
77
that title to reflect some other expenditure your project might entail that is not captured anywhere in the first column.
78
79
All pink cells calculate the costs you add in the white cells for each year, in the corresponding type of expenditure.
80
For example, NPS funds for salaries are entered in each year and automatically totaled in the pink cell for NPS Funds Salary.
81
This is done so that costs can be consolidated per year overall for the project, to meet an EPA requirement
82
(in the Project Master Sheet and in the Funding Sources Summary tabs). The consolidation will be done
83
automatically, but you will need to break the costs per year.
84
85
NOTE: There is room for flexibility and adjustment later. The reason to use the spreadsheet for the proposal phase
86
is to have accurate estimates of the total budget. You will be required to add additional details regarding
87
match and federal contributions if the proposal is selected for funding. The sources of match will
88
need to be specified in the "Funding Sources Summary" tab. At the proposal stage, the budget workbook is
89
provided to help establish the scope of the proposal and to provide an accurate estimate of NPS funds.
90
91
92
93
94
95
96
97
98
99
100