Open topic with navigation

Smart Client/ / Real Estate Portfolio Management / Strategic Financial Analysis /Financial Analysis (SC) - Smart Client

Define Capital Expense Matrix and Fields

There are two tasks that add-in managers can use to personalize the titles of the capital expense matrix and the fields that load when you select a box within the matrix.

These tasks are accessed from the Smart Client

Real Property Portfolio Management / Strategic Financial Analysis (SC) / Define Capital and Expense Matrix Fields task (under the Add-In Manager heading)

• Define Capital Expense Matrix
• Define Capital Expense Matrix Fields

Define Capital Expense Matrix

The Capital and Expense Matrix (finanal_matrix) table holds the structure and roll-up relationships for the Capital and Expense Matrix. See Capital and Expense Matrix. You can alter certain aspects of the matrix. However, you cannot redefine it entirely.

You can make the following changes:

• Add boxes in the column. To do so, add data in the following fields:
  • finanal_matrix.column_box_id - column ID;
  • finanal_matrix.rollup_fin_box_id - financial roll-up ID;
  • finanal_matrix.box_type – input type BO(box).
• The title of boxes in the column, by setting finanal_matrix.box_title values.
• The displayed values of the boxes, by setting finanal_matrix.value_formatted field values. You can also set finanal_matrix.value field values to ensure that the box size represents its actual value. If you set the value to 0, the box will be displayed using minimal size.
• The percent of the box inside the column, by setting finanal_matrix.rollup_percent_box_id. To do so, add in the field Percent Roll-Up.
• The order of boxes or roll-ups in the column, by setting finanal_matrix.display_order values.
• Assign boxes to analytic roll-ups, by setting finanal_matrix.rollup_anlys_box_id_01 – 05.
• Add financial roll-up boxes displayed in the column. To do so, add data in a field finanal_matrix.column_box_id - column ID and in a field finanal_matrix.box_type – FR (financial roll-up).
• The title of roll-ups in the column, by setting finanal_matrix.box_title values.
• Add analysis roll-ups in the column С3. To do so, add data in a field finanal_matrix.column_box_id the column C3 ID and in a field finanal_matrix.box_type – AR (analysis roll-ups). See the field descriptions for Analysis Roll-up 01-05.
• The title of analysis roll-ups in the column, by setting finanal_matrix.box_title values.
• The displayed values of the analysis roll-ups, by setting finanal_matrix.value_formatted field values. You can also set finanal_matrix.value field values to ensure that the box size represents its actual value.
• The tooltip for the columns, boxes, roll-ups, and analysis roll-ups, by setting finanal_matrix.box_tooltip.

You cannot add new types of columns, roll-ups, or boxes without also adjusting the matrix control JavaScript itself.

The cost analysis boxes

The Capital and Expense Matrix is composed of individual cost analysis boxes, with lower level boxes grouped into intermediary level boxes (roll-ups), and then into high level boxes (columns). All elements of the Capital and Expense Matrix are considered boxes, and each box represents a row in the Capital and Expense Matrix table.

You give each box a Box Type as follows:

• Individual Box (BO). Individual Boxes can be stacked into one of the first 2 columns (C1, C2).
• Financial roll-up and percent rollup boxes. Inside the columns C1 and C2, individual boxes can be grouped by Financial Roll-Up (FR) and by Percent Roll-Up (PR).

  Note: The percent roll-ups are not displayed as a box in the column, but are hidden, and used to determine the percentages displayed in the individual boxes.

• Analysis Roll-ups. The 3rd column (C3) is the Analysis Roll-Ups Column which contains only Analysis Roll-ups (AR) for the individual boxes in the second column (C2).

  Tip: In the table, there should be only one record each of box type C1, C2, and C3.

The following image shows the boxes in the Financial Analysis's Capital and Expense Matrix.

Click here to see the fields of the Capital and Expense Matrix (finanal_matrix) table

The following is the field reference for the Capital and Expense Matrix table:

