I’ve imported a batch of Sales Invoices, filling out the required fields by some extensive trial and error, and have a template I can comfortably work with. However, cost of goods sold for the items on the Sales Invoice import are not being posted. Everything posts with 100% profit.
Each item has a cost listed in the relevant buying price list
Each Item has a Valuation Rate on the Item record.
What am I missing? I was not using the Sales Invoice Item margin fields. Should I be? If I know the item cost, do I just calculate the actual margin value? I guess I assumed that the current cost at the time the Sales Invoice is submitted would be applied to the Sales Invoice Items.
Have separate system for shop floor inventory management. On sale need to create in ERPnext Sales Invoice for income and at the same time reduce Inventory Account and Increase Cost of Goods sold account. Would make sense for this to be done as a single data import.
In the sales invoice import template there is a field on each item for expense account, but no field for value to charge to that account. Furthermore no field for inventory account which is needed.
You have to use 1 in Update Stock. Then the Sales Invoice posts the stock valuation of those items to the cost of goods sold. If you dont, it becomes just an accounting transaction. So only the income account is booked. You can either do that (use 1 in Update Stock column) or submit a Delivery Note which is your stock transaction. Using Update Stock makes the Sales Invoice both an accounting and a stock transaction.
Helps partially. In our use case each package has different quantity and different cost per unit (though same sale price). Thus our use of a separate system for tracking and having no item stock entries in the normal sense. If we did, update stock or delivery note would work (which I had not noticed, thank you).
Your reply raises a related issue we had not considered for migrating to ERPnext. We currently (Quickbooks Enterprise) enter Purchase Invoices direct to Inventory Valuation (balance sheet account) to create payable (step 1). We create Sales Invoice of non-stock item for Income (Step 2). Then journal entry for each sale from Inventory Valuation Account to Cost of Good Sold Account. (Step 3). Steps 2 and 3 we can replicate in ERPnext, but have not been able to post a purchase invoice to a balance sheet account (Step 1). Can it be done?
You don’t need to do all that. If you buy something at say $5 and as yoy make a purchase receipt and you set up the item as a Stock Item, your Current Assets, Stock Assets go up by $5. Now when you ship this item at $7.5, $7.5 moves to your income account and $5 hits your COGS. So, it’s a different world out here.
For non stock items, as you make a Purchase Receipt and a Purchase Invoice, the value gets automatically expensed out, though you get to choose which Expense Account you want it to hit!
Jay really appreciate the feedback and the highlighting of alternatives. But this last one do not think works in our use case.
We cannot Purchase Invoice against Expense Account as pounds we purchase may not be sold for months and recognition guidelines require us to go to Inventory Account (any way to do!!).
Standard stock item we believe is not workable as we buy many materials from many suppliers at multiple prices per pound. We process with associated weight discrepancies and 1 package ends up with pounds from multiple suppliers purchased at different prices at different times. No Avg Cost, FIFO, LIFO system is going to track that accurately through stock to deduct the right amount when say 1,234 pounds are sold.
We enter each payment to supplier for raw materials in $ as a global number, accounts payable against inventory (we do not even know real quantity at that time as have not processed and weighed). As you indicate Purchase Invoice for stock item accomplishes this in $, but quantity would be wrong!! So eventually after registering many purchases and sales we could end up with negative quantity with positive value and other undesirable situations. The solution is probably to enable some Mfg functionality so received quantity does not have to equal sales quantity, but seems more steps to accomplish than what we do now.
When we sell, we do steps (2 and 3) described in previous post as our external system tracks cost for each pound and knows specific cost for each package (which is different even if they may have same quantity of same material).
We always have discrepancies in inventory account as pounds paid for are not exactly pounds received, plus losses in processing, etc. Twice a year we do a physical and revalue inventory account to what is actually on floor. Variances are normally < 10% of inventory value and < 2% of total COGS for period, so acceptable for us and auditor.
Would love to abandon our home made external system, but have not yet figured our how to use std ERP for our case where there are no standards.
Mar 1 buy 1,000 lbs for $1/lb (actually turn out to be 990 lbs, but already paid)
Apr 1 buy 2,000 lbs for $2/lb (actually turn out to be 2,200 lbs but already paid)
May 1 get 3,000 lbs for $0.50/lb (turn out to be 1,000 usable pounds threw out 2,000)
Process some in May (not all) and end up with 1 box 800 lbs with some of all 3 above (200, 400, 200), 1 box 600 lbs (150, 300, 150) with some of all 3 above and have unknown quantity unprocessed.
Sell 800 lb box June 1, for $1.50/lb so income is clear $1,200. How would we easily assign portions of above purchases to the 800 lbs for the system to cost correctly. Sales invoice would be a single transaction for 800 lbs, not transaction for 3 packages of 200, 400 and 200 lbs.
June process remainder and get 1 boxes of 1,000 pounds (500,500,0), 1,200 pounds and one of 1,500 pounds.
Sell on July 1 boxes of 600, 1,200 and 1,500 lbs at which time item inventory is now negative 100 lbs, but value has to be positive as have a box of 1,000 pounds in stock made with material for which each pound was paid for.
