Real Estate Portfolio Management / 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	On Demand 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	Project Management	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	Project Management	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 Management	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	Cost Administration	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	Lease Administration	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:
Aggregate and Forecast Costs tasks for
- Properties
- Buildings
- Projects
- Equipment.
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.