Suppliers' increase in item cost is adjusted how?

Hi there - I have been noticing margins dropping between Revenue and COGS on income stmts so I am familiarizing myself with how inventory works in Sage 50 2014 Quantum and also the various reports that can be generated. I do not input the purchase invoices as that is done by someone else. At the moment I have not update to July/14 PUP so still have Crystal Reports available. Inventory is based on FIFO.

In the inventory & Service sub-ledgers with all the various inventory items there is a tab called Pricing with has the usual
Regular, Preferred, Web Price, along with additional 20%, 25%, 30% and Cost. (I am not exactly sure how each price was generated - is this calculated using a desktop calculator and then manually inputted into each line?)

When material purchased initially the 'Cost' under the pricing tab was - for example - 28.15 per unit as Cost.

Then Regular was entered as 47.02, Preferred as 42.31 Web Price as 39.96, then all of 20%, 25%, and 30% as 27.61 each.

Now next time material purchased cost has increased to 32.21 and posted as that thru Acct Pay Purchase module using that same item number.

In looking at the Inventory and Services Transaction Report I can see the individual transactions of both the purchases and the sales along with the QTY in and out and the costs which is the total cost of qty purchased. I am noticing that the sales prices are as per the Pricing tab but the costs are as per the vendor invoices - which is how it should be. So in the initial purchase everything followed as per the Pricing tab but now that the cost of inventory item has increased the sales pricing remains the same but the cost is increased to new cost resulting in low margin. 

My question is - when one is entering the purchase invoices then does one also have to look at each inventory item sub-ledger to see if cost has increased and if so then manually change each pricing listed in the Pricing tab?  Or is there a better way?

My other question is - how can I get a report that shows the Cost list in the Pricing tab versus the cost on the last purchases made?

Thanks for any help one can give me.

Parents
  • 0

    1.  My question is - when one is entering the purchase invoices then does one also have to look at each inventory item sub-ledger to see if cost has increased and if so then manually change each pricing listed in the Pricing tab?

    Yes.   And to do it 'on-the-fly' as it were, you have to have two logins going so that you can update the prices in one while entering payables in the other.  Once an invoice screen is open, with an inventory item open on it, you can't make changes to the inventory item.  (at least as of version 2014.2, this is the case)

    There is no designated field in Sage 50 for the desired markup or margin, however you could sort of use one of the inventory 'Additional Fields' for that purpose.

    There is a 'discount' feature in Sage 50, but no way to restrict the base discount to particular items.

    Quick *ahem* books makes this automatic by default. (you can set a margin for each item, and the selling price 'floats' as the purchase price changes).  This way of doing things could be great for smaller business that sell any item or service, to everyone at one price, or can discount everything they sell to one customer by a fixed percentage, and who want to pass on 'deals' each time they buy better.

    2.  how can I get a report that shows the Cost list in the Pricing tab versus the cost on the last purchases made

    In the Enterprise Edition, under the 'Item Price Comparison' report.  

    This shows past purchases if "Automatically create inventory & supplier associations if they do not exist" was checked off, and / or past vendor quotes if the information was entered in the Vendor's Inventory tab, or the Inventory Items Vendor tab. 

    If you're putting together a SQL query, the data is in the tInvByLn (Inventory By Location) table, in the dLastPPrce field.   

    (the dLastCost field is the last average - dCostStk / dInStock, if using average costing).   FIFO is more complicated, as it keeps each 'pool' of cost separate.


    The 'Last Cost' calculated value is what is used by the 'pricing' module. 

  • 0 in reply to RandyW

    I wanted to give a more definitive answer to this part of the question, since this would take only a few minutes to do using Microsoft Access:

    can I get a report that shows the Cost list in the Pricing tab versus the cost on the last purchases made?

    So I checked out Sage 50 'Intelligence', so...

    I downloaded it, and

    clicked on Trial, then

    tried to open a report and

    got this:

    I'm using Windows 8.1, maybe this works in other versions of Windows.   The reason for the 'Access Denied' appears to be that the key 'Intelligence' is looking for is in HKCY_CURRENT_USER, and not in HKEY_LOCAL_MACHINE.   Or maybe I have to run Sage 50 as 'Administrator' for security reasons. 

    Anyhow, So Endeth the trial...  

    Has anyone gotten this 'Intelligence' addon to work?

  • 0 in reply to RandyW

    what's the download link

  • 0 in reply to RandyW

    Randy, check to make sure you are running with your third-party security set to Read/Write.  Otherwise I don't know what your issue is.

    Smithco, If all you need is the price list report, that is on the Report menu.  Those price lists in universl are just the ones in that file.  You can create what you need if your file is premium or higher.  However, to put the cost and a specific price list on the same report is not possible that I know of, directly in Sage 50.

    I took a quick look at the "Intelligence" program but there is no container that includes prices lists already predesigned.  So that would have to be a customisation.  So you are best to dump the two reports of Inventory Summary and Price Lists to Excel and use a few lookup commands to combine them.

  • 0 in reply to Richard S. Ridings

    Thanks to all of you for your responses - I don't have BI and I think I would rather get XLGL instead if I needed my client to purchase something along that line. Or check out Ridings’ BizMan to see if something available there.

    At the moment the double logins along with the Sales summary report for margins seems to be the best way to go for now.

    RandyW - the Price comparison report would have been great IF entries been made in the Price and Effective Date columns but unfortunately that did not happen. Pondering whether to still go ahead and enter each one of the 8K items at current prices

    Another option to look at is the Field description so possibly that could be used from here on in. Then one could do reports with this added in. But again this will involve extra work initially.

    Can one export inventory items to excel, copy the ‘Cost’ from Pricing tab into new column and import back into Sage with the new column going into field 1? Or anything along that line?

  • 0 in reply to Smith and Co

    Smithco, File, Import/Export will get you to the Price List export.  Play with it in Excel and then import it again using the same menu and change to Import as appropriate.

    I should add that my program will still require a report to be created.  Currently there are no canned reports that come with BizMan Reports because it was designed to allow people to continue to use the reports and forms that they currently have.  New reports can be created in Crystal and then run from BizMan Reports if you wish and you are not restricted to using the Crystal 8.5 version anymore.  I am using Crystal 2011 right now and Crystal XI works just fine too.

  • 0 in reply to Smith and Co

    Can one export inventory items to excel, copy the ‘Cost’ from Pricing tab into new column and import back into Sage with the new column going into field 1? Or anything along that line?

    If you can use Average Cost, and don't need the exact last purchase PO amount:

    The built-in price tool under Setup | Settings | Inventory | Price Lists | Update Price Lists, will do it easily:

    (requires single user mode)

    The 'Cost' in the Price List is totally different data from the 'Cost' in this tool.   If the client is keeping track of the last cost manually, they may want to change the name in the price list to avoid confusion.  (to something like 'PO_COST'.)

    If they specifically need the price to be updated from the most recent purchase invoice, that's another, much more difficult requirement. 

    But, if the client doesn't carry over much inventory, and isn't adding freight-in, etc. using Inventory Adjustments or Item Assembly to get Landed Cost, going by the cost in the system will give you the same answer as picking through all the detail.

