Si Hay Sistema - Incorrectly Configuring Item Default Income and Expense Accounts
Recently we had a case where our customer misconfigured items for sale by selecting incorrect accounts in the item defaults.
·
5 min read
This was ultimately our fault for providing haphazard training. The result was that we had to correct this mistake by modifying Sales Invoices, Delivery Notes and GL Entries for the corresponding periods.
Consequences of not configuring item default accounts and companies properly
On the Item master in ERPNext the users added an expense account where an income account should be specified, and they added an income account where an expense account should be specified.
Actions have consequences
When generating Delivery Notes, the only problem was the incorrect expense account. As you deliver inventory from your warehouse to the customer, the Item is expensed by the Delivery Note and its corresponding GL Entry. If an income account is listed in the expense account, when the delivery note is submitted and a GL Entry created, the GL entry will have the incorrect account being affected.
When the Sales Invoice was then created, it caused a two-fold problem: affecting an income account as an expense, and an expense account as income. Upon submission, the corresponding GL Entries will cause an obvious discrepancy.
Do I have the switched item default account problem?
This can be confirmed by opening Sales Invoice master in report view, filtering the desired period dates (per month), filtering out Draft and Cancelled Sales Invoices and selecting Menu > Show Totals.
It is crucial to set your filters right, lest you spend hours trying to fix a problem caused by showing more invoices than really are submitted for accounting. Ensure no Child Table fields are selected, as these tend to duplicate values, because it is the only way to show child-parent data in tabular form.
Make sure you are showing the net total column. Remove any columns for child table fields (the report must not show child rows or rows with parents)
The sum of the Net Totals for all Sales Invoices in a monthly period should equal the total amount of Income in the Profit and Loss Statement.
Values do not match! Help!
Usually, the problem is that you will have missing income. In a blank spreadsheet files, label and write down how much Income is shown in your Profit and Loss for a selected month. Do the same for the Sum of the Net Total amount shown in your Sales Invoice Report. Do you have missing income in the P&L? Or do you have extra income? This will give you clues as to the nature of the problem. The good thing is that it can be fixed with access to the database.
CAUTION: Before running any scripts or doing any database (MariaDB / SQL) queries to update values for submitted documents, I ask for you to do two things:
1. Backup the database and make a local copy
2. Take a snapshot of the server before and periodically as you fix the problem.
3. Any changes must be tested on a local clone of your production server. Make any mistakes on a non-essential copy, as you learn from them and figure out the path towards resolution.
- Verify your filters in the Sales Invoice report at least TWICE.(Date periods must coincide with profit and loss statement period, no cancelled or draft Sales Invoices, remove any child table fields)
- If you have verified the filters, then you might have the case of the incorrect accounts! Pick a couple of invoices at random and check their child tables, verify that the expense account stated on the child table for Sales Invoice (Sales Invoice Item) is of type "Expense" and the income account is an account of type "Income". Click on each account name to verify. If not, you need to run database queries on the Sales Invoices AND GL Entries.
- You also have to verify that any Delivery Notes linked to your Sales Invoices have the correct expense account entered. Do the same as above and check each Delivery Note Item row to confirm the Account is indeed an "Expense" account.
- You can then type "GL Entry" on any awesome bar in a logged in Frappe Framework ERPNext session on your production server. Using the Sales Invoice or Delivery Note voucher type or Voucher Name, find the GL Entries for a single Sales Invoice.
What should my GL entries look like?
The correct model for a single sample Sales Invoice with a Delivery Note is:
Accounts Receivable - DEBIT by FULL amount or Grand Total of Sales Invoice i.e. 2251.20
Sales Tax Payable - CREDIT by Sales tax amount of Sales Invoice i.e. 12% included sales tax would be: 241.20
Income Account - CREDIT by Net Amount (FULL Amount - Sales Tax Amount) of Sales Invoice i.e.: 2010.00
Each line above corresponds to 1 GL Entry.
Keep in Mind:
Any GL Entries with a Debit to the Income Account are erroneous!
Feeling Stuck? Do you have this exact problem? Contact us!
We should be able to help resolve your problem in 7 working days (or more depending on data size and structure), for a nominal fee.
What about sales using the Point Of Sale?
For POS Sales, the problem will be with Sales Invoices. The GL Entry is structured as follows:
Same three lines above:
Accounts Receivable - DEBIT by FULL amount or Grand Total of Sales Invoice i.e. 2251.20
Sales Tax Payable - CREDIT by Sales tax amount of Sales Invoice i.e. 12% included sales tax would be: 241.20
Income Account - CREDIT by Net Amount (FULL Amount - Sales Tax Amount) of Sales Invoice i.e.: 2010.00
Two lines reflecting payment
Bank or Cash Account - DEBIT by FULL amount of Grand Total of Sales Invoice (receives payment from customer in bank account) i.e. 2251.20
Accounts Receivable - CREDIT by FULL amount of Sales Invoice (pays the invoice from bank or cash account) i.e. 2251.20
Two lines reflecting stock expense
Inventory or Stock Account - CREDIT by amount of stock valuation of Item
Cost of Goods Sold Account - DEBIT by amount of stock valuation of Item. C.O.G.S. is an Expense account.
Notice that a POS sale is the only valid scenario where a GL Entry for Sales Invoice includes Credit to an Accounts Receivable, this is because the payment is done at the same time as the invoice.
The value of inventory expensed is equal to the value of the item being expensed.
Keep in Mind:
You should never have Sales Invoice GL Entries DEBITING an Income Account!
Any GL Entries with a Credit to the Expense Account (Usually Cost of Goods Sold) should also be erroneous!
Still stuck? Contact us! We want to see you feeling like this:
No comments yet. Start a new discussion.