Contents

Adding a Data Link

Using an Existing Link Source

Creating a Record Set Driver

Record Type: Driver

Record Type: Revenue, Operating Expenses, Personnel, or Adjustment

Preview and Apply

Editing Data Links

Disabling A Record Set

Data Links enable you to connect budget line items to other line items in the same application or another application in Planning (Operating Expenses, Revenue, Personnel, Drivers, or Adjustments). You can establish basic connections to simply “pull in” a line item from another application or build formulas to make one line item a percentage or proportion of another line item.

Example: In the Personnel application, you can use a Data Link to make employee holiday bonuses a percentage of the year’s profit. In the Operating Expenses application,  you can use a Data Link to make the cost of office rent depend on office square footage (in other words, the total cost is office square footage x cost per square foot).

This document shows you, a Planning Maestro user, how to use Data Links in the Planning applications.

Please Note: Data Links work the same way in every application within the Planning module. This document demonstrates how to use Data Links within the Operating Expenses application.

Data Links QuickView

Example: An office’s rent is based on a set fee per square foot. The fee per square foot is $20. Office square footage x $20 per square foot = Total office rent

Office rent line item in Operating Expenses:

Data Link for this line item: Total office rent = Office square footage x $20 per square foot

Adding a Data Link

To add a Data Link to a line item in Operating Expenses, Revenue, Personnel, Drivers, or Adjustments, open the Planning module and select one of the applications. Select the application where you want to locate the “main” line item which uses the Data Link to pull in information from the same or another application.

Please Note: If you do not see any menus or icons on this page, make sure you are connected to a Plan.

 

Please Note: This document uses Data Links within the Operating Expenses application as an example. This Data Link will calculate an Operating Expense based on information from the Drivers application.

Step 3: On the application’s page, select a line item that should depend on information from other line items and click on Expand icon (  ) to open the Properties pane.

Example: In the screenshot below, the line item is the cost of office rent. Because office rent costs $20/square foot, the amount of this line item depends on the office’s square footage.

 

The Properties pane opens. Each application has different sections and fields in the Properties pane. Each application has at least one Use Data Link field in one of the sections.

Please Note: If necessary, you can use a Data Link to connect between sections in the Properties pane within the same application. For example, in the Revenue application, you can make the Cost of a line item a percentage of the item’s Price.

Step 4: To use a Data Link for this line item, in the relevant section in the Properties pane, click the Use Data Link toggle switch to switch it to the ON position.

Please Note: Make sure you use a Data Link in the correct section of the Properties pane. For example, in the Revenues application, do not add a Data Link in the Cost section if you are trying to create a Data Link for a line item’s price.

Step 5: The toggle switch turns green and switches to the ON position, and a new Data Link field replaces several other fields (different in each application). Select Click to Edit.

A Define Data Link popup appears. The columns of this popup show the following information:

• # – Displays the number of items in this list. This column helps you keep track of how many line items there are in a Data Link formula.
• Operation – Displays any mathematical signs involved in a formula, such as a plus sign for addition (see below).
• Link Source/Value – Displays the name of the line item you’re “pulling in” to this application (that is, calculating with), or the Driver you created (see below).
• Measure – Displays Activity to show that this Data Link uses the activity (the period value) of the current period or Balance to show that this Data Link uses the Balance (running total from the beginning of the year) for the current period.

  Please Note: Not all transaction types have Balances; for example, input Drivers that measure percentages or Revenue line items’ prices. If you cannot change a Link Source’s Measure to Balance, that Link Source may be one of the transaction types for which Balance does not apply.

• Shift By (default 0) – To use data from a different period than the current one, enter a number in this field. This function is useful if you are building a budget and want to base this period’s budgetary figures on a previous period from the budget.

Example: If you are budgeting for Sales commission expenses for this March, you can shift back one month to base the amount on February’s sales (in the same Version).

• To use data from a period in the past, enter a negative number.

Example: If you are currently in Period 1, enter -3 to use data from Period 10 of last year.

• To use data from a period in the future, enter a positive number.

Example: If you are currently in Period 4, enter 2 to use data from Period 6 of this year.

• Tiered Rate – To use a Tiered Rate (a rate that changes based on amount or volume) click on this dropdown menu and select a rate. Planning Maestro applies this rate to the amount you entered.

