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:
- Process owners can set this action up to run on a schedule (for example, monthly).
- When you have historical data that you want to aggregate into the costs tables.
- Analysts and cost accountants can run this action on any time frame to ensure that costs are up-to-date. For example, you would run this action when you have imported external data, such as imported work order costs from your CMMS system, and wish to add them to the cost model .
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 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: |
||||
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: 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.
|
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:
- 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.
- 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.
- Click Aggregate Operating Expenses.
The Aggregate and Forecast Costs form appears.
- 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:
- Deletes existing 'AUTO-SUMMARY' cost_tran_sched records for the selected date range:
- 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:
- 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.