Balance Sheet and Cash Flow tabs

Add a worksheet tab in your Profit and Loss budget workbook for your Balance Sheet budget and automatically derive your Cash Flow budget for 3-statement budgeting.

User permissions: Budgets & Forecasts and Manage Budgets & Forecasts

Available in financial budgets only, this worksheet tab contains your Balance Sheet budget. When adding this tab, you can include the following:

• A Cash Flow budget: This is automatically derived and displayed in a separate worksheet tab. Including the Profit and Loss, Balance Sheet, and Cash Flow budgets in one budget workbook creates a 3-statement budgeting model.

• Mini drivers: These additional rows in the worksheet tab represent the links between your statements when you use 3-statement budgeting. They reference your Profit and Loss (P&L) accounts to forecast or budget your Balance Sheet items so your Cash Flow statement better reflects reality. They are called mini drivers because they have a similar purpose to a driver tab, but they are for use only in the Balance Sheet tab.

Add the Balance Sheet and Cash Flow tabs

Watch this video (the UI is out of date but the context is the same) or follow the steps below.

Watch these videos:

• Phocas demo: 3 statement forecasting
• PUG Training: 3-statement budgeting and Tracking performance + re-forecasting

First, create or open the financial budget workbook. Next, either click the Budget menu > Add tab or click the Add tab button (at the bottom of the workbook), then select the Balance Sheet tab type. Proceed to set up the Balance Sheet and Cash Flow tabs as follows:

1. Create Balance Sheet (configuration)

Balance Sheet

1. Select the Balance Sheet statement.

   • This setting determines the layout of the Balance Sheet budget.

   • A default statement is selected for you but you might have others to choose from if your organization has created them in the Financial Statements module.

2. Show or hide nested calculations as required. This setting determines whether nested calculation rows are shown in the worksheet or not. These calculation rows come from the selected statement. By default, Show is selected, which means the calculation rows in the budget are expandable and you can see the nested derived rows underneath. If that's not desirable, select Hide to remove these rows and only see the top-level calculation values.
3. Set the Balance Sheet budget hierarchy levels.

   • By default, the first level is the top-level group (category) for the Balance Sheet statement, and you can't change it.

   • You can't add levels here if there are no other levels in the P&L budget setup.

   • Where levels are available, you can have fewer levels in your Balance Sheet than are in your P&L. For example, you might want to do your Balance Sheet and Cash Flow budgets at the company level but include your company and branch in your P&L.

   • To add a level, click Add level, then select the dimension you want to add at that level. The dimensions that are available in the list correspond to the levels in the P&L budget setup.

   • If you add multiple levels, you can reorder those levels to determine how the dimensions are grouped in your Balance Sheet budget. Click and hold the level's Move button (a blue box displays around the row), then drag the level up or down to its new position.

Opening Balance

1. Select the opening balance stream. This is the stream from which your Balance Sheet values come. In most cases, this is the Balance Sheet stream but you might have other options to choose from.

2. (Optional) Change the opening balance start date (previous period end date).

   • By default, this date is selected for you, as it is linked to the start date of your P&L budget setup. For example, if your budget period is April 2023 to March 2024, the Balance Sheet opening balance date is the month before that period, March 2023.

   • This date must be on or before your Balance Sheet budget period start date, as it informs the initial opening balance for your Balance Sheet budget and enables changes (movements) to be entered to create a sensible budget opening position. When the Balance Sheet budget starts in a future month, the opening balance helps you to form the bridge between the last closed period and the beginning of the budget period.

   • You can change this date in the future. For example, when you're in the budget period, you can actualize your Balance Sheet by bringing in the actual opening position for that budget period.

1. (Optional) Change the Measure. By default, the measure is selected for you, as it is linked to the stream you selected in step 5. The measure is typically the same as the measure used in the P&L budget.

2. Set the measure format (prefix, decimal, and suffix) to determine how the measures (numerical values) display in the grid.

