Modeling with Microsoft Excel

You can leverage Microsoft Excel to define modeling rules and calculations to populate budget amounts. Standard Microsoft Excel functionality, including formulas, can be incorporated with data from Hubble. Models are defined and uploaded into Hubble by approved users. Contributors can then apply a model, as needed, to an Input Form, without the risk of seeing unsecured data or making unauthorized changes to the model definition. Model Scope can be defined to control whether modeled amounts can be overwritten by budgeting Contributors.

Define and Apply Model Workflow

Create Model Template

A ready to use Microsoft Excel Model Template can quickly be exported by following these steps:

Open the desired Form. Model templates can be created from any Hubble Inquiry that is eligible to be included as a Budget Form. This includes GL Balances, JC Balances, or a DX Inquiry.
Click Create on the Budgeting Menu to export a ready to use Microsoft Excel Model template for the active Input Form.

Browse and save the workbook to a desired location.
Note: A model template can only be saved in XLSX format. However, once created it can also be re-saved to an XLSB format file if required. Both these formats are supported by the “Apply Model” functionality.

If required, the exported template can be copied to an existing or manually created Excel workbook.
Tip: Best Practice:
1. Name the Microsoft Excel Model the same as the Input Form.
2. To reduce the size of your Excel model, save the file as a binary workbook, e.g..xlsb.
3. Include a tab in the Excel spreadsheet for assumptions used in the model, e.g. assuming 8 hrs/day, 40 hrs/week, etc. When this need updating, you change it once and it updates all spreadsheets in the workbook.

Define Model in Microsoft Excel

Model Intersections

Once a template has been exported to Microsoft Excel, you’ll see that there are additional rows at the top of the worksheet. Hubble uses these rows in conjunction with the row labels (Business Unit, Object, Subsidiary) to locate an intersection. Utilize Microsoft Excel functionality to write formulas in cells available for data entry.

Note: Apply Model rules, which dictate how values are pushed from and to the Hubble Input Form are described in the Apply Model section below.

Model Example

The below model is designed to project next year’s budget for the three accounts listed. The model contains intersections with Actuals from the AA ledger (orange cells), and a Hubble Ledger (blue cells) with no previous submissions. The Hubble Ledger intersections contain a formula that increases the prior year amount by 10% and spreads it evenly over 12 periods.

When the above model is executed via Apply Model, the following steps are performed. For more information, see the Apply Model section below.

Actuals (orange cells) are updated in Excel, using the latest values pushed from the Hubble Form.
Hubble Ledger intersections (blue cells) are returned to the Hubble Form, after the defined formulas are recalculated using the updated values.

Apply Model Padding

The Apply Model function considers database padding when locating a match. In the example below, Business Unit 1 is actually stored as ‘ 1’ in the database. Even a blank field, such as Subsidiary, may contain padding. We recommend using the create button to build the model, as this will use the correct formatting and padding required.

It is worth noting if you click into a cell with General formatting containing ‘ 1’, Excel will change it to 1, and the Apply Model Function will not operate correctly. This can be avoided by setting the cell format to Text or by adding a single apostrophe at the start of the cell.

Wildcard Modeling

Wildcard Modeling is a powerful feature that allows models to become dynamic and reusable. Wildcard Modeling allows us to define a set of formulas once and reuse them across the organization.

For example, if the same formula applies to every Business Unit, then it is not necessary to include each Business Unit code in the model. Instead, a wildcard “*” can be used for Business Unit. That model can now be applied to any Business Unit, eliminating the need to update similar formulas across multiple workbooks. Wildcards also reduce the need to update or revise your model when a new Business Unit, Segment, or account is created.

Wildcard Apply Model

When the Apply Model function is used, Hubble separates the intersections from the Input Form into iterations that are passed through the model. During each iteration, amounts are updated in Excel and returned to Hubble following the normal Apply Model Rules.

The below model will be applied to any Business Unit from the Input Form with matching Object and Subsidiary Accounts. The % increase will vary depending on the Object Account as shown.

Wildcard Criteria

