Learn how to manually add a budget to a financial database for use in Financial Statements and/or Budgets & Forecasts.
Last updated
User permission: Administration > Databases and Sync
If you use Budgets & Forecasts:
You can publish your budget to a stream, avoiding the need to follow this import process.
If you export a budget to Excel, 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.
Watch this video of the process
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.
If you have Budgets & Forecasts, you can export a budget or forecast in a flat file format via the Publish 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. Watch this video to learn how.
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.
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.
The budget file must contain the following data, in the specified format (expand the sections to view the details):
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.
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.
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:
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 Upload a file.
Designer: You can either upload or sync the file. See Get your data into Phocas. Ensure you select the File has header row checkbox, to enable Designer to recognize the file as a budget file.
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.
In Designer, click the New Stream tab.
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.
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.
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.
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.
The next steps depend on whether you want to use the budget data in Financial Statements and/or Budgets & Forecasts.
User permission: Financial Statements > Manage Custom Statements
You must add the new budget stream(s) to a budget package. See Manage budgets to learn more about this.
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.
You can then view the budget data in the Budget column in the financial statement. 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.
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.
