Disclosure Studio - Calculated Variables

In addition to using Page Variables, Report Variables, and Linked Numbers to help automate your ACFR or other report, Calculated Variables (also known as MDFM Expressions) can automate your Management’s Discussion and Analysis (MD&A). 

There are several steps to creating and adding Calculated Variables in Disclosure Studio. Select the step below to jump directly to that step in the process.

1. Navigate to Your Report in Disclosure Studio
2. Use the Import Number Menu
   • Select the Statement Structure
   • Select the Functions/Programs and Reporting Funds
   • Select the Periods
   • Select the Metric
3. Select the Mathematical Operation and Create your Next Argument
4. Format the Number and Import it

Navigate to Your Report in Disclosure Studio

Navigate to your preferred report within the Disclosure Studio, and find the page on which you want to add a Calculated Variable. Select the area of text where you want the variable to appear, then follow these steps.

1. Select the Insert Variable button within the text editor menu.
2. Select the MDFM Expression option.

A new screen will display called the Import Number menu where you will create a mathematical formula to represent the financial figures to include in the calculated variable.

Use the Import Number Menu

These formulas are written in SQL which means you are now learning a programming language!
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.

To begin defining the argument:

1. Select the (*) symbol to the right of the word Cube.
   Note: The term cube stands for a financial model.
2. Select the statement group (e.g. Statement of Activities, Statement of Net Position, Cash Flow, etc.) you want to represent.

Next, you'll choose all of the parameters to be included in the argument: the Statement Structure, the Functions/Programs, the Reporting Funds, the Periods, and the Metric. We'll walk through each of these below.

Note: Some parameters, like Functions/Programs will only display if a certain statement type is selected in the Statement Structure.

When done, your first argument will look similar to this:

Select the Statement Structure

The Statement Structure is the line from the statement you wish to automate.

1. Select the (*) symbol to select the Statement Structure.
2. Select the chevrons or utilize the search bar to find the statement line you want.
3. Then, select the statement line or statement line group you wish to utilize.

Now it's time to select the Functions/Programs and Reporting Funds. 

Pro-Tip: To de-select your choice and pick a new one, select the X symbol to the right of the search bar.

Select the Functions/Programs and Reporting Funds

Select the Function/Program and Reporting Fund based on your specific reporting needs. For instance, if you want to report program revenues for the general fund, you will need to select General Fund, and Governmental Activities. Whereas, if you wanted to report all revenues regardless of activity and fund, you can leave the asterisk. The asterisk means it will grab all funds or functions. This is useful for primary governmental totals.

Select the (*) symbol to the right of Functions/Programs and Reporting Funds to hone in on the data you need.

Note: Functions/Programs will only display if a certain statement type is selected in the Statement Structure. 

Select the Periods

Select the period in which the financial figure exists.

1. Select the (*) symbol to the right of Periods.
2. Then, select the period you wish to utilize.

Select the Metric

The metric is the value from your trial balance you wish to use. Generally, for an ACFR, it is the ending balance.

1. Select the (*) symbol to the right of Metric.
2. Then, select the metric (typically Rounded Ending Balance).
3. Once you're done defining your first argument, select the APPLY button to lock in your selections.

Select the Mathematical Operation and Create your Next Argument

Fund balances are calculated inside of Disclosure Studio by applying mathematical operations to your formulas. Select the formula bar and insert the mathematical operation you want to apply, followed by the next variable (remember, 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. 

Pro-Tip: Our example used the mathematical operations of plus and minus, but any mathematical operation is possible. 

After entering in your next argument, select $a2 and select the (*) symbol. Follow the same steps as above to select the Statement Structure, the Functions/Programs, the Reporting Funds, the Periods, and the Metric for your second argument. Select the APPLY button to lock in your selections.

Format the Number and Import it

After defining the arguments and selecting the mathematical operations, you will be able to format how the number will appear in the report.

1. Prefix allows you to automate a phrase regarding that financial figure. For instance, an increase or decrease of net position. The prefixes will automatically change in relation to prior year data.
2. Number Format sets the presentation of the amount, so whether it is a dollar amount, a percentage, or absolute value. 
3. Scale automatically changes the decimal places of the value selected. If your figure is $4,000,000, and you set the scale to millions, it will be automated as “$4 Million”. 
   • Note: Auto will select the largest scale possible for your figure. For instance, $4,000 will become $4 thousand; $4,000,000 will become $4 million, and so on. 
4. Suffix enables you to automate the phrase attached to the figure. For instance, a $4 million increase or decrease. Similar to the prefixes, the wording will change automatically based on prior year data.
5. The preview shows you what the calculated variable will look like in your report.
6. Once complete, select the Import button to insert this calculated variable into the report.

View a Step-By-Step Walkthrough Below