Example: Your company pays $10 per user per month for a software license up to the first 100 users. After that, there is a group discount of $8 per user per month up to 200 users. If you set up this Tiered Rate using the Tiered Rate application within the Financial Intelligence Setup module, budget for 120 users, and then add the Tiered Rate in this section, Planning Maestro automatically budgets based on the Tiered Rate.

Step 6: To select a link source (a source of the information you’re pulling in to create this Data Link) click on the Ellipsis icon (  ).

Step 7: A Link Source popup appears (see below). On this popup, you can:

• Create a Data Link based on an existing link source (one of the Drivers, or non-financial factors or variables, from the Drivers application).

Example: In this example of office rent, which is based on the price per square foot, the link source would be office square footage.

• Create a new Data Link based on a Record Set Driver (in other words, base this line item on information from a group of line items in any other application).

Using an Existing Link Source

Step 1: To use an existing link source (pull in information from the Drivers application), select one of the items in the table and click Apply.

Example: In the screenshot below, you can select a Driver (non-financial factor or variable) named Office square footage that records the square footage of the office. When you edit the office’s square footage, all calculations based on this Driver change automatically.

Step 2: The line item you selected appears in the Define Data Link popup as a row in the table and in the Data Link: section at the bottom.

• If necessary, you can click Apply to simply pull this line item into this application as it is.

Example: If needed, you can use a Data Link to “pull in” a Personnel line item for 401K contributions or health insurance into the Operating Expenses application.

• You can also create a formula so that this Data Link is a dynamic calculation based on several factors.

Example: You can multiply, divide, add to, or subtract from this link source and other link sources and values to create a line item that is a percentage or proportion of other line items.

Creating a Data Link Formula

Step 1: To build a formula using this link source, use the buttons at the top.

• Add Link Source Row – Add another link source to this formula.
• Add Value Row – Add a constant value (a number) to this formula.
• ( [left parenthesis] – Use a left parenthesis to start to build a separate part of a formula.
• ) [right parenthesis] – Use a right parenthesis to finish a separate part of a formula.
• + – Use a plus sign for addition.
• - [hyphen] – Use a hyphen for subtraction.
• * [asterisk] – Use an asterisk for multiplication.
• / [forward slash] – Use a slash for division.

Please Note: A link source formula follows the normal order of operations.

Step 2: Build your formula using other link sources, values, and the mathematical symbols at the top.

Example: In the screenshot below, office rent is $20 per square foot. You can build this formula by inserting the link source (Driver) Office square footage and setting it to Activity, clicking the Multiplication icon ( ), and adding a value row of 20 to create the formula: [Office square footage]*20.

• The Data Link section at the bottom records your formula as you create it.

• To move a link source, value, or mathematical sign to the left in the equation, select the Move up icon (  ).

• To move a link source, value, or sign to the right in the equation, select the Move down
  icon (  ).

• To remove an item in the formula (link source, value, or mathematical sign) select the Delete icon (  ) in that item’s row.

• To confirm this deletion, click Confirm.

• To make this Data Link calculate based on a line item’s Balance (year-to-date total as measured each period) instead of its Activity (transactional amount per period), click on the dropdown menu in the Measure column and select Balance.

Example: Your company is growing and hires at least one more employee per month. You want to budget based on the total amount of employees each month, not just the loss or gain to employee headcount. When you build a Data Link, make sure Employee headcount is set to Balance instead of Activity.

Step 5: After making sure your formula for this Data Link is correct, click Apply.

• The Data Link determines the amount for this line item per period, quarter, or year (however you set up your calendar view).

Please Note: Because this line item’s amount depends on the Data Link, not on a constant amount, the Amount column in the table displays 0.00, but the period/quarter/year amounts on the right side of the table are accurate and change if items within the Data Link change.

• Any link sources that are part of this Data Link appear in the Properties pane under the Data Link

Example: In this example, Office square footage displays under the Data Link field in the Properties pane.

 

Please Note: In this example, the Office Square Footage amount in the Data Link is an Input Driver that spreads 10,000 square feet evenly across the year. The cost of rent, $200,000, is also spread evenly. This company could manipulate this line item’s Recognition Schedule or Payment Schedule or set up a Prepaid Expense to further adjust how they recognize and pay for this expense.

Creating a New Record Set Driver

You can create a Data Link based on a Record Set Driver, or a line item based on the relationship of a group of line items.

Example: A company is considering closing one of its offices and opening a new office in a better location. The CEO asks the CFO to run some calculations to see the total amount the company spends on rent for all offices, the average cost of rent, and the highest and lowest rents per square footage. The CFO can use Record Set Drivers to answer all these questions.