Capital and Expense Matrix table (finanal_matrix)
Multi-Line Heading	Field Name	Description	Example	Data Type (Size)	Allow Null?	Default Value (if any)	Enum List (if any)	Comments
Box Code	box_id	The ID that best and uniquely identifies each box.	column_asset_net_worth column_expenses fin_rollup_project_fiscyr_market fin_rollup_expenses_operating anal_rollup_ownership box_structures_book box_services_indirect	Char (32)	No			Pkey 1 For easy distinction between columns, roll-ups and individual boxes we advise to use a prefix, such as: "column_" for boxes that represent columns "fin_rollup_" for boxes that represent financial roll-ups "perc_rollup_" for boxes that represent percent roll-ups "anal_rollup_" for boxes that represent analysis roll-ups "box_" for individual boxes that are neither columns, nor roll-ups
Box Title	box_title	The box title that ARCHIBUS displays in the Capital and Expense Matrix.	Capital Asset Net Worth Structures - Book Value Expenses Expense Roll Up Operating Expenses Indirect Services Ownership	Char (32)	No			The title does not have to be unique. Use a title that is concise yet meaningful for the targeted analysis and audience. For boxes that represent columns, this will be the actual column's header. The title of percent roll-ups does not display in the matrix.
Box Subtitle	box_subtitle	Currently not used.
Box Type	box_type	Each box can has a specific type in the organization o the Capital and Expense Matrix. Some represent columns, others financial roll-ups, others analysis roll-ups.	For example the box titled 'Expenses' has the type of the second column, which is box_type = "C2"	Char(2)	No	BO; Individual Box	C1;First Column; C2; Second Column; C3; Analysis Column FR; Financial Roll-up AR; Analysis Roll-up PR;Percent Roll-up BO; Individual Box	The basic element of a Cost Directory is the Individual Box (BO). Individual Boxes can be stacked into one of the first 2 columns (C1, C2). Inside the columns C1 and C2, individual boxes can be grouped by Financial Roll-Up (FR) and by Percent Roll-Up (PR). The percent roll-ups are not displayed as a box in the column, but hidden and are used to determine the percentages displayed in the individual boxes. The 3rd column (C3) is the Analysis Roll-Ups Column which contains only Analysis Roll-ups (AR) for the individual boxes in the second column (C2). In the table there should only be one record each of box type C1, C2, and C3.
Matrix Name	matrix_name	The matrix to which the Column Box is assigned.	DEFAULT	Char (24)	No	DEFAULT		You need to enter the appropriate Matrix Name only for column boxes (box_type in C1, C2, C3). For other types of boxes you can leave them with the default value. The application looks only for column boxes to determine the composition of a matrix. Do not allocate more than three column boxes to the same Matrix Name Do not allocate more than one column box of the same type (ex, more than one first column or more than one second column) to the same Matrix Name. DEFAULT = Capital and Expense Matrix
Assigned to Column	column_box_id	The box code of the column to which the box is assigned to. This is the name of the box that has the box type of either First Column, Second Column or Roll-Ups Column	column_net_worth_infrastruct column_expenses	Char (32)	Yes		N/A	Application logic notes: Avoiding self-reference. Boxes with Box Type as columns (C1, C2, C3) do not have a value in this column since there is no point to assign a column to a column. Users always assign boxes with the Box Type of Analysis Roll-up (AR) with Analysis Column (C3), only. Users always assign boxes with the Box Type of Financial Roll-up (FR) to one of the first two columns (C1 or C2).
Financial Roll-Up	rollup_fin_box_id	Inside the columns C1 and C2, individual boxes can be grouped by Financial Roll-Up (FR). This field stores the box code of the Financial Roll-Up to which the individual box is assigned to.	fin_rollup_expenses_operating	Char (32)	Yes			Application logic notes: Only individual boxes (box_type = "BO") are assigned financial roll-ups. Based on the app logic, columns and analysis roll-ups don't need to be further rolled-up/grouped.
Percent Roll-Up	rollup_percent_box_id	Inside the columns C1 and C2, individual boxes can be grouped by 'invisible' Percent Roll-Ups (PR), so that the matrix can determine the box's percent in the total Percent Roll-Up.	perc_rollup_networth-percentage perc_rollup_tco-percentage	Char (32)	Yes			Only individual boxes (box_type = "BO") are assigned percentage roll-ups. Percentage roll-ups live only in the first 2 columns. Based on the app logic, columns and roll-ups don't need to be further rolled-up/grouped.
Analysis Roll-Up 01	rollup_anal_box_id_01	Analysis categories by which individual boxes (BO) in the second column (C2) may be organized and rolled-up in the Roll-Ups Column (C3).	anal_rollup_variable_costs	Char (32)	Yes			Each individual box from the second column can be assigned to up to 5 Analysis Roll-Ups. Application logic note: Financial Roll-Ups and Columns cannot be rolled-up in analysis categories. The same goes for individual boxes (BO) assigned to the First Column (C1).
Analysis Roll-Up 02	rollup_anal_box_id_02		anal_rollup_total_cost_ownership	Char (32)	Yes
Analysis Roll-Up 03	rollup_anal_box_id_03		anal_rollup_total_cost_ownership	Char (32)	Yes
Analysis Roll-Up 04	rollup_anal_box_id_04		anal_rollup_workpoint_cost	Char (32)	Yes
Analysis Roll-Up 05	rollup_anal_box_id_05		anal_rollup_utilities	Char (32)	Yes
Display Order	display_order	The order in which individual boxes are displayed inside financial roll-ups and the order in which roll-ups are displayed inside a column.	The first financial roll-up box in the first column may have the Display Order of: "101"	SmallInt	No	0		Boxes with Box Type of First Column (C1), Second Column (C2), and Analysis Column (C3) are always displayed as column headers (top), disregarding the Display Order.
Background Color	display_bkg_color	The box's background color in hexadecimal RGB, that ARCHIBUS uses to display the box in the Capital and Expense Matrix.	#90EE90 This HEX value represents a shade of light green in the RGB spectrum, and thus ARCHIBUS displays the box with this color.	Char (8)	No	#ADD8E6		For more color HTML RGB to HEX color codes refer to: http://www.w3schools.com/colors/colors_picker.asp
Value - Calculation	value_calc	The SQL calculation that is used to calculate the value or value_book for the box.	SUM bl.value_book FROM bl WHERE bl.status="N/A"	VarChar (2000)	Yes			Use ARCHIBUS SQL binding expressions to make the SQL statement work on different database environments such as: Sybase, MS-SQL or ORACLE-SQL.
Value Market - Calculation	value_market_calc	The SQL calculation that is used to calculate the value_market for the box.	SUM bl.value_market FROM bl WHERE bl.status="N/A" For calculations independent of the book or market evaluations, the SQL formula is the same, in both value_calc or value_market_calc fields	VarChar (2000)	Yes
Value - Decimal Places	value_disp_decimals	This field specifies the rounding that the display controls should perform on values when displaying them.	For instance, you can display "-104,000,000" with the following rounding values: "Hundreds": ($1,040,000H) “Thousands”: ($104,000K) “Millions” : ($104M)	Char (1)	No	M;Millions;	H;Hundreds; K;Thousands; M;Millions;
Value	value	The calculated value for the box. This also may represent the book value.	-104,000,000	Numeric (12,0)	No	0		The cost values are saved without any decimals, because the Capital and Expense Matrix gives a high level portfolio overview of these values which usually are in hundreds of thousands (100K+) or millions (1M+). afm_flds.num_format = "Budget Currency" The Capital and Expense Matrix displays all cost amounts in Budget Currency.
Value - Formatted	value_formatted	The value formatted that is displayed, that is displayed when the cost directory is set to "Book Values". This value is formatted based on the settings in the field "Value - Formatting" (value_display_format)	( $104 M )	Char(24)	No	$0
Value - Market	value_market	The calculated market value for the box. This may be equal to the (book) value, for calculations which are independent of either of the evaluation methods.	90,000,000	Numeric (12,0)	No	0
Value Market - Formatted	value_market_formatted	The market value formatted that is displayed, that is displayed when the cost directory is set to "Market Values". This value is formatted based on the settings in the field "Value - Formatting" (value_display_format)	$90M	Char(24)	No	$0
Value - Date Last Calculated	date_last_calc	The latest date for which the box value was calculated	2016-01-31	Date	No	1900-01-01
Security Group Name	group_name	The Security Group assigned to the cost directory box.	PROCESS OWNER	Char (64)	Yes			FKey to afm_groups.group_name If there is no Security Group value then anyone can view the box.
Box Tooltip Text	box_tooltip	The tool tip text that is displayed when the user hovers the mouse over the box The tool tip gives additional helpful information that explains a particular cost or roll-up.	Value of buildings and their parcels of land on the last day of the last fiscal year.	VarChar (256)	Yes
Box Title (Chinese Simplified) Box Title (German) Box Title (Spanish) Box Title (French) Box Title (Italian) Box Title (Japanese) Box Title (Korean) Box Title (Dutch) Box Title (Lang01) ... Box Title (Lang05)	box_title_ch, box_title_de, box_title_es, box_title_fr, box_title_it, box_title_jp, box_title_ko, box_title_nl, box_title_no, box_title_zh, box_title_01, box_title_02, box_title_03, box_title_04, box_title_05	The translation of the Box Title into one of the languages for which ARCHIBUS or its Business Partners targets its product.	For example, Box Title = "Total Cost of Ownership" Then, Box Title (French) = "Cout total de l'Occupation" Box Title (Italian) = "Costo totale della occupazione" ... Box Title (Lang 01) = "Costul total al ocuparii"	Char (48)	Yes
Box Subtitle (Chinese Simplified) Box Subtitle (German) ... Box Subtitle (Lang05	box_subtitle_ch, box_subtitle_de, box_subtitle_es, box_subtitle_fr, box_subtitle_it, box_subtitle_jp, box_subtitle_ko, box_subtitle_nl, box_subtitle_no, box_subtitle_zh, box_subtitle_01, box_subtitle_02, box_subtitle_03, box_subtitle_04, box_subtitle_05	The translation of the Box Subtitle into one of the languages for which ARCHIBUS or its Business Partners targets its product.		Char (96)	Yes
Box Tooltip Text (Chinese Simplified) Box Tooltip Text (German) ... Box Tooltip Text (Lang05)	box_tooltip_ch, box_tooltip_de, box_tooltip_es, box_tooltip_fr, box_tooltip_it, box_tooltip_jp, box_tooltip_ko, box_tooltip_nl, box_tooltip_no, box_tooltip_zh, box_tooltip_01, box_tooltip_02, box_tooltip_03, box_tooltip_04, box_tooltip_05	The translation of the Box Tooltip text into one of the languages for which ARCHIBUS or its Business Partners targets its product.	For example, Box Tooltip Text (French): "La valeur des bâtiments et de leurs parcelles de terrain pour le dernier jour de l'année financière courrant." The English equivalent (Box Tooltip Text) is: "Value of buildings and their parcels of land on the last day of the last fiscal year."	VarChar (384)	Yes

Define Capital Expense Matrix Fields

Add-In managers use the Capital and Expense Matrix - Fields (finanal_matrix_flds) table to establish which analyses values are displayed in the Financial Analysis Console's panels when users click on one of the boxes in the (Capital and Expense) Analysis Matrix.

This table holds the list of drill-down fields for each box in the Capital & Expense Matrix.

Including the identification fields in the Asset Scorecard

If you are adding fields to the Capital and Expense Matrix's Asset Scorecard, the display does not automatically include the identifier for the metric; that is, the Asset Scorecard does not include the building, property, or equipment that the metric is for unless you explicitly add the identifier as a field.

To add the identifiers, you:

1. Create finanal_matrix_flds records, and assign the identification field (bl_id, pr_id, etc.) to the Box Code (box_id ).
2. Specify the table that the identifier is in - finanal_sum table - see the Analysis Table column in the following screen shot.
3. Set the Display Order so that the bl_id and pr_id are displayed before the metrics.
4. Set the Analysis Console Panel to Asset Scorecard.
5. Click off of the field to save the record.

Note: This applies to the Asset Scorecard, as for other panels (like Maps) it’s not relevant to display these asset identifiers.

Click here to see the field reference for the Capital and Expense Matrix - Fields (finanal_matrix_flds) table

Capital and Expense Matrix - Fields (finanal_matrix_flds)
Multi-Line Heading	Field Name	Description	Example	Data Type (Size)	Allow Null?	Default Value (if any)	Enum List (if any)	Other/Comment
Box Code	box_id	The Capital & Expense Matrix box to which this analysis field is assigned to. When the user clicks on this box in the Capital & Expense Matrix, then the analysis field's values are displayed in a specific console's panel.	column_expenses	Char(32)	No			Validates against the finanal_matrix.box_id Box Code + Console Panel + Table Name + Field Name = Primary Key
Analysis Console Panel	analysis_console_panel	The Financial Console's panel in which this analysis field's values will be displayed when the user clicks on the associated Capital & Expense Matrix box.	Analysis Scorecard	Char(18)	No	Asset Scorecard	Analysis Scorecard;Analysis Scorecard;Asset Scorecard;Asset Scorecard;Asset Map;Asset Map;Site Plan;Site Plan;Lifecycle Analysis;Lifecycle Analysis;Trend Analysis;Trend Analysis;
Analysis Table	analysis_table	The table name that this field of analysis values resides in.	finanal_sum	Char(16)	No		finanal_sum finanal_sum;finanal_sum; finanal_sum_life;finanal_sum_life;bl;bl	Analysis fields are columns in the finanal_sum or finanal_sum_life tables.
Analysis Field	analysis_field	The Field Name of this analysis field.	total_net_worth	Char(32)	No			FKey to afm_flds
Display Order	display_order	The display order of the analysis field(value) in the panel.	1	Smallint	No	999		The value of this field is not used for maps.