Real Property / Strategic Financial Analysis / Financial Analysis

Aggregate Operating Expenses

The Aggregate Operating Expenses action rolls up operating expenses that occur as part of maintenance work orders, service desk actions, or expensed project actions, and creates a set of Scheduled Cost (cost_tran_sched) records for these charges. The action aggregates these records on a per-building (or per-property if the Building Code is null), per month basis, so these costs can be included in examinations of monthly operating expenses.

When this action is run

Here's when this action is run:

The AUTO-SUMMARY status ensures that costs are not duplicated

To indicate that the scheduled costs records were auto-summarized from other applications for the purpose of cost analysis, the Aggregate Operating Expenses action will assign the Cost Status of AUTO-SUMMARY to these costs (cost_sched_tran.status enumerated value of AUTO-SUMMARY.) This ensures that the application does not duplicate existing per building per month cost summary values for the costs it summarizes when it runs.

How the Aggregate Operating Costs action aggregates costs

The action summarizes costs by building or property by month; that is, it creates one Scheduled Cost record per building per month per Cost Category, and adds them to the cost model for the purposes of cost analysis.

The following costs roll into the cost model as expense categories in the Scheduled Costs table. The categories of operating expense that the action rolls up are below. For each category of cost, the action creates one cost record per building per month within the fiscal year.

These Business Activities

Execute these typical actions

Capture Costs in these Archibus Applications

In these tables

Which the aggregate action summarizes into Scheduled Costs using...

Building Maintenance

Grounds Maintenance

Trash Removal

 

Fixing an AHU

Corrective Maintenance and Preventive Maintenance

Work Requests and Historical Work Requests (in the wrhwr SQL View)

that are assigned to Problem Types (in the probtype table)

Note: For work requests, the application also looks at the status for wr.status IN 'Com' 'Clo'

Problem Types mapped to Cost Categories, such as:

MAINT-BLDG-EXT
MAINT-CLEANING

MAINT-ELECTRICAL

MAINT-SAFETY

MAINT-SECURITY

MAINT-GROUNDS

MAINT-HVAC

MAINT-INTERIOR

MAINT-ELEV

MAINT-PLUMBING

MAINT-STRUC

MAINT-TRASH

MAINT-PREVENTIVE

MAINT-REACTIVE

You use the Define Problem Types task to map problem types to cost categories.

     

Note: For action items, the application splits the action items into:
- Actual costs: activity_log_hactivity_log.status IN ( 'STOPPED', 'COMPLETED', 'COMPLETED-V', 'CLOSED' )
- Estimated costs: status IN ( 'BUDGETED', 'PLANNED', 'SCHEDULED', 'IN PROGRESS', 'IN PROCESS-H' )

 

Employee Direct Services

Employee Indirect Services

Employee Moves

 

Ordering catering

Copy services

Moving a department using an external service provider

Service Desk

Action Items and Historical Action Items (in the activity_log_h_activity_log SQL view)

that are assigned to Action Types (in the activitytype table)

Action Types mapped to Cost Categories, such as:
SERVICES-EMP  

SERVICES-EMP-MOVES 

You map actions to cost categories using the Define Action Types task.

Expensed Projects

Repainting a room

Projects

Action Items and Historical Action Items (activity_log_h_activity_log SQL view)

that are assigned to Action Types (in the activitytype table)

Action Types mapped to Cost Categories such as:

PROJECT – CONSTRUCTION activity type which links to cost category PROJECT – EXPENSE

Capital Projects

Significant renovation of newly leased space

Addition of a new wing of a building

Bulk purchase of 100 computers which are depreciated as a batch

 

Projects

Action Items and Historical Action Items (activity_log_h_activity_log SQL view)

that are assigned to Action Types (in the activitytype table)

These Action Items are associated with Invoice Line Items (invoice_line_items) in order to connect them to the invoices that are billed against the project.
Individual invoice line items on a capital project may be capitalized or
expensed. 

 

 

