inventory solution -cost per item changes

[dbfreak]

Can anyone give me an idea of what I should do:

I am going to start building an inventory / daily sales solution that will run a little different that the norm.

what I am trying to figure out is how I should go about correctly figuring out profits if the cost of my items change from time to time?

Initially there will be set up costs added to the cost of the first order. But after time there is a possibility the unit cost will change after a few orders of the same item. say 5.00 the first time 4.75 the next couple of times and then maybe 4.00 there after.

If this happens is it smarter to make a new record for an identical item at a cheaper price and "discontinue" what is still currently in the inventory after that quantity has depleted? After which adding the cheaper items into the inventory

I would think if I "received" these items and entered a new cost, and added them into the current inventory and then say averaged the cost it would ruin all the previous data.

I also think having multiple entries with different pricing would get very confusing and if someone had no idea what was going on it might wreck the whole thing..

Has anyone ever had to deal with something like this?

Please keep in mind I consider myself a very small fry amongst you all and this solution will be probably the hardest one i have attempted to complete. Im sure the answer here might involve something very complicated so go easy on me.

[Søren Dyhr]

Read up upon lookups!

--sd

[dbfreak]

Thanks for your response, although I don't really understand how a look up will help me here.

Would you mind giving me an idea of what you meant so I can properly look into this?

Thanks for your help.

[Peter (duksis3)]

Hi,

Typical question.

Supplier change prices time after time as well as our discounts are applied time after time. After the period of time there is problem to calculate expenses, income, profit etc.

What is necessary to solve task?

Information about date, item number, prices and in-out item count.

It can be done on separate records in the same table or relational tables. Relationship is based on date and Item number.

Work fine or overall item count - there are approx. 300 000 item on list.

[dbfreak]

yeah, luckily all of these items don't have that long of a run. usually about 1 year. I could look into doing something like writing a script that would jump to the next item number when the current inventory was depleted and start pulling from the new items inventory with the new pricing.

I really did feel like adding new items with new item numbers would probably be the only way to go to get the the calculated (profits, etc) data as close to accurate as possible.

Thanks for your help!

[Søren Dyhr]

It's usually a matter of not quite getting the adequate number of tables and the business rules such as FIFO ...

Say you wish 10 of a certain item but they are in the stock of 7 @ a price of $10 and 2 @ a price of $11 and 1 @ a price 9 ... here must firstly the ID of the 7 pieces be occupied then the following in that strict order.

You can backward in the relation if not Anchor Bouying see where previous pulls have gone to establish the remaining. All these prices a usually irrelevant for the customer, so when the order goes into an invoice will the 3 itemlines the assignment actually required. Should be lumped into one single line via sub summary reporting.

But compiling the lines via here the 3 ID's of historic values in my humble opinion better than changing prices to the latest arrivals of items. Sales prices on the other hand should come from the quote lines.

--sd

[bcooney]

This really is a question for your accountant. You need to determine how to value Inventory: FIFO, LIFO, Wt. Avg COGS. Will you barcode items?

When you have that answer, we can offer the technique to achieve proper costing in your solution.

[dbfreak]

Yeah this one is a tricky one. I agree I need to sit down with my accountant and sales staff and do alot more homework before attempting to tackle this one. I have asked around and the answer has been pretty close to the same if I'm looking to go as in depth as the idea I have in my mind.

Thank you both for taking the time to follow up.

[Peter (duksis3)]

Hi,

I add sample for this.

Some part of sample is in English, some not, but I hope - one of possible solutions.

The main is - each Item "movement" - each Buy, Sell or Reservation is as new record.

This allow register different sale price for different customers, all dates, amounts etc.

There are 3 tables - table of Items (Prod), table - item

movement (Prod movement) and table PIN - all "movements" ar documented by user "PIN" (timestamp can be added).

[Peter (duksis3)]

Sample file is "dissapeared".

I' ll try again.

Shop.fp7.zip