Page Comparison

The Lookup tab is designed to be used with the Lookup measure row type in driver-based budgeting but can also be used stand-alone in a similar way to the Manual Entry tab. You can think of Lookup tabs as a structured Manual Entry tab, with a prebuilt dimension structure and entities inherited from another tab.

Lookup tabs allow you to build budget models that pull out various inputs or assumptions, and then model different scenarios by changing those assumptions. For example, you might have a lookup for sales growth for each branch, or for pricing and margin for each product or group of products. You can build a budget model on base-case assumptions for these, then adjust the assumptions to see the impacts. These assumptions are in a separate tab and not embedded in formulas throughout the budget, making it simple to change assumptions at the budget level.

Add a Lookup tab

1. Click the Add button at the bottom of the worksheet.

   

2. Enter a name for the tab, select the Lookup tab type, then click Add.

3. In the Lookup setup window, configure the tab:

   • Related tab - Select the tab that contains the data for which you want to add the lookup information. Typically, the related tab is the Main tab in the budget but you might want to use another tab, such as a Database driver tab.

   • Hierarchy selection (levels) - Select the levels to determine how the Lookup worksheet to be organized. The available options (dimensions and entities) depend on whatever is in the related tab. The hierarchy in the Lookup worksheet can be the same, or at a higher or lower level of detail, as the related tab.

   • Exclude periods - Select whether you want to use period columns or not.

     • By default, the Exclude periods checkbox is NOT selected, which means that period columns (such as months) are included in the Lookup worksheet and therefore, each lookup item displays as a row. This option allows you to enter different lookup information for item in each period in the budget. This option is suitable, for example, if you have different prices across the year due to seasonality.

     • If you do not need period columns in the Lookup worksheet, select the Exclude periods checkbox. One ‘Total’ column for each lookup item displays in the worksheet, rather than one for each period. This option allows you to enter one lookup value for each budget row. This option is suitable, for example, if you have a price than remains constant throughout the year.

4. Add the rows that contain the look up items:

   1. Enter a name and placeholder amount (default amount), and select the aggregation method for the first row.

   2. (Optional) Click Add row and repeat the above step to add other rows as required.

5. Click Create Lookup tab. The new tab displays with the default amount in each row, which you can then edit as required.

6. If you want to use this information in the related tab for driver-based budgeting, proceed to edit the setup of the related tab.

   

Use a Lookup tab separately from driver-based budgeting

You can add a Lookup tab (see steps above) and use it in formulas in other tabs in your workbook to quickly calculate your budget values. You can then edit the data in the Lookup tab as required and your changes will flow through to those formulas. The following video demonstrates how to do this, along with using the Copy Forward and Copy Down features to quickly completed your budget values.

Use a Lookup tab with driver-based budgeting

The process for using a Lookup tab with driver-based budgeting involves moving between two tabs, the Lookup tab and the related tab. Those two tabs must be linked. You create this link in the setup of the related tab by adding additional measures for each item (row) in the Lookup tab.

For example, suppose your budget (Main tab) has three levels (Country > Region > Sales Rep) and you want to enter the predicted growth rate for each country. The easiest way to do that is to create a Lookup tab for the growth rate data and select the Main tab as its 'related tab'. Then in the setup of the Main tab, you add a measure that links to that lookup data. As a result, the Main tab displays an additional row underneath each Sales Rep, in each region, in each Country. You can then return to the Lookup tab and make changes to the growth rates and the Main tab will automatically update.

1. Add the Lookup tab (see steps in section above).

2. Edit the setup of the related tab to link to the lookup data. See Include additional measures (for driver-based budgeting) for detailed information but in summary, the steps are as follows:

   1. Click the blue Add measure button on the right side of the screen and select Lookup.

   2. Enter a name for the row.

   3. Select the lookup tab.

   4. Select the lookup item.

      

   5. Click Update and Finish. A new row with the lookup data is inserted under each row in the related tab.

3. Edit the data in the Lookup tab as required. Your changes will flow through to the related tab.

Examples of using a Lookup tab with driver-based budgeting

The following videos show some of the ways you can use the Lookup tab, alongside the driver-based budgeting feature, to help you complete your budget or forecast.