The Cost Category of the Action Type to map to these Cost Categories:

PROJECTS-EXPENSED

PROJECTS-CAPITALIZED

 

Other costs that do not need to be aggregated

Energy and Utility Costs

Bills paid to gas company

Energy

Bills that are assigned to Cost Categories in the Bill Types (bill_types) table.

The Bill approve and archive action adds one Scheduled Costs record per bill. Those Scheduled Costs will have the Cost Category that the business process owner associated with the bill's Bill Type, e.g.,

UTILITY - ELECTRIC

UTILITY - FUEL OIL

UTILITY - GAS

UTILITY - PROPANE

UTILITY - SEWER

UTILITY - WATER

The bill type’s cost category is mapped from the cost categories (cost_cat) table into bill_type.cost_cat_id.

Other Operating Costs

Legal Fees, Inspection Fees

Costs

Cost, Scheduled Costs, and Recurring Costs

That are entered directly into the Cost tables using assigned Cost Categories.

Lease Costs

Base rent and escalations

Snow removal fees

Leases

Cost, Scheduled Costs, and Recurring Costs

That are entered directly into the Cost tables using assigned Cost Categories.

Procedure: Aggregating operating expenses

To run the Aggregate Operating Expenses action:

  1. Select any of the following tasks:
    • Real Property / Strategic Financial Analysis / Financial Analysis / Properties Cost Forecast
    • Real Property / Strategic Financial Analysis / Financial Analysis / Buildings Cost Forecast
    • Real Property / Strategic Financial Analysis / Financial Analysis / Projects Cost Forecast
    • Real Property / Strategic Financial Analysis / Financial Analysis / Equipment Cost Forecast

    The Aggregate and Forecast Costs form appears.

  2. Select the Aggregate Operating Expenses tab.

    The Aggregate Operating Expenses for All Assets form appears. This form lists the scheduled costs that were generated the last time the action was run.

  3. Click Aggregate Operating Expenses.

    The Aggregate and Forecast Costs form appears.

  4. Enter the time frame for which you want to generate cost records, and Click OK.

    The action creates one scheduled cost record per building, per month, per cost category. See the next section for a description of what occurs.

Calculations

The Aggregate Operating Expense action:

  1. Deletes existing 'AUTO-SUMMARY' cost_tran_sched records for the selected date range:
  2. Creates scheduled cost records for work request costs where the work request has the status wrhwr.status IN ( 'Com', 'Clo' ). The action creates scheduled cost records for each represented Cost Category that is associated with a Problem Type for each building for each month:
  3. Creates scheduled costs for service request/ action items. The action creates Scheduled Cost records for each cost category that is associated with an action type for each building for each month: Both estimated costs and actual costs are used by querying the following action item Statuses:
    - Actual costs: activity_log_hactivity_log.status IN ( 'STOPPED', 'COMPLETED', 'COMPLETED-V', 'CLOSED' )
    - Estimated costs: status IN ( 'BUDGETED', 'PLANNED', 'SCHEDULED', 'IN PROGRESS', 'IN PROCESS-H' )Estimated costs have 'Estimated" added as a prefix in the Description field.

    For projects, the application obtains the costs as the sum of

    ( cost_actual + cost_act_cap )

    IF the actual costs = 0 THEN ( cost_est_design_cap + cost_est_design_exp )

    IF the design costs = 0 THEN ( cost_estimated + cost_est_cap )

Note: When the Aggregate Operating Expenses action generates a Scheduled Cost for a time period, it deletes any Scheduled Cost record for that asset and Cost Category that already exists for that period. These costs have the AUTO-SUMMARY Cost Status. For instance, if building HQ already has a scheduled cost with an "AUTO-SUMMARY" status for the February 2017 period, the Aggregate Operating Expenses action deletes that Scheduled Cost record before creating the new aggregated cost record for February 2017.