Changing costing method

Changing costing method
Jump to Best Answer
Follow
Graeme Gallagher
Graeme GallagherNov 21, 2022 12:37 AM
Hi everyone, I’ll openly admit that I’ve asked our vendor this question and Googled until I’m blue in …

1. Changing costing method

Graeme Gallagher
Posted Nov 21, 2022 12:37 AM
Hi everyone,

I’ll openly admit that I’ve asked our vendor this question and Googled until I’m blue in the face and really didn’t like the answer… so I thought to ask you good people.

We have roughly 25k products in our database, many of which are imported raw materials. With the mess in the global supply chain, the yo-yo-ing freight costs and crazy currency fluctuations, the board have said we need to move to an average cost basis on our raw materials.

The answers seems to be ‘yes you can but’. Basically, we need to rename existing products, create the new products as average cost, transfer the inventory between the two, update any open POs/SOs/AOs and BOMs, and then figure out what on earth to do with our data warehouse. Technically not difficult but a significant piece of work.

In short, we’ll be working Christmas and I’ll be going greyer!

Please tell me someone has figured out an easy way to do a “update blahblah set costingmethod = ‘average’ where costing method = ‘standard” in Business Central?

Thanks
Graeme

——————————
Graeme Gallagher
New Zealand Panels Group
Auckland
——————————

2. RE: Changing costing method
NAVUG ALL STAR
Lewis Rosenberg
Posted Nov 21, 2022 02:27 PM
Sorry to tell you that you have been given the correct response based on everything I’ve ever seen, read and been told.

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare/Mars Horsecare
Chalfont PA
http://www.apifishcare.com
————————————————————————
D365UG-BC/NAVUG All-Star
D365UG-BC/NAVUG Advisory Board Member (Former Chairperson)
D365UG-BC/NAVUG Programming Committee

Twitter: @RosenbergL
LinkedIn: https://www.linkedin.com/in/rosenbergl
————————————————————————
——————————

3. RE: Changing costing method
Graeme Gallagher
Posted Nov 23, 2022 12:24 AM
sigh

Thanks Lewis, appreciate the response.

——————————
Graeme Gallagher
New Zealand Panels Group
Auckland
——————————

4. RE: Changing costing method Best Answer
NAVUG ALL STAR
Robb Delprado
Posted Nov 23, 2022 11:39 AM
Edited by Graeme Gallagher Nov 27, 2022 05:52 PM
Graeme,

I refuse to accept this as a final answer. Every time I discuss this with a Partner, they are afraid to risk that a costing issue might occur down the road. However, I have done this successfully in the down and dirty way. I have also discussed the down and dirty way with Tom Blaisdell at Microsoft.

However, Average Costing in Business Central with a large number of transactions in a SaaS environment is BRUTAL! There I said it. Every time you receive additional quantities or have a return, BC will go through the recalculation of the Average Cost.

The down and dirty way will work and I as I stated above, I have done it. However, I have always moved away from Average Costing.

Before doing this in your production environment, make a current backup and run this in test.

Step one, run an inventory valuation report for every Item in the database.

Step one and a half, (in your situation), calculate the Average Cost for each Item you will be changing the Costing Method based on the actual quantities on hand.

Step two, run the Inventory to G/L Reconcile report and verify your inventory to the G/L is in balance.

Step three, post a separate negative inventory adjustment against every open ledger entry for each Item you need to change the costing method.

Repeat steps one and two which become steps four and five.

This allows you to verify the valuation you have just written off.

Step six, verify there are no open Item Application Entries for the Items you are going change costing methods. This is very important. You cannot not have dangling transactions.

Step seven, using a processing only report, change the Costing Method to Average for those Items you need to update.

Step eight, for each Item you posted those negative transactions, post a positive inventory adjustment using the average cost you calculated in step one and a half and summarize on hand quantities by location.

Step nine and ten, repeat steps one and two verifying the Valuation has not changed and the inventory is still in balance with the General Ledger.

If there were no open Item Application Entries in step six, Business Central’s costing take over and the next time you post a transaction the calculation will be at the Average Cost.

Merry Christmas!
God Bless those who try what others say cannot be done.
#theworldisflat

​

——————————
Robb Delprado
Senior Trainer and Consultant
New View Strategies
robb@newviewstrategies.com
860.558.8032
Houston, TX
——————————