Cash Flow (optional)

Select the Include tab checkbox to include a Cash Flow budget alongside the Balance Sheet budget, then select the statement and set the levels as you did for the Balance Sheet.

• The Cash Flow budget will display on a separate tab in the workbook.

• The values in the Cash Flow budget are automatically derived from the values in the P&L and Balance Sheet budgets. The budget hierarchy levels are inherited from the Balance Sheet budget but if available, you can add custom levels that will help you to group your Cash Flow statement.

Drivers (optional)

Select the Include mini drivers… checkbox to add drivers to the Balance Sheet. For more information about this feature, see Use mini drivers section below.

Click Next.

2. Drivers (optional)

This step is applicable if you selected the Include mini drivers… checkbox in the previous step. Otherwise, you can ignore it and proceed to the Retained Earnings mapping step.

You can add four types of drivers. This part of the setup can be complex, so see the Use mini drivers section (below) for detailed information.

After you add the drivers, click Next.

3. Retained earnings mapping

Map the entities in the selected dimension to the Retained Earnings account(s).

• This setting determines the General Ledger Retained Earnings account code(s) in the Balance Sheet budget to which the profit from the P&L budget is allocated. Retained Earnings represent the equity of the business (what the business is worth).

• If you have no levels in the budget, such as in the case of a consolidated Balance Sheet budget, you will only have one Retained Earnings account to map. Otherwise, you might have multiple accounts to map. Depending on your account structure, you can use the same Retained Earnings account for several entities or use different accounts as required.

• If you have a lot of entities, you can filter the list of entities and/or select the Not mapped option from the All dropdown list on the top right to help you complete the mapping.

  
• To map one entity, click the yellow box on the right and select the account from the list that displays.

• To map multiple entities to one account, select the checkboxes of those entities or click the Select all button in the top left corner of the grid, then select the account from the list that displays.

  

Click Next.

4. Cash account mapping

Map the entities in the selected dimension to the Cash account(s).

• This setting determines the General Ledger account code(s) in the Balance Sheet budget that captures all the changes to cash.

• Depending on your account structure, you can use the same General Ledger Bank account code for several entities or use different accounts as required. Typically, you use a separate bank account for each entity. If you have no levels, such as in the case of a consolidated Balance Sheet budget, you will only have one bank account to map.

• Map entities on the left to the Cash account code on the right as you mapped to the Retained Earnings accounts. The bank and/or cash accounts display at the top of the list (marked with a bank icon).

  

Click Save and finish. Proceed to explore your new Balance Sheet and Cash Flow budgets, which are displayed in separate worksheet tabs.

---

Use mini drivers in the Balance Sheet budget

Several out-of-the-box templates cover common scenarios for debtors (accounts receivable), creditors (accounts payable), stock, and loans. There’s also a custom option that allows you to create your own driver from scratch. These templates have pre-built lines with basic inputs for a calculation, allowing you to quickly model common interactions between your Profit and Loss (P&L) and Balance Sheet items without having to manually enter formulas.

Watch this video for an introduction to mini drivers (the UI is out of date but the context is the same).

These steps follow from step 2. Drivers above. If you've already added the tab, you can open the setup to include the drivers. Right-click the Balance Sheet tab and click Edit drivers setup.

1. On the Drivers tab, click + Add driver in the bottom-right corner and select the required template: Debtor Days, Creditors, Stock, Loan - New, Loan - Existing, or Custom. See below for detailed information on each template.

   
2. Add a name for the driver. This name is just for reference purposes here, it doesn't get carried through to the Balance Sheet.

3. Select the account you want to drive. This is the account in the Balance Sheet whose values will be automatically driven. The driver lines will display underneath this account row. You can only select one account per mini driver, and you can't select a Bank or Retained Earnings account.

4. Click the Expand button to view the driver’s default components (lines). See below for detailed information on each one.

5. Update the lines as needed, then update the calculation to reflect your changes.

6. Click Save and finish.

