萝莉社

Small Community Assistance Program Asset Management Tool: Guide for Managers

Purpose

This guide provides information to Small Community Assistance Program (SCAP) Asset Management Tool managers, including directions for modifying, updating, and maintaining the tool as needed.

The guide is applicable to both the Drinking Water and Wastewater tools, although screenshots reflect only the Drinking Water tool.
The guide is organized as follows:

• Accessing Hidden Sheets
• Modifying Asset Categories and Types
• Modifying Estimated Useful Life
• Adjusting Question Weights

Note: These instructions for the tool were created using Microsoft Excel/Microsoft Office 16. The same or comparable functions are available in other versions of Excel.

Accessing Hidden Sheets

The tool contains pre-populated data in the dropdown menus and formulas used to generate asset condition, estimate remaining useful life, etc. To modify the pre-populated data and formulas, tool managers will need to access worksheets hidden within the Excel file.

Instructions

1. To access the back-end sheets that control the dropdown menus, asset lists, and other information in the non-macro enabled versions of the tools, click the 鈥淩eview鈥� tab on the top of the page and unselect 鈥淧rotect Workbook.鈥�
2. At the bottom of the Excel workbook, right click on any sheet and select 鈥淯nhide.鈥� This is also the first step to access the hidden sheets in the macro-enabled versions of the tools.
3. The list of hidden sheets will appear. Note that you must unhide each sheet individually (i.e., you cannot select/unhide multiple hidden sheets at a time).
4. For additional security and control over edits to the master versions of the tools, consider adding password protection functionality. In the non-macro enabled version of the tools, you may set a password by re-selecting 鈥淧rotect Workbook鈥� once you are finished modifying a hidden worksheet.
5. Enter a password in the 鈥淧assword to unprotect sheet鈥� field and reenter the password in the 鈥淩eenter password to proceed鈥� field when prompted. You must make note of the chosen password, as it cannot be recovered once entered. If you do not wish to password protect a hidden worksheet, leave the 鈥淧assword to unprotect sheet鈥� field blank in the 鈥淧rotect Sheet鈥� box and click 鈥淥K.鈥�
6. The macro-enabled versions of the tool contains an optional macro titled ProtectDataSheets. A password can be incorporated into this macro by modifying the VBA code. To make changes to the code, open the Developer bar in the top ribbon. Click 鈥淢acros鈥� to open the list of available macros. (Note: If you do not see the Developer tab in the top ribbon, you may have to add the bar by going to File > Options > Customize Ribbon. Then make sure Developer is checked, and press OK.)
7. Click 鈥淧rotectDataSheets鈥� and 鈥淓dit鈥� to open the VBA developer window.
8. The macro code is show in the pop-up window. To add a password, add the following parameter to the .Protect code for each sheet: Password:=鈥滿yPassword鈥� Include the desired password in quotations and a comma to separate the new code from the existing parameters.
   For example, the second line of the macro would read: shtConditionAssmt.Protect UserInterfaceOnly:=True, Password:=鈥滿yPassword鈥�.
   
   
9. Protect all sheets by clicking 鈥淩un鈥� for the ProtectDataSheets macro in the Macro window as shown above. Note that if password protection is enabled, instructions in this Guide for Tool Managers should be updated accordingly.

Modifying Asset Categories and Types

Overview

You can edit either asset categories or asset types:

• Edit asset categories in the 鈥淒ropdown Menus 鈥� HIDE SHEET鈥� worksheet.
• Edit asset types in the 鈥淎sset Tables 鈥� HIDE SHEET鈥� worksheet.

DO NOT edit the asset categories without editing the corresponding asset tables:

• Asset tables depend on the names in the asset categories list; changes to the text in the asset categories table will invalidate the dependent dropdown menus in the asset inventory worksheet.

Instructions

Adding or Editing Asset Types

1. Adding new or editing existing asset types does not require any changes to other table names or lists.
2.  Make changes directly to the tables in the 鈥淎sset Tables 鈥� HIDE SHEET鈥� worksheet by editing existing asset types or typing new assets at the bottom of each list. To add the new asset to the table, create an additional row by grabbing and dragging the blue mark in the lower right corner of the asset column.
   
   

Editing Existing Asset Categories

1. Make edits directly to the 鈥淎sset Category鈥� table in the 鈥淒ropdown Menus 鈥� HIDE SHEET鈥� worksheet.
2. After editing an asset category, navigate to the 鈥淎sset Tables 鈥� HIDE SHEET鈥� worksheet and edit the corresponding table headers. Find the asset table with the same header as the asset category you changed and ensure the text matches exactly.
   
   
3. Edit the corresponding table names using the Excel 鈥淣ame Manager鈥� tool, which you can find in the 鈥淔ormulas鈥� tab, as shown above.
   
   1. Find the name in the 鈥淣ame Manager鈥� list that matches the edited asset category. Click 鈥淓dit,鈥� as indicated by the arrow in the screenshot above.
   2. In the 鈥淓dit Name鈥� window that appears (see below), edit the 鈥淣ame鈥� and 鈥淩efers To:鈥� boxes, as indicated by the arrows in the screenshot below. Be sure to make edits that correspond exactly to the new asset category name. (Note, no spaces are allowed in the asset Name field.)
   3. After you make changes in the 鈥淣ame Manager鈥� tool, the following items should all match exactly: asset category name (step 1), asset table header (step 2), asset table name (step 3), and the 鈥淣ame鈥� and 鈥淩efers To:鈥� formula (Step 3).

