See also Reconciling Vehicle Stock with the Nominal Ledger.
If there is a difference between the value in the nominal ledger and the value in the vehicle record, the reconciliation report shows a figure in the Variance column.
If there is an asterisk (*) in the Description in the expense code record in Nominal Ledger, the reconciliation report shows the stock numbers causing the variance.
What do you want to do?
View a list of accounts that should appear in the reconciliation report and an explanation of the balances they should represent
View a list of accounts that should not appear in the reconciliation report and examples using a standard chart of accounts
View a list of zero balance accounts in the report
Correct for an expected expense code not appearing in the report
Correct for a variance in the report
These are the accounts that should appear in the reconciliation report and an explanation of the balances they should represent using a standard chart of accounts:
The balance in this account should represent all new vehicles at A/c status A.
The balance in this account should represent all new vehicles at A/c status C. Not all franchises use consignment stock.
When a cost is put onto a new vehicle record (including estimates but excluding fixed costs) the debit entry goes into this account. When the vehicle goes to I status the entry credits this account and debits the relevant P & L account. All entries should clear from this account after the vehicle is at I status, unless 9033/9507 New vehicle costs in progress postings are not used. In that case, any un-received estimated costs are accrued in this account.
When a cost is added to a new vehicle record (including estimates but excluding fixed costs) the credit entry goes to this account. When a cost is posted from source (PL, SL or SO) the debit goes into this account. This account should therefore reflect outstanding estimates only.
All used DEMO vehicles with an A/c status (for new or used vehicles) of A
The balance in this account represents the depreciation accrued to demonstrators using the Adjusted Stock value on the Purchase Brief tab of the vehicle record.
The balance in this account should be zero. When a vehicle is sold/converted with a sales type of D, both the sales values and the cost values pass through this account. If this account is showing a variance, firstly the nominal ledger should be checked to make sure that there are no nominal ledger journals to clear any outstanding balances. Sometimes balances can occur in this account if options have been used on this vehicle that are not set to post to 9059 (for example, finance commissions, house charge reserve, fuel reserve, insurance sales, or warranty sales). This can be checked by looking at the detailed posting table for the model profit and loss postings, under the column for sales type D.
All used vehicle stock with an A/c status of A
The balance in this account represents the depreciation accrued to used vehicles using the Adjusted Stock value on the Purchase Brief tab of the vehicle record.
The difference between a used vehicle's purchase price and its stock value is posted into this account when the vehicle goes to A status. Also the over allowance on a prime vehicle (as shown on the Finance Details tab of the vehicle record) is posted here when the vehicle goes to I status. Both entries are usually made with the suffix of the part exchange stock number and should add to zero.
If there is a balance in this account, you should carry out a transaction analysis by suffix in the nominal ledger to determine the stock number. Check the vehicle record to see if it is a direct purchase where the purchase price is not the same as the stock value. If it is a part exchange, check that the status on the part exchange and prime vehicle is correct.
For example, in a part exchange with over allowance at point of purchase, the Purchase price and VATable amount on the Purchase Brief tab of the vehicle record are identical. Also, check that the Stock value and Adjusted stock value are identical. The difference between the latter two figures is the over allowance against the prime vehicle. If there was no over allowance, all four figures would be identical at point of purchase.
In the contrasting example of a direct purchase, all four figures are the same. There are no details in the PX ref box, which is one way of determining whether the vehicle is a direct purchase or a part exchange.
Using the example of a part exchange purchased from the customer prior to the sale of the prime vehicle, the vehicle should be a direct purchase. When the purchase invoice has been printed, the vehicle can be written down by amending the Adjusted stock value. This causes a debit posting into a used vehicle write down account in profit & loss. This could then be posted into a reserve account to be used to offset the profit of the prime vehicle when it is sold.
When a cost is put onto a used vehicle record (including estimates but excluding fixed costs) the debit entry goes into this account. When the vehicle goes to I status, the entry credits this account and debits the relevant P & L account. All entries should clear from this account after the vehicle is at I status unless 9065/9508 Used Vehicle Costs in Progress postings are not used. In that case, any un-received estimated costs are accrued in this account.
When a cost is added to a used vehicle record (including estimates but excluding fixed costs) the credit entry goes to this account. When a cost is posted from source (PL, SL or SO) the debit goes into this account. This account should therefore reflect outstanding estimates only.
When a vehicle is invoiced, the sales values credit this account and when the vehicle goes to I status the sales values debit this account and credit profit and loss. This account should have a zero balance.
When a vehicle is invoiced, the VAT margin debits this account and then, when the vehicle goes to I status, this account is credited and the debit goes to profit and loss. This account should have a zero balance.
This is a system bank account and any balance in it should be investigated immediately. Allocations for part exchange values, deposits, HP owing and customer owing amounts pass through this account.
The balance in this account is the opposite credit entry to 9021 Consignment stock account.
When a used vehicle is put to A status, a credit entry for the purchase price is entered into this account. When a used vehicle purchase invoice is produced, a debit entry is made.
Any nominal ledger account where the total balance is not being cleared through the vehicle should not appear on the reconciliation report. Examples of this using a standard chart of accounts are:
When a new vehicle is put to A status, a credit entry for the consignment price is entered into this account. The balance is cleared when the manufacturer invoice is batched to this account through the purchase ledger.
This applies to any balance sheet reserve account. When a fixed cost line is placed onto the vehicle record, a credit entry is made into the balance sheet reserve account and a debit entry into the profit and loss. The balance on the reserve account is cleared via an entry from either the sales or purchase ledgers.
The following examples using a standard chart of accounts, which might not show as a variance, should always have a zero balance. (You could have a report running daily that details all accounts that should be at zero):
9059 Demonstrator Clearing Account
9062 Over Allowance Clearing Account
9335 Sales Ledger Contra Account
9337 VSB VAT Margin Suspense Account
9422 VSB Default Bank Account
9504 Used Vehicle PL Contra Account
Note: Whether 9504 should have a zero balance depends on your parameter settings.
If these accounts show a balance on the reconciliation report but do not show a variance, the stock numbers relating to the balance will not show on the reconciliation report. In this instance, the stock numbers concerned will need to be determined using a nominal ledger report. Use the transaction analysis by suffix report, where the suffix is the stock number.
Carry out the checks in Correcting for a Variance in the Report.
To correct for an expected expense code not appearing in the report:
Check that the Expense code exists in the vehicle posting tables.
If the preceding step does not resolve the problem, check that the Expense code in the NL expense codes form in Nominal Ledger has a Posting Priority of 7 or above.
If neither of the preceding steps resolve the problem, check that Suffix mask of the Expense code on the NL expense codes form in Nominal Ledger is correct.
If none of the preceding steps resolve the problem, check that there are vehicles in your system that would post to the expense code account.
Note: The account does not appear on the reconciliation report until Vehicle Stock Books posts to the account.
There are some general actions that you can take if there is a variance in an account and there are some specific actions.
Tip: The easiest way to check postings on a vehicle record is to run the Vehicle Transaction Locator report in the nominal ledger. This is report K25 in Report Generator.
If there are several variances in the reconciliation report, check the end of day timed operations that both the vehicle recalculation and the vehicle reconciliation successfully completed.
If the preceding step does not resolve the problem, check that the accounts postings for the vehicle to ensure that there are no nominal ledger journals affecting them.
If neither of the preceding steps resolve the problem, check the end of day timed operations to ensure there are no withheld daybooks. You can also check this in the View Posting buffer in the nominal ledger.
If there is more than one location in the company, there may be a variance on one account and the opposite variance for the same vehicle on the same account but a different cost centre. This usually happens when costs have been added when the vehicle was at one location and the vehicle subsequently changed locations. Check the vehicle record and the posting. You might need to journal using the Control Account Journal but you should check with Software Support first.
Where there is a divisional/global nominal and multiple Vehicle Stockbook companies, vehicles from one company might appear on another company's reconciliation. This is normally caused by an incorrect location on a vehicle record in one of the companies concerned. Check which record is correct with an ad-hoc enquiry in the new or used vehicle stock file listing LOCN BUYLOCN and possibly SELLLOCN and excluding the correct locations.
Check to make sure that the Vehicle Specification tab of the vehicle record (new or used) is complete and correct, and also check to make sure that the physical and Buying location fields on the Purchase Brief tab of the vehicle record are correct or if they have been changed. There may be an opposite variance in another cost centre.
Check the lines on the vehicle to see if the variance matches a cost and check to see if the cost has been entered manually rather than from the source - there will be an actual cost but no cost details (order number, date or account number). This does not apply to fixed costs (source code F).
Check the PL, SL and SO entries in the nominal ledger for this expense code and make sure that all lines have updated the vehicle record.
If the suffix on the reconciliation report has an X on the end, call support for further investigation.
Check to see whether the location has changed and whether there is an opposite variance in another cost centre.
If a balance exists on this account with no variance, the vehicle is not at I status. This can be caused by a user dropping the accounts status after invoicing or by specification lines (for new or used vehicles) in the vehicle record that are yet to be invoiced.
Check the invoices and amounts of the invoices produced on the vehicle against the invoices that have posted into the sales ledger. The relevant account numbers can be found on the Finance Details tab (for new or used vehicles) of the vehicle record or an enquiry in the transaction file on the sales ledger.
If it appears that not all documents have updated the sales ledger or that the profit & loss postings have been duplicated, please place a support call for further investigation.
The following ad-hoc report can be run on both the current and the archive transaction files (Sales Ledger, reports & enquires, enquiries menu, transactions/archive transactions) to identify the account numbers and document numbers relating to the vehicle:
Formulate query: ACCOUNT DOCNUM DOCDATE BASEVAL BASEPAID IF REFERENCE CO "N000123"
Execute query: DISPLAY '1.trans.Account code' '1.trans.Document number' '1.trans.Document date' '1.trans.Document base total' '1.trans.Amount paid (base)'; IF '1.trans.Customer reference' CO "N000123"
ACCESS=BLAST; DATE=DD/MM/YY;
You should also check that the Specification tab (for new or used vehicles) of the vehicle record is complete and correct and that the Selling price line for the vehicle is the first line on the page.
As with the 9335 VSB Sales Ledger contra account, you should check the sales ledger to ensure that there are no documents that are not detailed on the Finance Details tab (for new or used vehicles). Also, check for zero value documents where the system has posted a VAT only document. If a VAT only document exists, please place a support call for further investigation.
Balances on this account result from the prime vehicle not being at I status. You can also expect this vehicle suffix to appear on the 9335 VSB Sales Ledger contra account and also the 9337 VSB VAT margin suspense account on a margin VAT vehicle. If you carry out all of the checks detailed in 9335 VSB Sales Ledger contra account section and the problem persists, place a support call for further investigation.
Check the purchase ledger to ensure that the purchase ledger invoices and credit notes have gone through correctly. Search the purchase ledger transaction file and archive transaction file using the same ad-hoc report shown for 9335 VSB Sales Ledger contra account. A common problem on this account is where a figure has been put in the field under the purchase price in the Purchase Brief tab of the vehicle record.
You can print a purchase invoice and alter it. (The three fields under Purchase price in the Purchase details group of the Purchase Brief tab remain editable). If you then print a purchase credit, the value is changed accordingly. You can check it in the vehicle log file.
Another common problem is that the A/c status has been raised to A but the purchase invoice has not yet been printed.
Note: Whether this constitutes a problem depends on your parameter settings.
The program works by loading each non X status vehicle and, assuming an initial accounts status of M for memorandum, and given its current accounts status, diverts all of the postings that would be made between those two points into a sort file, which is then sorted by nominal ledger account code and, within that by suffix, and which is then compared to the actual values in the nominal ledger transaction file.
This procedure works for all the stock accounts where both sides of the stock posting are generated by the same vehicle. Further to this, a work-in-progress account may be reconciled, as it is possible to derive the debit postings to the work-in-progress from the current actual values held on the vehicle specification lines for Point-of-Sale, Purchase/Sales ledger batch input and outside purchase ordered lines.
Clearing accounts where the opposite sides of the entries are generated from different vehicles, such as sold vehicle and part-exchanges, may not always balance using this procedure as the sold vehicle may go to X status or be deleted off the system while the part-exchange is still in stock. This does not cause a major problem, as the clearing accounts should always have a zero balance as far as the Nominal Ledger is concerned, so Vehicle Stockbook balances can be ignored. If the Nominal Ledger balance is not zero then obtain a listing sorted by suffix for that account, reconcile the equal and opposite entries for the corresponding vehicles, this makes errors easily found.
The only Vehicle Stockbook related accounts which cannot be reconciled by the methods previously described are those where the opposite entry comes from outside the Vehicle Stockbooks, such as the vehicle creditors account for new vehicle purchases, where the Vehicle Stockbook debits fully paid and/or consignment stock if it is posted and credits the PL contra or clearing account. The manufacturer's invoice or direct debit advice is batch input through the Purchase Ledger, crediting the control account and debiting as well as VAT input and sundry accounts an amount to the same PL contra or clearing account.
The PL contra or clearing account will be of a suffix open-item type, where the suffix is the vehicle stock number. Even though one side of the entry (the debit) was not posted to this account through integration from the Vehicle Stockbook, it will be itemised within the account. It is possible that the direct debit or tax invoice does not reconcile exactly to the original consignment value, and any such errors should be investigated by producing a report of transactions for the account, sorted and sub-totalled by suffix, and if necessary, making an adjustment to either the Purchase Ledger posted value or the Vehicle Stockbook consignment amount, whichever is incorrect.
Other accounts may be set up in a similar way, where the contra entry has been generated externally to the vehicle stock book, and if this is the case, the priority set against the analysis or 'expense' code within the Nominal Ledger should be either at or below priority 7 so that it is ignored by this automated reconciliation procedure.
This program will only produce a report on those Vehicle Stockbook balance sheet accounts where the access priority is greater than level 7 and the account is controlled at a suffix level. The suffix in all cases should be the Vehicle Stockbook number.
It is possible to achieve a reconciliation at suffix level rather than just an account total by adding an asterisk to the description field of the analysis or 'expense' code. All accounts will be reconciled down to a unique suffix level, and where any variance exists, details of the postings made to the Nominal Ledger and Vehicle Stockbook balance will be printed.
When the reconciliation report is printed there will be two columns, one displays the entries within the Nominal Ledger, the other column displays the values from the Vehicle Stockbook records. It is possible to get entries in the Nominal Ledger column without corresponding entries in the Vehicle Stockbook column, because the vehicle has reached accounts status X, but the Nominal Ledger entries have not cleared. If this is other than a timing delay then a Nominal Ledger journal will need to be posted for the appropriate suffix. Provided that the total value on an account for a specific suffix 'adds up' to zero, then the transactions will be consolidated out of the Nominal Ledger transaction file at the next period end run.
Note: It should be noted that it will not be possible to carry out a suffix reconciliation if opening balances for vehicle related accounts are entered on a 'global' level.
It is essential that opening balances for vehicle related accounts are posted on by individual journal lines for each vehicle, quoting the suffix in its correct format, being either N for new or U for used followed by the full six-digit numeric stock number (including leading zeros), for example N000123.
Note: In order for this program to run most effectively, and not produce misleading errors, it should be run after all daybooks have been printed, and before any further vehicle administration is carried out. Ideally it should be run as part of the End-of-Day procedure.
