Asset Management, Exams of Investment Management and Portfolio Theory

Download Asset Management and more Exams Investment Management and Portfolio Theory in PDF only on Docsity!

ASSET MANAGEMENT

MRWA

Asset Management

Data Tab

Contains all of the asset management data. This is setup in Table format. End users will enter information into the columns that are NOT a yellow fill.

Warning!

You CAN NOT enter data over the yellow cells or you will destroy the formulas and data integrity.

They are:

• Infrastructure Type: Select Source, Treatment, Distribution, or Storage from a drop down list box.
• Assets: Enter in the defining name of the asset
• Useful life: enter in the estimated useful life of the asset
• Item: Enter in the specific name of the asset
• Location: enter in the specific location of the asset
• Year Installed: enter in the year the asset was installed

• Original Cost: Enter in the original cost of the asset
• Criticality Analysis: Refer to Asset Rating tab to enter in the number
• Comments

Do not fill in any of the columns that are in yellow. These cells contain formulas that will automatically fill the correct data in.

• Condition
• Replacement Date
• Remaining Life
• Current Value
• Annual Depreciation

The information in the Data Tab will drive and supply the information for the following tabs:

• Water System Inventory
• Source
• Treatment
• Storage
• Distribution
• Asset Graphs
• Overall Condition

Refresh

These worksheet tabs will automatically update when you open the file. When you make changes to the Data worksheet tab you will need to Refresh the worksheets manually. There are two main ways to perform this task:

1. Save, close, and re-open the file; the worksheets will all be refreshed.
2. Select the Data Tab >> Connection Group >> Refresh All. This will refresh the data in each worksheet.

Note: if you want, you can add that function to your Quick Access Toolbar by Right-mouse clicking the icon and selecting Add to Quick Access Toolbar.

Clear a Filter

To clear a filter off of one column, click the drop down for that column. Select Clear Filter From… Your records will be displayed.

If you have many columns filtered, you can use the Data Tab >> Sort & Filter group >> Clear.

Records Found

If you have your data filtered, the area just above your start button (lower left corner) will display how many records are found.

Multiple Filters

This example first filters Infrastructure Type for Treatment records. Then, for Chemical Equipment in the Asset Column.

Results:

This example filters data for all records in the Condition column that display “Replace”

Fields you put in the different layout section are as follows:

1. Report Filters: filters are shown at the top-level report above the PivotTable and will filter the entire table at once.
2. Column Labels: are shown in column layout (horizontal) at the top of the PivotTable.
3. Row Labels: are shown in Row layout (vertical) on the left side of the PivotTable.
4. Values: are shown as summarized numeric values.

Update Value Field Settings

You may need to update the format of your values, i.e. currency. To do this:

1. Right mouse click the value you would like to change. Select Number Format…
2. Update to the desired format. Click OK. This updates how your numbers are formatted in your PivotTable and will carry over into your charting.

PivotChart

1. Click within the PivotTable information.
2. From the PivotTable Tools>>Analyze tab click PivotChart from within the Tools Group.
3. Select the type of chart you would like from the Insert Chart window. Click OK.
4. Next, we will turn the elements of the chart we want off / on using the Chart Element button

to the right of the selected chart. Select or deselect the elements for your chart.

5. Use the Paint brush to change the style and colors of the chart.

Select the data from the slicer you wish to show. I.e. Distribution from the Infrastructure slicer will only display that type in the Pivot Table and Chart.

Clear a filter off by using the clear filter button in the upper right hand corner of the Slicer.

PivotTable Tips & Tricks

As soon as you create a new pivot table (or select the cell of an existing table in a worksheet), Excel displays the Options tab of the PivotTable Tools contextual tab. Among the many groups on this tab, you find the Show/Hide group that contains the following useful command buttons:

• Field List to hide and redisplay the PivotTable Field List task pane on the right side of the Worksheet area.
• +/- Buttons to hide and redisplay the expand (+) and collapse (-) buttons in front of particular Column Fields or Row Fields that enable you to temporarily remove and then redisplay their particular summarized values in the pivot table.
• Field Headers to hide and redisplay the fields assigned to the Column Labels and Row Labels in the pivot table.

Format the Values in the PivotTable

To format the summed values entered as the data items of the pivot table with an Excel number format, follow these steps:

1. Right Mouse click the cell in the pivot table. Select Number Format.
2. In the Category list, select the number format you want to assign.
3. (Optional) Modify any other options for the selected number format, such as Decimal Places, Symbol, and Negative Numbers.

Sort a PivotTable

You can instantly reorder the summary values in a pivot table by sorting the table on one or more of its Column or Row fields. To sort a pivot table, follow these steps:

1. Click the filter button for the Column or Row field you want to sort.
2. Click either Sort A to Z or Sort Z to A at the top of the field's drop-down list.

Click the Sort A to Z option when you want the table reordered by sorting the labels in the selected field alphabetically, from the smallest to largest numeric value, or from the oldest to newest date. Click the Sort Z to A option when you want the table reordered by sorting the labels in reverse alphabetical order (Z to A), values from the highest to smallest, and dates from the newest to oldest. You can also click in any data in the PivotTable and use the Sort and Filter options on the Data Tab within the Ribbon.

Modify the PivotTable Fields

1. From the Field List: (if this is not dispalyed, click in the PivotTable, Analyze Tab >> Show Group

   Field List.

a) To remove a field from the table, drag its field name out of any of the drop zones and, when the mouse pointer changes to an x, release the mouse button; or click its check box in the Choose Fields to Add to Report list to remove it its check mark.

b) To move an existing field to a new place in the table, drag its field name from its current drop zone to a new zone at the bottom of the task pane.

c) To add a field to the table, drag its field name from the Choose Fields to Add to Report list and drop the field in the desired drop zone.

Modify the Summary Function

By default, Excel uses the SUM function to create subtotals and grand totals for the numeric field(s) that you include in a pivot table. Some pivot tables, however, require the use of another summary function, such as AVERAGE or COUNT.

To change the summary function that Excel uses in a pivot table, follow these steps:

1. Right-mouse click the field. Select a new summary function in the Value Field Settings dialog box.