Within the Excel Model, you cannot use ranges or multi-selections in the dimension cell; it must always be either a selection or a wildcard denoted by “*”. This restriction does not apply to the filters on the reports in Hubble itself.
A wildcard can be used for more than one or even all dimensions. If all dimensions are wildcarded, the model can be used for any account combination. Apply Model will match all account combinations to the single line below.

If multiple matches exist, Hubble will return the result from the most specific line. In the below example an account combination with Object Account ‘5002 will match with both lines. The result of the ‘5002 line will be returned to Hubble as it specifically identifies the Object Account. If neither line is more specific (both lines wildcarded), Hubble will return the result from the top line.

Tip: Best Practice:

When using wildcards in Excel, the cell format for label columns, e.g. Company, Business Unit, Object Account and Subsidiary, should be set to text (not general). This can be performed quickly by adding a single apostrophe in front of the asterisk, such as ‘*.

Upload Model to Hubble

You can use Administrator or the Hubble application to upload the Microsoft Excel workbook into the Object Repository. You can then define permissions to control which users/groups of users can read, edit

or manage the Microsoft Excel files (just as with standard Hubble reports). You can organize the Microsoft Excel files using standard folder management options.

To upload the file from within Hubble, click Upload in the Budgeting Menu.

Navigate to where the saved Microsoft Excel file is located on your computer and select it.
Click Open.
Navigate to the location you want to save it under the Documents Folder within the Object Repository.
Click Save. This saves it to your Object Repository.
Important: When uploading an Excel Workbook into the Hubble, be sure to retain the Excel file extension such as .xlsx or .xlsb. Failure to do this could result in Hubble viewing the Model as an inquiry, potentially overwriting an Input Form of the same name.

You can now set Permissions on this file as desired, just as you would do with a standard Hubble report.

Assign Model to Form (optional)

A budget administrator can assign a model to an Input Form. when contributors want to apply a model. When a Model is assigned to an Input Form, the assigned model will be applied when the Contributor selects Apply Model. The Contributor will not need to select a model themselves, removing the risk of selecting the incorrect model. To do this, follow these steps:

From the Budgeting Menu, click Forms.

Highlight the input form with which you want to associate a model and click Edit.

In the Edit Form dialog, next to Model, click on the ellipsis or “…” to select the appropriate model:

Navigate to the model stored in the Repository and click Open. Note that if your model has multiple worksheets, you will be asked to pick the right one.

Click OK. Your input form is now assigned a model which will be used automatically when applying a model.

Apply Model

Apply Model Rules

When designing a Microsoft Excel Model, it’s important to understand the flow of data between the Hubble Input form and Microsoft Excel. The following occurs during Apply Model, Apply & Finish, or a Full – Cascade Step.

Eligible values are pushed from the Hubble Input Form to the matching Excel Model intersection.
1. Formulas within the model are automatically recalculated using the newly pushed data.
Eligible values are pushed from the results of the Excel Model to the matching Hubble Input Form intersection.
The contributor never sees the model itself; this is to prevent them from accessing irrelevant or inappropriate information. If they have authority, they can download and open the file in Microsoft Excel.

Apply Model Precedence

Whether a cell’s value is pushed to or from the Excel Model depends on the intersections data source. Generally, actuals and manual inputs/overrides are pushed to the Excel, while intersections with no

budget history or history only from a previous Apply Model are returned to the Hubble Input Form. The below chart includes a detailed listing.

DATA SOURCE (IN HUBBLE)	1. INPUT FORM TO EXCEL	2. EXCEL TO INPUT FORM
Actuals, Calculations, Non-budgeting amounts	Yes*	No
Bulk Populated – populated via Ledger Copy	Yes*	No
Hubble – populated manually in Hubble	Yes	No
Excel – populated via Apply Model	No	Yes
Unknown – a blank cell with no previous submission or cleared	No	Yes
All Other – text, comments, labels, subtotals	No	No

* Non-budgeting amounts (Actuals) are always updated in Excel, UNLESS the intersection in Excel contains a formula.

PushDescopedValues

