BUDGET SUMMARY SHEET - INSTRUCTIONS
These instructions are to help in completing the Project Budget Summary Sheet for each project.
To begin working on the Budget Summary worksheet, input the project name and contract number in the first sheet of the workbook, “Contingency Data.” Once this information is entered it will populate all the other sheets. At the end of each reporting period, create a new “BSS per. Ending…” sheet by copying the sheet from the previous period ending for the current period.
*Each Project is required to meet with Project Controls in order to establish each Project’s planned contingency drawdown (project start/finish date and contingency amount will be needed).
1. Contingency Data Sheet
a) Project Name: Formal name of Project. (This field is populated once the information is entered on the first sheet, “Contingency Data.”)
b) Contract Number: Assigned 7-character alpha/numeric code for the project. For project in design, use the design contract number and for a project in construction, use the construction number.
c) Plan Contingency: This value will be set up by Project Controls. This value includes allocated contingency, unallocated contingency and uncommitted funds within the project. The Project Manager (PM) and/or Budget Analyst (BA) will provide this value to Project Controls and Project Controls will populate the spreadsheet with the value and set up the Contingency Drawdown tab.
d) Additional Funding: If additional funding is provided, this value should be entered in this row under the proper period.
2. Project Budget Detail Sheet
a) Period Ending: Last day of the month for the particular reporting period (ex. March reporting would be Period Ending 3/31/2013 and known as the March 2013 Report. This data must be entered in two locations. First, input data on the appropriate “BSS per. Ending” sheet and on the “PS Cost Changes” sheet).
b) Grant Value: For each grant to the project, enter the grant number and the associated work order(s) available for the project. This is the responsibility of Project Manager (PM) and/or Budget Analyst (BA). Input information in the table below the summary table. The total of the grant value should match the value in CMS.
c) Grant WO (Col A): In the first column of the Budget Summary Sheet, enter the grant work order numbers associated with the Responsible Area (PS, CN, etc).
d) Responsible Code (Col B): Column B provides the list of Responsible Areas as shown in CMS. This includes PS, PA, CN, etc.
e) Description (Col C): List of tasks to be included in project. Responsibility of PM and BA. Typical descriptions are Construction, Professional Services, Force Account, etc.
f) CMS / Funding (Col D): Input the value for the task as it is reported in the Distribution value in CMS. This should equal the funded amount for the task. The BA provides this information. This should match CMS as well as the total Grant amount. If there is a difference, the highlighted cell to the right of the Grant Total will show the variance.
g) Base Estimated Cost / Base Committed Value (Col E): The value entered here is the Engineer’s estimate for the task or the awarded amount. (In CMS this would be the Committed Value). The BA provides this information.
h) Contingency (Col F): This value is the contingency in the Engineer’s estimate or the value of the contingency associated with the awarded contract (Allowance). The Budget Analyst (BA) and Project Manager (PM) collaborate on this input. Any unallocated contingency should be reflected in this column.
i) Total Estimated Cost / Total Committed value (Col G): This represents the Total Estimated cost + the Allocated Base Contingency or the Total Committed value + the Allocated Base Contingency. This number may be less than or equal to the value in column D. This is a calculated field and it will be populated when columns E and F are populated.
j) Percentage EST/Contract vs. Funding (Col H): This is the percentage of Estimated Cost / Committed Cost and the funding.
k) Estimate at Completion (EAC) (Col I): This column represents projected final cost and includes any change orders, proposed changes or other costs that may be realized for the task. This column is managed by PM and it will be adjusted if it is anticipated that the forecast final cost of the project will change.
l) Percentage EAC vs. Funding (Col J): This is the percentage of EAC and Funding. This column is formulated.
m) Expenditures (AC) (Col K): Actual Cost to date, provided by BA.
n) Remaining Contract Value to Date (Col L): Total Estimated Cost / Total Committed Value less Expenditures. This column represents the Actual Remaining Budget as of the reporting period. This column is formulated.
o) Estimate to Complete (ETC) (Col M): Amount remaining to complete each task, EAC – AC. This column represents the projected cost required to complete task. This column is formulated.
p) Required Funding before Use of Uncommitted Funds (Col N): This formulated field provides the funding available to the task before utilizing all project contingency and any uncommitted funds. The project will have to reallocate funds if this amount exceeds the task’s funded value. The value in this column will be a negative or zero. The negative value indicates that a transfer/reallocation of funds is required.
q) Available Uncommitted Funds (Col O): This formulated column provides the uncommitted funds available for the task and the project. This amount will always be a positive number or zero.
r) Additional Funding Required (Col P): The data reflected in the column is an indication of the funding needed to cover the project cost. Additional funding is required if EAC is increased and/or cost from change orders are higher than anticipated in the initial EAC.
3. Period Cost Changes: BA/PM to use these sheets to document cost changes over time. There is a sheet for each category with the exception of the XG “Unallocated Contingency,” since any change to unallocated contingency will be reflected in the other categories. In general, any anticipated Potential Change Orders, Amendments, and executed change orders or any cost changes are to be tracked in these sheets. Once the Cost change is approved and executed, the number associated with the approved change should be added under the CO # column.
a) PS Cost Changes Sheet: Note: For each Period update, ensure that the Period Ending Data Date is inserted in the appropriate row and column in the “PS Cost Changes” sheet. This date will automatically populate the other Cost Changes sheets. Please note that there are 50 lines for change input in each of these sheets and the total for the columns (period) is at the bottom of the table or row 58. On the right of each sheet is a total column for each line where you can verify the total cost for each change.
b) CN Cost Changes Sheet: For each package, enter the change order or propose change orders for each period in the appropriate column. In column B, “Change Description,” enter the description of the change. In column C, “List of Proposal of Change Orders Anticipated,” enter an abbreviated description for the change and in the next column enter the number. The sheet has 200 lines for inputting changes. To see the total for each period, please scroll to the bottom of the sheet or to row 208. Scroll to the right to see each Change Order total.
c) Cost Changes sheets for Force Account (FA), Inspection (IN), Project Administration (PA), Material Procurement (MP), and Real Estate (RE) have also been provided.
3