CP Excel Spreadsheet - Funding Need

The BMAR Need, FACIMP Need and Combined Need worksheets are where all of the "what ifs" are done. The following information must be filled out for these worksheets to calculate properly. BMAR indicates Deferred Maintenance, and IFCS is the same as Capital Renewal.

• WebTMA Capital Planning input from BMAR and FACIMP (Facility Improvements). Entered by WebTMA in cells I3 to L7 of each of these worksheets, the BMAR and FACIMP totals provide a summary of the Capital Planning data. Here you find the Work Priority totals (Urgent, Needed and Can Wait) for each Work Reason (Health & Safety, Prevent Damage, Scheduled Maintenance, and Repair).
• These values can be manually found through the graph accessed from the Work Element Graph link on the CP Browse Action Menu.

• CRV Construction Costs. This is entered by WebTMA Capital Planning in cell I9 and is the Construction Cost of the Current Replacement Value (CRV) for all of the facilities, utilities and grounds at your institution. The Construction Cost calculation is found in WebTMA at Organization > Building / Capital Planning Tab-Work Element Subtab. The Construction Cost is calculated by taking the original bid amount and adding inflation, based on the number of months between the estimate date and the scheduled date. The Project Cost adds the Work Markup to the Construction Cost for all other project "soft costs". This field is needed on the spreadsheet to plot the Facilities Condition Index (FCI), which is the sum of BMAR Constructions Costs / CRV Construction Costs.
• BMAR Construction Costs. This is entered by WebTMA Capital Planning in cell I10 and is the sum of all Construction Costs of all BMAR items in WebTMA Capital Planning. As described above, this is used to help calculate the Facilities Condition Index (FCI).
• Starting Cyclic Renewal Budget Need. This is entered by WebTMA Capital Planning in cell D6 and is twice the Annual Goal (example: if you're doing biennial budgeting) amount found in the Cyclic Renewal Leveled Inflated Graph form calculated by WebTMA Capital Planning during the Cyclic Renewal Leveling process. This is provided as a reference for the Starting Cyclic Renewal Budget Need in B6 which is entered by the user and explained in greater detail later.

Cell E6 is the percentage applied to Cyclic Renewal vs. the Cyclic Renewal Need (B6/D6). F6 is the average cyclic multiplier of the frequency in years in the Cyclic Renewal Items table that is required to make the user inputted Budget Need in B6 possible. The Cyclic Multiplier field quickly tells you if the funding plan you are working on is reasonable in terms of Cyclic Renewal cycles.

• Mid-Point Current of Budget. This is entered by WebTMA Capital Planning based on the budget date set in WebTMA Capital Planning > CP Setup.
• Need to Graph. Entered by the user in cell B5, this is the portion of BMAR and/or Facility Improvements you wish to eliminate.
• Cyclic Renewal (Cy-Ren) Budget Need. You can enter the Cyclic Renewal amount that WebTMA Capital Planning calculated and entered in cell D6 (see Starting Cyclic Renewal Budget Need above) or an adjusted Cyclic Renewal amount in cell B6.

Note that the Cyclic Percentage (E6) and Cyclic Multiplier (F6) columns change as you change the Cyclic Renewal amount entered in cell B6. If the amount you determined you will have to spend on Cyclic Renewal items (B6) is different from the amount calculated for Cyclic Renewal (D6), the Cyclic Multiplier (F6) calculates the factor you will need to multiply your Cyclic Renewal cycles by to achieve the predicted funding level.

• Projected Budget Funding. Entered by the user in cells B7 and D7 as follows:

B7: This can either be the funding you think you can get or what you need. It is recommended that you start with what you need as this is an obvious driver to quickly learn that the funding need must exceed the Cyclic Renewal need to allow the backlog to be reduced or eliminated.

D7: This is the funding that you should reach. This can be inputted from the Strategic Plan graph on this spreadsheet. Usually the most cost-effective funding goal is one that exceeds the Cyclic Renewal need and eliminates the BMAR in approximately 30 years (see the Section on the Strategic Plan Graph below).

• Inflation & Funding Inflation. You can enter separate inflation factors for Cyclic Renewal & BMAR items as well as for funding.

Inflation (B8): This should be the amount used for your Cyclic Renewal and BMAR items.

Funding Inflation (B9): This allows you to inflate the funding need separate from the work to be done.

• BMAR Constant Level. Entered by the user in cell B10, this is for use by those who think a constant Backlog Reduction (BMAR) or Facilities Improvement (FACIMP) balance should always be kept. The amount entered here (in thousands) should reflect the inflated value desired at the mid-point of the current budget. With inflation at typical rates, you will find that a constant backlog balance does not make sense since the amount of backlog left forever could usually be eliminated if funding continues for a few more budget cycles. As you vary the BMAR Constant Level, notice the impact this amount has on how the BMAR and/Facility Improvement (FACIMP) level (red) line intersects with the Cyclic Renewal (green) and funding (blue) lines.
• Constant Budget Funding. Entered by the user in cell B11, this allows you to experiment with constantly increasing funding. This amount will also be inflated based on the value entered in cell B9 (Funding Inflation) described above.

• The Facilities Condition Index (FCI) and Year. Entered by the user in cells D10 and D11, these are used to provide data for the "Need" Macro that calculates the desired condition by the desired year, and the "Plan" macros (FCI only), which provide data for the planning charts. Enter the desired condition in cell D10 and the year you would like to achieve that condition in cell D11 (as a 4 character year).

The minimum value you can enter in cell D10 depends on how large your Cyclic Renewal amount is for each budget period. Because it is always there, the Condition Index will always reflect this amount in the calculations. If you enter an amount that is too low, the program will start running in an endless loop because the entered condition index can never be met. Keep an eye on the numbers as the program runs. If the Projected Budget Funding (Cell B7) gets too high and fails to stop, press the Escape {ESC} key to stop the macro, check the conditions being met, and enter a slightly higher number in the cell, then run the Needs macro again.

BMAR (Backlog of Maintenance and Repairs) is Deferred Maintenance. IFCS is the same as Capital Renewal.