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
  • 1. Get the raw data
  • 2. Prepare the budget file
  • Required data structure
  • Required data
  • Recommended data
  • 3. Save and close the budget file
  • 4. Upload (or sync) the budget file
  • 5. Add (connect) the budget file to a stream
  • 6. Map the budget data
  • 7. Save and build the database
  • 8. Use the budget file
  • Financial Statements: Create the budget package and view the data
  • Budgets & Forecasts: Use the new stream in a budget

Was this helpful?

  1. Administration
  2. Designer

Add a budget file to a financial database

Learn how to manually add a budget to a financial database for use in Financial Statements and/or Budgets & Forecasts.

PreviousView and revert to older design versionsNextAdd a budget file to a non-financial database (easy budget upload)

Last updated 3 months ago

Was this helpful?

Related page:

User permission: Administration > Databases and Sync

If you use Budgets & Forecasts:

  • You can to a stream, avoiding the need to follow this import process.

  • If you , you can get it back into Phocas using this import process.

Adding a budget file to a financial database involves multiple steps in different places, as illustrated in the following diagram.


1. Get the raw data

Export the raw data from the underlying database into a Microsoft Excel file. The data might come from Phocas, your ERP system, or another source.

2. Prepare the budget file

Prepare the data in the budget file according to the requirements outlined below, to ensure it is in the correct format for uploading to Phocas.

Required data structure

Structure the data in the budget file in either of these two ways:

  • A table with dates along the top (this is the structure of the template).

  • A list of values with a Date column.

Required data

The budget file must contain the following data, in the specified format (expand the sections to view the details):

General Ledger account code
  • The account must be in code format, such as 11003, as this is what is used to map your data to the database.

  • You can also have the name of the account, such as Sales, for your own information purposes.

Date

The dates must be in one of the following formats:

  • YYYY-MM-DD for example, 2022-10-27

  • yyyy-MM-dd HH:mm:ss for example, 2022-10-27 09:26:01

  • MMM-yyyy for example, Oct-2022

  • MMM yyyy for example, Oct 2022

  • MMMM-yyyy for example, October-2022

  • MMMM yyyy for example, October 2022

  • MMM-yy for example, Oct-22

  • MMM yy for example, Oct 22

  • MMMM-yy for example, October-22

  • MMMM yy for example, October 22

If you are using a full date (the first two examples below), the date should be the first day of the period. For example, for a May budget, the date is 2022-05-01.

If you exported a file from Budgets & Forecasts, you must change the dates in the column headers to comply with one of the formats listed above.

Value
  • In this context, the values are the movements in the account balance for the period, such as day, week or month. The values must NOT be the year to date or life to date of the account balance.

  • In a Balance Sheet budget file, the values also include the opening balances for the budget period. The budget Profit and Loss must be included within the Retained Earnings account, so the Balance Sheet file balances.

  • The values must follow the standard accounting convention, whereby: Revenue, Liabilities, and Equity are negative values. Use a negative sign and remove the commas, for example, -1000.00. Expenses and Assets are positive values. Remove commas, for example, 1000.00. Note: You can use the Reverse sign checkbox later within your financial statement setup. You can also use the Inverse value option, which is available in your database setup.

  • The upload file cannot contain formulas, so if you use formulas to calculate the budget values, copy and paste special > values to remove the formulas.

Recommended data

You might need to add other dimensions to your file, depending on your database setup. These other dimensions, such as Country, Department and Cost Centre are for analysis purposes.

Whereas these instructions involve using one file, you can have data in multiple files and add all those files to one stream (dataset) for your budget.

3. Save and close the budget file

Save the budget file in CSV (preferred) or XLXS format, then close the file.

If you use the XLXS format, all worksheets in the workbook will be uploaded and the worksheet names will be suffixed to the file name. While this is useful if you want to upload multiple worksheets, it's not recommended for single (one-sheet) budgets, as it over-complicates the sync item name and makes the mapping process in Designer more difficult.

4. Upload (or sync) the budget file

If you are creating a Balance Sheet budget, you need to upload the opening balances in addition to the budget file. This data can be in the same file or a separate file.

