Get to Know the Flat File: Variables, Initial Testing, and Advanced Layout

1. Template

Once you have built your model in Excel/sheets (if any of these terms seem unfamiliar, read the introduction guide for a better understanding), you must configure it to upload into our system. To create this file for upload into VisualizeROI’s engine, we have provided a default template for how to lay everything out, with error checking. For every variable in your model, you will have to fill out a row in the correct tab of the template to upload your model into our system. Check out the link as you’re reading: VisualizeROI Spreadsheet Model.

The template has 3 tabs: “VROI_INPUTS”, “VROI_OUTPUTS”, and “VROI_ENUMS” which are the ones you have to fill out. They are currently filled out for our reference model, which is displayed in the “Lemonade Stand Calculator”, and as a complement, to this guide, we have included an “Instructions” tab that will guide you through the process.
Do NOT change the name of the tabs or column headers - it will cause an error when importing into our system.
The tabs are fairly intuitive - there is one for each type of variable in your model (variables explained here). For each variable in your model, you will need to enter it in a separate row in the corresponding sheet and fill out each column with your preferred settings

This guide will now go in-depth on all the configuration options available. For now, it is highly recommended that you only use the guide below to fill out the required sections, then check out this article to upload the first version for testing, and then come back here to configure the advanced settings before re-uploading. This ensures that you don’t waste your time setting advanced options, only to have to start from scratch because your model had errors (explained further in the article)

2. Inputs Tab

The Inputs tab has 14 columns, with the majority of them being required. We’ll go through them one by one now, but all of these can be changed later on in the system:

Variable: [Required] The desired name of your input variable. In the UI, you can label a variable whatever you would like to, but in our system, the variable can be located by this name.

Value: [Required] The default value for your input variable. Input variables are dynamic, meaning they can be changed in the visualizer later on, but will initially have this value.

Tab Tag, Section Tag, Category Tag: [Required] The labels for different levels of navigation (Explained in detail in Section 5 of this article)

Tooltip: [Optional] Variables can come with helpful comments, called tooltips, usually containing explanations or sources of data.

Variable Type: [Required] Set to the number or enumerated variable depending on the type of variable.

Format: [Optional] Variables can be represented as amounts, percentages, currencies, and duration (years, months, quarters, weeks, days, hours, minutes, seconds) depending on your desired format type and will be displayed in the UI as such (“amounts” are unchanged). For example, percentages will be displayed with a percent sign (i.e. 2.5% instead of 2.5) and currencies will be displayed with the chosen currency’s symbol (default USD i.e. $3.42).

Decimal: [Optional] Variables that are numbers can be displayed with a chosen number of decimal places to represent the value however you would like (ex: 1.5% is 1 decimal place, if you chose 2 it would show 1.50% and if you chose 0 it would round to 2%).

Min/Max: [Required] Since input variables are dynamic fields, these columns allow you to set parameters on what variables can be inputted. For instance, if your model only works credibly if a certain variable, say Average Contract Value, is positive but less than $100,000, you can set the min to 0 and max to 100,000. Any values entered outside of those bounds will not be accepted by the visualizer. Otherwise, you can set most variables from 0 to a very large number.

Slider: [Required] If set to 0, input variables will be displayed as dynamic fields with default values in the UI. Here is an example of what that looks like:

If set to 1, input variables will be displayed as dynamic sliders with the min and max values, allowing the user to slide left or right to choose a value in the UI.

Frequency: [Optional] Variables can be displayed in the UI with frequencies as well, meaning the variables will show up as “$403,000 / year” instead of just “$403,000”, for example. You can choose between entering “Second”, “Minute”, “Hour”, “Day”, “Week”, “Month”, “Quarter”, and “Year”.

Code Reference: [Optional] Used when re-uploading models to prevent duplicates - don’t worry about this, for now, it’s explained in further detail in this article.

Action: [Optional] Used when re-uploading models to control variables

3. Outputs Tab

The Outputs tab has 10 columns: Variable, Value, Tab Tag, Section Tag, Category Tag, Tooltip, Variable Type, Format, Decimal, and Frequency. Each of these has already been discussed in Section 2 (Inputs) of this guide, but “Value” is handled differently for outputs.

Instead of entering a default value, you should enter an equation to calculate it that only refers to previous outputs or inputs in this sheet. This is because outputs are only calculated, the user cannot enter values for them but can change the inputs that are used in calculations.

4. Enumerated Variables (Picklists and Drop Downs)

