Creating & Editing Formulas
Formulas connect the assumptions in your ROI Model or Calculator to its calculated results
They let you use values from Inputs and other Outputs to calculate measures such as savings, benefits, ROI, payback and operational improvements.
ROI Studio provides a Formula Editor for building and checking these calculations.
How formulas work
A formula combines:
References to values in your model
Numbers
Arithmetic operators
Supported functions
Logical comparisons
For example, a simple annual benefit calculation might represent:
Annual Volume × Saving per Transaction
Rather than manually entering the current values, the formula references the relevant model elements.
When those values change, the calculated result changes with them.
Create a Formula Output
To create a standard calculated Output:
Open Show Components.
Go to Outputs.
Select Std Formula Output.
Enter a Display Label.
Open the Formula Editor.
Insert the required references and operators.
Check the displayed Result.
Configure the result formatting.
Select Save.
A Std Formula Output may also appear as Standard Output when using the component type swap controls.
Use references
Formula references connect the calculation to existing elements in your model.
For example, imagine the model contains:
Annual Calls
and:
Cost per Call
A formula can reference both:
[Annual Calls] × [Cost per Call]
If Annual Calls or Cost per Call changes, the calculated Output updates accordingly.
Reference Inputs
Use Input references when the calculation depends on an assumption or value entered into the model.
Examples might include:
Annual Volume
Employee Count
Cost per Employee
Current Performance
Improvement Rate
Implementation Cost
These values become the starting points for your calculations.
Reference Outputs
Outputs can also be referenced by other Outputs.
This lets you build calculation chains.
For example:
Calls Contained
could be calculated from:
Annual Calls × Containment Rate
Then:
Annual Gross Saving
could reference Calls Contained rather than repeating the entire first calculation.
Finally:
Net Annual Benefit
could reference Annual Gross Saving and subtract annual technology costs.
This creates:
Inputs
↓
Operational calculations
↓
Financial calculations
↓
Final KPIs
Build calculations in stages
For more sophisticated business cases, consider using several meaningful Outputs rather than one very large formula.
For example:
Annual Calls
↓
Calls Contained
↓
Agent Hours Saved
↓
Labour Cost Saving
↓
Total Annual Benefit
↓
Net Annual Benefit
↓
ROI
This makes the model easier to understand and maintain.
It also gives individual calculations a clear business meaning.
Arithmetic operators
The Formula Editor supports standard arithmetic operations.
+
Add
-
Subtract
×
Multiply
÷
Divide
^
Power
%
Percent helper
( )
Group calculations
< > =
Comparisons
Use parentheses when you need to control the order in which calculations are performed.
Use numbers in formulas
You can include numeric values directly in a formula.
ROI Studio also provides quick constants including:
10012365
These can be useful for common conversions and annualisation.
For example, a monthly value could potentially be annualised using:
Monthly Value × 12
Use direct constants only when the value genuinely belongs in the calculation rather than representing a business assumption users should be able to change.
If a value is an important assumption, consider making it an Input instead.
Percentage calculations
The % control acts as a percent helper.
When working with percentage-based assumptions, make sure the formula treats the value in the intended way.
For example, if a model uses an improvement percentage, verify the resulting calculation using the live Result before saving.
Don't rely only on the fact that the Output looks like a percentage.
The underlying calculation must also be correct.
Logical functions
ROI Studio supports logical functions for calculations that need different results under different conditions.
These include:
IF
AND
OR
For example, IF can be used when one result should apply when a condition is true and another when it is false.
Logical functions can be useful for business rules, but keep them understandable.
If a formula becomes difficult to follow, consider separating the logic into additional Outputs.
Aggregation and calculation functions
The Formula Editor also supports functions including:
SUM
AVG
MIN
MAX
ABS
NPV
These provide additional ways to work with groups or transformations of values.
For example:
SUM
can calculate a total.
AVG
can calculate an average.
MIN and MAX
can identify or constrain values around lower or upper cases.
ABS
returns the magnitude of a value without its negative sign.
NPV
supports net-present-value-style calculations for discounted cash-flow modelling.
For the complete operator and function list, see Formula Reference.
Check the Result
The Formula Editor displays a live Result.
Use this before saving.
Don't check only whether the formula can be accepted.
Ask whether the result makes business sense.
For example, if you're calculating:
Users × Saving per User
test the calculation using values where you already know the expected result.
This is particularly useful when building more complicated formulas.
Worked example
Imagine you want to calculate annual savings based on the number of users and saving per user.
First create:
Numerical Input — Users
Then create:
Numerical Input — Saving per User
Next add:
Std Formula Output — Annual Saving
Build the formula:
[Users] × [Saving per User]
Check the Result using sample values.
If:
Users = 100
and:
Saving per User = £2,000
the expected result is:
£200,000
Once the result is correct, configure the Output as currency and save it.
Then Preview the model.
Formula validation
ROI Studio checks the formula before allowing an invalid calculation to be treated as complete.
If the formula isn't valid, you may see:
Please confirm the formula.
If this happens, review the Formula Editor.
Check for:
Missing references
Missing operators
Incomplete expressions
Empty gaps between operators or values
Incorrect grouping
Correct the formula and check the Result again.
Avoid empty operator gaps
Every part of the formula should form a valid expression.
For example, don't leave a calculation in an incomplete state such as:
[Annual Volume] ×
or:
[Annual Volume] + ÷ [Cost]
If the formula isn't complete, finish or correct the expression before saving.
Formula dependencies
When one Output references another, a dependency is created.
For example:
Net Annual Benefit
may depend on:
Total Annual Benefit
which may itself depend on:
Labour Saving
If you later change one of those upstream calculations, downstream results can also change.
This is expected behaviour.
Preview important results after changing formulas used elsewhere in the model.
Deleting referenced elements
ROI Studio protects formula dependencies.
If you attempt to delete an element that another calculation still references, deletion can be blocked.
You may see:
Cannot delete! This component is used in other components for calculations.
Before deleting the element:
Identify the formulas that reference it.
Change or remove those references.
Check the affected results.
Retry the deletion.
For more information, see Editing, Copying & Deleting Elements.
Formula complexity
A technically valid formula isn't necessarily a good model design.
For example, one very long formula containing many nested conditions may be difficult to understand later.
Where appropriate, break the calculation into meaningful intermediate Outputs.
Instead of:
One large formula → ROI
consider:
Volume Impact
↓
Time Saving
↓
Labour Saving
↓
Total Benefit
↓
Net Benefit
↓
ROI
This makes the logic easier to review and explain.
Formulas and Charts
Charts can also use calculated values.
The Formula Editor provides a Chart-specific token:
index
index represents the position of a data point:
1, 2, 3...
Use this when configuring Chart calculations that need the position of each data point.
For more information about Charts, see Charts.
Configure the result
After the calculation is correct, configure how the result should be displayed.
Standard Formula Outputs can present values using formats including:
Numerical
Percentage
Time
Currency
You can also configure supported presentation options such as:
Decimal places
Prefix
Suffix
Currency
Colours
Tooltip
Icon
Attached Resources
Formatting should communicate what the result represents without changing the underlying calculation.
For more information, see Styling, Icons & Number Formats.
Formulas and hidden Outputs
Calculated Outputs can also participate in conditional visibility.
Where supported, Calculate on Hidden controls whether an Output continues to calculate while it isn't visible.
This can matter when another Output depends on the hidden result.
For more information, see Conditional Visibility & Calculate on Hidden.
Test formulas with different assumptions
A formula that produces the expected result for one set of values can still behave incorrectly elsewhere.
Before publishing, test important calculations with different Input values.
Consider:
Typical values
Low values
High values
Zero where appropriate
Percentage extremes where appropriate
Check that the result continues to make sense.
Preview the complete calculation chain
After saving formula changes, Preview the Calculator.
Change relevant Inputs and confirm that:
The expected Outputs change
Downstream calculations update
Percentages behave correctly
Currency and units are displayed correctly
Conditions still work
Headline KPIs remain credible
Formula validation confirms that a formula can be processed.
It does not replace reviewing whether the business calculation itself is appropriate.
Best practices
When building formulas:
Use clearly labelled Inputs and Outputs.
Build complex calculations in logical stages.
Use references rather than repeating calculations unnecessarily.
Use parentheses where calculation order matters.
Check the live Result before saving.
Test with values where you know the expected answer.
Avoid leaving incomplete operator gaps.
Avoid unnecessarily nested logic.
Treat important assumptions as Inputs rather than burying them in formulas.
Review downstream Outputs after changing an upstream calculation.
Preview the complete model before publishing.
Last updated