There are two ways to get your file into Phocas and each requires a different user permission:

5. Add (connect) the budget file to a stream

Add the budget file to a budget stream in the finance database design, so you can view the budget data via that stream in the Financial Statements module.

  • For Profit and Loss and non-financial budget files, you can either create a new stream or use an existing budget stream.

  • For Balance Sheet budget files, you are likely to need a new stream for each budget period, as your opening balance position will have changed.

  1. In Designer, click the New Stream tab.

  2. Click the Edit button in the new stream then:

    • Enter a more suitable name for the stream, such as P&L Budget 2023.

    • If you're adding a Balance Sheet budget, select the Balance Brought Forward check box. Otherwise, the budget will be treated as a Profit and Loss budget.

    • If you are adding a non-financial budget, select the Variance Stream check box. If you do not do this, the non-financial data will display in the Current column rather than the Budget column.

    • Click Save.

  3. Expand the data sources panel on the right, locate the budget file, and drag it into the stream’s Drag Here box.

    • Designer recognizes that you are loading a budget-type file and displays a message to that effect. Click Yes.

    • Designer converts the budget data into the required format and displays it in the new stream tab with some notable differences. Firstly, the value column in your source data displays as the Measure column. Secondly, regarding the date, if your source data had multiple date columns in the header row, Designer unpivots the data and displays all the dates in one Moment column. If your source data had all the dates in one column, Designer retains that column but changes the header to Moment.

6. Map the budget data

The minimum mapping requirements are as follows:

  • General Ledger account code → Account

  • Value → Measure > Value

  • Moment (date/period) → Date

You can also map other dimensions to their corresponding dimensions, as applicable.

7. Save and build the database

8. Use the budget file

The next steps depend on whether you want to use the budget data in Financial Statements and/or Budgets & Forecasts.

Financial Statements: Create the budget package and view the data

User permission: Financial Statements > Manage Custom Statements

When you create the budget package, the Cash Flow budget is automatically derived from both the Profit and Loss budget and the Balance Sheet budget.

Budgets & Forecasts: Use the new stream in a budget

User permission: Budgets & Forecasts and Manage Budgets & Forecasts

The new budget stream is available in the Stream list in the Budget, Comparison, and Forecast setups.

For example, if you want to import a budget, you can create a new financial budget workbook in Budgets & Forecasts and select the new budget stream as the baseline data structure.

of the process

If you have Budgets & Forecasts, you can export a budget or forecast in a flat file format via the feature.

Alternatively, you can download your current data (actuals) from Phocas and use that as a template for your budget file, as it contains the required data structure and format. to learn how.

The account codes should be applicable to the statement type. For example, in a Profit and Loss budget file, you must not have any Balance Sheet account codes and vice versa. If the file contains account codes from another statement type, you can either remove that data from the budget file before you upload it in Designer or filter out that data after you upload the file in Designer. If you select the latter option, you should have a column in the budget file that identifies the financial statement applicable to each row of data. You can then in Designer.

It is possible to have multiple measures, such as local value and reporting value, but you add these later in Designer. See .

Toolbox > Upload files: When a budget file is setup in Designer, this method allow users with upload permission to upload an updated budget without having access to Designer. The file must be uploaded under the Shared User Uploads section for it to be available to an administrator in Designer. See .

Designer: You can either upload or sync the file. See . Ensure you select the File has header row checkbox, to enable Designer to recognize the file as a budget file.

in the budget file to the corresponding elements in the database.

and to apply those changes. You can either build the database now or schedule a build for later.

You must add the new budget stream(s) to a budget package. See to learn more about this.

You can then view the budget data in the Budget column in the financial statement. Depending on your setup, you might need to , and before the budget data displays in the grid.

Watch this video
Publish
Watch this video
Create a calculated measure
Upload a file
Get your data into Phocas
Map the data
Save your design changes
build the database
Manage budgets
change the budget dataset
add the Budgets column
change the budgets period
Add a budget file to a non-financial database (easy budget upload)
publish your budget
export a budget to Excel
apply a filter to that column
image-20240611-025335.png