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 for scenario planning purposes, but it is also useful by itself in other circumstances.
The Lookup tab works in a similar way 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.
Example - Use the 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.
Step 2: Add the Lookup tab with link to related tab and add look items
Add a Lookup tab
Click the Add button at the bottom of the worksheet.
Enter a name for the tab, select the Lookup tab type, then click Add.
In the Lookup setup window, configure the tab:
Related tab - Select the tab, such as the Main tab , that contains the data for which you want to add the lookup information. If you do not have any options to select from, ensure you have set up the related tab for driver-based budgeting (see section above).Hierarchy - Select how you want the lookup 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.
Periods 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 Lookup worksheet, select the Exclude periods checkbox. One ‘total’ ‘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.
Add the rows that contain the look up items that you want to display in each row:
Enter a name, and placeholder amount (default amount), and select the aggregation method for the first row.
(Optional) Click Add row and repeat the above step to add other items rows as required.
Click Create Lookup tab and review the data in the new tab.
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
tab. The new tab displays with the default amount in each row, which you can then edit as required.
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 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.
Add the Lookup tab (see steps in section above).
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:
Open the reference tab setup and go to the Additional measures for driver-based budgeting screen.
Click the blue Add
measure button on the right side of the screen and select Lookup.
Enter a name for the row.
To Do
Step 5 - View lookup data in the reference tab
To DoSelect the lookup tab.
Select the lookup item.
Click Update and Finish. A new row with the lookup data is inserted under each row in the related tab.
Edit the data in the Lookup tab as required. Your changes will flow through to the related tab.
On this page
| Table of Contents | ||||
|---|---|---|---|---|
|