LogoLogo
More help and supportPhocas website
  • Home
  • Getting started
    • Navigation
    • Homepage
    • Data access
    • User licenses, profiles and permissions
    • Account settings
    • Databases
    • Periods
      • Offset dates
      • Default period for a database
    • Sharing and folders
      • Share a dashboard, favorite, or alert
    • Subscriptions
  • Analytics
    • Take a tour of Analytics
    • Work with the grid
    • View a summary of the data
    • Customize your view (Analytics)
      • Change the mode
        • Period mode
        • Period Variance mode
        • Period Stream mode
        • Moving mode
        • Stream mode
        • Total mode
        • Transaction mode
        • Matrix mode
        • Matrix Variance mode
        • Matrix Share mode
        • Variance mode
        • Search mode
        • Market mode
        • Custom mode
      • Change the properties (Analytics)
      • Change the measures (Analytics)
      • Change the stream
      • Use the activity filter
      • Change the data format
      • Change the period (Analytics)
    • Drill down into your data (view transactions)
    • Filter data (use dimensions)
    • Select data (Analytics)
    • Focus on data (Analytics)
    • Copy or export data
    • Search for data
      • Perform a basic search
      • Perform an advanced search
      • Search for similar or comparative data (right-click filter)
    • Add levels to the grid (grid nesting)
    • Compare data with Matrix mode
    • Get more options for viewing data
    • Visualize your data in a chart
      • Bar and Column charts
      • Combo chart
      • Pareto chart
      • Waterfall chart
      • Line chart
      • Area and % Area charts
      • Pie and Donut charts
      • Bubble chart
      • Radar chart
      • Gauge chart
      • Bullet chart
      • Summary and Ring Summary charts
      • Map charts
      • Configure the chart options
      • Select and focus on a chart segment
      • Export a chart as an image
      • Use the chart legend
    • Save favorites and add to dashboards (Analytics)
    • Reset data
    • Phocas AI
    • Flex Modes
      • Switch modes
      • Filter data (Flex Modes)
      • Apply conditional formatting
      • Add levels to the grid (Flex Modes)
      • Manage columns (Flex Modes)
      • Save and share a favorite (Flex Modes)
      • View your data in a chart (Flex Modes)
  • Financial Statements
    • Take a tour of Financial Statements
    • Overview of access to financial statements
    • Customize your view (Financial Statements)
      • Change the properties (Financial Statements)
      • Change the measures (Financial Statements)
      • Change the period (Financial Statements)
      • Change the current data
      • Change the budget data
      • Change the columns
      • Change the style
      • Change the rows (activity filter)
    • Filter the financial information
    • Select and focus on data (Financial Statements)
    • Copy or export financial information
    • Add a level in a financial statement
    • View financial information in a matrix (change column groups)
    • Add and view comments (Financial Statements)
    • View transactions (Financial Statements)
    • View financial information in a chart
      • Create a Pie and Donut chart (Financial Statements)
      • Create a Value card
      • Export a financial chart widget as an image
    • Save and share favorites (Financial Statements)
    • Add financial analysis to a dashboard
    • Manage financial statements
      • Overview of financial statements and dimensions
      • Create a financial statement
      • Delete a financial statement
      • Customize a financial statement
        • Rename a financial statement
        • Reorder a financial statement
        • Customize account groups
        • Customize calculations
          • Functions for Profit & Loss calculations
          • Formulas for financial ratios
        • Customize headings and spacers
        • Set the revenue row in a Profit & Loss statement
        • Customize columns
        • Customize a Cash Flow statement
          • Common account mapping for Cash Flow statements
        • Rebuild the database
      • Set the financial year end date
      • Map the Retained Earnings account(s)
      • Restrict access to accounts
      • Manage budget streams (Financial Statements)
        • Create a budget (Financial Statements)
      • Use statistical streams
      • Use multiple currencies
      • Manage dimension groups
      • Map categories from your Chart of Accounts
  • Budgets & Forecasts
    • Overview of key terms and concepts
      • Budgets versus forecasts
      • Users and data access
      • Relationship with the Financial Statements module
      • 3-statement budgeting
      • Filtering methods
      • Demand planning
      • Loan repayments
    • Take a tour of Budgets & Forecasts
      • Explore the budget workbook
      • Explore the forecast workbook
      • Explore the Balance Sheet and Cash Flow budgets and forecasts
    • Customize your view (Budgets & Forecasts)
      • Filter a worksheet (search for items)
      • Group period columns
      • Use layouts
    • Use session filters
    • Edit values in a worksheet
      • Open workbook notes
      • Use keyboard shortcuts
      • Copy and paste values
      • Use comparison rows
      • Use sum and working lines
      • Use formulas
      • Spread totals
      • Revert to the baseline values
      • Edit the Balance Sheet budget
      • View and use headcount data
    • Refresh a workbook
    • Use workflows
      • Contribute to the workflow
      • View the workflow history
      • View workflow comments
    • View budget information in a chart
    • Open baseline, comparative, or opening balance data
    • Review the budget or forecast
      • Add and view cell comments
      • View and restore the cell history
      • View the audit log
      • Compare actuals, budgets and forecasts
    • Export a workbook or worksheet tab
    • Manage budgets and forecasts
      • Create a budget workbook
        • Filtered budget or forecast
        • Multi-currency budget
        • Unclassified data
        • Period types in budgets
        • Additional measures for driver-based budgeting
      • Create a forecast workbook
      • Clone a workbook
      • Import a budget or forecast
      • Edit the workbook setup
      • Roll a budget forward
      • Add more detail to a budget or forecast
      • Add worksheet tabs (drivers)
        • Balance Sheet and Cash Flow tabs
        • Manual Entry tab
        • Database tab
        • Headcount tab
          • Headcount tab FAQs
        • Reference tab
        • Lookup tab
        • Allocations tab
        • Scratch Pad tab
      • Manage worksheet tabs
      • Add notes to a workbook
      • Customize the format of measures
      • Manage the workflow
      • Manage user access and notifications
      • Publish a budget, forecast or other worksheet tab
      • Fix the baseline in a budget
      • Delete or restore a workbook
      • Delete a stream (Budgets & Forecasts)
      • Troubleshoot performance issues
  • Rebates
    • Overview of rebates
    • Get started with Rebates
    • Projects
    • Rules
      • Parent-child rebate rules
      • Rebates calculation methods
      • Yearly rebates
      • Quarterly rebates
      • Monthly rebates
      • Whole rule period rebates
      • Retrospective rebates
      • Pro rata rebates
    • Calculations
      • Run a calculation
      • Schedule calculations
      • View calculation results and transactions
      • Post calculation results
    • Seasonal calendars
    • Rebates analysis
    • Rebates near miss analysis
  • CRM
    • Overview of CRM
    • Agenda
    • Contacts
    • Accounts
    • Leads
    • Activities
      • Microsoft 365 Outlook calendar integration
    • Campaigns
    • CRM analysis
    • CRM administration
  • Favorites
    • Save a favorite
    • Manage favorites
    • Set a favorite as your default view
    • Use alerts
  • Dashboards
    • Use dashboards
    • Create a dashboard
    • Set a dashboard as your homepage
    • Manage dashboards
  • Administration
    • Overview of access to data (Administration)
    • Users
      • Add, update or delete a user account
        • User maintenance form
        • User permissions
      • Create a user template
      • Manage a user's database access and restrictions
      • Manage user passwords
      • Monitor user activity
      • Lock user accounts
      • Impersonate a user
    • Profiles
    • Folders (Administration)
    • Favorites (Administration)
    • Dashboards (Administration)
    • Subscriptions (Administration)
    • Periods (Administration)
      • Period types
      • Working days
    • Databases (Administration)
    • Sync Sources
    • Connectors
    • Configuration
      • Set up SSO
      • Set up SSO with Microsoft Entra ID
    • Logs
    • Designer
      • Modify a database
      • Get your data into Phocas
      • Design a database
        • Create a database
        • Add (connect) data to a database
        • Map data to the database
        • Save your database design
      • Design a financial database
      • Build a database
      • View and revert to older design versions
      • Add a budget file to a financial database
      • Add a budget file to a non-financial database (easy budget upload)
    • Sync
      • Automatically join or split items during Sync (split view)
      • Schedule the sync process
      • Migrate Sync
      • Test the sync process
    • Reserved words and characters
    • End of year maintenance tips
  • OTHER INFORMATION
    • File upload
    • Encrypt (share secrets)
    • Glossary
    • Release notes
      • Analytics release notes
      • Financial Statements release notes
      • Budgets and Forecasts release notes
      • Rebates release notes
      • Administration release notes
    • System requirements
    • Technical limitations
    • Security information
    • Scheduled upgrade and maintenance periods
    • More help and support