When an Excel Model contains the “PushDescopedValues” keyword in cell A1, budget amounts which have been made non-submittable via Scope (meaning regular, input scope not model scope) will be treated as a Non-budgeting amount (i.e. Actuals) and pushed to and update the Excel Model, provided they don’t overwrite a formula.

Finding the Source of your Data

To find the source of your data, hold CTRL + SHIFT then click Enter Budget. You will see a colored dot in the upper right corner of all Budgeting cells, and when you hover over this dot, you will see where the data came from:

Teal “Hubble” – populated manually in Hubble
Green “Excel” – populated via Apply Model
Cyan “Bulk Populated” – populated via Ledger Copy
Red “Unknown Source” – a blank cell with no previous submission or cleared

This is important because there is a precedence in Hubble (see Apply Model Rules above). A model can never overwrite a number manually entered in Hubble. (This is correct behavior because maybe you do want to overwrite certain things and if you run the model again, you do not want it to overwrite what

you’ve already entered manually.) If you ever clear a value by right-clicking and clearing it, this will bring the state back to Unknown or red as the cell is blank.

Apply Model to an Input Form

A budgeting contributor can apply a Microsoft Excel model to an input form. Multiple models can be applied using the Cascade function. Refer to the Model Cascade section below for details.

From the Input Form in Hubble , click Enter Budget on the Budgeting Menu to enable Enter Budget Mode.
Apply the model. You can do this in either of the following ways:
1. Click Apply Model on the Enter Budget Menu, to apply the model and, once completed, remain in Enter Budget Mode to review the results and make any edits.
2. Click Apply and Finish on the Enter Budget Menu to apply the model, save the results, and finish Enter Budget Mode. (Be aware that using this option means you cannot review the apply model results prior to saving.)
If a Model has not been assigned to the Input Form, you be prompted to navigate to the Model uploaded to the Repository and click Open. Note that if your model has multiple worksheets, you will be asked to pick the right one.
Once the process is complete, you can click Save and Continue on the Enter Budget Menu to save the input form and stay in Enter Budget Mode.
If you need to cancel any input you've done since the last time you saved, click Cancel to cancel and exit Enter Budget Mode.
If you wish to see the results of applying the model (also called the Audit Log), click Audit Log on the Enter Budget Menu while still in Enter Budget Mode.
Click Finish on the Enter Budget Menu to save and exit Enter Budget Mode.

Note: Applying Microsoft Excel Models to Input Forms:

Only data items (e.g. actual intersections of Business Units, Object Accounts, Subsidiaries, Period, Ledger, Year, Subledger, Currency) are pushed to Microsoft Excel during the Apply Model process. Items like subtotals, calculations, etc. are not updated in the model. Therefore, calculated cells should be updated in the model with actual calculations from Microsoft Excel. As new data items are pushed out during the Apply Model, the totals, etc. will be recalculated by Microsoft Excel and can be used to drive further calculations in the model.
Hubble only supports associating an Input Form to a single worksheet. However, models can reference other worksheets and be applied successfully.

Viewing the Audit Log

The audit log shows the result of the most recent Apply Model for each intersection.

While still in Enter Budget Mode, click Audit Log from the Enter Budget Menu.
You will get an Apply Model: Results screen displaying the count of intersections that were and were not found by category. Each category can be expanded to see intersection detail.
1. Any intersections that were successfully found mean that a 1 to 1 match was found between the two places.
2. Any intersections that were not found mean that an intersection was only found in either Microsoft Excel or Hubble Budget Form, but not both. When this happens, confirm intersections included in one place are also included in the other. (For example, if Period 1 is included on the Hubble Budget form, but not in the Excel workbook, all Period 1 intersections will be Listed under “Available on Hubble Budget form but not found in Excel Workbook)”.
3. “Found but contained invalid input” are cells that were found in both Excel and Hubble but have invalid input, such as text instead of a value in a cell.

We recommend expanding each item to see the details. After expanding, make the pop-up dialog as large as possible to see more fields at a time.

Other options available in this dialog are:

