Skip to main content

Navigator’s Guide to Producing a set of Management Accounts

  • Updated

Prior to the commencement of producing a set of management accounts, there are a number of other tasks that need to be completed.

  1. All Vehicle deals need to be closed and profits confirmed. The Suspense code will need to be checked to ensure they have cleared down. eg:- 0.0.70.91, 1.*.70.97 & 1.*.70.96, the only items showing under these codes should be items relating to open deals.
  2. Vehicle write backs posted and any accrued costs posted against stock.
  3. All WIP should be checked before month end and monitored, jobs should be invoiced off if they can be.
  4. Cashbook should be up to date and reconciled.
  5. All sales ledger & purchase ledger accounts should be up to date. GANI should be checked and should have nothing over 3 months old – the only exception to this is vehicle bonuses as they can take longer to be paid out.
  6. All month end journals posted eg: Wages, Accruals etc.
  7. Trial Balance & Reconcile should be checked and any imbalances corrected.
  8. VAT should be checked via the full report.

 

Once everything has been posted and checked, then you can start building your Management Accounts.

 

Management Accounts

 

Management Accounts is an Excel spreadsheet, which is external to the main Navigator system, however the two are linked via an API Key .  

Each department has its own analysis and there is a Summary sheet for the Dealership please see below:

The standard Pack includes:

LOC        Summary of dealership

VS           Summary for the Vehicle Sales Department

NR          New Retail Vehicle Sales

NF          New Fleet Vehicle Sales

NA          New Agency Sales – these are commission only deals

UV          Used Vehicle Sales

P             Parts Department

S              Service Department

I               Indirect Expenses (Head Office)

BS           Balance Sheet

 

A level of customisation can be made to the Management Accounts - additional sheets can be added if you have a requirement, for example Bodyshop or Forecourt. Sheets can also be hidden if there is no requirement for them.

 

Getting Started

 

Prior to starting, there is a requirement for you to detail the nominal codes for the Makes & Models that are sold new.

This can be located via Accounts – Nominal Ledger – Utilities – Make & Model Tab – see screen below

mceclip0.png

You will need to note down your Finance periods and an API key will be required – this is available from the Navigator Helpdesk or DMS Project or Account Manager.

Once you have all this information together, you are ready to commence work on Management Accounts spreadsheet.

 

Located on the Parameter tab is where you will need to complete the following steps.

mceclip2.png

 

 

  1. On line 8 you will see were you will need to input the API Key.

    2. Enter Description & Nominal codes in Column B & C lines 16 to 35

   3. Enter your Financial Periods in Column F & G lines 16 to 27

   4. On the right hand side of the screen there is a button entitled “Initial Map” click now and wait for the sheet to update, this will take a minute to complete.

  5. Click on the button “Remap Make & Models” this will update some of the pre-loading mapping

 

There is one last step to carry out, which  is located on the Control Tab

Here in the middle of the screen there are a few buttons you will need to click on. The one at the top, No.6 “Import Data” This process may take up to 5 minutes as it is pulling through your Extended TB from Navigator.

6.mceclip3.png

Mapping

Once the set has been done and data imported in, this will result in some Nominal codes that will need mapping. A better way of understanding what this means is by thinking of a map and its grid reference.  Each of the department tabs have grid references down the left hand side, we need to match these to our nominal codes that are outstanding on the Reference Table. These will show at the bottom of list

 

To do this there are two options.

  1. Manually enter the code add a C at the end of code if this is a cost of sale or
  2. Click in the cell and then press CRTL+S at the same time and a box will appear on screen. From here you can select which Sheet it needs to go on in the first drop down box and from the second box the code (grid ref) you want to use.

As you do this it is good practice to keep saving your spreadsheet and to click on the “Recalculate” button located on the Control Tab. This will update what you have done and move the codes up to where they should be in the table.

mceclip4.png

A tip to help you with this is that if nominal code has a 60 or 70 type then this has to go on the Balance Sheet.

Anything that has been code with a department 80 goes on the Indirect Expenses sheet.

If you code (map) these first it will leave you with codes for Vehicles/Parts & Service.

Codes can be changed at any time by entering a new code over the old one.

The Vehicle Department have separate tabs for individual sales types, so that Retail, Motability, Fleet and Used vehicle sales can be analysed out. You will need to make sure you are coding to the correct sheet.  If you don’t require this depth of breakdown you can code everything to one sheet.

New Lines can be set up if you require an item separate to what’s already there. These will need to be added to Valid Reference Table.

Confirm Management Accounts

On the Control tab you are looking to see that all the Yellow boxes balance to zero. If they don’t then you will need to review your coding (mapping) and amend. It will also tell you if you have mapped all codes.

 

mceclip7.png

Once balanced you will need to save in to a file on your Desk top. 

Keep the Main Spreadsheet as you template as this will retain all you coding (mapping) and you will just keep up dating this one.

On the control tab you can also select how may copies you would like to print off.

You can re-run your management accounts at any time through your main spreadsheet.

Trouble Shooting

#Ref – how to fix

if you get #REF in the box on line 6 it means that one of the lines in the reference table has lost its formula, to find this you need to do the following.  Go to the reference table and filter column Q by #REF and it will give you the line number which is to blame, make a note off this and then un-filter column.  You will need to go to this line number and drag the formula from the box above in column Q down to this line and this will correct issue.

Mapping Errors

If you find you have coded a line incorrectly you can simply over type the code to what it should be and then click on the recalculate button on the control sheet and this will move it to its new place.

 

Invalid Code

If the control sheet indicates an invalid code in the box on line 6 you can do the same process as above for the #REF error. You will find that this normally happens if you have put a C at the end of the code when it was not needed.

TB imbalance

You need to make sure that you have not coded a balance sheet nominal code to one of the P&L sheet’s by mistake. To check you can filter column E to show just Assets & Liabilities and then check that these have all got a coding reference as 01B in column A. if you find any mistakes just over type to correct code and then click on the recalculate button on control sheet. Make sure you un-filter before doing this. You can also double check the 01B codes at the top of the reference table to make sure you have not coded any P&L codes to you Balance sheet page.

Use LOC Tab to balance & find errors

 

Budgets

There is a budget page for each department.  These haven been set up to make filling them in nice and easy. On each sheet there are 3 coloured selection – Sales, Cost & Units

Related articles