Powered by GitBook

© Phocas Software

On this page
  • Add a Lookup tab
  • Use a Lookup tab with driver-based budgeting
  • Import data into a Lookup tab
  • Use the template file
  • Use your own file
  • Use a Lookup tab separately from driver-based budgeting

Was this helpful?

  1. Budgets & Forecasts
  2. Manage budgets and forecasts
  3. Add worksheet tabs (drivers)

Lookup tab

Add a blank, structured worksheet tab in a budget or forecast workbook in which you can manually enter data for inputs or assumptions.

PreviousReference tabNextAllocations tab

Last updated 4 months ago

Was this helpful?

The Lookup tab is designed to be used with the Lookup measure row type in but can also be used stand-alone, similarly to the Manual Entry 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.

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.

Watch these videos:

  • Phocas demo:

  • PUG Training: (jump to 17.14 to see how a Lookup tab can be used to manage assumptions and drive sales budget values) and (also see )

Add a Lookup tab

Do you need to add a new Lookup 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 Lookup tab type. Then proceed to configure the Lookup tab:

  1. Enter a name for the tab.

  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. Typically, the related tab is the Main tab in the budget, but you might want to use another tab, such as a Database tab.

  3. Select the hierarchy (levels) to determine how the data in the tab is organized. The available options (dimensions 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.

  4. Determine if you want to 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 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, which is suitable, for example, if you have different prices across the year due to seasonality.

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

  5. Add the rows that contain the lookup items:

    1. Enter a name and placeholder amount (default amount).

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

    3. Select the aggregation method for the first row.

    4. (Optional) Click Add lookup item to add another row, and follow the steps above to configure it.

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

You can then import data into the Lookup tab or use the Lookup tab with driver-based budgeting (see steps below).

Use a Lookup tab with driver-based budgeting

Watch the following videos to see how you can use the Lookup tab alongside the driver-based budgeting feature to help you complete your budget or forecast:

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.

First, add the Lookup tab (see section above).

  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. Click Update and Finish. A new row with the lookup data is inserted under each row in the related tab.

Then, edit or import the data in the Lookup tab as required. Your changes will flow through to the related tab.

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.

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.

Watch the following videos to see how you can import data into the Lookup tab. Note the UI in the videos is out of date but the context remains useful.

Use a Lookup tab separately from driver-based budgeting

Review the new tab and, if required, .

Next, edit the setup of the related tab to link to the lookup data. See for detailed information, but in summary, the steps are as follows:

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

You can add a Lookup tab (see steps above) and use it in 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.

to see how to use a Lookup tab separately from driver-based budgeting, along with using the Copy Forward and Copy Down features to quickly complete your budget values. Note the UI in the videos is out of date but the context remains useful.

Model sales growth by customer based on last year’s sales
Do scenario planning on assumptions (price and margin) at the product level
Use the Bill of Materials to drive your Sales budget
Additional measures (for driver-based budgeting)
Customize the display format of the measures
Import a single value for each product using exclude periods
Import a different value for each month in a budget Lookup tab
formulas
Watch this video
driver-based budgeting
Scenario planning
Sales budgeting
Demand planning
this related page
Format measures in a Lookup tab
image-20240409-214302.png
image-20240221-232458.png
image-20240430-211011.png
image-20240430-211643.png
edit the tab setup