Publish a budget, forecast or other worksheet tab

Publish the budget or forecast data to the underlying database so others can use it for analysis and reporting or export it to Microsoft Excel for use elsewhere.

User permissions: Budgets & Forecasts and Manage Budgets & Forecasts

When the budgeting or forecasting workflow is complete, the next logical step is to publish the data to a Budgets & Forecasts stream (dataset) in the underlying database. Other users of that database can then view the data, use it to create dashboards, and analyze it against other streams, such as actuals, other budgets, forecasts, and so on.

Another reason for publishing a budget or forecast is to create a safe (backup) copy in the event of any issues that might arise later. Note that only the values are published, therefore it's not possible to restore driven rows, formulas (including references), working rows and sum rows.

We recommend you do this in the following situations:

  • Before you make any changes to the underlying financial statements. Changes to financial statements can result in the loss of budgeted values due to the way they are stored.

  • At the end of the budgeting or forecasting process. It’s not currently possible to lock a final budget or forecast, but you can publish (export) it to Microsoft Excel and import it back into Phocas if the need arises later.

  • Before making any structural changes to the workbook, such as adding a level to the hierarchy or changing the period.

Publish a budget

Learn about the publishing process

Before you start publishing Budgets & Forecasts data, it's important to understand what you can publish and where the published data is stored.

What you can publish

You can publish the data in the following worksheet tabs:

Tab
Notes

Main

Examples of when you might want to publish this tab:

  • When the workflow tasks you assigned to your team have been completed and submitted back to you, you can publish the budget/forecast for analysis and finalization. Note that not every row needs to be marked as Completed before you can publish the budget or forecast, as marking a task as Complete is for reference purposes only.

  • If you are still adjusting the budget/forecast, you can publish the draft to analyze the impact of your changes midstream.

  • If you create multiple filtered budgets and want to publish them to the same stream.

Database and Reference

For example, if you have completed your Sales budget as a database driver in your financial workbook, you'll likely want to publish this budget for analysis in Analytics.

Headcount Output

You can publish the Headcount Output tab as a statistical stream (Headcount and FTE). This can then be used in Financial Statements to compare to actuals and for budgeted financial metrics. The Publish option isn't available in the Headcount Output tab if no dimensions are included in the budget hierarchy.

You can't publish the Headcount Input tab for data privacy reasons.

Balance Sheet and Cash Flow

The Cash Flow statement is created using values from the Profit and Loss and movements from the Balance Sheet. For this reason, it isn't possible to publish the Cash Flow tab (by itself). To view a Cash Flow budget or forecast in Financial Statements you can publish both the Main (Profit and Loss) and Balance Sheet tabs, then create a budget or forecast scenario using those streams. Applying this scenario to the Cash Flow statement in Financial Statements will provide a Cash Flow for reporting and analysis.

How the data is stored

There are three publishing methods:

New stream

This method creates a new Budgets & Forecasts stream in the underlying database in Phocas and publishes the data into that stream. This publish method is selected by default if you have not yet published the worksheet tab to a stream, but you can select a different method if required.

Existing stream

This method overwrites the matching data in the selected Budgets & Forecasts stream in the underlying database with the data in the worksheet tab. The benefit is that you don't need to update any dashboards and favorites that use the stream. For example, you might want to publish to an existing stream when:

  • You make changes to the current budget - you can republish the same budget.

  • You are rolling over to a new period, such as next year’s budget - you can publish the new budget into the same stream.

  • You have split your budget into multiple workbooks based on a dimension and want to consolidate the results for reporting and analysis in Financial Statements.

This method and the applicable existing stream are selected by default when you have already published the worksheet to a stream. However, you can select a different Budgets & Forecasts stream or create a new stream as required. See the warning message below for examples of how publishing to an existing stream will work in different scenarios.

Excel

This method doesn't publish data to the underlying database in Phocas. Instead, it exports the data into an XLXS file, which opens in Microsoft Excel. The exported data is in an unformatted state (flat file) suitable for importing into other applications, such as your ERP. See Export a workbook or worksheet tab for other exporting methods (this is method 3 on that page).

Detailed steps for publishing data in a worksheet tab

In the bottom right of the worksheet tab, click the Publish button.

First, select the publish method (see above for details):

  • Publish to new stream: Select this option, then type a name for the new stream.

  • Publish to existing stream: Select this option, then select the stream from the list.

  • Export to Excel: Select this option, then type a name for the file.

Next, select the required publishing options.

Option
Description

Only publish completed rows

This option only publishes the rows marked as completed in the workflow.

When selected, it allows you to publish what you are confident are the final budget values and ensures that incomplete budget values do not appear in the Financial Statements or Analytics modules until they are completed.

This option is also suitable if you are working on a particular section of the budget, such as Revenue, and you only want to publish that section, as that’s all you are interested in.

Include zero value rows

This option publishes all rows in the worksheet, including those that have zero values (0.00) in all their cells. It is selected by default.

If you don’t want to include the rows with zero values, clear the checkbox. The exclusion of zero-value rows from the published dataset is useful if you are exporting to Microsoft Excel, as there will be no redundant, zero-only rows.

Delete all existing records from the published stream that match the budget period

This option is available when these two criteria are met:

  • You have already published the budget to a stream; in other words, there's an existing stream.

  • You have applied a dimension entity filter in the budget setup.

When selected, it overrides the default publishing behavior, so all the values in the stream (that match the workbook period) are deleted and replaced. This is useful if you are using the filters to remove dimension entities from your budget. For example, you might want to remove products that you no longer sell by filtering them out in the workbook setup. If you have already published the workbook with the products included and then filter them out, you will not remove them with the subsequent publish. In this scenario, the checkbox can be selected to delete the existing records. Please note that this checkbox doesn't override the behaviour for a single entity filter and isn't available when a single entity filter is the only filter applied in the setup.

Publish to days

This option overrides the default behavior whereby the budget period value is published into the first day of that period. It splits the budget period value proportionally by the number of days in the budget period and publishes the data to each day in the period. Select the checkbox, then select one of these options to calculate the number of days:

  • Calendar Days: The number of days in each period in the budget. For example, if you have a monthly budget, you will get 30 days for November, 31 days for December and so on.

  • Week Days: The total count of the number of Monday through to Friday days in the budget period. For example, you will see 20 days in the months that have 4 weeks and 25 days for the months that have 5 weeks.

  • Working Days (calendar): The number of days based on a selected predefined working day calendar. For example, you might have a calendar for your region that excludes public holidays. See example 1 below to learn more about this option.

  • Months: Workbooks where the period type is Year include the option to publish to months.

Publish date range

This option publishes values for a particular date range and is useful in the following scenarios:

  • When publishing very large workbooks, publishing can take a long time and may fail due to the server becoming overloaded. In this scenario publishing only the periods that have been amended or breaking the budget up into smaller increments can help (for example publishing by quarter).

  • When reforecasting, you might want to replace the remaining periods in the year (forecast periods) and not publish the periods populated with actuals.

Publish to measures

This option is available when publishing the Headcount Output tab to a statistical stream.

Select the measures you want to publish the FTE and Headcount data to.

Then click the Publish button.

  • If you exported to Excel, you can proceed to open the file to view its contents. Save the fie to your computer so you can access it later. For example, you might want to import it back into Phocas someday.

  • If you published to a stream, a success message displays at the top of your screen. Click the Open link to view the published stream in the underlying database, where you can analyze the data immediately. Operational databases, such as Sales, open in the Analytics module, whereas financial databases open in the Financial Statements module. The stream is opened in the same configuration as the budget, including any levels, dimension filters, budget filters, and budget period, as in the following image.

Example: Publish a budget to a working days calendar in an operational database and analyze the data immediately

Suppose you want to publish your Sales budget to an existing stream based on a working day calendar. In the working day calendar, Saturdays and Sundays are non-working days, and Friday 3 March is a half day, as illustrated in the image below.

Click the Publish button on the Main tab, then select the stream, select Publish to days > Working Days (and select a calendar) and click Publish.

Click the link in the success message to open the stream in the Sales database, which in this case, opens in the Analytics module.

In the Analytics module, select Period > Days. You will see that the data is published only to the days that are worked in that working days calendar. Saturdays and Sundays have zero values and are highlighted blue and Friday 3 March has half the value of the other days, as only a half day was worked.

If you publish a budget or forecast to a financial database, you need to add the stream to the Financial Statements module (see below) so you and other users can view it later.

View a published stream in Analytics or Financial Statements

The underlying database determines how you view and analyze the published stream. Budgets & Forecasts data published from operational budgets, such as Sales, opens in the Analytics module, whereas data published from financial budgets opens in the Financial Statements module.

You can analyze a published stream straight after you publish it using the Open link in the success message at the top of your screen.

Afterward, you and other users can view the stream at any time. The process is a little different, depending on the type of database (module):

  • Operational database: Open the database (it opens in Analytics) and select the stream from the Stream menu. See Analytics > Change the stream.

  • Financial database: Open the database (it opens in Financial Statements) and select the stream from the Streams menu. See Financial Statements > Change the streams and scenarios. You can also add the budget stream to a scenario, which is a combination of streams, sometimes called a package of data. See Manage streams and scenarios.

Last updated

Was this helpful?