How to produce monthly management accounts?
Many small businesses simply do not have the budget to invest in enterprise resource planning (ERP) solutions with comprehensive financial reports but does this mean that these businesses cannot produce proper management accounts?
Definitely not and it is actually quite easy to produce monthly financial reports for management which are on the same level as what large corporations are using. All you need is an Excel model which makes it easy to include your account balances for various periods (like the current year, prior year and maybe even a forecast or budget) and to switch between periods when analyzing data.
- Creating an import feature for checking the account number sequencing of the trial balance accounts which can be used before copying trial balance data into the template. The idea is that users would export their trial balance data from their accounting software and then copy the data into the template after checking that the account number sequence is in the correct order.
- Formulating the line items which needed to be included in the management accounts and linking each of the lines to a pre-defined reporting class code.
- Adding trial balance sheets to the template and linking each account in the trial balance to one of the pre-defined reporting classes. These are the same class codes that are linked to the management account line items.
- Monthly trial balances are required for each period that you want to analyze. If you therefore want to compare the current financial year to the previous year and to a forecast, you would require three trial balance sheets. We have therefore included three separate sheets for trial balance data.
- Accounts in all three trial balances need to be linked to the appropriate reporting class codes and we have therefore linked all the trial balance accounts on one sheet and then just look up the codes on the trial balance sheets from this key sheet.
- After all the links were established, we added formulas to the management accounts to calculate the appropriate line item amounts based on the trial balance data.
- These formulas were based on a user-input cell for selecting the period to analyze which makes it very easy to switch between periods and to automatically update the template calculations when a new period is selected.
Now that we've covered the steps that we used in designing the template, it should make more sense when viewing the trial version of the template which is provided through our secure trial version macro. Just enable the trial macro to view all the formulas that we used in designing this template.
