Create Calculated Variables from Financial Data in the MDFM

Select a section of the article below to navigate directly to it. 

1. Create a Calculated Variable
   • Create the Variable
   • Build the Formula
   • Format and Import the Variable
2. Troubleshooting
3. Frequently Asked Questions

Create a Calculated Variable Walkthrough

Select Next to move through the guide. You can also zoom in and out of the image by selecting the Magnifying Glass in the bottom right-hand corner. You can view the full guide by selecting the External Link icon. 

Step-by-Step Instructions

Create the Variable

1. Click into a text block and click where you want the variable to be inserted.
2. Click Insert Variable and select "MDFM Expression"

Build the Formula

These formulas are written in SQL. To build a formula, you start by defining arguments (placeholders like $a1, $a2, etc.). Once these are set, you can apply mathematical operations to them to calculate your final variable.

Note: The only two values that are required to be defined are the Cube and Metric. 

1. Click (*) to begin defining the $a1 argument.
2. Select the (*) next to Cube and select the statement group to reference.
   • Note: Cube stands for financial model.
3. Click (*) for the Metric to select the specific financial data to reference and select the value from the trial balance to use.

   • Note: Generally, for an ACFR, the metric is the ending balance.

4. Add values to the dimensions to filter the argument further. 
   • Note: The dimensions available are based on the model selected.

5. Click APPLY when ready to commit the first argument.

   • Note: You will see the figure for the first argument in the preview after clicking Apply. Click on the argument to make any adjustments.

6. To create the formula, add a mathematical operation and the next argument. Add as many arguments as needed to complete the calculation.

   • Note: In SQL each variable must have the same syntax. An example is: $a1 - $a2 or $a1 + $a2 where $a2 is the start of your next variable. Any mathematical operation is possible, not just addition or subtraction.

7. Select APPLY when finished.

Note: You'll see a preview of the calculation after applying a second argument.

Format and Import the Variable

• Format the variable as needed with a prefix, number format, scale and/or suffix by selecting a value from the corresponding drop-downs.
  • The Prefix adds contextual wording before the value and includes dynamic options that change with the value's sign, such as “increases / decreases.” It adapts automatically if the underlying value changes sign in a future reporting period. Example: “increased by $19,826,154”
  • Number Format controls how the number itself is displayed. Choose Full Dollars for a currency-formatted figure, or None for a plain number. Example: $19,826,154 vs. 19,826,154
  • Scale rescales large values for readability in narrative text (e.g., Thousands, Millions, Billions). Example: $19.8 million instead of $19,826,154
  • Suffix adds wording after the value, such as a unit label or descriptive phrase. Example: “ $19,845,345 million increase”
• Click "Import" when ready to insert the variable.

The Calculated variable will be inserted where you initially clicked within the text in a purple box with purple lettering. Double click on the variable if you need to edit it. You can also copy and paste the variable to other text blocks within your report by selecting it and using Ctrl + C and Ctrl + V to copy and paste.

Troubleshooting

Issue: Cannot divide by 0. 

Solution: Make an alternative formula to handle the equation like “If(X, True or False) to get the right value.

Frequently Asked Questions (FAQs)

Q: What do each of the values refer to in the argument? 

A: The Cube is the financial model for the statement and the Metric is the value from your trial balance you wish to use. Generally, for an ACFR, it is the ending balance. The rest of the values are based on the dimensions set up within the model to build the table. You can see this set up within the MDFM under Admin > Models. 

Q: Do I need to select a specific value within function/programs or reporting funds? 

A: The only two values that are required as part of the model are the Cube and the Metric. The rest of the values allow you to filter based on the relevant dimensions built in your system.