Expand All - expand all items

Collapse All - collapse all items
Save as CSV – save all details in a .csv file
Close – close the dialog

Download the Model from the Object Repository

Users with permissions can download the model from within Administrator or the Hubble application in order to edit and update it as needed (including the ability to view it with current data).

To download the file from within Hubble:

Click Download from the Budgeting Menu.
Navigate to the location of the Microsoft Excel file and click Open.
Navigate to the location on your local drive where you wish to save the file and click Save.

To download the file from within Administrator:

Navigate to the location of the file underneath the Documents Folder.
Select the file in the right panel and right-click.
Choose the option to Download Excel File:

Navigate to the location on your local drive where you wish to save the file and click Save.
You are now prompted whether you wish to open the Excel file or not.

Update and Download

Update and Download allows the user to download a new version of the model that has been updated with data displayed on the current Hubble Input Form, to aid with troubleshooting and development. The Update process uses the Apply Model rules when determining which values to push from the Hubble Input form to the Excel Workbook.

To Update and Download the Model:

Enable Enter Budget Mode on an Input Form assigned to the desired Model.
Click Download > Update and Download, from the Budgeting Menu.

Browse and save the updated workbook to a desired location.

Model Debugger

The Model Debugger is a useful tool to examine the processes of a Microsoft Excel Model with Wildcards, which would usually remain behind the scenes. The Debugger displays, for each iteration, which values are pushed to the Excel Model and which will be returned to Hubble. Only turn this on for troubleshooting as it will impact performance.

Enable “Use Debug Modeling Engine” capability within the Administration tool. Remember, Hubble must be closed and reopened for any capability change to be made effective.
1. Advanced Capabilities under Hubble Inquiry Settings > Budgeting and Forecasting.

When executing an Apply Model, the Model Debugger will begin automatically. When executing a Cascade, the following pop-up is displayed during steps with Full defined as the Model Mode. Click OK to execute the Model Debugger.

The Model Debugger will then open a temporary version of your model in Microsoft Excel. The Debugger displays which values are pushed to the Excel Model and which will be returned to Hubble.
- Values Highlighted in Yellow – have been pushed to the Microsoft Excel Model from Hubble.
- Values Highlighted in Pink – will be returned to the Hubble Input Form.
See the Apply Model Rules section for more information on the priority of different data sources.
- Break After Next Iteration - If your model uses wildcards and multiple iterations are available from your input form to be passed through the model, the Model Debugger will ask if you wish to break after next iteration.

Once the Model Debugger is complete, the temporary Microsoft Excel file will close, and the contributor will review the results of the model as normal. If the Model Debugger was executed as part of a Cascade, a notification will then be displayed.

Model Cascade

Cascade enables you to run multiple models with a single button click. Cascade allows you to set the order as well as the mode each model should run in. This feature proves useful for pushing data into an Excel Model, or defining a Model to be dependent on the results of a previous Model.

Cascade: Setup

Before you can use Cascade functionality, you need to set up the model for each form you want to be part of the Cascade. Note that each Cascade will have one driver form; this is the form that you define your Cascade on and from which you will be running Cascade.

It is important that all models for the forms you want to be part of Cascade are on the same workbook as your driver form. Cascade will only allow you to pick forms and models that have been assigned to the same Excel Model Workbook.

If you unassign the model from the driver form, this will also result in removal of your Cascade definition.

Required Steps

Design a Form for each step and add as a Budget Form:
- Forms > Add > (Browse and Select) > Open.
Create a Model using a Microsoft Excel workbook with tabs representing each form and step.
Upload Model to Hubble.
Assign the Model to each Form:
- Forms > (select form) > Edit > click on the “….” of the Model row > Open.
Define the Model Cascade on the Driver Form.

Defining a Cascade

Once you have setup all your forms and have assigned models to them (all being in the same workbook), you can begin defining and using Cascade.

In order to define a Cascade, navigate to the following dialog: Budget Setup > Forms > (Select your driver form) > Edit. You will see the following dialog:

From this dialog, click on the “…” on the same row that says Model Cascade. This brings you to a new dialog which lets you define the Cascade structure:

Select which Forms are to be included in the Cascade, and their order. The items on the left are the forms available to be part of the Cascade. The forms that are listed are the ones that have models assigned to the same workbook that the driver form (the form you are defining Cascade) is on. The dialog supports multi-select, meaning you can pick multiple items and add/remove them accordingly. Once items are brought into Cascade, they can be multi-selected and moved up or down, making it easy to edit the order in which the forms should be run in.
Define the Model Mode for each step (see section below for an explanation of each mode)

Optionally, define a name and description for the Cascade definition:

Click OK to save the Cascade definition.

Cascade: Model Modes

Once you select the Forms to be included in the Cascade, you can configure the Model Mode for each Form. There are three possible Model Modes:

Full - This mode performs the Full normal Apply Model. Values from Hubble are pushed to Update corresponding intersections in Microsoft Excel, then other values are returned to Hubble after the worksheet is re-calculated. This is the only mode where values are returned to Hubble.
Update – This mode performs only the Update portion of Apply Model. Values from Hubble are only pushed to Update corresponding intersections in Microsoft Excel. No values are returned to Hubble.
Create - This mode acts the same way as Export to Excel but exports to the worksheet that is assigned as the model. The contents of the worksheet are completely overwritten with the results of the exported form, so any formulas you may have there will be cleared out. The content exported will be the results of the re-ran Form or, if the Form is currently open it will export the content as it appears. No values are returned to Hubble.
When defining a cascade:
- Descriptions for both Cascade itself and its individual Forms are not mandatory.
- All forms except the ones that are to run in Create mode must be enabled for Budget Entry where they must have at least one cycle in common. Cascade will try to intelligently pick a cycle;

if more than one common cycle is found (e.g. all the forms are under more than one common cycle) then Cascade will prompt you to pick a cycle before execution. If no cycle is found, a validation message will be displayed within the Progress dialog.

The following example calculates the employee payroll (Full Apply Model), then runs a form which summarizes payroll numbers over business unit and object account (Create) and finally pushes those numbers, via Excel Lookup formulas, into a GL Balances form.

Running Cascade

Once your Cascade is defined and ready to be used, the Cascade button will be enabled on the

Budgeting Menu when the Cascade’s driver form is open.

Note, the driver form does not need to be in Enter Budget Mode for Cascade to be able to run, however Cascade button is also available while in Enter Budget Mode, on the Enter Budget Menu.

Upon clicking the Cascade button, a progress dialog appears:

This dialog indicates the progress of each of the items separately as well as the overall progress of the Cascade. It also has a running timer to show the time duration taken so far.
The cascade can be canceled by pressing the x next to the status of the Cascade. Note that tasks which involve wildcarded models cannot be canceled midway through the process.
You can hover over a specific item within the cascade to see the name and the description:

Once the Cascade is complete, the Progress dialog will display results on how long each of the tasks took and the total duration taken to run Cascade.

Note: Cascade Create mode as well as Create Model functionality in the Budgeting Menu will both pad the exported label column values accordingly.

Cascade: Validation

When you click the Cascade button, behind the scenes the first step is to validate whether the Cascade can be executed. There could be many causes that could stop Cascade from being able to execute, some of which are more common:

One or more forms (not the driver form) no longer has models assigned to them. Remember that as soon as you unassign a model from the driver form, you also remove the Cascade definition.
One or more forms is no longer part of a common cycle.
The model (worksheet) for one of the forms no longer exists in the same workbook.
One or more forms is assigned a model that is not on the same workbook as the driver and other forms that are part of the cascade.
One or more forms have been removed.
It was not possible to identify any valid budgeting input cells on the form.
When any validation error(s) occur, the progress dialog will display a message on the exact cause.

The example below shows how an error is reported when validation fails for a specific item within the Cascade. In cases when an error is too big to be displayed to the user, an expander will appear allowing the user to expand it to see the full message.

When a common error occurs, such as Cascade could not work out a common cycle to use, the error will be reported in the following format: