Management Accounts Template - Excel Skills Australia

How to use the Management Accounts template

Open the sample or trial version when reviewing these instructions

This template enables users to compile comprehensive monthly management accounts based on any trial balance with any account structure. The management accounts are compiled by linking each account in the trial balance to a pre-defined reporting class code and all the amounts that are included in the management accounts are automatically calculated based on the linked reporting class codes. The template includes an income statement, cash flow statement and balance sheet and compares the actual results to a forecast on a monthly and year-to-date basis. Prior year results are also included on all the financial reports.

Note: All account balances on the trial balances (TBCY, TBPY and Forecast sheets) need to be included on a cumulative basis in order for the template calculations to be accurate. If a business has turnover of $100,000 per month, the cumulative basis means that you need to include $100,000 in month 1, $200,000 in month 2, $300,000 in month 3, etc.

The template includes the following sheets:
Setup - enter your business name, enter the current reporting year, select the appropriate financial year-end period and select the current reporting period. The business name is used as a heading on all the sheets and the reporting period selections are used to determine the monthly periods that need to be included on the trial balance & forecast sheets and the periods on which the monthly and year-to-date calculations on the income statement, cash flow statement and balance sheet reports need to be based.
Groups - this sheet contains all the pre-defined reporting classes that should be used to link the trial balance accounts on the Key sheet to the calculations on the income statement, cash flow statement and balance sheet.
Key - all the individual accounts that are included on the trial balance need to be copied or entered onto this sheet and each account number then needs to be linked to the appropriate reporting class. All the calculations on the income statement, cash flow statement and balance sheet are automatically updated based on the reporting class that is linked to each account on this sheet.
TBPY - all the account numbers and appropriate monthly account balances that are included in the trial balance for the prior year reporting period needs to be included on this sheet. The reporting classes in column A can be added to each account by simply copying the formula into all the rows that contain an account number. Note that the balance sheet balances for the year before the prior year reporting period also need to be specified in order to facilitate accurate cash flow calculations for the prior year financial period.
TBCY - all the account numbers and appropriate monthly account balances that are included in the trial balance for the current reporting period need to be included on this sheet. The reporting classes in column A can be added to each account by simply copying the formula into all the rows that contain an account number.
Forecast - a cumulative monthly forecast balance needs to be specified for each account that is included in the current period trial balance on the TBCY sheet. Note that all the forecasted balances need to be entered on a cumulative basis (the same basis which is used in order to compile any trial balance) in order for the forecast balances to be included correctly on the income statement, cash flow statement and balance sheet reports. Income, equity & liability account balances should be represented by negative values while expenses & asset balances should be represented by positive values.
IS - this sheet contains an income statement which is automatically calculated based on the reporting classes that have been linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if necessary. The monthly & year-to-date periods for which the income statement is compiled is determined based on the period selections on the Setup sheet.
CFS - this sheet contains a cash flow statement which is automatically calculated based on the reporting classes that have been linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if necessary. The monthly & year-to-date periods for which the cash flow statement is compiled is determined based on the period selections on the Setup sheet.
BS - this sheet contains a balance sheet which is automatically calculated based on the reporting classes that have been linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if necessary. The monthly & year-to-date periods for which the balance sheet is compiled is determined based on the period selections on the Setup sheet.
ISMonth - this sheet contains a monthly income statement which is automatically calculated based on the reporting classes that have been linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if necessary. The monthly income statement is based on the actual results that are represented by the account balances on the TBCY sheet and the monthly periods for which the income statement is compiled is determined based on the period selections on the Setup sheet.
TBCheck - when you copy a trial balance from another Excel workbook into this template, it is important to ensure that the account numbers on the source worksheet are the same as the account numbers on the target trial balance worksheet. We have therefore included this sheet to enable users to check whether their account number sequence is consistent with the appropriate trial balance before copying the trial balance data into this template.

In order to start using the template, you basically need to complete the following steps:

Change the default settings in the cells with yellow cell backgrounds on the Setup sheet.
If you do not want to use your own account structure and you would rather use our standard template setup, you only need to include your cumulative monthly balances on the three trial balance sheets and leave the Key sheet as is. All the reporting classes will automatically be linked to the appropriate account balances and populated on the income statement, cash flow statement and balance sheet.
If you want to use your own account structure, replace our default data by copying the account numbers & descriptions of all the accounts on your trial balance onto the Key sheet and then link each individual account to the appropriate reporting class by copying or entering the class code into column C. Note that a full list of all the main classes that are used on the income statement and balance sheet are included on the Groups sheet.
Then also copy the account numbers to the TBPY, TBCY and Forecast sheets and copy the formulas in columns A and C if you have more accounts than the default data in the template.
Copy all your prior financial year balances onto the TBPY sheet and all your forecast balances for the current financial year onto the Forecast sheet. Actuals should be copied onto the TBCY sheet throughout the year. All trial balance amounts must be cumulative.
The income statement, cash flow statement and balance sheet are automatically calculated based on the amounts that are included on the three trial balance sheets. Simply select the appropriate reporting period on the Setup sheet and all calculations are updated automatically.

Note: These are only the main steps that need to be completed and we strongly recommend that you go through the detailed instructions below in order to obtain a more through understanding of the template design.

Setup

The input cells that contain yellow cell backgrounds on the Setup sheet need to be completed in order to include your business name as a heading on all the appropriate sheets and to specify the appropriate reporting periods for which the management accounts need to be compiled. The cells with light blue cell backgrounds contain formulas which should not be replaced!

The appropriate year-end period needs to be selected in cell B6. The year-end month that is selected in this cell determines which months are included in the year-to-date calculations and also determines which monthly periods are included on the trial balance sheets & the monthly income statement on the ISMonth sheet.

The financial year for which the management accounts are compiled needs to be specified in cell B8. The year-end date that is displayed in cell B10 is determined based on the year-end period that has been selected in cell B6 and the financial year that has been entered in cell B8.

The current reporting period needs to be selected from the list box in cell B12. The months that are included in the list box are determined by the year-end and financial year selections in cells B6 and B8. The current reporting period that is specified determines which monthly and year-to-date periods are included in the income statement, cash flow statement and balance sheet reports. You can therefore change the period for which the management accounts are compiled by simply selecting a new period from the list box in this cell.

The reporting period end date and the prior year reporting period which is displayed in cells B14 and B16 are determined based on the current reporting period that is selected in cell B12. These cells contain formulas which should not be replaced!

Note: If any of the user input cells on the Setup sheet are not completed or if the data that is entered is invalid, the cell background of the appropriate cell will be highlighted in red. A red cell background therefore indicates that you should replace the data in the appropriate cell with valid input.

Example: If you change the financial year in cell B8 and the current reporting period which has been specified in cell B12 falls outside of the new financial year, the cell background of cell B12 will be highlighted in red in order to indicate that a new current reporting period needs to be selected. When you select a valid period from the list box, the red highlighting will be removed automatically.

Default Reporting Classes

The default reporting classes on which the management accounts are based have been included on the Groups sheet. These are the default income statement and balance sheet classes that need to be used in order to link the accounts on each of the three trial balance sheets to the appropriate income statement or balance sheet item on the income statement and balance sheet.

These links are however not established on the Groups sheet but on the Key sheet where all the accounts that form part of the trial balances need to be included. The Groups sheet is therefore largely for information purposes although the income statement and balance sheet line item descriptions are based on the reporting class descriptions that are entered on the Groups sheet.

Note: Each line on the income statements (IS and ISMonth sheets) and balance sheet (BS sheet) contain a reporting class in column A which makes it easy to identify the reporting class which is used to calculate the appropriate line. All the account balances on each of the trial balances which have been linked to the appropriate reporting class will be included in the balance calculation.

When users initially link each trial balance account to a reporting class on the Key sheet, users should therefore refer to the Groups sheet in order to determine which reporting class code relates to which income statement or balance sheet account group.

Linking Accounts to Reporting Classes

All the general ledger accounts that form part of the trial balance should be linked to a reporting class in order to facilitate compiling automated income statement, cash flow statement and balance sheet reports. The management accounts in this template include actual results for the current financial period, forecasted results for the current financial period and a comparison to prior year results which are all automatically calculated based on the account balances that are included on the TBCY, Forecast and TBPY sheets.

The accounts that are included on these three trial balance sheets therefore all need to be linked to the appropriate reporting classes. It is however essentially the same accounts that need be included on all three sheets and instead of requiring users to link accounts to reporting classes on all three sheets, we have set up a separate sheet for this purpose and included a formula in column A on the three trial balance sheets in order to look up the appropriate reporting classes from this sheet.

All the account numbers that are included in the trial balance as well as the appropriate account descriptions therefore need to be entered or copied onto the Key sheet in columns A and B. Users then need to link each account to the appropriate reporting class in column C (refer to the Groups sheet for the appropriate codes and descriptions that can be used to link an account to an income statement or balance sheet line item).

Note: All the entries on the Key sheet must form part of a continuous cell range - you should not add any blank rows between rows that contain data otherwise the formula in column D will not flag all the invalid reporting class codes that are included in column C.

Note: We strongly recommend using the default reporting class structure that we have provided on the Groups sheet because these codes have been included in column A on the income statement, cash flow statement and balance sheet reports. If you use different codes and you include them incorrectly, the template calculations will not be accurate.

You can use any trial balance regardless of whether the account structure is consistent with our standard template or not. Our default account numbers have been derived from the account structure that is used in our accounting templates but it can be replaced with the appropriate account format which is used in your own trial balance by simply replacing our default data. Each account should then be linked to one of our default reporting classes which creates the consistency that is required in order for the reports to be automatically compiled.

All the accounts that are included in the trial balance should be linked to a valid reporting class otherwise the template calculations may not be accurate and the management accounts may therefore not balance. We have added a formula in column D on the Key sheet which should be copied for all the new rows that are added to the Key sheet and will contain an error if an incorrect reporting class has been linked to any of the accounts.

The formula in the Status column will display an error code if an account has been linked to an invalid reporting class. Refer to the Error Codes section of these instructions for guidance on how to fix these errors. It is important that you fix any errors that are displayed in this column because it probably means that the balances of the affected account will not be updated to the income statement or balance sheet.

New Reporting Class Codes

If you want to add a new line to the income statement or balance sheet, we recommend that you explore changing one of the default lines of an unused income statement or balance sheet item before considering adding a new reporting class. If there is an unused item that you can change, it is simpler to do so than to add a new reporting class and therefore also easier to do so. The unused line should however be similar to the line that you want to add otherwise you will need to add a new reporting class instead.

When you change a similar item, you can use the same reporting class and only edit the description of the account group if the reporting class is one of those listed on the Groups sheet. If the similar item's reporting class is not listed on the Groups sheet, you can still use the same reporting class code and link this code to the appropriate account(s) on the Key sheet but the line item description will then be based on the account description that is included for the first account that is linked to the reporting class on the Key sheet.

If there are no similar items available, you will need to create a new reporting class code and use this code when adding a line for the new item on the income statement or balance sheet. The easiest way of adding a new line is to insert a new row at the appropriate location and to copy the formulas in all columns from one of the existing rows which is similar in nature. You can then just replace the reporting class of the copied line (in column A) with your new reporting class.

The following rules apply to creating new reporting classes:

New reporting classes must be unique and we strongly recommend staying with our default reporting class code convention (for example I-99G or B-99G). The "G" at the end is essential in order to prevent the reporting class codes from clashing with account numbers. In the unlikely event that your account numbers contain a "G" at the end and consist of the same account structure, replace the "G" with for example a "C"!
All new income statement codes should start with "I-" and all new balance sheet codes should start with "B-" followed by a unique two-digit number for the account group which is followed by the "G".
If you want to display individual accounts instead of account groups, we recommend switching from the default 5 character reporting classes to a 7 character reporting class which includes an additional two digits before the "G". These additional two digits can then be used to include line numbers and therefore provide for an additional 100 lines within each reporting class (for example the default account group class of I-03G for operating expenses was changed to I-0301G to I-0318G in order to provide for 19 additional individual accounts on the income statement).
If column D on the Key sheet contains an error code, it needs to be fixed immediately because it may lead to template imbalances. Refer to the Error Codes section of these instructions for guidance on how to fix these and other errors.

Note: After adding a new reporting class and linking it to an account on the Key sheet, you may see an error in the Status column indicating that the reporting class cannot be found on the income statement or balance sheet. This is normal and this error will automatically disappear as soon as you add the new reporting class to the appropriate location on the income statement or balance sheet.

Note: If the reporting class is the same as the ones that are listed on the Groups sheet, the income statement or balance sheet line item description will be picked up from the Groups sheet. If there is no match on the Groups sheet, the line item description will be picked up from the account description on the Key sheet of the first line that matches the reporting class code.

Prior Year Trial Balance

A complete trial balance for the prior financial period should be copied or entered onto the TBPY sheet. The account balances that are included on this sheet are used to update the "Prior Year" columns on the income statement, cash flow statement and balance sheet reports.

Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the TBPY sheet - there should not be any blank rows between rows that contain data otherwise all the accounts may not be included in the management accounts calculations and your balance sheet may therefore not balance!

The reporting class in column A and the account description in column C are automatically looked up based on the entries on the Key sheet. The formulas which have been included in these columns therefore need to be copied for all the rows on the TBPY sheet which contain account numbers in column B.

Note: If an account number is included in the trial balance but the account number has not been linked to a valid reporting class on the Key sheet, the appropriate cell in column A will contain a "No key!" message and the column heading in column A will be highlighted in red. If any of the three trial balance sheets contain accounts that are not linked to valid reporting classes, your management accounts may not balance. If the "No key!" message is therefore encountered, you should add the appropriate account number to the Key sheet and link the account number to a valid reporting class. Once this step has been completed, the red highlighting in the column heading cell will be removed automatically.

The monthly periods that are included in the column heading row are automatically determined based on the financial year-end and reporting year that are specified in cells B6 and B8 on the Setup sheet. All the appropriate account balances for these monthly periods need to be entered or copied into the cells in the appropriate columns but you should not edit any of the column heading cells! If you want to change the dates that are included in the column headings, you should do so by changing the reporting period selections on the Setup sheet.

The monthly management accounts can only be as accurate as the trial balance data that is included on the three trial balance sheets. If your trial balance therefore does not balance, your monthly management accounts will also not balance! You should therefore always ensure that all the monthly totals that we have included above the column heading row equal nil - we have also added conditional formatting to these cells which highlights all the monthly totals that are not equal to nil in red.

Note: You may notice that while you are adding accounts to the trial balance on this sheet (by inserting new rows in the appropriate location based on the account number sequence), the totals are highlighted in red - this formatting simply indicates that empty rows have been inserted and will automatically disappear when an account number is entered in column B for all the blank rows.

Note: If you are setting up management accounts for a new business with no prior financial year, we recommend that you still add all the trial balance accounts to the TBPY sheet but that you include nil values in all the monthly columns. This will ensure that all the "Prior Year" columns in the management accounts reflect nil values.

You may notice that the TBPY sheet includes an additional column that has a column heading which has been formatted with a dark blue cell background and contains the date of the year end month which precedes the prior financial year. This column is required in order to produce accurate cash flow statement calculations for the prior financial year.

It is not necessary to include a full trial balance for this period in column D - you can only include the appropriate balance sheet account balances as at the end of this period (income statement accounts can be omitted). The trial balance will however only balance if the retained earnings balance as at the end of this period is included in the account which is linked to the retained earnings reporting class.

Note: A trial balance usually only includes the retained earnings balance at the beginning of the appropriate financial period because the profit or loss for the current financial period is in effect included in the individual income statement account balances but because we are not including the income statement accounts in this column, the retained earnings account balance needs to be adjusted in order to reflect the retained earnings balance at the end of the financial period. This adjustment is therefore only required in this column - no adjustments are required for any of the other trial balances which form part of this template.

Note: You can check the adjusted retained earnings balance by comparing it to the retained earnings account balance for the subsequent months on the TBPY sheet - the retained earnings balance in column D should be the same as the retained earnings account balances in columns E to P. This is because these columns include the retained earnings balance at the beginning of the prior financial year which will be the same balance as at the end of the month in column C.

Current Year Trial Balance

A complete trial balance for the current financial period should be copied or entered onto the TBCY sheet. The account balances that are included on this sheet are used to update the "Actual" columns on the income statement, cash flow statement and balance sheet.

Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the TBCY sheet - there should not be any blank rows between rows that contain data otherwise all the accounts may not be included in the management accounts calculations and your balance sheet may therefore not balance!

The reporting class in column A and the account description in column C are automatically looked up based on the entries on the Key sheet. The formulas which have been included in these columns therefore need to be copied for all the rows on the TBCY sheet which contain account numbers in column B.

The monthly periods that are included in the column heading row are automatically determined based on the financial year end and reporting year that are specified in cells B6 and B8 on the Setup sheet. All the appropriate account balances for these monthly periods need to be entered or copied into the cells in the appropriate columns but you should not edit any of the column heading cells! If you want to change the dates that are included in the column headings, you should do so by changing the reporting period selections on the Setup sheet.

Forecast Trial Balance

A complete trial balance for the current financial period forecast or budget should be copied or entered onto the Forecast sheet. The account balances that are included on this sheet are used to update the "Forecast" columns on the income statement, cash flow statement and balance sheet.

Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the Forecast sheet - there should not be any blank rows between rows that contain data otherwise all the accounts may not be included in the management accounts calculations and your balance sheet may therefore not balance!

The reporting class in column A and the account description in column C are automatically looked up based on the entries on the Key sheet. The formulas which have been included in these columns therefore need to be copied for all the rows on the Forecast sheet which contain account numbers in column B.

Important: Trial balances are always compiled on a cumulative basis - this means that the monthly movement in account balances are added together when calculating the appropriate month-end trial balance amounts. Forecasts are however usually not compiled on a cumulative balance basis which means that you may have to convert your forecast balances to cumulative balances before copying or entering the appropriate balances on the Forecast sheet otherwise your management accounts may not be accurate (this conversion is usually only necessary for income statement accounts because balance sheet account balances are always cumulative).

Income Statement

The income statement on the IS sheet is automatically compiled based on the trial balances that are included on the TBCY, TBPY and Forecast sheets and the financial reporting periods that have been specified on the Setup sheet. All the amounts in the Forecast columns are calculated from the Forecast sheet, all the amounts in the Actual columns are calculated from the TBCY sheet and all the amounts in the Prior Year columns are calculated from the TBPY sheet.

The income statement is calculated automatically and no user input is required on this sheet. If you want to change the period for which the monthly and year-to-date balances are calculated, you need to select a new current reporting period from the list box in cell B12 on the Setup sheet.

The monthly section is based on the appropriate month which has been selected and the year-to-date section is based on all the months from the beginning of the financial year up to the selected period. Note that the financial year-end month is also specified on the Setup sheet.

The calculated amounts for each individual line item on the income statement are based on the reporting classes that have been entered in column A. All the accounts that have been linked to the appropriate reporting class (on the Key sheet) will be included in the calculation of all the monthly and year-to-date amounts.

Note: If the reporting class is the same as the ones that are listed on the Groups sheet, the line item description will be picked up from the Groups sheet. If there is no match on the Groups sheet, the line item description will be picked up from the account description on the Key sheet of the first line that matches the reporting class code.

Cash Flow Statement

The cash flow statement on the CFS sheet has been designed on the same basis as the income statement on the IS sheet. The cash flow statement is automatically compiled based on the balances on the income statement and balance sheet. No user input is required on this sheet and the reporting period is based on the current reporting period selection in cell B12 on the Setup sheet.

If you want to remove a line from the cash flow statement, we strongly recommend that you hide the appropriate line instead of deleting it. If you delete cells which are used in some of the other cash flow statement calculations, you may encounter a calculation error in some of the other rows on the report.

If you have created new reporting class codes for balance sheet items and you need to add a cash flow statement calculation for these new balance sheet items, we recommend inserting a new row in a suitable location, copying one of the existing lines for a similar balance sheet item and changing the reporting class code in column A of the copied line item to the new reporting class.

You also then need to review the formulas in the monthly and year-to-date "Actual", "Forecast" and "Prior columns" to ensure that the part of the formula which refers to the BS sheet includes the row number of the new balance sheet item. The part of the formula that we are referring to should look something like this: BS!D14.

Balance Sheet

The balance sheet on the BS sheet has been designed on the same basis as the income statement on the IS sheet. The balance sheet is automatically compiled based on the account balances that are entered on the TBPY, TBCY and Forecast sheets. No user input is required on this sheet and the reporting period is based on the current reporting period selection in cell B12 on the Setup sheet.

If you want to remove a line from the balance sheet, we strongly recommend that you hide the appropriate line instead of deleting it. If you delete cells which are used in some of the other balance sheet calculations, you may encounter a calculation error in some of the other rows or on the cash flow statement.

You can also add additional lines to the balance sheet by simply inserting a new row, entering the appropriate reporting code on which the calculation should be based in column A and copying the formulas from one of the other similar lines on the balance sheet.