Step 1: Repeat steps 1-3 under Adding a Data Link to open the Link Source popup.

Step 2: On the Link Source popup, click + New Record Set Driver.

Step 3: A Define Record Set Driver popup appears. Enter the following information in the fields provided.

• Name – Enter a clear name you and other users can recognize easily.
• Current Data Link Only – If you want to make sure this Record Set Driver is unique and cannot be used for any other line items, click on this toggle switch to switch it to the ON position.

 

• A warning message appears. Click Confirm.

• To enable yourself or other users to re-use this Record Set Driver, leave the Current Data Link Only toggle switch in the OFF position (or click Cancel in the warning message).
• Record Type – To choose which application to select records from, click on this dropdown menu and select one of the following types:

 

• Driver – Select this type to make this Record Set a non-financial factor or variable you can use in multiple ways.

Example: Select Driver to choose a group of Drivers, such as the square footage of each office of your company.

• Revenue – Select this type to make this Record Set a simple line item based on a group of Revenue line items.
• Operating Expenses – Select this type to make this Record Set a simple line item based on a group of Operating Expenses.
• Personnel – Select this type to make this Record Set a simple line item based on a group of Personnel line items.
• Adjustments – Select this type to make this Record Set a simple line item based on a group of Adjustments.

The other fields on the popup change based on the Record Type you selected. The following sections describe what fields appear based on whether you chose Driver or one of the other options as the Record Type.

Record Type: Driver

If you selected Driver as the type of Record, enter the following information in the other fields:

Example: You could find the total square footage for all offices combined by creating a Record Set Driver that calculates the total of a group of Drivers measuring each office’s square footage.

• Driver Type – Click on this dropdown menu and select one of the following types:

 

• Input – Select this type if this Driver is a simple input (one number or value).
• Record set – Select this type if this Driver is a Record Set (a group of line items).

• Data Type – Click on this dropdown menu and select one of the following types:

 

• Monetary – Select this type is this Driver is an amount of money.

Example: $20 per square foot (for rent).

• Unit – Select this type if this Driver is a simple unit.

Example: Headcount, number of stores, square footage.

• Percent – Select this type if this Driver is a percentage of something.

Example: Each department pays office rent based on the percentage of total office space their area occupies.

• Transaction – The Transaction is the specific line item from the Details pane of a line item (the amount debited or credited to each GL Account relating to Income Statement, Balance Sheet, etc.). This field copies from the Data Type field by default. If necessary, you can click on this field and select a more specific transaction from the dropdown menu.

• Set Operation – Click on this dropdown menu and select one of the following options:

 

• Total – Select this option to make this driver the sum of all the records included.
• Average – Select this option to make this driver the average of all the records included.
• Minimum – Select this option to make this driver the minimum record (record with the lowest value) of all the records included.
• Maximum – Select this option to make this driver the maximum record (record with the highest value) of all the records included.

To select which specific records to include in this Record Set Driver, click Next (see the Selecting Records section below).

Record Type: Revenue, Operating Expense, Personnel, or Adjustment

If you selected Revenue, Operating Expense, Personnel, or Adjustment as the type of Record, enter the following information in the other fields:

Example: In this example, you can find the total amount of rent for all offices by finding the total of all Office Rent line items in the Operating Expenses application.

• Transaction – If necessary, click on this dropdown menu to select a GL Account from the Details pane for each line item (Income Statement, Balance Sheet, etc.).

Example: You can choose to use the amount debited or credited from a specific revenue, expense, asset, liability, or equity account.

• Set Operation – Click on this dropdown menu and select one of the following options:

 

• Total – Select this option to make this driver the sum of all the records included.
• Average – Select this option to make this driver the average of all the records included.
• Minimum – Select this option to make this driver the minimum record (record with the lowest value) of all the records included.
• Maximum – Select this option to make this driver the maximum record (record with the highest value) of all the records included.

To select which records to include in this Record Set Driver, click Next (see the Select Records section below).

Select Records

After you select the Driver’s properties, a Record Selection tab opens on the popup. Use the fields below to choose which records to include in this Record Set.

• Select based on – Click on one of these radio buttons:

 

• Filters – Select this radio button to look for all records that match certain criteria (such as Version and GL Account). All future records or line items you add are automatically included in this calculation.

