Cost prices confusion

Unfortunately, the Item Cost is pulled into transaction tables as the sale is made. It has to be this way so the database can determine the cost AT THE TIME of the sale. Since the Item Cost can change over time, you would not want to update prior COGS based on the current cost. The TransactionEntry table in the database contains the COGS for each line item on each sale.

Since you have NO useful COGS data in the database for your HISTORY, you might want to update some tables using the current cost information from the Item table now. I am not SQL proficient enough to do the table joins, but someone here might be able to help...

Basically you are trying do something like this:

(Do not run this) UPDATE TransactionEntry SET Cost = x.Cost FROM ... INNER JOIN ... etc... whatever!

Now this might have a ripple effect on other things -> can't think through all the implications right now. Your Batch information table will still not be correct, so some totals reports will still not report correctly. I sure there is some even more fancy SQL to fix the Batch table.

At least you would have some COGS data from the database this way for detailed sales reports, but basically you are out of luck I think unless someone a little smarter than me steps forward (okay - a lot smarter) :)

Jason

Thanks for that guys - The more i think about it the mode logical it is that cost prices are captured at the time of sale and are not updated historically, as obviously costs can change. I'm not particularly bothered about the cost not being in for those sales, so i will just leave it that way, and at least i know for the future.

Just out of interest if i have an item which is supplied by several different suppliers, which cost price does RMS put into the historic data? the primary one? if i have purchased 10 from one supplier and 10 from another does it know to translate that to my margins?

The Item Cost (the cost you see on the general and pricing tabs of the item dialog) are independently stored from the Supplier costs. You can force the system to update the supplier cost when you receive items, but it may still not correspond to the Item cost (unless you are using the Last Cost method of inventory value).

When a transaction is entered at the POS, it takes COGS from whatever the cost is on the General tab of the item dialog. The transaction history has nothing to do with what you have entered on the supplier tab for the item. As far as i am concerned, the cost on the supplier tab is only there to make it easier to do POs. unfortunately, most of the time this information is outdated unless you are in a pretty stagnant business without supplier cost changes. I rarely even use the supplier cost information. I just put zeroes and correct the PO when I get my invoice. Otherwise it takes me twice as long to try and find the changed prices.

Now what you should be concerned with is the cost on the General tab. This should be an accurate reflection of your inventory cost if you want your COGS data to be correct (and your inventory value for that matter).

I'm guessing you are from Europe due to your frequent use of the term "cost price" which is not common in the US and is not used in RMS. There are three terms used in RMS that might make things more clear:

-Supplier Cost: The amount that you pay the supplier for an item. This is on the Supplier tab.

-Item Cost: Your total cost of an item that has been received (inventory value). This is on the General tab.

-Price: The price that you sell to your customers. General and pricing tab.

Now we could get into a whole discussion about how RMS doesn't allow enough options for capturing the true Item Cost, but there are a couple of schools of thought on the issue. Some believe that the Item Cost should reflect exactly what you pay your supplier, which is how RMS handles things. Some believe that your item cost should include all costs to get the item into your inventory (including inbound freight and taxes paid). Those who believe the former think that freight, taxes, and all other costs not paid to the supplier, regarless of the significance, are an accounting function that can and should be handled outside of RMS. Those who believe the latter think that an inventory management system should actually spit out total inventory value numbers (think - balance sheet). RMS does not work this way unless you manually update your item costs, either by doing it at the item level individually or entering your true total cost on the PO prior to receiving.

But I digress (serious tangent). I'm sorry to hijack this into an accounting discussion. I hate accounting.

The bottom line: RMS pulls a snapshot from the ITEM COST (on the General tab) at the time of the sale when it enters a transaction in the database. This will constitute you COGS for that sale. Of course, unless you have captured your full costs somehow in this number as discussed above, at the end of the month when the accounting department gets back to you, you might find your COGS were higher and gross profit lower. In other words, it's almost a waste of time calculating COGS in RMS in my opinion. You might as well just let the accounting dept work out the gross profit

Gross Profit = Sales - (Opening Stock + Purchases - Closing Stock)

If the two stock numbers don't include freight, taxes, and other direct costs of getting the items to you, there will be an accounting adjustment necessary.

Now some jurisdictions let you expense inbound freight and taxes paid on purchases immediately. If you are in this situation, and you want do that, then you have no problem. RMS will work great. If you would benefit from including these costs in your inventory value and your jurisdiction allows it, then by all means, have at it...

My big complaint is that I order things from overseas at times. When that item comes in, I pay duties (call it taxes if you will). That is a part of my inventory value - no doubt about it. RMS does not have a method to handle this, and the add-on modules out there are harder than manually changing the costs in my opinion. Have you ever asked yourself why RMS has a place for Freight and Tax on the PO but they have no effect on item costs? That's bizarre. At least there should be an option!

Rant... rant... rant...

:)

Excellent explanation... You should be writing the documentation for RMS...8)

