Hi we use Business central and our costing is FIFO. we resell subscription based items to customers that gets billed annually and some are billed monthly. We buy these items from vendor and we get billed monthly. As I post the purchase invoices for items we purchase, I expect the cost of material GL account to reflect the cost of items, however, it doesn’t seem. I see the COGS account is specified for the gen. product posting group correctly along with purchase account. what am I missing here?
Hi Patrick,
How did you setup your posting group? If the COGS account does not reflect the expected cost, what does it reflect? Have you validate the posting accounts against the inventory valuation report?
Thanks Erik let me run the inventory valuation report. Also since it’s FIFO do I need to run inventory revaluation similar in the case with AX?
were you on ax before and moved to business central? or have you personally had experience with both products?
Hi jake, no. I personally have experience with both the products.
Patrick,
Based on what you wrote let’s walk thru all the transactions so you understand each Debit and Credit. Note that I am NOT turning on Expected Cost in my example. A discussion for another day. Now, you purchase subscription Items you are selling and yes, these Items require a Gen. Product Posting Group and Inventory Posting Group to define the G/L Accounts that will be posted too when transacted. Your picture shows the accounts and I will use the Account Numbers in my example.
I will create a Purchase Order for the Item Subscription. Below are all the entries and how they are derived:
PURCHASES WITH “AUTOMATIC COST POSTING” OPTIONS TURNED ON:
Step Transaction Description Entry Amount Setup in Setup Column Details/Comments
1 Post Receipt against PO No G/L transactions Qty. on Hand of purchased item is increased
2 Post Invoice against PO Dr. Purchases - 10700 Actual Purchase Cost General Posting Setup Purch. Account Combination of Bus. and Prod. Posting Groups
Note: The Purchases and Direct Cr. Accounts Payable - 3xxxx Actual Purchase Cost Vendor Posting Group Payables Account
Cost Applied Accounts should Dr. Inventory - 10700 Actual Purchase Cost Inventory Posting Group Inventory Account
either be the same account or Cr. Direct Cost Applied - 10700 Actual Purchase Cost General Posting Setup Direct Cost Applied Account Combination of Bus. and Prod. Posting Groups
be in the same G/L grouping on
the Chart of Accounts.
3 Run Inventory Periodic Activities
I will create a Sales Order for the Item Subscription. Below are all the entries and how they are derived:
SALES WITH “AUTOMATIC COST POSTING” OPTIONS TURNED ON:
Step Transaction Description Entry Amount Setup in Setup Column Details/Comments
1 Post Shipment against SO No G/L transactions
2 Post Invoice against SO Dr. Accounts Receivable - 1xxxx Actual Sales Price Customer Posting Group Receivables Account
Cr. Sales - 40200 Actual Sales Price General Posting Setup Sales Account Combination of Bus. and Prod. Posting Groups
Dr. Cost of Goods Sold - 50100 Average Item Cost General Posting Setup COGS Account Combination of Bus. and Prod. Posting Groups
Cr. Inventory - 10700 Average Item Cost Inventory Posting Group Inventory Account
Note: Value Entry is
at Average Cost.
3 Run Inventory Periodic Activities
You noted that your are using the FIFO Costing Method or First-In-First-Out. First-in-First Out (FIFO) is a costing method that assumes that your oldest goods are sold first. Companies who need a low-maintenance inventory accounting system that will give them clear views of their actual costs at point of sale, but do not yet have the data to help establish standard costs, may find that FIFO costing is best for their business. I often see this method used in distribution companies where variance analysis is not as necessary. FIFO means that the items taken out stock for production or sales shipments will be taken out in the order of first received, first out and are costed with the actual cost of procurement for that FIFO layer (there are specific exceptions to this costing method if you are using lot or serial number tracking, but they are beyond the scope of this discussion). Using Dynamics BC FIFO, the balance sheet will reflect the actual procurement/production cost of items remaining in inventory.
| Costing method | Description | When to use |
|---|---|---|
| FIFO | An item’s unit cost is the actual value of any receipt of the item, selected by the FIFO rule. In inventory valuation, it is assumed that the first items placed in inventory are sold first. |
In business environments where product cost is stable. (When prices are rising, the balance sheet shows greater value. This means that tax liabilities increase, but credit scores and the ability to borrow cash improve.) For items with a limited shelf life, because the oldest goods need to be sold before they pass their sell-by date. |
I hope this all helps.
Thanks,
Steve
Thanks Steven. this is quite helpful. I looked at the entries for one of the posted sales invoice, it doesn’t looks like the system reflects COGS entries (but when I try to preview posting for one of the item, it did show the GOGS entry). btw we don’t create sales orders or purchase orders (invoices only)
Hi there, it looks like we have negative quantity for this item that could be in how we purchase and sell the item. For a given month, we sell the item to several customers based on how much they use it. just for example item a is sold to customer 1, 2 and 3 as $100, $500 and $1000 with 1 qty each (total 3 qty). However, we bulk purchase same from vendor for 1 qty at $1600 (rebates/commissions received quarterly and entered separately) so there is less qty purchased compared to item sold in a given month. is there a way to use some type of journal to purchase the item with correct quantity (we have two years worth of purchase entries need to be input). I looked at purchase journal but that doesn’t seem to included item! Going forward, could we treat this item as non-inventory but how to reflect the cost of material purchased? we are not manufacturing or stocking this item.
Patrick,
Did you run Adjust Cost-Item Entries and post them? The process produces and adds the COGS entry you stated you are missing.
Thanks,
Steve
Patrick,
I kind of follow but was the quantity purchased incorrect? I am not following how I can purchase an Item with a Qty = 1 but sell this Item with more than a Qty of 1. Would it not be correct to Receive and Invoice the correct quantity, for Inventory, and update the Line Discount or add an Invoice Discount to reduce the Cost/Invoice.
You can create Items that are Non-Inventory, however, you will expense the Purchase of the Item. When you sell the Item you will not have matching COGS since this is not an Inventory Item.
I would recommend correcting the the PO when you receive and invoice and make the Quantity correct. You can also enter Purchase Credit Memos as Charge(Item) that impact the Received Item reducing the Unit Cost.
Hope this helps.
Thanks,
Steve
Thanks Steven. Let me explain. This is a metered product. Just like for e.g. electricity; where customer 1 may use for $100 a month, while customer 2 may user for $500 in a given month. that we resell. So I should create three line items for each quantity (sold to customer) as in the example above when creating purchase invoices. Correct? will this work in our scenario? Assuming i have a SKU for this item as MX100 and we purchase at 10% discount, my purchase invoice should look like this?
MX100 qty(1) $90
MX100 qty(1) $450
MX100 qty(1) $900
We have almost two worth of purchase invoices that we need to create, is there a type of journal entry that we can use to create or do we have to create them manually?
should we consider having separate product sku for each customer? and reflect those SKUs in PI? that will create large number of items in the system.
Patrick,
OK, I see now. It is like a subscription Item. So I have customer who subscribe to a Subscription and utilize whatever value for the Item. Sales Invoice can denote a Qty = 1 and the Amount to be invoiced per Customer.
As for the purchase side, your PO, I see you have 10% off each line Item so why not have 1 Purchase Line for Qty = 3 Line Amount Excl. Tax = 1600.00 with a Line Discount of 10%. This nets to 1440. Would this not be fine or do you need each line separate. This give you the COGS entry you want when the Item is sold. Please note that the Unit Cost will not be directly affiliated to the Sales Line layer. If you create the 3 lines you can sell each and enter the Applies-To Item to the specific Posted Purchase Receipt layer/cost.
Just giving you two ideas.
Thanks,
Steve
Hi Steven, yes. we sell subscription items. This is even better. I could go with 1 line item with 3 qty and net amount of $1440. Now that I have sold the items first (SI posted first) and now creating PI, do I have to run the adjust-cost item entries?
Patrick,
Question - You said you can post the Sales Invoice first. This is OK, if the Item has QOH. If not, you need to post the Purchase Receipt to bring the inventory in, the invoice can post after. Now remember, I am using an Item Type = Inventory, as you wanted to show COGS when the Item is sold.
Hope this helps.
Thanks,
Steve
Hi Steven, that’s the issue now that this has negative quantity now because I didn’t do the purchase Invoice or purchase receipt. Now that I have negative inventory and didn’t enter the purchase invoices etc., what is the best option for me at this point of time. for future sales invoices, I will follow the purchase receipt approach to bring the inventory. But now I need to bring the negative quantity to zero for this item. Could I post a bulk purchase invoice missed out for the previous year with post date of beginning of the year for all quantities sold in that period, i.e we sold 250 qty with purchase cost). After I post the PI, what else should I need to do, in order to reflect the cost in COGS?
Patrick,
OK, I follow. Yes, for the past, feel free to Post a Purchase Invoice for the Inventory you need to zero out from past Sales. After you Post Receive and Invoice this will update inventory. Make sure you run Adjust Cost-Item Entries and post. This will update all the COGS amounts for each Sale transaction.
Hope this helps.
Thanks,
Steve
Thank you Steven. This is the on-hand qty for that item ( we did few purchase invoices for few month in 2020 )
|
2017 |
2018 |
2019 |
2020 |
2021 |
Grand Total |
|
|---|---|---|---|---|---|---|
|
Row Labels |
||||||
|
Purchase Credit Memo |
-1 |
-1 |
||||
|
Purchase Invoice |
13 |
1 |
14 |
|||
|
Sales Credit Memo |
1 |
34 |
15 |
16 |
10 |
76 |
|
Sales Invoice |
-3 |
-82 |
-93 |
-81 |
-28 |
-287 |
|
Sales Shipment |
-3 |
-1 |
-4 |
|||
|
Grand Total |
-2 |
-51 |
-79 |
-53 |
-17 |
-202 |
I tried to do a preview post (PI) for 2 qty 2017 with a post date of 1/1/2017, I am happy to see GOGS GL account is updated however, it seems to post for year 2019 but not for 2017! Will it correct if I run the “Adjust Cost-Item Entries” ?
| GL Accounts | 2017 | 2019 |
|---|---|---|
| 10700 (inventory) | 11000 | -11000 |
| 20100 (A/P) | -11000 | |
| 50100 (COGS) | 11000 | |
| Grand Total | 0 | 0 |
In inventory setup we have the automatic cost adj set to “always” . Should I still run the item cost adjustment manually? Just wondering what could cause the COG to post in year 2019. Could it be because I have few PI posted in 2020? . Also to clarify, I have not restricted any posting date in the ledger setup and haven’t closed prior years as yet.
