Overview
Introduction
Important: Note this guide now is out of date, as these management accounts have been updated further to comply with some of the new and revised I.F.R.S. rules.
Running a company requires that you keep an active book of records, so you can keep track of its affairs. In practice, this usually means maintaining a set of what are called ‘Management Accounts’ – for recording the company’s financial transactions, and to help track its financial performance and position.
Such financial records can be maintained in well-known proprietary accounting software, such as: XERO, QuickBooks, and SAGE. However if you prefer the fluidity and flexibility of something like M.S. Excel, then that is also possible.
Maintaining records in M.S. Excel based Templates, rather than in Accounting softwares, has certain advantages.
Namely, the ability:
- To customise various output pages, for any Accounting rule changes (which they often do);
- To integrate the financial data with a wide eco-system of other softwares and E.R.P.s, with the potential to connect the data to M.L., and A.I. tools.
- To connect the data more easily to remote locations, such as data servers hosted and maintained by Cloud Service providers (like M.S. Azure, and Amazon A.W.S.)
If your data is in an M.S. Excel spreadsheet, it can be possible to do a lot more with it, compared to off-the-shelf accounting packages - that may have poor external connection capabilities, and limited data export features.
The goal of this guide, therefore, is to show how it is possible to create a fully workable set of Management Accounts, all inside M.S. Excel.
In this guide, we will cover the main components of this Template, including ‘Tabs’ that show: the Profit & Loss, Balance Sheet, and Cash Flow situation, and also how one could handle Tax, and the various aspects Accrual-based accounting, such as: Accrued Expenses, Prepayments, Accrued Revenue, and Deferred Income.
Important: This guide assumes that you are already quite familiar with the concept of duality in finance – that is, the required pairing of parties to a financial transaction.
That is, just as to every buyer, there is a seller; or to every receiver, there is a giver; in financial record-keeping, to every debit, there is always a matching credit (of some kind). So, if you record a transaction in one place, it will have an equal and opposite effect somewhere else - no exceptions!
If you’re still a little unsure about the concept, try searching for the term “duality principle in finance” on your favourite search engine on the internet.
Management Account Template
A link to the example management accounts illustrated throughout this guide can be found here:
Worksheets
We have summarised the main purpose and function of each tab in the workbook below:
| Name of tab | Purpose & Function |
|---|---|
| Index | Menu page that allows you to directly navitage to the other tabs |
| Validation | Page for keeping all data validation lists, e.g. used in drop down menus, elsewhere in the workbook |
| Accruals | Page that calculates the accrued amount of transactions, based on their start and end date |
| Deferrals | Page that calculates the deferred amount of transactions, based on their start and end date |
| Combined | Page that combines the data in the ‘Accruals’ and ‘Deferrals’ tabs |
| Mnth 1: Mnth 12 | Tabs for recording (almost) all daily and monthly transactions |
| Trans. Line Analysis | Page that summarises all transactional data across the tabs: Mnth1 to Mnth 12 |
| Statutory Statements | Page that shows a set of abridged statutory accounts. Data is pulled from across the workbook |
| Balance Sheet | Page that shows the financial position of the company |
| Profit & Loss Report | Page that shows the financial performance of the company |
| Cash Flow Reporting | Page for showing the main operating, investment, and financing cashflows |
| Corporation Tax | Page for carrying out simple Corporation Tax calculations |
| Trial Balance | Page for checking that all Debit and Credit accounts match off and net to nil |
| Payroll | Page for calculating all Payroll payments, and PAYE & NICs obligations |
| Bank Cash | Page that shows the monthly opening and closing cash balances |
| Customer & Supplier Balances | Page that shows all Debtor and Creditor balances |
| Aged Receivables | Page that shows the movement in all Receivables |
| Aged Payables | Page that shows the movement in all Payables |
| Stock Register | Page for recording the position and movement of any Inventory |
| VAT Returns | Page for helping file any Quarterly V.A.T. returns |
| Fixed Asset Schedule | Page for showing the position and movement, e.g. depreciation, of any Fixed Assets |
| Other Transactions | Page for recording any miscellaneous journals, not recorded in the tabs: Mnth1 to Mnth 12 |
| Working Papers | Page for recording and tracking the movement in all the major T-accounts |
The rest of this guide goes through each Tab in further detail.
Click ahead to find out more.
Feedback
Submit and view feedback