Add a budget file to a financial database

Owned by Denise McGettigan
Last updated: Jan 24, 2024
7 min read

This page outlines how to manually add a budget to a financial database, for use in Phocas Financial Statements.
If you want to add a budget for use in Phocas Analytics, see Add a budget file to a non-financial database (easy budget upload).

If you have the Budgets and Forecast module, you can publish your budget workbook to a stream from there, avoiding the need to follow this process.

The process to add a budget is complex, as it involves multiple steps, in different places, as outlined below. It requires the use of the Designer tool in the Administration module, for which you need both the Administration > Databases and Sync user permissions.

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.

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. Watch this video to learn how to create a Phocas-friendly template.

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:

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

  2. 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):

  • 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.

  • 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 apply a filter to that column in Designer.

  • The dates must be in one of the formats listed in the table below.

  • 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.

Format

Example

YYYY-MM-DD

2022-10-27

yyyy-MM-dd HH:mm:ss

2022-10-27 09:26:01

MMM-yyyy

Oct-2022

MMM yyyy

Oct 2022

MMMM-yyyy

October-2022

MMMM yyyy

October 2022

MMM-yy

Oct-22

MMM yy

Oct 22

MMMM-yy

October-22

MMMM yy

October 22

  • The values in this context 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, for the budget period. The budget Profit and Loss must be included within 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.

  • 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.

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

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.

3. Save and close the budget file

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

4. Upload (or sync) the budget file

Open Designer in Phocas, then either upload or sync the file (see Get your data into Phocas). Either way, ensure you select the File has header row checkbox, to enable Designer to recognize the file as a budget file.

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

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 Edit button in the new stream, then:

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

    2. Take the applicable action:

      • If you are adding a Balance Sheet budget, select the Balance Brought Forward check box. If you do not do this, 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.

    3. Click Save.

  3. Expand the data sources panel on the right and locate the budget file, then 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.

      • All the dates in one column, Designer retains that column but changes the header to Moment.

6. Map the budget data

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

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

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

8. Create the budget package

Add the budget stream(s) to a budget package in the Financial Statements module. See Manage budgets to learn more about this.

9. View the budget

After you add the budget stream to a budget package, you can view the budget data in the in the Budget column in Financial Statements module. Depending on your setup, you might need to change the budget dataset, add the Budgets column and change the budgets period before the budget data displays in the grid.