1

I am stuck in a situation where I need to come up with an effective way to calculate cost of pipes which is bought in KG and is sold in length and record cost for profit calculation in accounts..

things to consider:

  1. Even if i weight the pipes in feet and meter and add conversion quantities it wont work... because the material used in manufacturing varies sometimes there is e.g. 1000ft in 50 kg and sometimes there is 1150ft in the same weight..

  2. The bundles purchased are sometimes of 52kg, 49kg and 50kg.

Ideas:

(a. i could purchase in unit... and sell in feet and have a customization where after every order i have an option to mark the end of product.. and when i mark the end of the product the purchase cost i.e. $1000 can be divided by the length sold... but issue is it might take a week to sell the product, so it wont show accurate profit at end of day alterntively i can have an approximate cost and have it replaced once the item has ended? thats the best i can come up with. The con of this is what if its time for closing accounts of the year and only half of the bundle has been sold?

what would be the most accurate way of handing this logic in any ERP? POS? The reason i tagged Magento, SAP because i am curious how Magento and SAP handles this situation?

I am feeling accountants and accounts and finance related guys can also chip in, so I am adding the accounting tag

inN0Cent
  • 363
  • 2
  • 18
  • This seems more like a business process question than a programming question. I would only bother weighing the pipe to cross-check with the supplier's invoice. As far as your budget is concerned, what matters is that the cost of a bundle of pipe varies, but the price you're charging your customers is a fixed $/unit length. That seems like something off-the-shelf accounting packages ought to be able to handle. – zwol Aug 30 '16 at 11:59
  • i need to program / provide a solution to this issue in my app, sales would show perfect but what about actual cost of pipe sold? – inN0Cent Aug 30 '16 at 12:12
  • When you say the bundles come as 52kg, 49kg and 50kg.. Why is this difference? Do they have the same length but different weight? Are they made of the same material? Do they have the same diameter? – user2399432 Aug 31 '16 at 06:55
  • @user2399432 most of the times the length is short and other times the material used varies in weight or the pipe quality has decreased due to which in lesser weight there is more pipe length. – inN0Cent Aug 31 '16 at 07:20
  • So in my opinion.. I think you have to decide if you need precision to your calculations or not.. If not, then decide on some standard values on the length, weight etc for example if the pipes you sell are 52kg, 49kg and 50kg go with the average or what you sell most.. so if you sell them equally choose 50kg or 50.5 kg..If you want precision.. then maybe you have to do some extra work to classify each of the pipes and have your function taking as parameters the weight and length of the pipe to calculate a bulk order you made pipe by pipe. – user2399432 Aug 31 '16 at 07:43

1 Answers1

1

You need to have different UoM for this kind of Item. Purchasing UoM, Inventory UoM, and Sales UoM. I will consider the Inventory and Sales UoM as the same.

Pipe_A001:
Purchase = Kg |
Inventory = meter |
Sales = meter

So, the challenge is to have the measurement in your Inventory UoM then get the price cost, and you need to take into account when you actually receive against the cost they charge in the Invoice.

Ex: you buy on Aug 1, your PO is 50 Kg @ $ 10/Kg, so it Cost you $ 500. Receive actually 49 Kg, but they still charge you for the PO amount which is $ 500. The 49 Kg is not relevant anymore, since you own the item and you will convert to your measurement which is meter. Let's say you measure and it's 320 meter, so the cost per meter is $ 500 / 320m = $ 1.5625/m.

The next batch on Aug 15, you buy another 50 Kg at the same price. Receive 51 Kg, Invoiced $ 500, Length measured 350m. So now you will have the new batch price which is $ 500 / 350m = $ 1.4285/m.

What matters is not the Kg received, but the cost the Supplier Invoiced and the measurement in your Inventory UoM. You might have an agreement with Supplier that they charge only the weight measured at your receiving point.

From this point, it is back to your procedure whether to count as FIFO with different batch or count as Moving Average.

JMS786
  • 1,103
  • 2
  • 11
  • 22