Management accounts wizard for Sage and Excel
How Management Accounts Wizard Works
Wizard Management Accounts Wizard uses two spreadsheets, one to collect, group and organise the underlying nominal ledger data and one to assemble the output into an easily read report. Such systems are tried and tested amoungst management accountants who frequently find that reporting functionality in accounts software is not versatile enough for their purposes. You will need to be reasonably proficient with spreadsheets in order to utilise this tool. Most qualified accountants will have the necessary Excel skills to understand and use this tool.
- A chart of accounts is set up by grouping nominal ledger codes along the top of the collation spreadsheet according to the user requirements. Once set up the only maintenance is to add new nominal ledger codes as needed. The key to its utility is that the nominal codes in the chart of accounts are all grouped in columns which are visible and remain stable from month to month. The code behind the wizard transfers the nominal values based on the visible chart of accounts. It is much more intuitive that one based on Excel functions. It is easy to add a new nominal account code even if it is not in sequence with the rest of the group.
- The spreadsheet has numerous validation functions to ensure accurate data transposition and it is eash to see exactly what individual values make up each chart of accounts sum. If any new nominal codes are added to the accounts software nominal ledger the audit function in the wizard will flag this highlighting the unused nominal code. It can then simply be added to the chart of accounts on the collation spreadsheet.
- Each month I copy both spreadsheets into a new folder then paste in the new trial balance. That way a complete set of accounts is built up for the year.
- If you used department numbering in Sage Line 50 you can split the nominal balance into department allocations.
- An example spreadsheet is provided demonstration the grouping into a chart of accounts. A second management accounts report is also provided but you can link your existing report to the collation spreadsheet and enjoy the benefits month on month.
Management accounts wizard for Sage and Excel - Manual
Download PDF manual click here
Introduction
Developed by a management accountant with advanced visual basic programming skills to simplify the task of assembling the monthly management accounts. If you use spreadsheets in the traditional ways to collate management accounts, you are dependant on hundreds of links or complex SumIf functions. Each month changes to the nominal code base of the trial balance results in broken links between the report and the data spreadsheet. These can often take the best part of a day to resolve. To circumvent the problems with such links a system was developed whereby the nominal codes are entered in a table (a user defined chart of accounts) which allows you to group nominal code balances irrespective of the grouping in your accounts package. The nominal values for each group are then transferred using code running in the background thus removing the dependence on links and the absolute position in the row of each nominal balance. Although spreadsheet links are still used to pull the values into the report after this process these are now fully under the control of the management accountant.
Furthermore, the system is self auditing and you can easily see when the nominal ledger changes and new codes need to be collated, or when the same nominal balance is transferred more than once.
3 easy steps to set up management accounts using the wizard’s chart which collates nominal code values into groups.
- Does not break links when new nominal account codes are added to the accounts database without the need to reset numerous existing links - no row dependence.
- Numerous data checks (audit functions) that enables complete validation of the whole process.
- Nominal balances for overheads can easily be split by department and entered manually.
Installation
- Move the downloaded zip file (AccountsWizard.zip) to a temporary folder and unzip all files to same folder.
- If using the example management accounts report spreadsheet (MAN_ACC_Period.xls) keep it in the same folder as NL_Output.xls which is used to hold the nominal balances.
- Once installed you can make multiple copies of the Excel files, e.g. for each month end.
Table of contents
1. Introduction
1.1. In brief
2. Instructions
2.1. Input the nominal code balances from Sage Line 50
2.2. Organise the nominal codes into a chart of accounts suitable for your business Grouping nominal codes into a “chart of accounts codes" Preparation 6 Grouping nominal codes into a “chart of accounts codes”
2.2.1. Dealing with departmental data directly from nominal ledger balances
2.3. Run Update events
2.4. Validation of the basic dataafter pasting
2.4.1. Trial balance check
2.4.2. Are all nominal codes collated ?
2.4.3. Departmental Splits
2.4.4. Duplicate nominal entries or other copy errors
2.4.5. When non-existing codes are entered in the table of codes 2.4.6. Find out where a nominal value was collated
3. Advanced features
3.1. Locked worksheets
3.2. Hidden cells
3.3. Late journal entries
3.4. Worksheet frozen
3.5. Cell format 10
3.6. Use of NL_Link_2 worksheet
3.6.1 Secondary analysis of data
3.6.2 Large nominal code output
3.7. Columns and rows
4. MAN_ACC_Period.xls
4.1. Display links macro
4.2. Highlight Links macro
5. Registration
6. Legal notices
1.1. In brief
Management Account Wizard uses two spreadsheets, one to collect, group and collate the period data and one to assemble the output in an easily reviewed report. Such systems are well known to management accountants who frequently find that reporting functionality in accounts software is not versatile enough for their purposes. The descriptions in this manual will assume the use of Sage Line 50 which readily outputs nominal code balances (trial balance) balances to an Excel spreadsheet.
From Sage Line 50 the nominal balance output is in four columns:
column A contains nominal codes,
column B: contains the short description,
column C: contains debit balances,
column D: contains credit balances.
Column E is used to provide balances in a single column. In this system trial balance credits are set to be negative values and trial balance debits are positive. This can be changed if required by altering the formula in column E cells
2. Sheet NL_link_1 (and if used, NL_link_2) is used to collate the nominal ledger codes and balances for manipulation. The data input as above is linked to NL_link_1 starting at cells A:51, B:51 and C:51
3. A chart of nominal codes are grouped in columns of cells starting cells AA:4 to AA:49 through to IR:4/IR:49. In the example spreadsheet note that similar nominal codes are grouped together, for example, that column AE is used to collate all bank accounts and cash thus in the example 12 bank or cash accounts are pulled together and summarised. To use add a group name (e.g. Balance sheet cash) in cell AE:2, list all nominal code index values you wish to collate in cells AE:3 to AE:49. Note that after running the update that the sum of the nominal ledger balances appears in cell AE:1 ready for picking up on the report.
4. Fill in all the groupings necessary to represent your company accounts. In the example spreadsheet groups are organised and colour coded as follows: balance sheet, sales, direct cost of sales, indirect cost of sales and overheads.
5. Some spare columns are left to enable additional groups to be added in sequence with the major groupings although if necessary they could be added at the far right end.
2. Instructions
The description will assume use of Sage Line 50 as the accounts database. It will work with other packages that provide a trial balance or a period balance.
When working with the workbooks always enable macros.
2.1. Input the nominal code balances from Sage Line 50
In Line50 export the nominal ledger balances to Excel. This will paste the balances into a new workbook. Copy these values 4 columns wide by as many rows as your database uses. Open Nominal_Output.xls workbook and navigate to Sage_paste worksheet. Paste the Line50 nominal ledger balances into cell A:4
- Up to a maximum of 2501 rows can be pasted. If you need to have more rows please contact the author.
- Cells C:1, D:1 and E:1 in Sage_paste show the debit, credit and trial balances.
2.2. Organise the nominal codes into a chart of accounts suitable for your business
Grouping nominal codes into a “chart of accounts codes”
Preparation
Prepare on paper a table that will group your nominal ledger index values either from your knowledge of your existing management accounts summary spreadsheet or work back from your existing management accounts.
Grouping nominal codes into a “chart of accounts codes”
Enter the nominal code groups onto NL_Link_1.xls workbook starting at column AA.
- The spreadsheet tables starts at column AA and runs to IV. This gives room for 230 groups which can be used to build the management accounts.
- The number of rows available per group is 46. If you need to use more than 46 nominal codes in a group you will need to spill over to the next column.
- Enter a name for the chart of accounts group in row 3 that describes its purpose in the management accounts.
- Keep groups organised e.g. keep balance sheet items together and colour code.
- Leave 5 or 6 columns before starting the next group to allow room.
- for future sub-division.
Work your way along the columns adding all the groups you need to make up your management accounts report.
2.2.1. Dealing with departmental data directly from nominal ledger balances
When you want to report nominal ledger balance data by department you can split the nominal ledger balance manually by entering departmental values into the columns (DEPT1, DEPT2 etc.) to the right of the nominal ledger balance data (Fig. 4). [You can obtain the information from Departmental reports using Sage Line 50 nominal reports]. Of course if your Accounts System nominal codes are already broken down by department there is no need to use manual entry. It is possible to have up to 10 departments analysed this way.
- Enter the department split values in columns J to S (see figure above).
- When making departmental splits manually it is necessary to use the department number as the first part of the description in the chart of accounts group see illustration below for Department 3 (DEPT3). The program uses the first 5 characters so if your analysis extends to 10 departments Department 10 must be entered as DEP10.
- When this manual entry system is used the same nominal codes can be used for each department without duplication of results (e.g. each department can collect values 7000, 7005 and 7300 for salaries)
2.3. Run Update events
After you have entered all the data in the chart of accounts and entered any split amounts for departments, run the program by clicking on the “Run update” button. The previously transferred data will clear and the colour highlighting will disappear. As the program runs a number of checks and data highlighting events will occur. On running the update each nominal code balance represented in the table of codes is copied to a cell to its right, in the same row and placed in the column directly under its entry in the table of codes. The figure opposite shows such a range of copied data. This allows the data copied to locations under the table of codes to be summed as is seen in the yellow coloured cells.
Note that each sheet NL_Link_1 and NL_Link_2 need to be updated independently.
Note: when the nominal structure is altered such as when new codes are added to the accounts database the collation of data in the existing table of codes is not affected. You only need to add the new codes to the appropriate column in the table of codes.
2.4. Validation of the basic data after pasting
Management Accounts Wizard has a number of advanced validation steps to ensure accuracy of the final management accounts. These are described in this section.
There are a number of data checks and validation steps in Management Accounts Wizard.
2.4.1. Trial balance check
Check the data pasted into Sage_paste worksheet. Cell E:1 should equal 0 indicating that the trial balance is 0.
Check cell G:2552 on NL_Link_1 and NL_Link_2 worksheets. Both cells should show a balance of 0.
2.4.2. Are all nominal codes collated ?
When the update is run and all values corresponding to the chart of accounts are transferred to the correct column directly under the chart of accounts section. When this happens the nominal codes (column A) and description (column B) fields change to show a blue background and red text. This gives a visual indication of all the codes being collated. Note that in this example codes 7103, 7200, 7201, 7202 and 7203 are still in default colours. This is due to the fact that although they are part of the nominal code structure in Sage Line 50 system but are not in fact used and their balances are 0. They are not represented in the chart of accounts.
Each time you prepare management accounts visually run down these fields to check that the balances of all the cells in default colours are in fact zero. Of course when the nominal structure changes in the accounts software new codes will appear that are not entered in the chart of accounts and these will be picked up using this check.
2.4.3. Departmental Splits
Where departmental splits are entered manually the cells containing the nominal values in the departmental columns also change colour to show that each value is collected and summed in the table of codes. This gives a quick indication that all the necessary information has been collated. Note that columns H and I contain a check on the transfer of the departmental data. Column I sums the data entered in DEPT1 to DEPT10 for each row and column I calculates the difference between that sum and the nominal balance.
2.4.4. Duplicate nominal entries or other copy errors
Another check on the data takes place in columns X and Y of NL_Link_1 and NL_Link_2 worksheets.
Column X shows the difference between the nominal balance as output from the accounts database and the total amount transferred (shown in column Y) and captured in the table of codes. In the example shown (underneath) the yellow highlighting shows that there is a variance of –506,866.06 in the transfer of warehouse equipment. The reason is obvious in that it has been copied twice and a quick look to the columns on the right (underneath the chart of accounts shows that indeed this is the case.
This error warning will also show up when for example errors occur in the manual entry of departmental splits as is the case with the example workbooks.
Cell Y:2552 sums all the transferred values and of course should equal zero if all balances have been accurately collated for the management accounts.
These errors are also reported in Update_errors_1 and Update_errors_2 (see example workbooks).
2.4.5. When non-existing codes are entered in the table of codes
Occasionally a mistyped nominal code will be entered in the table of codes. When this happens the data validation algorithm warns the user that this has happened at the end of the processing by means of a popup warning.
The font colour of the entries in the table of codes are changed to red to enable these errors to be tracked down quickly.
The example workbooks has two non-existing nominal codes (in column FF) to demonstrate the error management.
An entry is also made in the Nominal_code_errors_1 or Nominal_code_errors_2
Sheets to track such errors.
2.4.6. Find out where a nominal value was collated
This feature is especially useful. To find out where a nominal code was collated look down the first column A until you find the nominal code you are interested in and then track rightwards until you find the transferred value. If you scroll up to the top you will see which category the value was grouped with in the chart of accounts.
3. Advanced features
Advanced users may wish to adapt the worksheet. Here are some examples.
3.1. Locked worksheets
The workbook is shipped with key cells in NL_Link_1 and in NL_Link_2 worksheets locked to prevent inadvertent scrambling of the setup. Advanced users who wish to modify the worksheet can unprotect the worksheet from [Tools >> protection]. The password is not set so do not enter anything in the password dialog.
3.2. Hidden cells
Note that columns C to F are hidden as they are not normally required. They can be un-hidden using [Format>>column>>unhide] after the worksheet is unprotected.
3.3. Late journal entries
For example cells in NL_Link_1 and NL_Link_2 worksheets column G is the sum of Columns C to F although columns C to F are hidden.
These spare columns may, for example, be used to reflect late journal entries to the accounts database that were not made at the time the nominal ledger balances were exported from Sage Line 50. The author has used this many times to add “late accruals” to the accounts after the month end nominal balances have been taken from the accounts database. Such late “entries” can also be made on the Sage_paste worksheet.
3.4. Worksheet frozen
NL_Link_1 and NL_Link_2 worksheets are frozen at cell G:4 and this reflects the author’s preference. Advanced users may wish to apply a different splits to the worksheet to reflect their preference.
3.5. Cell format
The cells in Sage_paste column A are formatted as text so that leading 0s on the output from Sage nominal balances are maintained. Please contact the author if the output from your accounting package will not work with text format.
3.6. Use of NL_Link_2 worksheet
A second worksheet which can be used to organise the nominal accounts data to provide additional flexibility.
Secondary analysis of data
The example workbook uses the second worksheet to provide a different analysis of the nominal balance data. In the example given when sales and or cost of sales are posted into Sage Line 50 these are allocated to departments to reflect geographical organisation of sales activities. The data can be analysed by “departments” to give regional sales data. As was the case for true departmental allocation of overheads, the process requires running departmental report to obtain the departmental (regional) allocations from Sage Line 50 and splitting the data into the departmental columns manually. This only takes a few minutes. Obviously if you use a different nominal code to enter this data in the accounts package this type of manual splitting in not required.
In the table of codes enter the nominal code numbers corresponding to the data you wish to collate. As was the case when making departmental splits of nominal balances it is essential to name the group in the table of codes using DEPT1 Sales England, DEPT2 Sales Scotland, etc. as the first part of the description is used to strip out the departmental allocation from the appropriate column. See figure on next page.
Note that when using NL_Link_2 worksheet in this manner that there will be a lot of errors since not all the nominal codes are being collated.
3.6.1. Large nominal code output
Some Accounts database packages use double coding (e.g. dept code + nominal code) to identify nominal balances. With these packages 2000 lines of data may not be enough to cover all the nominal balance output. In this case use both worksheets to collate the nominal ledger balances. If you need a workbook with additional worksheets for analysing nominal code balances please contact the author.
3.7. Columns and rows
Do not add or delete columns or rows in NL_Link_1 and NL_Link_2 worksheets as this will prevent the code from working properly the programming .
Chart of accouns
The first column must be AA
The first row must be row 4
The last row must be row 49
Nominal codes, descriptions and balances
The first row must be row 51
The nominal code index must be column A
The description must be column B
The nominal balance must be column C
The nominal balance picked up if there are no departmental splits must be column G
Departments splits must be in columns J to S
Difference between the nominal code balance and the value transferred must be column S
4. MAN_ACC_Period.xls
This provides one example of how to assemble the trial balance into a management accounts report.
It has two bonus functions:
- VB code to print out all the spreadsheet links.
- VB code to highlight all the links.
4.1. Display links macro
This spreadsheet has a macro which will print the links in worksheet xLlinks.
- To use go into xLlinks and delete any existing data in the cells.
- Open the worksheet that you wish to print out the information from.
- Tools >>Macro >> Macros >> xlLink>>Run
4.2. Highlight Links macro
This useful tool can be used to highlight all the links from a particular source. It can be used to pick out the links from a budget spreadsheet to ensure that all of them are updated each month.
- Tools >>Macro >> Macros >>Link_Highlight >>Run
- An input box will pop up asking for filename information. Enter part of the file path which will uniquely identify the link in the cell formula. Usually the spreadsheet name will suffice (e.g. Budget_2005.xls).
- The font in cells containing links with the text entered will have their font changed to red.
- If you do not want to use the example spreadsheet but wish to keep these features delete all the worksheets except xLlinks and build up your own reporting system.
5. Legal notices
Sage Line 50 is a registered trademark of The Sage Group plc.
Microsoft Excel is a registered trademark of Microsoft Corporation.
All other trademarks are trademarks of their respective holders.
Spreadsheets: NL_Output.xls and MAN_ACC_Period.xls
These spreadsheets supplied are originally designed and copyright of First Athlete Ltd.
No warranty is given or implied in respect of any of the products downloaded from this website.