Top tips for academy trusts budgeting in excel

If you are an academy trust and prepare your budgets using excel spreadsheets, we have highlighted tips and techniques for you to consider which are intended to help you respond to the refined requirements of the ESFA’s Academies Financial Handbook 2018

Transparency

Keep it simple: transparency is key. As the trustees are required to review the budgets and monitor them against actual results, they will need to be comfortable with the reported numbers. This requires transparent budgeting, so that they can understand the assumptions easily and assess the reasonableness. A transparent model is evident from its overall quality, usability and auditability. It is more robust to keep all underlying analysis within one workbook, so that it can be referenced easily to inform the output. This makes the data easier to follow and reduces risk of corruption due to use of links to external files.

Separation

Input areas should be separated and clearly formatted to avoid over-typing formulae which can create errors and unexpected results. It is important that an individual cell contains only data or a formula, never both. Without the use of specialist spreadsheet analysis tools, formula including hardcoded values are difficult to identify which may cause problems if the person responsible for the budget changes as the hardcoded value may remain and may not be reviewed / updated. Cell formatting should be used across all spreadsheets so that it is clear which cells are inputs and which are calculations. A ‘house style’ could be developed which assists with visual distinction and provide clarity.

Protection and validation

If protection is applied to worksheets, this will prevent over-typing of formula and unintended errors being introduced. Data validation can also be used to restrict both the types and ranges of input data that a user can input into a particular cell.

Consistency

As you are now required to prepare budgets covering a three year period as opposed to one year in duration, in order to reduce risk of error in the underlying spreadsheets, a timeline approach should be used with a consistent formula applied across each row. Supported by appropriate formatting, this reduces risk of error within your spreadsheets. It also supports ease of roll forward to future years, and facilitates the flow of changes to assumptions, for example growth assumptions or capital spend programmes.

Integrity

Error checking can be applied to assist with identifying potential errors appearing within spreadsheets. An example of a simple check is to ensure that the balance sheet balances, or that percentage allocations equal 100 per cent if costs are being allocated. We recommend automating as much of the process as possible (to avoid manual data entry) to improve efficiency and reliability of the results.

Linearity

Linearity is helpful, i.e. calculations should flow through the workbook from top to bottom and from left to right, as if reading a book.

These top tips for budgeting are based on the financial modelling best practice guideline that was authored by the RSM financial modelling team and adopted by the ICAEW. For a copy of this, or for more information, please get in touch with Clare Cowie