Define Capital Expense Matrix and Fields
Smart Client/ / Real Property / 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 / 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.
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.
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) | ||||||||
|---|---|---|---|---|---|---|---|---|
| Field 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 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:
- Create finanal_matrix_flds records, and assign the identification field (bl_id, pr_id, etc.) to the Box Code (box_id ).
- Specify the table that the identifier is in - finanal_sum table - see the Analysis Table column in the following screen shot.
- Set the Display Order so that the bl_id and pr_id are displayed before the metrics.
- Set the Analysis Console Panel to Asset Scorecard.
- 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.
Field reference for the Capital and Expense Matrix - Fields (finanal_matrix_flds) table
| Capital and Expense Matrix - Fields (finanal_matrix_flds) | ||||||||
|---|---|---|---|---|---|---|---|---|
| Field 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. |
|