7. Proceed to explore the mini driver lines under the applicable accounts in the Balance Sheet tab.

Debtor Days (Accounts Receivable)

Debtor Days is the most commonly used mini driver. It allows you to predict how your debtors are going to behave based on the level of sales you have in a given period.

Learn more...

The Debtor Days template has the following components:

Calculation: (P&L Movement/Days()) * Accounts Receivable Days, where:

• Days = Accounts Receivable Days. The number of days it takes to receive payment from your customers.

• Reference = The P&L Movement; the accounts in your P&L that drive your accounts receivable. In other words, the accounts from which you are going to get the sales that determine your debtor balance.

For example, suppose you typically collect debtors every 22 days and this is based on the values in your Sales (Retail) and Sales (Online) accounts. You can customize the Debtor Days template to meet your needs, as illustrated in the following image.

In the Balance Sheet, when you expand the Accounts Receivable account row, you can see the mini driver lines. When you look at the values, you can see the movement in your debtor balance, which reflects when there are higher and lower sales.

Watch this video for a demonstration of adding the Debtor Days mini driver.

Creditors (Accounts Payable)

The Creditor Days mini driver works similarly to the Debtor Days except that it applies to costs.

Learn more...

The Creditor template has the following components:

Calculation: (P&L Movement/Days()) * Accounts Payable Days, where:

• Days = Accounts Payable Days. The number of days it takes to pay your suppliers.

• Reference = The P&L Movement; the accounts in your P&L that drive your accounts payable. In the case of creditor days, you need to identify the accounts from which you are going to determine your creditor balance.

For example, suppose you typically pay your creditors every 40 days and this is based on the values in your Purchases and Expenses accounts that are paid on credit terms. You can customize the Creditor Days template to meet your needs, as illustrated in the following image.

In the Balance Sheet, when you expand the Accounts Payable account row, you can see the mini driver lines. When you look at the values, you can see the movement in your creditor balance, which reflects when there are higher and lower purchases and expenses.

Watch this video for a demonstration of adding the Creditor Days mini driver.

Stock (inventory)

The Stock Turnover mini driver has a slightly different calculation to that of the Debtor Days and Creditor Days.

Learn more...

The Stock template has the following components:

Calculation: (P&L Movement * Stock Days) / Days(), where:

• Days = Stock Days. The number of days it takes to sell stock.

• Reference = The P&L Movement; the accounts in your P&L that drive your accounts payable.. In the case of stock days, you need to identify the accounts from which you are going to determine your stock balance, which is typically your Cost of Sales.

For example, suppose you typically turn over stock every 32 days and this is based on the values in your Cost of Sales accounts. You also need to make adjustments for seasonality impacts throughout the year. You can customize the Stock Days template to meet your needs, as illustrated in the following image.

In the Balance Sheet, when you expand the Inventory account row, you can see the mini driver lines. When you look at the values, you can see the movement in your stock balance, which reflects when there are higher and lower cost of sales. The adjustment line is empty, ready for when you need to manually enter values.

Watch this video for a demonstration of adding the Stock mini driver.

Loan (amortization)

The loan mini drivers allow you to calculate interest payments and principal values for loans in your Balance Sheet. You can calculate the payment for a new loan, and also the interest and payment amounts, and the principal for an existing loan.

There are two loan templates you can use to calculate loan amortization. Both templates take an initial loan value, add the interest cost, and then take away the payment amount to get to an opening balance for the next month. Over time, you get back to a zero value.

Points to note about this mini driver:

• If there are multiple dimensions (levels) in the Balance Sheet, such as Region and Branch, and these dimensions share one loan account, this driver will create multiple loans, one for each dimension. If that's not what you want, you can:

  • Ensure you have a unique account code for each loan.

  • Ensure the account only appears under the dimension you want to drive. If this isn't the case, delete the duplicate account.

