how to calculate costing methods in inventory program

Hi rrjegan17,

i new in inventory system. can you help to explain more like what kind of field that i need in the inventory database. and how to do the calculation

Even I am not sure about the costing methods or algorithms and the calculations involved..

If you would be able to give me a pseudocode of the logic involved, I would be able to give a working code of the procedure or function.

Hi rrjegan,

i also don't know how it work.

If you have at least the logic with you, I can help you with necessary SQL code..
Otherwise I don't think I would be able to help you on this.

pls advice

Moderators/ Zone Advisors,
    Kindly add this question to corresponding questions so that some other experts can help out..

I will try my best here since I live in the costing world sometimes with a boss who is a CPA and being responsible for an ERP system.

We use Average costing, so forgive me if my information on the other areas are weak -- but I will give you as much as I can.

First in First Out Method:
Firstly, in case it is unclear from the name, FIFO indicates that first cost into inventory is first used; therefore, if you build / purchase quantity 10 of an item and inventory it at $100 and then later acquire another 10 at $200, then on a sale of 15 you will have a cost of goods sold that includes 10 x $100 and 5 x $200.  

So from data standpoint, you would need to store costing as multiple records per item.  This is typically done in other costing methods any way in the form of Inventory Transaction History; therefore, you would just ensure that you build this portion properly with date and time stamp.  Additionally, you will need to keep track as items are utilized so that it is truly FIFO.


Month Average moving Method
Will have to get back on this one.  The name would suggest you costs are averaged at the end of month / period so you could have varying transactions mid month but always use cost from prior month's average until average is recalculated at end of this month.  Not sure though and this would be most peculiar to me only because of my experiences with the others.


Fixed cost Method
You store your cost per item period.  It only changes when you change the cost; therefore, if you have costed an item as $100 and then end up making 10 for $125, then your cost of goods sold will be 10 x $100.  When you received in those 10 items, 10 x $100 would go to inventory and 10 x $25 would flow to a variance account.  


So from data standpoint, you would need a flat Inventory Costs table or store this data on your Item Master.


Moving Average Method
As stated previously, using Inventory Transaction data storage what happens here is that you are constantly recalculating costs on inventory transactions.  So if you receive in 10 at $100 and then 10 at $200 like above then you will now have a cost of $150; therefore, same scenario of 15 sold will result in 15 x $150.

Guess you can technically store this in one field as well, but each time you receive product it is recalculated.


M-1

Hi mwvisa1,

can i have some sample on database design for FIFO,Fixed cost Method and
Moving Average Method.

Thank you.

The data design for these are above in the second paragraph of each section that I started with "So from a data standpoint..." except the last one accidentally.  I couldn't find anything useful on Monthly Average Moving Method except is not GAAP.

For the other three, believe I laid out how data should look with respect to cost.

Maybe you need it a little visual, so here you go keeping in mind these are all conceptual (please work through database design that fits your needs).

FIFO:

Item       InvDate         Qty   Cost
123456  2009-01-01    10    10.00
123457  2009-01-12    4      50.00
123456  2009-02-23    50    8.50
123459  2009-06-12    14    100.00
123456  2009-08-14    15    10.00

Simple scenario (with different parts to show you "real" life interlacing of inventory records) - item 123456 is $10.00 a piece when purchased in low quantities; however, purchased in packs of 50 is discounted to $8.50/ea.  In this same situation, note the date is stored with when we inventoried these costs.

So buying 25, the first 10 come from 2009-01-01 cost at $10.00/ea and the other 15 at $8.50/ea in terms of cost of goods sold.  Customer sees same price; therefore, what you are affecting here is margin.


Fixed Cost:

(costs table)
Item        Cost
123456   9.25
123457   50.00
123458   2.12
123459   100.00

Inventory transactions look same as above in terms of real cost, but actual inventory doesn't care when it is was added like costs so can be:

Item        Qty
123456   75
123457   4
123458   1000
123459   14

Now same scenario of buying 25, the cost of goods sold is $9.25/ea for the entire quantity of 25 despite the fact that the first 10 were invoiced from the vendor at $10.00.  As said earlier that difference has to be maintained in a variance table / account and then every so often it is your responsibility to update standard costs so you are not losing money. :)


Moving Average Method:

Database structure can look like that of the fixed cost example above except that on 2009-01-01 the data could look like this:

Item        Cost
123456   10.00
123457   0.00
123458   0.00
123459   0.00


On 2009-06-01:

Item        Cost
123456   8.75
123457   50.00
123458   2.12
123459   0.00

And then 2009-12-31:

Item        Cost
123456   9
123457   49.50
123458   2.15
123459   100.00

The other numbers are made up, but you will see the 123456 number was changing by the weighted average of each transaction so 50 x 8.5 will impact the cost in inventory more than 10 x 10.  (I always think of this as weighted since quantity counts, but that may be the technical difference in Average and Month Average is that average is affecting balance as transactions move along whereas as the Month is calculated as a weighted average for ending inventory value at end of each month or accounting period)

So margins change here by how efficiently you run and when something is purchased (what average the cogs uses).  These discussions all get a bit more involved and GAAP/accounting specific that you may need to consult some reading material in that arena for more details.


Hope that helps.

Regards,

Kevin

Hi kevin,

i new in this area. pls help.

should i design something like below pls advice.


FIFO:

STOCK TABLE
StockAutoID    item                    desc           TotalQty
1                   123456                CPU                 75
2                   123457                Casing              4
3                   123459                keyboard         14

FIFO TABLE
StockAutoID   Item       InvDate         Qty   Cost
1                    123456  2009-01-01    10    10.00
2                    123457  2009-01-12    4      50.00
1                    123456  2009-02-23    50    8.50
3                     123459  2009-06-12    14    100.00
1                     123456  2009-08-14    15    10.00

if the above design correct then how to i add the Fixed Cost and Moving Average Method.

Thought I already answered this, what else did you need ?
You have not responded to my last column.  An inventory system is not something folks here at EE are going to be able to create for you and I can't create one for you in one question, so not sure what more about costing methods I would need to tell you to be quite honest.  If I forgot to suggest it above, if you really need to drill into this further you will probably need to get a consultant or number of consultants involved.

Since you have enterprise software in zones, you may be installing an ERP system.  If you check, most vendors have application consultants who have been versed in multiple facets of operations to help you with the implementation.

Hi mwvisa1,

thank you