We have added two control totals and their amounts to the four rows immediately below the balance sheet. The first control total (and its amount above it) reflects an "ok" status if the appropriate balance sheet balances. If not, the amount of the imbalance is displayed and an error status is displayed. The error and amount will be removed automatically when corrections have been made and the balance sheet balances.

The second control total (and its amount below it) reflects an "ok" status if the appropriate bank balance on the balance sheet agrees to the closing balance of the cash flow statement (CFS sheet) . If not, the amount of the imbalance is displayed and an error status is displayed. The error and amount will be removed automatically when corrections have been made and the two amounts are equal.

We have also included an Error Code line below these control totals which is displayed if there are any errors in the template whether the above control totals balance or not. This line has been included to assist users in finding the cause of errors especially if there are imbalances in the template. Refer to the Error Codes section for guidance on how to resolve these errors.

Monthly Income Statement

The monthly income statement on the ISMonth sheet is automatically compiled based on the account balances that are entered on the TBCY sheet. No user input is required on this sheet and the reporting period is based on the current reporting period selection in cell B12 on the Setup sheet.

The calculated amounts for each individual line item on the monthly income statement are based on the reporting classes that have been entered in column A and linked to trial balance accounts on the Key sheet.

Trial Balance Check

If you use a trail balance export file in order to compile management accounts, the trial balance export file will have to be amended so that the account numbers are in the exact same order as the accounts on the appropriate trial balance sheet before you will be able to copy the account balances onto the appropriate sheet. This is a necessity in order to ensure that the correct account balances are included next to the correct account numbers.

New accounts may also have been added during the current financial period which may not be included in the previous trial balance on the TBCY sheet. These accounts therefore need to be identified and inserted in the correct row position before the account balances can be copied. This could be quite a time consuming exercise and we have therefore added the TBCheck sheet to assist users in simplifying this exercise.

Note: All the accounts that are included on the TBCheck sheet should form part of a continuous cell range - there should not be any blank rows between rows that contain data otherwise all the account balances may not be included in the total calculation above the column heading row.

The following steps need to be completed in order to ensure that the relevant trial balance is correctly included on the appropriate trial balance sheet:

Select the trial balance that you need to amend from the list box in cell E3. You can select the Current option in order to check the account sequence on the TBCY sheet, the Prior option to check the account number sequence on the TBPY sheet or the Forecast option to check the account number sequence on the Forecast sheet.
Sort the data on the appropriate trial balance sheet (TBCY, TBPY or Forecast sheet) in an ascending order based on the account numbers in column B.
Sort the data in the trial balance export file in an ascending order based on the account number column.
Copy the account number and account balance from the export file and paste the data into column A and B on the TBCheck sheet (replace the existing data on this sheet).
Copy the formula in cell C6 and paste it into all the rows that contain an account number in column A. Note that all the cells in column C will now contain formulas and formatting which will assist you in identifying the changes that need to be made to the existing account structure on the TBCheck sheet before the account balances can be copied onto the appropriate trial balance sheet.
Review the contents in column C - a row that contains a green "ok" cell is in the correct sequence but if an "error!" is displayed in orange, it means that the sequence of accounts on the TBCheck sheet is not the same as the sequence of accounts on the appropriate trial balance sheet.
Check the contents of all the rows that contain errors by comparing the account sequence on the TBCheck sheet to the account sequence on the appropriate trial balance sheet (TBCY, TBPY or Forecast).
Where an account number is included on the TBCheck sheet but not on the trial balance sheet, determine whether the account number has been included on the Key sheet. If the account number cannot be found on the Key sheet, insert a new row in the appropriate location on the Key sheet, add the account number and account description to the Key sheet and link the account to a valid reporting class code.
Then also insert a new row on the appropriate trial balance sheet (TBCY, TBPY or Forecast), copy the account number into column B and enter a nil value into all the columns on the trial balance sheet. After completing these steps, the error in the particular row will automatically be removed from the TBCheck sheet.
Where an account is included on the appropriate trial balance sheet but not on the TBCheck sheet, insert a new row on the TBCheck sheet, copy the account number from the appropriate TB sheet and paste it into column A, enter a nil value in column B and copy the formula in column C from one of the existing cells in column C. Column C should now contain a green "ok" message.
Repeat the previous three steps until the TBCheck sheet contains no errors.
Ensure that the total of the amounts in column B on the TBCheck sheet is nil - the total calculation is included in the row above the column headings. If the total of all the account balances on the TBCheck sheet is not nil, the difference will be reflected in red in the cell above the column headings with an "error!" message next to it (note that blank rows may cause an inaccurate total calculation).
If the total row contains a nil value, the amounts in column B can be copied and pasted as values into the appropriate column on the relevant trial balance sheet (TBCY, TBPY or Forecast). After completing this step, the appropriate trial balance has successfully been included in the template.

Roll Forward

The following steps need to be completed in order to roll the management accounts template forward for the next financial period:

Save the workbook under a new file name in order to create an exact copy of the template which can be used for the next financial period (use the File Save As feature).
Open the TBPY sheet and copy the account balances in column P into column C. This step effectively transfers the prior financial period account balances into the period which precedes the prior financial period.
Compare the account number sequence on the TBPY and TBCY sheets and ensure that all the accounts that are included on the TBCY sheet have also been added to the TBPY sheet. The account number sequence on both of these sheets should be exactly the same.
Open the TBCY sheet and copy all the account balances in columns D to O into columns E to P on the TBPY sheet (but do not copy the column heading row!). This effectively transfers the current financial period account balances into the prior year financial period.
Open the TBCY sheet and replace all the existing account balances with nil values in order to replace the results for the previous financial period with nil values.
Open the Forecast sheet and replace the forecast for the previous financial period with the forecast for the current financial period (you may have to use the TBCheck sheet for this purpose).
Open the Setup sheet and change the reporting year in cell B8 to the current reporting year.
Select the first monthly period from the list box in cell B12.
Check that the Actual columns on the IS sheet now contain nil values. Also check that the Forecast and Prior Year columns contain values and that all the balance sheets balance. If you encounter any imbalances, refer to the Troubleshooting section of these instructions.
The monthly trial balances for the current financial period can now be added to the TBCY sheet after each month end and the template has now effectively been rolled forward for the new financial year.

Error Codes

The following error codes have been used to identify errors in setting up the template for your business. If you notice any of these error codes, you can complete the below steps in order to resolve the appropriate error(s):

E1 - trial balance does not balance. If you see this error in any of the columns in the section below the balance sheet, it means that there is an imbalance in one or more of the months on the appropriate trial balance. Forecast column refers to trial balance on Forecast sheet, Actual column refers to trial balance on TBCY sheet and Prior Year column refers to trial balance on the TBPY sheet. The TBCY and Forecast sheets may also contain errors if the last month on the TBPY sheet contains an imbalance. If you see this error, you need to fix the appropriate trial balance amounts so that the totals above the column headings on the appropriate sheet are nil.
E2 - this error code refers to a missing key. This means that a reporting class which has been linked to one or more accounts on the Key sheet is not included on the income statement or balance sheet. If a reporting class is not included on these sheets, the balances that are linked to the appropriate class will not be updated on the financial reports. You therefore need to include the appropriate reporting class on either the income statement or balance sheet depending on the nature of the accounts that are linked to the reporting class.
E3 - this error code indicates that there are one or more accounts which have not been linked to a reporting class. Refer to the appropriate trial balance sheet and find the accounts that have a "No key!" message in column A. These accounts need to be added to the Key sheet and linked to a valid reporting class.
E4 - this error code indicates that there is a blank key. Refer to the Key sheet and look for the error code in the Status column. The error code means that no reporting class has been assigned to the affected accounts and you therefore just need to assign a valid reporting class in order to fix the error.
E5 - this error code indicates that there is a duplicate key. All reporting classes must be unique and therefore only included once on either the income statement or balance sheet. If a reporting class is included twice, it will lead to some of the trial balance amounts being duplicated on the appropriate financial report which will probably cause a balance sheet imbalance. Find the error code in column D on the Key sheet, identify the reporting class and remove the duplicated reporting class from either the income statement or balance sheet.

Note: The above errors need to be fixed as soon as they are discovered (in the Status column on the Key sheet or in the section below the balance sheet) because in most instances, these errors will result in template inaccuracies and/or cause balance sheet imbalances.

Excel-Skills Australia

100% UNIQUE EXCEL TEMPLATES

02 Management Accounts Template

How to use the Management Accounts template