Adding New Asset Categories

1. To add new asset categories, you must create a new named table in the 鈥淎sset Tables 鈥� HIDE SHEET鈥� worksheet and populate it with asset types for that category.
2.  Add a new category to the asset category table in the 鈥淒ropdown Menus 鈥� HIDE SHEET鈥� worksheet, and make sure to include the new category in the table (similar to step 2 under 鈥淎dding or Editing Asset Types鈥� above).
   
3. Create a new table in the 鈥淎sset Tables 鈥� HIDE SHEET鈥� worksheet. Ensure that the header matches the new asset category exactly.
   1. Select the new asset table, click 鈥淔ormat as Table鈥� in the 鈥淗ome鈥� tab, select any table 鈥淪tyle鈥�, and select 鈥淢y table has headers,鈥� as shown below and click the 鈥淥K鈥� button.
4. Select all asset types within the new table (NOT including the table header) and name the table using the box to the left of the Excel formula bar (see screenshot below). The name cannot include spaces but must otherwise match the asset category exactly.
   
   
5. Verify that the table was named correctly in the 鈥淣ame Manager鈥� window (in the 鈥淔ormulas鈥� Tab). Note in the screenshot below that the table name has no spaces but the 鈥淩efers to:鈥� formula does include spaces. The text in the 鈥淩efers to:鈥� formula should match the table header exactly. You may also use 鈥淣ame Manager鈥� to directly create a new named table.
   
   
6. The new asset category and associated asset types should now appear in the dropdown menus in the 鈥淎sset Inventory鈥� worksheet, as shown below.
   
   
7. If you add a new asset category and asset types, you must also add the estimated useful life, as described in the next section.

Modifying Estimated Useful Life

Overview

• The tool currently includes useful life estimates that were derived from other vetted sources, including (primarily) the Southwest Environmental Finance Center Asset Inventory Database.
• Note that you may hard-enter custom values for estimated useful life in the 鈥淎sset Inventory鈥� worksheet. You should view the default estimates as a starting point from which you can provide more specificity for your assets, if necessary.
• The tool uses the CONCAT function to combine asset category and asset type to automatically populate column A, 鈥淔or Lookup,鈥� and lists an estimated useful life for each combination of category and type. If you add new asset categories or types, you must also add new rows in the 鈥淓st. Useful Life 鈥� HIDE SHEET鈥� worksheet.

Instructions

1. You can directly edit values for estimated useful life for existing asset types in the 鈥淓st. Useful Life 鈥� HIDE SHEET鈥� worksheet, as shown in the screenshot below.
   
   
2. If you add new asset categories and/or types, you must enter them in the 鈥淓st. Useful Life 鈥� HIDE SHEET鈥� worksheet, following the convention shown in that sheet.
   1. The tool automatically populates 鈥淔or Lookup鈥� (column A) by combining asset category and asset type using the CONCAT (B#, C#) formula. You should only enter data in columns B, C, and D鈥�"Asset Category,鈥� 鈥淎sset Type,鈥� and 鈥淓stimated Useful Life,鈥� respectively.
   2. Any new asset category/type combination must exactly match the category name in the 鈥淒ropdown Menus 鈥� HIDE SHEET鈥� worksheet and type name in the 鈥淎sset Tables 鈥� HIDE SHEET鈥� worksheet.

Adjusting Question Weights

Overview

• The tool calculates condition score, consequence of failure, probability of failure, and critical assets and includes weights for each question. You can view and change these weights in the 鈥淲eighting Tables 鈥� HIDE SHEET鈥� worksheet.
• Each calculation depends on the weights of individual questions and the total maximum score for each question given the defined weights. The tool normalizes the total score for each calculation to the maximum possible value.
• Changes to the weighting for each question will not change the value for each individual answer. For example, an answer of 鈥淓xcellent鈥� for the asset鈥檚 current condition is worth 4 points with a weight of 1. If you change the weight to 2, 鈥淓xcellent鈥� would be worth 8 points.

Instructions

1. Open the 鈥淲eighting Tables 鈥� HIDE SHEET鈥� worksheet to view weights for each question. The sheet shows the question, other sheets that use the question, and the current weight. It also shows the total maximum score for each calculation. These values automatically calculate and should not be modified.
2. Change weights in column C. Weights are multipliers for existing scores and can be any value greater than 0. For example, changing the weight from 1 to 2 will double the value for each response to that question. See the table below for an example of how scores change based on weights for the current conditions question.
3. You do not need to make any modifications to individual score calculations once you adjust the weights.

Saving and Hiding Tabs

When you have finished editing the hidden tabs and are satisfied the edits work in a manner you expect, you should re-hide the tabs and save the workbook.

• To re-hide previously hidden tabs, right click on each tab you want to hide, and select 鈥淗ide.鈥�
• When saving the edited workbook rename the workbook using a file naming convention that indicates the workbook has been modified, while maintaining a copy of the original workbook.

  ---

Questions? Contact: Brian Bohnsack, Program Manager, brian.bohnsack@wichita.edu, (316) 978-6421