• Current limitations that might change in the future:

  • This mini driver only works for loans with regular monthly payments or where the repayment schedule can be approximated. The calculations can be edited to work for other repayment schedules.

  • There can only be one mini driver per loan account.

Existing loan template

The Loan - Existing template takes an opening loan balance, adds the interest on a periodic basis, and reduces the loan by the repayment amount.

Complete the following fields:

• Annual interest rate: Enter the interest rate for the loan.

• Recurring payment: The amount you are repaying each period.

Watch this video for a demonstration of adding a mini driver for an existing loan. It also shows how you can use the interest cost to drive the Interest account in the Profit and Loss.

New loan template

The Loan - New template uses the principle, interest rate, and number of periods to calculate the repayment amount. It then takes the principle, adds the interest on a periodic basis, and reduces the loan by the repayment amount.

Complete the following fields:

• Annual interest rate: Enter the interest rate for the loan.

• Term: Enter the number of payment periods over the life of the loan. The template is set up to work for monthly payments. If you need to calculate other periodic payments, you'll need to adjust the calculation.

• Principle: Enter the amount of the loan.

• Payment timing (0-end, 1-beginning): Enter 0 or 1 as applicable to state whether the payments are at the beginning or end of the period.

• Counter: Enter the start period. For example, if the budget period starts in January and the loan is drawn down in March, enter 3.

Watch this video for a demonstration of adding a mini driver for a new loan.

Custom mini driver

The custom option is simply a blank calculation from which you can build your own mini driver using a range of line types.

Before:

After (example):

This video shows how to add a mini driver to better drive non-current borrowings (long term loans). This driver contains the following lines: Calculation, Opening, Reference, and Adjustment.

Mini driver lines (components)

By default, each driver contains a calculation that drives the selected account row in the Balance Sheet. Each template, except for Custom, has prebuilt lines that are referenced in that calculation. You can edit those lines and add more to update the calculation. These lines are visible in the Balance Sheet when you expand the account row. In the case of a Custom template, you can use a range of lines to build your custom calculation.

The following table has a brief description of each line type. Watch the video in the custom mini driver section above to learn more.

Line type	Description
Calculation (header or parent)	Every mini driver includes a calculation line by default. The calculation box contains a formula that can reference the other lines (variables). The resulting value of the calculation is included as the balance of the driven account in the Balance Sheet.
Calculation (sub or child)	You can add more calculation lines to a mini driver for sub-calculations or to calculate sub-totals for reference purposes. For example, you can use this line to calculate the interest cost in a term loan calculation, where the header calculation determines the balance of the loan but the interest cost calculation drives the Profit and Loss interest expense.
Opening (balance)	This line returns the balance of the reference account from the prior period. This is often the starting position for cumulative calculations. For example, you can use an opening balance for an existing term loan, where you calculate the interest cost and principal reduction given the opening balance, repayments and interest rate.
Adjustment	This line inserts a working row under the referenced account in the Balance Sheet, in which you can enter values where there is variability across periods. For example, you can use an adjustment row to include adjustments for seasonality and loan drawdowns or repayments. Here in the mini driver setup, this line can be referenced in the calculation lines but it's not editable.
Reference	This line allows you to select to one or more Profit and Loss accounts from your Main tab to display in your Balance Sheet, for use in a calculation. In the case of multiple accounts, the sum of those accounts is displayed. Click Add/Edit References, then locate and select the account(s) you want to reference. The Referenced count increases accordingly to let you know how many accounts are being referenced.
Days	This line captures the number of days for use in a calculation. It's like the Adjustment line in that it inserts a working row under the referenced account in the Balance Sheet, however, you can enter the initial value for this line here in the mini driver setup. This line is intended to be a key element of the Debtors, Creditors, and Stock Days calculations. For example, it might be the number of days it takes to receive payment from your customers, pay suppliers, or sell stock.
Constant	This line is used in the Loan mini driver.
Period	This line is used in the Loan mini driver.
Counter	This line is used in the Loan mini driver.