Add a budget file to a non-financial database (easy budget upload)

The Easy Budget Upload method is a simpler way to add a budget to a database, as Designer automatically detects the budget file and converts it into the required format. Depending on how the data in your source file is arranged, this method might not be suitable, for example, if it contains pivoted data. If that is the case, you need to add the budget the same way you would add any other type of item, and ensure you check the date format.

The process to add a budget file involves multiple steps, in different places, as outlined below.

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.

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.

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.

The basic requirements are:

1. The raw budget data must contain only a single measure.

2. Codes should be used, not names, for example, Rep007, not Justin Time.

The following table gives is an example of a typical budget in the required format:

Other requirements are as follows:

Custom periods

If the selected database is summarized by a custom period type that is not aligned with calendar periods, such as a fiscal period, ensure that the date column (YYYY-MM-DD), especially the day of the month, or DD, takes into account the appropriate fiscal period start date. This ensures your budget stream is summarized using the correct date range.

Example of a typical budget in the required format when using a custom period type such as fiscal period:

Date column headings

The column headers in the data source must be in one of the following date formats:

If using a full date (the first two examples above), typically the date should be the first day of the period. For example, for a May budget, the date required is 2017-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.

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

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

Use the File upload tool

The File upload tool is the easiest way to get data into Phocas. You can add new files or update existing files. When a budget file is already setup in Designer, this tool allows you (administrator) to quickly update the file without having to go into Designer. It also allows you to delegate the file upload task to other users who don't have access to Designer. See Upload a file for detailed information, including the required user permissions.

You must upload the file into the Shared User Uploads folder for it to become available to other users in Designer. If the file is already in Designer, select the Replace option, as in the image below.

Designer

You can either upload or sync the file in Designer. See Get your data into Phocas for detailed information.

You must select the File has header row checkbox to enable Designer to recognize the file as a budget file.

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

When you add the budget file to a stream, Designer converts (unpivots) the budget data into the required format and displays it in the new stream tab. All the dated columns are changed into a Moment column and a Measure column.

1. In Designer, click the New Stream tab.

2. Click the Edit button in the new stream tab, enter a more suitable name for the stream (such as Budget) and 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.

4. Click Yes to confirm you are adding a budget file.

   
5. Split the budget, if required. This option is only available for the first data source you add and your selection here determines the setup for subsequent data sources you add to the stream - expand the section below for more information.

   

6. Map the budget data

Map the data in the budget file to the corresponding elements in the database. Typically, you need to map over the top of existing dimensions and measures.

• Value → Measure > Value

• Moment (date/period) → Date

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. Use the budget

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

• Analytics: You can view the new budget in Analytics by selecting it as a stream. Use Stream mode to compare your total budget and actual values side-by-side or use Period stream mode more more granular comparisons across the period.

• 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 Sales budget, you can create a new operational budget workbook and select the new budget stream as the baseline data structure.