| |
| Publicado el 28,octubre 2015 - 06:42 |
Hello,
This is not a WX problem. I don't know as to how to explain this. I hope you all will be able to understand what I am trying to say....
I am developing a small inventory solution for a canteen. Here there is a need to implement FIFO based inventory management and costing (calculated on per menu item based on amount of raw material used in preparing).
I am not able to understand as to how I should do this.
Can someone with experience on inventory management help me here please.
Here is what I want to achieve for raw material:
01/10 OB 68 units @ $15.00 per unit 05/10 Purchase 140 units @ $15.50 per unit 09/10 Issue 94 units 11/10 Purchase 40 units @ $16.00 per unit 16/10 Purchase 78 units @ $16.50 per unit 20/10 Issue 116 units 29/10 Issue 62 units
Here I have to calculate the cost of an item based on which prices raw materials are used in preparing a plate.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 09:40 |
Hi,
maybe this helps you: moving average |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 10:44 |
Hi Yogi
To get your cost based on Fifo, you need to allocate your stock issues against an purchase.
ie
Purchase ID 1 = 10 units @ $1
Purchase ID 2 = 20 units @ $1.50
then
Issue 15 units must be :
Issue 10 units @ $1 - add column to Purchase record as "Available" and make it 0 units
Issue 5 units @1.50 and allocate to Purchase ID 2 and make available quantity = 15 units
IE: each purchase instance must be used up in order of purchase date until its balance is 0 and then move onto next purchase and use up till its balance is 0.
Your costing therefore has to be able to hold costs as multiple records'
ie 10 @ $1 - linked to PurID 1
and 5 @1.50 - linked to PurID 2 = total cost of $17-50
For audit purposes probably need to track how calculation done,
Hope that helps. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 23:29 |
Hi Yogi,
I will add just one point to Mark's explanation. You need to use Lots (Lot1, Lot2, etc...) for every new purchase
Your purchase record will be
Lot number
Date of purchase
supplier_id
Total
etc..
for lines
Line_id
Item_id
Quantity
price
purchase_id
...
So you can issue your quantity from LOT
Hope I was clear |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,octubre 2015 - 06:41 |
Mark & Aelfassi,
Thanks for your inputs. I will try out what you have suggested. But from my first look I feel if I follow this method then I will not be able to allow a user to edit a record once entered. Like say example that a user enters a purchase inward and then uses the material for two days. After two days the user realizes that he had made a mistake in entering the quantity received in purchase inward. So what he will do is go to the purchase inward entry and modify it. The moment he does this the whole of inventory will become invalid because other data based on this inward as already created and calculated.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,octubre 2015 - 07:01 |
Hello Yogi
The only way it can work is to use transaction records so that adjustments are by made posting a plus or minus transaction rather than editing existing data.
The available product at any price point is then the sum of the different transaction types: opening balance, purchases, issues and adjustments.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,octubre 2015 - 18:22 |
Yes, people make mistakes (all the time).
My solution is to have a live inventory file that tracks transactions as they occur and a montlly closing file that recasts the inventory according to how it should have been sequenced. The monthly closing file uses a layer file to keep track of the various receipts at various times and prices. When recasting, all inventory in transcatoins for a given day are done first and then all the inventory reductions are done second. If an error is discovered, then you go back to that point and rerun each of the various monthly closings until current again. You are right, errors are the BIG problem because they can completely change the FIFO and the end result.
Good luck. It's a tough problem.
Stewart Crisler |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,octubre 2015 - 06:10 |
Thanks everyone for your suggestion.
I will sit on all ideas suggested here and hope to work out something soon.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |