Best practices for efficient budgeting

Get solutions and knowledge of best practice to help you avoid performance issues and improve your budgeting processes and user experience.

The creation of a budget workbook is quick, especially if you use the one-click method, so it's tempting to overlook some of the settings which have a huge impact on the workbook's performance. Often this impact isn't known until much later in the workflow, at which time it's too late to make structural changes without losing your manually-entered budget data.

To create an efficient budget and avoid issues later on, it's important you read and understand the concepts outlined on this page before you create the budget.

If you've already created a budget and are experiencing performance issues, the information on this page will help you to troubleshoot issues and take action to make your workbooks more efficient. However, keep in mind that implementing some of the solutions might lead to the loss of budget data.

Improving performance isn't just about speed; it's about reducing complexity and making things easier for users to understand (especially new users), reducing the risk of mistakes, and increasing user engagement in the budgeting process.

For complex workbook or publishing issues, use the Upgrades and services form to request help from one of our specialists.

Root causes of poor performance

Budget and forecast workbook performance issues are often caused by large amounts of data, millions of cells, being loaded into the browser. The more levels, tabs, measures and comparative data you have in the workbook, the more data you have overall. The presence of a large numbers of formulas is also problematic, as they need to be updated every time a change is made.

The browser has a limit, and if that limit is exceeded, you'll experience issues such as:

• It takes a long time to open (load) a workbook or specific worksheet, or it doesn't open at all

• The workbook doesn't respond to your actions or apply your changes quickly

• Scrolling or navigating around the worksheet results in delays

• You've reached the memory limit the browser can handle, so the browser crashes

The main reasons for large volumes of data in one workbook are:

• Multiple levels in the worksheet hierarchy

• Multiple worksheet tabs

• The workbook includes customers, products, or other other entities that are no longer used

• Excessive comparative data

• Inefficient baseline usage

• Workflow at a lower level than is necessary

For example, suppose you have 50,000 customers and 100,000 products and you create a monthly financial budget with two levels, in which the Sales values are driven by a Database tab. 50,000 customers x 100,000 products x 12 periods x two worksheets = a lot of data.

Suggested solutions

If you haven’t created a budget yet, keep all of these solutions in mind when designing your budget structure and consider how users will use the budget later.

If you’ve already created a budget, review how you're currently budgeting and look for feasible ways to reduce the amount of data in the workbook. Start by trying the suggestions in the quick wins section, then consider making more substantial changes to your workbooks.

1. Quick wins for existing workbooks

To quickly limit the number of cells and data volume in a workbook:

• Use session filters: Open only a subset of the workbook data to substantially reduce the loading time, view only necessary data and get straight to work on the relevant items.
• Flatten the worksheet hierarchy: Do you really need all the dimension levels in the worksheet? Each time you expand a level, the browser needs to load more rows of data. Removing excess levels from the workbook setup instantly reduces the amount of data in a worksheet.
• Reduce the number of worksheet tabs: Do you really need all the worksheet tabs in this workbook? Could you move some of this data elsewhere? Each worksheet tab multiplies the amount of data you have, so removing a tab instantly reduces the amount of data in the workbook.

  • Database tabs often account for a high proportion of the data in a workbook as they contain comparative data and formulas that need to be recalculated frequently, requiring more memory usage.

  • Unused tabs still contribute to the overall data size and consume memory. Deleting redundant tabs is an easy way to remove excess data.

  • If your financial budget contains a Database tab for your operational budget or vice versa, consider separating those budgets into two workbooks (see below).

  • If you're worried about deleting a tab, clone the workbook as a backup.

2. Budget creation: Consolidated workbook approach

You might think it makes sense to have all of your budgeting data in one place, one workbook. Often, however, this isn't the case, as it requires the need for multiple dimension levels in the budget hierarchy. In some scenarios, you don't need all the levels, so including them causes more harm than good.

Solution

Before creating a budget workbook, rather than have all your budgeting data in one workbook, consider using a consolidated workbook approach. It involves splitting the planned budget up into multiple workbooks (subsets of data), carrying out the budgeting process in those individual workbooks, then bringing it all back together at the end. The consolidated workbook approach involves the use several filtered workbooks that are all published into one stream. As a result, you substantially reduce the amount of data in each workbook, as each one has only the levels and tabs it needs.

Example

Suppose you budget at the Product level for your 10 largest customers, at the Customer level for all of your trade customers and at the Region level for your internet sales. If you use one workbook for your budgeting, you'll be forced to budget at the Product level for all customers and sales channels. You can optimize your budgeting process by having a separate workbook for each specific sales type, then bring them all together in one consolidated stream.

How it works

First, create a consolidated workbook to use as a template for publishing your budget. This workbook has all the levels and measures you need, but only a subset of the data. It doesn’t need any driver tabs, as they'll be included in other the workbooks. When you publish this workbook, you create one stream into which you can publish the data from the other workbooks. It's possible to publish to a stream that has more levels or measures than the workbook being published, however, this isn't possible where the workbook includes dimensions or measure not included in the published stream.

Next, create multiple workbooks that are a subset of the consolidated workbook. Each of these workbooks has a dimension filter applied to serve a specific purpose or scenario and contains only the relevant levels, comparative data and worksheet tabs. To continue with the example above, the large customer workbook would include the Customer and Product dimensions and would be filtered to specific customers. The Product dimension wouldn't be included in the other workbooks thus reducing the data requirement and complexity, and ultimately improving efficiency.

Then, as each workbook is completed, it can be published into the consolidated stream. The consolidated stream can then be viewed and analyzed in the Financial Statements module. Alternatively, the consolidated stream can be viewed in the Budgets & Forecasts module by setting it as the baseline stream in the consolidated workbook that was created earlier in the process.

3. Budget creation: Separate operational and financial budget workbooks

A common use case for a Database tab is to bring in your operational budget to drive values in your Main tab. If the budget in the Database tab is being actively worked alongside the budget in the Main tab, you effectively have one budget inside another, resulting in a complex, difficult to manage workbook and the potential for poor performance.

Solution

It's better to create and maintain distinct operational and financial budget workbooks. Not only will this help improve performance, it'll prevent interdependencies, and improves access control.

For example, the people responsible for the Sales budget (such as sales managers or sales reps) are quite different from the people responsible for your finance budget (such as department managers or CFO). Also, the finance budget is likely to have tab for a Balance Sheet, Cash Flow, Head Count and so on, but such tabs are not required in a Sales budget. Therefore, it makes sense to separate these budgets into two workbooks, each containing only the relevant tabs, levels and so on.

4. Budget setup: General tips

The workbook setup contains many options (settings) to allow for flexibility and let you create a custom workbook that meets your organization's budgeting needs. However, if you expect your budget will contain a large volume of data, you consider the following points:

Solutions

• Hide the nested calculations: In financial workbooks, calculation rows are shown by default but you should hide them when they're not needed. This removes multiple expandable rows of data from the Main tab and prevents unnecessary calculations at multiple levels.
• Reduce the number of measures: The inclusion of additional measures in operational budgets and in Database and Reference worksheet tabs might be required for some line items but not necessarily for all items in the worksheet. For example, you can add the Quantity and Price measures to calculate the Sales value for your products. If you have 100,000 products, that's a lot of additional rows. Do you really need to do these calculations right here in the budget? If you reduce the number of measures, you reduce the number of rows.
• Use a fixed baseline: When you use a live stream as the baseline, the values are subject to change. If those values are used in formulas, the formulas need to recalculate frequently. If you switch to a fixed baseline, that dynamic data is replaced with fixed (static) values, reducing memory load and removing the recalculation effort. There are several ways to fix the baseline (copy and paste, spread, and publish).
• Filter the budget: Applying a level filter or dimension entity filter in the budget setup results in a subset of the data being loaded. These filters are used in the consolidated workbook approach explained above.

5. Budget setup: Workflow at a higher level

There's a separate workflow for most types of worksheet tab and by default each one sits at the lowest level in their respective hierarchies. When the workflow is at a low level, such as the product level, it might apply to thousands of rows. This means the status, comments and other information needs to be stored against all of those rows.

Solution

Move the workflow to a higher level in the hierarchy in the budget setup. This will reduce the workflow management overhead, as there are less submissions and approvals, there are fewer statuses to update, and there may be less duplication in terms of commentary. For example, if you move the workflow from the Product level up to the Sales Rep level, it might apply to 50 rows instead of 1,000, substantially reducing the memory load and administration cost.

6. Comparative data: Less is more

Even if you setup up the budget in the most efficient way, it's possible to add more data into the workbook later and introduce performance issues. The addition of comparative or historical data in a workbook can be very helpful. For example, you might want to look back on the budgets in the previous two years. However, the presence of multiple streams of additional data substantially increases the data load and reduces the performance of the workbook. This is especially true when the comparatives are used in formulas and Lookup tabs across multiple periods and worksheets. It's important to remember that formulas are constantly being evaluated and therefore, a large number of formulas will have an impact on performance of your workbook.

Solutions

• Only add the comparative data you need to enter the budget values. If you don’t need the comparative figure to reference in a formula, consider removing it.

  
• Use the Analytics module to view and analyze comparative data. You can create favorites with the same hierarchy as the budget to quickly reference the data. Keep the Analytics grid open on another screen while you’re entering the budget values.

  
• If you believe you need all the comparative data and related formulas, consider switching to a fixed baseline (see general tips section above).

7. Publishing tips

Like the workbook setup, the publishing window contains many options to allow for flexibility. However, some of these settings are not optimal in workbooks with large volumes of data.

Solution

• Don't include zero value rows: This option is selected by default, but if you clear the checkbox, you'll remove all rows in the worksheet that have zero values (0.00) in all their cells.

• Don't publish to days: Avoid publishing daily data where possible, as this breaks down the cumulative period data into individual days, increasing the total amount of data to be published. If you need to publish daily data, and the publish is crashing then you can try using the Publish date range option to publish the data in batches.

• Publish to a new stream: If you're re-publishing a filtered worksheet into an existing stream and it is taking a long time, it will likely be quicker to delete the original stream and publish the worksheet as a new stream. The publish to existing stream logic for filtered worksheets is complicated, as each row is assessed to see if it meets the filter criteria. Note that this delete and replace solution will not work if you are trying to keep the existing published data.