Example: You can set filters for a certain Version and an Office Rent GL Account. All offices you add to your company after this are automatically included.

• Record Selections – Select this radio button to look for only specific records. You can include or exclude any records you want. Records you add later are not automatically included in this Record Set, but you can add them manually.

Example: You can select three out of four offices to include in this Record Set. If your company expands and adds more offices, you need to add them to this Record Set manually before they are included in the calculation.

• Version – Select the Versions from which you want to pull line items. To select all Versions, select Version (Master) at the top.

• Account Number – Select the Account Numbers from which you want to pull line items. To select all Account Numbers, select MEATGROUPS -- (Master) at the top.

Please Note: MEATGROUPS stands for Maestro Equivalent Account Type groups (that is, account type – Asset, Expense, Liability, etc.). MEATGROUPs is the top “node” or level of the account group hierarchy.

The rest of the fields on this page display the Dimensions and Dimension Members in this Plan. In each section, select the Dimension Members you want to “pull” line items from to create this record.

Example: In this example, if you group offices by region, you could select a Region from your Region Dimension and use office rents only in that region.

 

Please Note: You can also leave these fields blank to pull in all line items from all Dimension Members that fit the Version and Account Number(s) you selected.

A gray checkmark (  ) appears beside every Dimension Member you select.

After selecting the necessary Dimension Members, click Add to Set.

Preview and Apply

After you select records and click Next, a Preview and Apply tab opens on the popup. Your options in this tab depend on whether you chose to build this Record Set based on Filters or Select Records in the Select Records tab (see the previous section).

Filters: Applying the Record Set

If you chose to create a Record Set based on Filters (see the previous section), a preview of the line items included in this Record Set appears. Click Apply.

Selecting Records: Removing Selected Records

If you are setting up this Record Set through Select Records (specific records, instead of Filters – see the previous section), you can see a preview of the Record Set you chose.

Step 1: To remove records you do not want to include in this Record Set, select the records you do not want and click Remove Selected.

Step 2: To apply this Record Set, click Apply.

The Define Data Link popup reopens and displays this Record Set Driver. If necessary, you can use this Record Set Driver to build a formula (see Creating a Data Link formula above).

Example: You could use a Data Link formula to add Total Office Rent to Total Office Electricity, Total Office Supplies, or to create other calculations.

To add this Record Set Driver (with or without a formula) to this application, click Apply.

The Record Set Driver appears in the application. Any time you change one of the records included in this record set, this Record Set Driver changes in response.

Example: The Total Office Rent line item is a Record Set Driver that calculates the total of all office rents. If you increase or decrease one of the office rents, this Record Set increases or decreases.

• If you set up the Record Set with a Filter, every time your company adds an office, that office is automatically included in this calculation.
• If you set up a Record Set by Selecting Records, you need to manually add any new offices to make sure they are included in this calculation.

Please Note: Because this line item’s amount depends on the Data Link, not on a constant amount, the Amount column in the table displays 0.00, but the period/quarter/year amounts on the right side of the table are accurate and change if items within the Data Link change.

Editing Data Links

Occasionally, you may need to edit a Data Link to add or remove link sources or change formulas.

Step 1: To edit a Data Link, on the application’s page, select the line item with the Data Link and click the Expand icon (  ) to open the Properties pane.

Step 2: In the Properties pane, select Click to Edit in the Data Link field.

Please Note: If you are in a application with multiple Use Data Link fields (such as the Revenues application) make sure you are editing the correct Data Link.

Step 2: The Define Data Link popup appears. Make the necessary edits.

Please Note: See the Create a Data Link Formula section above for a reminder of how to build or edit formulas.

Step 3: To save your changes, click Apply.

The line item connected to this Data Link is updated.

Disabling a Record Set

You can disable a Record Set so that the line item connected to the Record Set no longer pulls in information from other line items.

Example: If you decide to budget based on a single amount instead of a formula, you can disable the Record Set and enter an individual amount for the line item.

Step 1: To remove a Data Link, on the application’s page, in the Properties pane, click on the Use Data Link toggle switch to switch it to the OFF position.

Step 2: Click Confirm.

• The toggle switch turns gray and switches to the OFF position.
• The line item is no longer connected to a Data Link.
• New fields appear below that enable you to set up this line item individually. These fields differ based on what application or section of the Properties pane you are using.

Example: In the Expense Input section on the Operating Expenses page, new fields for Amount and Spread Method appear.