5. RE: Changing costing method
Jimmy Yu
Posted Nov 22, 2022 10:21 AM
Graeme,

I’m not sure what the rules are where you live, but here in Canada, for a publicly traded company, changing costing methods means to restate the financial statements for prior years. Most companies I’ve worked for ask the same question, how do we get from average costing to FIFO. Then they consult their auditors and I never have to perform the task. Perhaps this is something to ask to avoid the grey hairs you will inherit this Christmas.

Regards,

——————————
Jimmy Yu
Enterprise Architect
Step Energy Services Inc
AB
——————————

6. RE: Changing costing method
Graeme Gallagher
Posted Nov 27, 2022 06:09 PM
Hi Jimmy,

Luckily, we’re privately owned so the external scrutiny is less BUT we will still need to re-state the historics. Again, luckily, we moved to BC about 18 months ago from a system where they used average cost so we just have to restate the original state (if you know what I mean).

Bottom line, settling on a costing method for the future has been a real pain in the butt!

Thanks
Graeme

——————————
Graeme Gallagher
New Zealand Panels Group
Auckland
——————————

7. RE: Changing costing method
NAVUG ALL STAR
Kim Dallefeld
Posted Nov 29, 2022 11:30 AM
I totally agree with Robb, I’ve done the change of costing method before many times without creating new inventory items.
Just one thing, to add to Robb’s answer, run Adjust Cost before you start and as the last step before changing the costing method.

We did this a little different than Robb
1. Run adjust cost
2. We ran a physical inventory journal for the desired items
3. Saved a copy of the journal
4. Adjusted all quantities to zero
5. Ran adjust cost
6. Used a processing report to change the costing method
7. Prepared another physical inventory journal
8. Loaded back the quantities previously saved.
9. Ran adjust cost
10. Validated inventory to GL

Note: we used the current standard cost as our new average cost. Robb gives you good instructions for loading back with a better average. And if you want to change the cost when reloading quantities, item journal is the best way for that.

And I agree with Robb, I’ve done the change of costing method before many times without creating new inventory items.

——————————
Kim Dallefeld, [email protected]
CEO/Principal Consultant
Dallefeld Consulting, LLC

New View Strategies, Trainer and Consultant

NAVUG Legend
NAVUG All-Star
NAVUG Board and Programming Committee
——————————

8. RE: Changing costing method
Jimmy Yu
Posted Nov 29, 2022 12:47 PM
Since Kim is adding her two cents, here’s my little contribution.

Kim, Robb, please confirm. When zeroing out quantities and reloading the quantities, you have to use different dates or average costing may kick in and change the amounts loaded. This also has to do with the setup of when average costing is run and how. In the Inventory Setup page, pay attention to the General tab, Average Cost Calc Type and Period.

——————————
Jimmy Yu
Enterprise Architect
Step Energy Services Inc
AB
——————————

9. RE: Changing costing method
Adam Jones
Posted Nov 30, 2022 07:21 AM
Hi Graeme,

We’ve had to do this for a few items, usually only a couple at a time, so not sure how it would handle lots. We were told how to do this, but also told it is NOT supported by MS and so at our own risk. We use NAV17.

IMPORTANT: If you’ve not used Rapid Start before get some help, some good YouTube video’s about as it can be a pain (and dangerous to your data).

Take the stock off stock using an item journal (if you use bins make sure you know where it was from). Cancel any open sales/purchase lines and make sure there are no invoices to post.

Using rapid start on the item table select just “No.” and “Costing Method”, make sure the validate field check box is unchecked on “Costing Method”.

Filter for just the item(s) you want to change (maybe test just one to begin with). Export the Excel data sheet, make the change from standard to average (or vice versa) and import it back again. Don’t forget to apply the package once it’s imported.

Once done, put the stock back along with any PO/SO lines.

Hope this helps 🙂

——————————
Adam Jones
Celebration Paper & Plastics Ltd
Burton upon Trent
United Kingdom
——————————

10. RE: Changing costing method
TOP CONTRIBUTOR
Andrew Good
Posted Dec 02, 2022 04:24 PM
Also, remember all inventory related documents must be completely invoiced. You cannot have half-baked transactions present. This includes production orders.

Finally, your journey to Average Costing runs differs from what most organizations are doing with their moves to FIFO.

——————————
Andrew Good
President
Liberty Grove Software
Oakbrook Terrace IL
——————————