Reply
  • 0 in reply to Smith and Co

    Can one export inventory items to excel, copy the ‘Cost’ from Pricing tab into new column and import back into Sage with the new column going into field 1? Or anything along that line?

    If you can use Average Cost, and don't need the exact last purchase PO amount:

    The built-in price tool under Setup | Settings | Inventory | Price Lists | Update Price Lists, will do it easily:

    (requires single user mode)

    The 'Cost' in the Price List is totally different data from the 'Cost' in this tool.   If the client is keeping track of the last cost manually, they may want to change the name in the price list to avoid confusion.  (to something like 'PO_COST'.)

    If they specifically need the price to be updated from the most recent purchase invoice, that's another, much more difficult requirement. 

    But, if the client doesn't carry over much inventory, and isn't adding freight-in, etc. using Inventory Adjustments or Item Assembly to get Landed Cost, going by the cost in the system will give you the same answer as picking through all the detail.

Children
  • 0 in reply to RandyW

    Thanks - however my client uses FIFO and prefers to use that. I have found a workaround for now. There is an inventory report called Sales Detail report that gives the margins within a specific range of dates. This shows up any abnormal margins and then it is just a matter of drilling down in the Sage 50 report to find if any discrepancies and correct them. However the ‘sorting’ does not seem to work within the Sage 50 report itself.

  • 0 in reply to Smith and Co

    My example used average cost, but the program works pretty well the same for FIFO - The percentage shown in the Inventory Summary report is still based on the average of the value of the outstanding uncleared FIFO cost pools.  It shows the margin % calculated on (regular) price.

    The Sales Detail report will show what has already happened, the Inventory Summary report shows what will happen, and the inventory pricing module will help set up pricing.  

    There is no 'filtering' on the sales detail report, so it can be a bit messy if there are a lot of adjustments.  It should help catch instances when someone is discounting too much, giving 'deals', or not refreshing prices on recurring transactions.