1. His question was how does the COGS get figured if you have several suppliers for the same item. That is obtained after you specify the way RMS should calculate it in Manager | Configuration. Either last cost or weighted average. The program uses this setting to update the cost in the General tab either by averaging it in (weighted average) or changing it to the last cost paid(Last Cost).
2. If you really must figure in the freight and taxes paid into your inventory value, this can be acheived by applying a formula to the cost on the PO. Example: If you receive 100 items and paid shipping on them the formula would be 'cost + .10' and apply it to all rows. This would make the cost ten cents higher for all items on the invoice and you have just accounted for the shipping in your inventory costs. That was a very simple example as there are multiple ways to apply formulas to the cost on the PO. Having said that, I do agree that it would be better if you could just enter the freight and taxes in their appropriate fields and be done with it, but it does give you a way to account for it if you want to do it that way Craig

That often has nothing to do with the Supplier Cost. Let me try to explain what happens in sequence. If you have the Last Cost method selected, the ITEM COST will be changed to whatever is on the PO when you commit the received quantities to the database. This is not the same as the Supplier Cost because you can change the cost on the PO without affecting the Supplier Cost (unless you have the system set to update Supplier Cost when receiving). If you have the weighted average method selected, again, this has nothing directly to do with the Supplier Cost because the cost can be changed on the PO - and your Item Cost may end up different from the Supplier Cost.

Next, when you do a transaction, the COGS is pulled from the ITEM COST, which may or may not be the same as the Supplier Cost as explained above. Again, that's why I do not put much effort into the Supplier Costs. They are just there for ease of PO preparation in cases where your supplier costs rarely change or if you have nothing better to do than constantly update your supplier costs based on thier new price lists. The important thing is that you have the correct cost on the PO (or transfer in) at the time of receiving/committing. Then, the Item Cost (which is used to calculate COGS) will be properly updated based on the inventroy valuation method you have selected.

Wrong. You cannot do an operation on the cost entered on the PO (a huge pet peeve of mine). You can only do operations based on the Supplier Cost or Item Cost, which are NOT the same as the cost on the PO. What happens if your supplier costs are not up to date? What happens if you manually change a cost on the PO to match an invoice. You CANNOT do a formula operation on the cost value CURRENTLY ENTERED on the PO. This is a major oversight.

You can manually change the PO costs of course, but this makes the feature of updating the supplier cost useless. The tax and freight on the PO should be applied to the line item costs before RMS calculates the new Item Cost (or make this optional) so that the base cost can stay the same an accurately update the Supplier cost if you so choose.

If you don't care about updating the supplier cost, the solution is a little SQL. I developed a custom Excel sheet that sets up the statement for my staff and then runs it using VBA via OBDC. The SQL would be something like:

UPDATE PurchaseOrderEntry SET Price = Price * XXXX WHERE PurchaseOrderEntry.PurchaseOrderID = XXXX

Of course, you need to figure out your factor and PurchaseOrderID first. After running the SQL, you go back into the PO and, voila, you have some real cost information. You can now receive and commit. Unfortunately, it does nothing to help you update the Supplier Cost, but it gets the job of having correct Item Costs, and thus, COGS, into your system.

If anyone is interested in my VBA macros, I could provide them, but they are a little long and would need significant adaptation to your situation.

Jason

This issue shows how each business does things there own way. In my opinion if the system is set up properly you will have it set to 'Update Supplier Cost when Receiving'. This way when you receive your Invoice and reconcile it with your PO(changing the cost and quantities to match the invoice)it will update both the supplier tab and the General tab. This way all your costs are current. Has nothing to do with changing costs on the supplier tab manually as this is done automatically. This is how the weighted average function works. If your cost on an item in the General Tab was $1 and now it is on sale for .50 your item cost will change from $1 to .75(assuming you have selected weighted average), and the Supplier Cost will also change to .50. So, no the Item cost is not the same as the Supplier cost, but does get updated when you receive a PO. Therefore the item cost is calculated using the Supplier cost from the PO(not from the supplier tab, but from the invoice) and assuming you have it set to update suppliers cost, then your costs under Supplier Tab will also be updated. Craig

Ummm. $0.75? Only if you received the exact same quantity you previously had in stock. If you had 1 in stock and received 10 more, your Item Cost would now be $0.545, not $0.75.

Also, why would you want your Supplier Cost to change to $0.50 in this case? When the sale is over and you prepare your next PO, you would have the wrong cost on it.

Just an example, glad you could figure out changing quantities would change the math.

This way I know what my last cost from this supplier was. Prices change continually, I like to have a handle on how much. I will change it to the new price when I get my next invoice.

Sorry, Craig... Didn't mean to step on your toes... I just wanted to be accurate in case someone missed math class and forgot what a weighted average is. Based on the oddities of RMS, I wouldn't be surprised if there was some fuzzy math going on!

As you said, we all like to do things our own way - and that's what being a business manager/owner is all about. The point would be that some flexibility built into the system to support users' preferences would be nice. That, I know, we can agree on!

These types of threads are good for the newsgroup. I like the different perpectives and it forces us to think through the business processes that might otherwise get little attention.