Setting up enumerated variables is a little more difficult:

In the VROI_ENUMS sheet enter the desired variable name and optionally, write a description for this enumerated variable.
For the “Elements” column, use this formatting to enter each option where there is blue text for your picklist - if you need more options, extend the patterned formatting:

“1st Option,0##2nd Option,1##3rd Option,2##4th Option,3”

If you choose, you can replace the first option with the words “Please Select” or “None”. Correctly filled, it should look like this (example in the reference model sheet):

“Please Select,0##Conservative Estimates,1##Liberal Estimates,2”

After you fill out the “Elements” column, enter the corresponding number for the default option you want to be displayed in the “Values” column. Notice how every option has a number immediately after it - this is the number you want to enter for the option. For example, to default “Please Select” in the above example, enter in 0. The options will appear in a picklist in the UI.
The last step is to link the value of the Enumerated Variable to the Inputs Tab since the user can input a selected option that can change the outputs. To do so, in the VROI_INPUTS sheet, create an input variable using the above guide - this will be the variable you reference in your equations (the input variable, not the Enum variable) if you choose to change the math based on the selected Enum. For the “Variable Type” and “Format” columns fill out “Enum”. For the “Value” column, set it equal to the “Value” in the “VROI_ENUMS” tab for the corresponding enum.
You’re done! Just remember the input variable for the Enum will store the selected option out of the picklist, the actual Enum variable just informs our system. So even with Enums, your outputs must only refer to inputs, or other outputs, NOT anything in the Enums tab.

Creating Logic with Enums

This will typically come in the form of "IF" formulas in your VROI_OUTPUTS sheet. You will want to include logic that addresses each possible value the Enum can be. However, you must reference the cell within the VROI_INPUTS sheet (not VROI_ENUMS).

5. Beta Model Layout for Quick and Easy Testing

Before implementing your model, it is recommended to ensure all math, references, and variables are set up to import correctly, otherwise, the upload will result in an error. To do this, our engine can quickly create a rough User Interface for you to test to make sure the model works functionally and if all needed variables exist, etc. before you build out your full User Interface (design/layout-wise).

To enable this, you must first fill out all the required columns in the sections before configuring your variables. Advanced layout configuration for design is explained below in this guide, but for now, each input’s “Tab Tag” should simply be “Inputs” and “Section Tag” should be “Input Testing”, and each output’s “Tab Tag” should simply be “Outputs’ and “Section Tag” should be “Output Testing”.

After the labeling is done, follow this guide to upload this template into your Visualizer. After doing this, you should see a simple two-panel User interface, with your inputs and outputs, so that you can test all the features of your model. Make sure to play around with all the features in your model to ensure functionality.

6. Advanced Layout Introduction

Before reading this section, it is highly recommended to read this guide to test your model’s functionality, before designing a more complete User Interface.

Once you have tested your model’s functionality, you can now configure the design aspects of it using our template.

Here is an example of a User Interface (UI) to visualize a model.

As you can see, there are 3 levels of hierarchical categorization, for easy navigation. On the top row, there are “Tab Tags”. These are the highest level, followed by the labels on the left, which are “Section Tags”. Under these tags, as a subsection, are “Category Tags”, the lowest level of categorization.

Before you can configure your model to upload, you will need to decide at a high level how you want your UI to be structured. This is because each variable has an initial Tab, Section, and Category. The “Total Annual Benefit” variable in the picture above, for example, would have Tab: “Output”, Section: “ROI Summary”, and Category: “ROI”. The system needs to know where to place the variables in the UI.

Notes:

Don’t worry too much about placement - all of this can be changed later via re-uploading the model, as explained in this guide.
Variables aren’t solely confined to their placed section, you can place them again in different Tabs or Sections of the UI, but they will always at least appear in the section they’re configured for.
If you have variables that you wouldn’t like to show in the UI, fill out “Not in UI” for the Tab, Section, and Category Tags.
Category tags are entirely optional and can be left blank if you don’t want that level of navigation available in your UI. You can make this as simple or complex as you would like - the decision is up to you!

After you’re done configuring your layout, follow this guide to upload your model into our system and see your Visualizer.

7. Additional Concepts

Code Reference

This is what the platform looks for when determining what value a variable should be. Every one of these must be unique. If left blank, the platform will automatically generate a unique identifier. This can lead to duplicate variables (with only the variables in the most recent upload being used) so it's important to make sure these are filled in with the existing code references or create your unique code references.