1 2 Previous Next 17 Replies Latest reply on May 20, 2014 2:12 PM by hobbiesdeveloper

# How to keep track of Purchase Order Inventory Price Changes?

### Title

How to keep track of Purchase Order Inventory Price Changes?

### Post

Hi,

Is there a way to have filemaker automatically change the cost of the product if the Purchase Order Cost is higher or lower when purchasing more stock?

For example, if you purchase 20 same items with the cost of \$5.00 per item and in the sales/order layout you sell them for \$10 per item, than you re-order when the stock quantity reaches 2 and the cost of the item now costs \$6.25, which would mean that we would have to change the cost and price of the item via sales/order layout starting after quantity of 2.

I hope that is clear.

Thanks.

• ###### 1. Re: How to keep track of Purchase Order Inventory Price Changes?

I'm not sure that I am understanding that. I can read it two ways. a) you want to be able to automatically adjust your selling price for an item when the purchase cost changes. b) you want the cost of the item to be controlled by the quantity purchased (different quantities = different unit costs).

My best guess is b).

Two methods are possible in theory, but you'll have to study your actual situation to see which works best for your particular business:

a) a formula (calculation) is used that takes the base unit price for a quantity of 1 and the actual quantity ordered and computes the adjusted unit cost.

b) a Table with multiple records is set up where you have multiple records for each product. Each record records a product ID, a unit cost, and the smallest qty that can be used to get that cost.

With a) you look up either the unit cost and apply the formula, or you look up both the unit cost and the formula to be applied (to have more than one formula for different classes of products) and compute the cost.

with b) you just look up the unit cost by matching the actual qty ordered against the table of products and minimum quantities. There are at least two different ways to set up auto-enter options to look up the price from such a table.

• ###### 2. Re: How to keep track of Purchase Order Inventory Price Changes?

It will definitely be option b. I will try this method out.

Thanks for your help.

• ###### 3. Re: How to keep track of Purchase Order Inventory Price Changes?

Hi,

I'm not able to get the Option B working as that is the one I would most likely need.

How would I match up the quantities as sometimes we re-order the same quantity but there are price changes on the purchase order side. We currently don't want the price change to take affect on the current stock but only for the new stock that we are getting in.

For example.

Quantity on Hand: 10
Cost: \$10.00
Price: \$15.00

Purchase Order: 5 more is being ordered but now the cost is \$12.50

Now, the Quantity on Hand changes to 15, 10 of them having the \$10.00 per unit cost and 5 of them being \$12.50. How would I keep track of this?

Please let me know if you can give me a really good way of doing this so I can keep track of when prices changes need to occur?

Thanks.

• ###### 4. Re: How to keep track of Purchase Order Inventory Price Changes?

I do not think this is a PO issue.  You have a PO to buy 10 at \$10.  That is in the PO table.  But when invoiced there should be a table for on hand.  Using the PO table for this only increments UP.  The other side is to decrement DOWN what gets sold.

So if you have bought 10 at \$10, then bought 5 at \$12.50, your on hand is 15 for a value of \$162.50.  If you sell 12, the costs of the 12 would be \$125.  Your on hand (after the sale) would be 3 at \$12.50 each.

Did I understand correctly?

• ###### 5. Re: How to keep track of Purchase Order Inventory Price Changes?
That is correct. Do u know a way to resolve this?
• ###### 6. Re: How to keep track of Purchase Order Inventory Price Changes?

I do not have the expertise that Phil does (he is my Filemaker idol who I want to be when I grow up).

When you receive the PO into inventory, update the records in the inventory table for the receipt.  I would imagine the item record would have a field for amount received and costs.  Then there would be a sold field or fields.  A field for balance would be PLUS RECEIVED and SUBTRACT SOLD.

The records with a balance of zero can be suppressed, to minimize the view that makes up the active records.  I hope this helps you get the schema going.

• ###### 7. Re: How to keep track of Purchase Order Inventory Price Changes?

That is what I had already setup. The problem I'm facing is how to have Filemaker change the Price of the Inventory item when received stock has a greater cost. We wouldn't want to increase or decrease the price until the Quantity on Hand reached the new stock we received.

I hope that is clear enough.

• ###### 8. Re: How to keep track of Purchase Order Inventory Price Changes?

What you describe does not match what I posted as "Option B" nor for "Option A".

I understood this to be a case where the customer is quoted a different unit price depending on the volume ordered. That has nothing to do with managing price changes--it's a "volume discount" pricing structure.

When it comes to managing price changes, some businesses specify an effective date for price changes. When business opens on 5/28/2014, these price changes go into effect...

Such an approach can be set up in its own pricing table linked by date and ProductID with an inequality and sorting to refer to the most recent price for that item that has a date on or before the current date.

But it seems you want the price you paid to purchase the item to determine the price for which you sell the item?

That creates a problem since you end up with a set of items in inventory that have an identical product ID, but different prices. (in extreme cases, maybe more than two such prices...) Not only is there no way to tell, just by looking at the item which price should be used, I would think that this would create issues when you need to communicate those prices to your customers. ("We can deliver the 35 items you ordered, 12 will cost you \$10 and the remaining 23 will cost you \$12...")

This could  be handled in a database system, but note that you will not only need to check off items sold from their original PO quantities, but you will also need to create two line items on some sales invoices to document the fact that the customer is receiving items that are identical, but priced differently. The complications that this produces is probably why most businesses don't try to manage pricing in this fashion.

