Add a Reference tab

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.

1. Edit the name of the new tab, if required. 

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 also have other options, such as a Sales or Purchasing database.

3. Select the baseline data structure:

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

   2. 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, the offset 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 (and disable 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.

8. (Applicable when linking to a financial database) Select the template to determine the layout of the contents on the new tab:

   • The available templates depend on the types of Profit and Loss statements your organization has created in the Financial Statements module.

   • The template determines the layout of the data. 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 difference a template can make to how the data displays in the tab.

     Open

     

9. 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 as it is, or add up to four more levels.

   • If you are referencing a financial database and don’t select a template, you must add at least one level, up to a maximum of four levels.

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

   • 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, as required. You can also change the level at which the workflow sits.

10. (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.

11. Click Next.

(Optional) Define additional measures to drive the budget data. By default, one measure input row is included for you. You can either 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.