Use formulas

You can enter simple formulas in the budget worksheet. These formulas can reference cells in different worksheets, working rows or comparison rows. Rather than including more complex spreadsheet functions, Budgets and Forecasts keeps a level of simplicity by allowing for basic arithmetic in these formulas.

Enter a basic formula

In Budgeting & Forecasting, you enter formulas in a similar way to how you do it in Microsoft Excel or Google Sheets - you can enter a formula either directly into a cell or in the fx (formula) bar at the top of the grid when the cell is selected.

It is easy to identify and understand formulas in Budgets and Forecasts. When a cell contains a formula, the cell value has a blue fx indicator next to it. You can hover over this indicator to view the formula or click a cell and view the formula in the fx bar.

Use cell references in formulas

To enter more advanced formulas, you can refer to values in other cells, just as you would do in Microsoft Excel. In Budgets and Forecasts you can refer to cells in the same worksheet, including comparison, sum and working lines, and other worksheets (driver tabs). When you view a formula in the fx bar, a description of the referenced cell displays, along with a colored dot corresponding to that cell. So you can easily see what you’re calculating, rather than just a reference to a cell number.

Copy and paste formulas

To save time, you can copy and paste budget values and this includes formulas. When you copy a formula, the pasted formula might be absolute or relative reference, depending on the situation:

• Absolute referencing occurs when you copy forward a cell in the Total column is absolute, i.e. the formula will always reference the total.

• Relative referencing occurs in the following situations:

  • When you copy forward a cell, the formulas are pasted into the target cells are relative, i.e. if a cell references January data, when you copy the cell to February, it will reference February data.

  • When you copy a formula that references a comparison row, the formula you paste into the target cell(s) is relative. This applies both to formulas copied to the same row and to different rows from the original cell.

  • When you copy a formula that references a working row, the formula you paste into the target cell(s) is relative. For example, if the original cell has two working rows and a formula references both of those rows, copying the formula to a cell which has two or more working rows will result in a relative formula being pasted into the target cells. The target cells need to have the same working row offset.

  • When you copy a formula that references a cell in one level, the formula you paste into the target cell, in a different entity in the same level, is relative. For example, if you copy a formula in Branch A’s Cost of Goods Sold, which references sales in Branch A, and paste it into Branch B’s Cost of Goods Sold, the formula will reference to Branch B’s sales.

Examples

The following examples might be useful in your budget.

Reference a cell within the same worksheet

Your IT Computer Costs are a function of wages. In the formula below, the IT costs are 2% of AU wages.

Reference a cell in another worksheet

You added a Manual Entry Driver tab (which is named Assumptions) in which you entered an AUD exchange rate. You want to use that rate to calculate the budgeted cost of the Australian Conference. In the formula below, the Conference - AUD value is multiplied by the exchange rate in the Assumptions tab.

Reference a cell in a comparison row

You want the Sales budget values for this year to be ten percent higher than last year’s actuals. In the formula below, the April 2021 sales value is made up of the actual value from 2021 plus ten percent.