Page Comparison

The Lookup tab is designed to be used with the Lookup measure row type in driver-based budgeting, as outlined on this page. However, the Lookup tab is also useful by itself in other circumstances. The Lookup tab works in a similar way but can also be used stand-alone, similarly to the Manual Entry tab in that it provides a place where you can record information, so it is available for quick reference purposes or for using in formulas in other tabs. The key difference between a Lookup and Manual Entry tab is that where a Manual Entry tab is empty to begin with (you need to add rows), the Lookup tab has a structure to it - it is mapped to the dimension(s) in another tab, so contains rows based on that hierarchy.

Lookup tabs are useful for making assumptions about tax or growth rates. This is particularly useful when used with driver-based budgeting, as you can change a value in the Lookup tab and the corresponding Lookup measure rows in the other tab automatically update, without the need for formulas.

The process for creating a lookup tab involves moving between two tabs, as illustrated in the following image and outlined below.

Image Removed

Example - Use Lookup tab in driver-based budgeting

Suppose you have a Main tab, in which the hierarchy is Country > Branch > Sales Rep. Then, based on that Main tab, you create a Lookup tab. The hierarchy in the Lookup tab can be the same, or at a higher or lower level of detail, as the Main tab but in this case, you are only concerned with the Country level. In the Lookup tab, you enter the predicted growth rate for each country.

Back in the Main tab setup, in the Additional measures for driver-based budgeting screen, you add a Lookup row that links to your Lookup tab. Now your Main tab displays a growth rate row for each Sales Rep, in each branch, in each Country.

You can now return to the Lookup tab and make changes to the growth rates. If you change the rate for one country in one period, such as Australia in February, back on the Main tab you will see that the rate for February for all Sales Reps in Australia has changed but the rate for the other months does not change. The rate for February does not change for any Sales Reps in the other countries.

Step 1 - Set up the related tab for driver-based budgeting

As illustrated in the flowchart above, the Lookup tab and another tab (the ‘related’ tab) are connected. The Lookup tab is only available when you use driver-based budgeting in the related tab. Typically, the related tab is the Main tab in an existing budget but you might want to use a Database driver tab, or you might also want to start by creating a new budget or Database driver tab.

Regardless of whether you are setting up a new tab or editing the setup of an existing tab, you need to ensure that there is at least one additional measure the Additional measures for driver-based budgeting screen. You can then proceed to the next step.

Image Removed

Step 2: Add the Lookup tab with link to related tab and add look items

In the Lookup setup window, configure the tab:

. Think of Lookup tabs as structured Manual Entry tabs, with a prebuilt dimension structure and entities inherited from another tab.

Add a Lookup tab to build budget models that pull out various inputs or assumptions, then model different scenarios by changing those assumptions. For example, you might have a lookup for sales growth for each branch or 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. Add the tab using one of these methods:

   • Click the Tab menu > Add tab > Lookup.

   • Click the Add button at the bottom of the workbook and select Lookup.   

2. Configure the tab:

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

   2. Select the related tab. This is another tab in the workbook that contains the data for which you want to add the lookup information.

   If you do not have any options to select from, refer to step 1 above.Hierarchy - Select how you want the lookup worksheet to be

   1. Typically, the related tab is the Main tab in the budget, but you might want to use another tab, such as a Database tab.

   2. Select the hierarchy (levels) to determine how the data in the tab is organized. The available options (dimensions

   options

   1. and entities) depend on whatever is in the related tab. The hierarchy in the Lookup tab can be the same, or at a higher or lower level of detail, as the related tab.

   Periods - Select whether

   1. Determine if you want to

   use period columns or not

   1. exclude periods in the tab.

      • By default, the Exclude periods checkbox is NOT selected, which means that period columns (such as months) are included in the

   Lookup worksheet

   1. • tab, and therefore, each lookup item displays as a row. This option allows you to enter different lookup information for each item in each period in the budget

   . This option

   1. • , which is suitable, for example, if you have different prices across the year due to seasonality.

      • If you

   do not

   1. • don’t need period columns

   in the lookup worksheet

   1. • , select the Exclude periods checkbox. One

   ‘total’

   1. • Total column for each lookup item displays in the

   worksheet

   1. • tab, rather than a column for each period. This option allows you to enter one lookup value for each budget row

   . This option

   1. • , which is suitable, for example, if you have

   a

   1. • one constant price

   than remains constant

   1. • throughout the year.

3. Add the look up item(s)the rows that contain the lookup items:

   1. Enter a name and default value and select the aggregation method. placeholder amount (default amount).

   2. Define the display format of the lookup items: Enter a prefix, number of decimals, and suffix as required. See Format measures in a Lookup tab.

   3. Select the aggregation method for the first row.

   4. (Optional) Click Add lookup item to add another row, and repeat follow the steps above step to add other items as requiredto configure it.

4. Click Save and review the data in the new tab.

   Image Removed

Step 3 - Review new Lookup tab and enter lookup data

You can do this later, if required.

To Do

Step 4 - Edit related tab setup to add row for Lookup measure linked to Lookup tab

1. finish. The new tab opens with the default amount in each row, which you can then edit as required.

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

   Image Added

3. (Optional) Import data into the Lookup tab (see steps below).

4. (Optional) Use the Lookup tab with driver-based budgeting (see steps below).

Use a Lookup tab with driver-based budgeting

Using a Lookup tab with driver-based budgeting involves moving between 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 Main tab setup, 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.

Image Added

1. Add the Lookup tab (see 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

1. detailed information, but in summary, the steps are as follows:

1. Open the reference tab setup and go to the Additional measures for driver-based budgeting screen.

2. Click the blue plus button > Lookup, then enter a name for the row.

3. To Do

Step 5 - View lookup data in the reference tab

1. 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. Select the measure in the underlying database to publish the data to, if required.

   6. Select the aggregation method.

   7. Customize the display format of the measures, if required: Click the menu button (three dots) at the end of the row and switch to Custom format, then define your format.

      Image Added

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

2. Edit or import 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 ways you can use the Lookup tab alongside the driver-based budgeting feature to help you complete your budget or forecast.

Import data into a Lookup tab

Importing data from a Microsoft Excel file into the Lookup tab will save you much data entry time. There are two methods for importing data.

Use the template file

You can download a template file that includes the structure and values of the Lookup tab, then make updates in Excel and import the file back into the Lookup tab. As the file is in the expected structure, you bypass the mapping step (required in the method below), and the data is imported directly. See the videos in examples 1 and 2 below.

1. On the Lookup tab, click the Tab menu > Import data > I need to download a template.

2. Open the file and make your changes, then save and close the file.

3. Back on the Lookup tab, click the Tab menu > Import data > I have a file.

4. Locate and select your file, then click Open.

   Image Added

Use your own file

You can import a file that you source from another application or export from Analytics. This method involves mapping the fields in the file to the columns and rows in the Lookup tab. See the video in example 2 below.

1. Prepare, save, and close your file.

2. On the Lookup tab, click the Tab menu > Import data > I have a file.

3. Locate and select your file, then click Open.

4. Map the fields in your file to the columns and rows in the Lookup tab, then click Import.

   Image Added

Examples of importing data into the Lookup tab

A common use case for Lookup tabs is to supply the price per unit and cost per unit for each product in a Sales budget, then combine this data with driver-based budgeting, where quantity sold is budgeted, and the Lookup tab provides the cost and price per unit to calculate the sales value and the cost value for each product.

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.