• ###### 9. Re: How to keep track of Purchase Order Inventory Price Changes?
What would you recommend for most business to use?
• ###### 10. Re: How to keep track of Purchase Order Inventory Price Changes?
Would you know how to create a volume discount pricing structure?
• ###### 11. Re: How to keep track of Purchase Order Inventory Price Changes?

In my accounting mind-set, pricing is what I do with MY customers.  Volume discount pricing would be reflected on the invoice.  I would have a field on the line-item I am billing that evaluates the quantity being sold to a matrix of discounts.  Then the extension across would reflect that in the final calculation.

• ###### 12. Re: How to keep track of Purchase Order Inventory Price Changes?

I'm also kinda stuck when it comes to figuring out a method to calculate pricing…

I'm a newb building a solution in FM Pro 12 for my retail business. We are a Tire Dealer and service station. We also sell Lubricants, parts and so on. My FM solution mainly focuses on making it easy for the staff to look up inventory and pricing for sales. About a thousand product codes when its done.

I'll use tires as an example. Let's say…

In February we received 10 tires of a particular product code.  Each tire cost 100.00 dollars each. Let's say the markup on this tire is 20%, so the retail price would be 120.00.  That's easy enough with a calculation field - (RecievedInvoiceItem::CostEach * 1.2)

So we sell 4 to Johnny for 120.00 each.  Then we sell 4 to Jane for 120.00 each.

Now we are down to 2 of the tire on hand that cost 100.00 each

If  ( Sum (ReceivedInvoiceItems::Quan_IN ) - Sum ( SoldInvoiceItems::Quan_Sold ) = 0 ; 0 ; Sum ( ReceivedInvoiceItems::Quan_IN ) - Sum ( SoldInvoiceItems::Quan_Sold ))

It's time to reorder and normally we expect prices to go up if they change at all but often they do go down in price. So we order 8 more of the same tire but this time our cost is 90.00 and the retail selling price would be 108.00.  Like PhilModJunk said:

"I would think that this would create issues when you need to communicate those prices to your customers. ("We can deliver the 35 items you ordered, 12 will cost you \$10 and the remaining 23 will cost you \$12…")"

Phil comes in and buys 4 of the same tire the next day and (as we do it now) looking at our good 'ol on paper inventory I would say the price is 114.00 each. (2 @ 120.00 and 2 @ 108.00 averages out to 114.00)

At first I thought about using Max(ReceivedInvoiceItems::CostEach) but that's not the answer if prices do go down.  I also thought about using the average cost but that doesn't take into account the actual cost of only the inventory actually on hand.

I would like to use a calculation or a script to generate a selling price taking all this into account. Or am I looking at the problem from a completely wrong direction?  What are some of the methods you pros use to generate a selling price?

Thanks for listening!

• ###### 13. Re: How to keep track of Purchase Order Inventory Price Changes?

I can help you set up your database, but how you choose to run your business is up to you.

If you are going to use vendor cost to automatically set customer price, then I think you'll need a system that computes that price from the quantities on hand and their individual unit costs. Chris Hass has kindly described one method for producing a unified item price to use for billing the customer. I think that might create issues when customers call in for a price quote on tires (you may sell some before they come it to buy and then your inventory has changed--producing a new price), but that is a manageable issue--you might start an invoice for the customer that documents the price quote that's "good" til close of business that you can refer to and the items can be left in inventory until the tires are actually purchased.)

Much depends on what system you use to track your inventory as it is delivered to you and sold to customers.

Building form the system I outlined here: Managing Inventory using a Transactions Ledger

You could have a table where each shipment of product received has the following fields:

ProductID
UnitCost
MarkUp
cUnitPrice  (calculation computed from UnitCost and MarkUp)
QtyOnHand
cProductIDonHand   (Calculation: If ( QtyOnHand > 0 ; ProductID )

You can then set up this relationship:

LineItems::ProductID = LineItems|Inventory::cProductIDonHand (keep in mind that this system uses LineItems for inventory management)

This relationship can be sorted by DateReceived in Ascending order. That will make the oldest record for this product that still has QtyOnHand > 0, the first related record.

But you'll need a script to compute the unit price for the line item record and a script to update the QtyOnHand fields each time a sale of product is finalized

Would you know how to create a volume discount pricing structure?

There are several options but since this thread has gotten focused on "vendor cost driven pricing", I recommend that you start a new thread for that topic.

• ###### 14. Re: How to keep track of Purchase Order Inventory Price Changes?

PhilModJunk,

All excellent points that you bring up.

I think that might create issues when customers call in for a price quote on tires (you may sell some before they come it to buy and then your inventory has changed--producing a new price)

For what its worth, I have quotes and quote line items in tables related to customers and products to store the quotes as they're created.

I've searched the forum a few times and often browse the posts but hadn't yet found your previous post.

Managing Inventory using a Transactions Ledger - Managing Inventory using a Transactions Ledger

Very Helpful Post! Recommended reading for everyone I think.

Along with the suggestions in the rest of your post, I'm now confident that I can get the pricing to work out well using the LineItems TO and a Transactions table.

Thank You Phil, the real-world techniques and tips in your posts have been the best boost to my learning curve I've found!

They are appreciated!

1 2 Previous Next