Reference tab

Add a Reference tab in a budget or forecast workbook to connect to data in another database.

User permissions: Budgets & Forecasts and Manage Budgets & Forecasts

The Reference tab allows you to view additional data right there in the workbook, helping you make more informed budgeting decisions. You can also refer to the reference data in formulas in other tabs to drive budget values. For example, if you are working on a financial budget, you could add a Reference tab that links to the Sales database to bring more detail, such as sales rep or product data.

The Database tab and Reference tab are very similar, as they both connect to another database to display additional data in the budget workbook. The two key differences are as follows:

• Automatic driving of budget values: The purpose of the Database tab is to automatically drive budget values in the Main tab. This is achieved through the mapping step in the Database tab setup, where you match the dimensions in the two databases. As the Reference tab setup doesn’t have a mapping step, no dimensions are mapped, so no values are automatically driven. However, you can manually drive values by referring to the reference data in formulas.

• Flexible hierarchy: As explained in the section above, the hierarchy of the Database tab is based on that of the Main tab. So, for example, if there are three levels in the Main tab, the Database tab will also have three levels. The Reference tab is independent of the Main tab, so you are free to determine the levels in the hierarchy to organize the data as required.

Do you need to add a new Reference tab? If there’s already one in the workbook, save time by cloning that tab and editing its setup.

Either click the Budget menu > Add tab or click the Add tab button (at the bottom of the workbook), then select the Reference tab type. Then proceed to set up the Reference tab. Many settings are automatically selected for you to save you time.

1. Configuration and hierarchy

The settings in the Configuration and hierarchy tab of the setup determine where the other data comes from and how it will be organized in the new worksheet.

Configuration

1. Enter a name for the tab.

2. Select the Database from which to source the data, along with the corresponding stream and value measure. The available databases and their stream and measure options depend on whatever is in your organization's Phocas site. The Budgets and Forecasts module is based on the Financial Statements module, so there will always be a financial database option. However, you might have other options, such as a Sales or Purchasing database.

3. Select the baseline data structure:

   • Stream: Fill the tab with values from the selected data stream as a starting point that you can adjust later. For example, you could use your current year's actuals or the previous year’s budget values.

   • Zeros: Fill the tab with zeros ($0.00) so you can start with a clean slate.

4. (Applicable when using stream values) Change the default offset if required. If you're budgeting by year, the offset defaults to -1 and if you’re budgeting by month, it defaults to -12.

5. Set the measure format to determine how the measures (numerical values) display in the grid.

6. Leave the Include ### suspense items checkbox selected if you want unclassified items to display in the Reference tab. If you do not want to include those items, clear the checkbox.

7. Leave the Allow users to edit values checkbox selected if you want other users to edit the data in the Reference tab. If you want to prevent users from editing the data (deactivate all workflows), clear the checkbox. You might want to do this at the end of the budget period to lock in the final budget values.

Hierarchy

1. (Applicable when linking to a financial database) Select the statement:

   • These are the Profit and Loss statements created in the Financial Statements module.

   • The statement determines the layout of the contents of the worksheet. It also applies the Reverse Sign setting (displays a negative number as a positive, or vice versa) from the corresponding financial statement.

   • For example, if you are bringing in data from last year’s Profit and Loss statement, you might want to use the same Profit and Loss template as you used in the budget setup so the data is laid out the same in both the Main and Reference tabs. The following images show the impact of a statement on how the data displays.

     
2. (Applicable when linking to a financial database) Show or hide nested calculations as required. This setting determines whether nested calculation rows are shown in the worksheet or not. These calculation rows come from the selected statement. By default, Show is selected, which means the calculation rows in the worksheet are expandable and you can see the nested derived rows underneath. If that's not desirable, select Hide to remove these rows and only see the top-level calculation values.

3. Add levels to determine how the data in the tab is organized.

   • If you are referencing a financial database and select a template, by default, the highest level (dimension) is the Profit and Loss Category, and the lowest level, where the workflow sits, is the account. You can leave the default hierarchy or add up to four more levels.

   • If referencing a financial database and not selecting a template, add at least one level, up to a maximum of four.

   • If referencing an operational database, add at least one level, up to a maximum of six.

   • To add a level, click Add level and select the dimension you want to add, then repeat to add more levels. The available dimensions depend on the source database. You can apply a level filter and drag the levels to reorder them. You can also change the level at which the workflow sits.

4. (Optional) Apply a tab filter: Select the Filter… checkbox > select the dimension that you want to filter (you can't select one that’s been added as a level) > select the entity you want to use as the filter > click out of the window to apply your selection. See Budget filter for more information, as the concept is the same.

5. Click Next.

2. Additional measures

(Optional) Define additional measures to drive the budget data. By default, one measure input row is included for you. You can leave it as it is and click Next to proceed or configure the measure and add additional measures.

Read the Include additional measures in the workbook page for more information.

Click Save and finish. The new tab opens with the data from the other database.

Review the new tab and, if required, edit the tab setup.