Results 1 to 7 of 7
  1. #1
    Norbayah is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    5

    How to create a material cost saving queries?

    Hi!

    I have tables of "Inventory", "Purchase Order Details" and "Suppliers" in my Database currently. My client would like me to provide a monthly/yearly report on cost savings. For example, in January, we purchased 10 light-bulbs at $1.70 each from Supplier-A. In the same month (January) with a new supplier; Supplier-B, we bought another 10 light-bulbs at $1.50. That gave us $2.00 saving. As such the client wishes to know the amount of savings in each month for all items. By end of the year, he wants to see the annual savings report.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Your order detail must have the field [supplierID], added with the product. Even tho the customer need not see this, you would need it for this report.

    if you did not capture it, add it now, and you could back-fill it by the date of purchase,
    And use the date you started with the new supplier.
    tho it may get fuzzy on the inventory where you used both suppliers at the same time.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just wondering...
    Is the product code/UPC for Supplier-A for light bulbs the same product code/UPC for light bulbs for Supplier-B?
    If not, how are you going to compare costs for a specific product?

    Does the order (date) affect whether or not there is a savings?
    For instance,
    10 light-bulbs at $1.50 each from Supplier-B was bought on Jan 6th, then
    10 light-bulbs at $1.70 each from Supplier-A was bought on Jan 16th.
    Would you still consider this to be a $2.00 savings???

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Hope this is a theoretical exercise - on its own this basic comparison is insufficient to show 'savings'. Best you have is a comparison of buying prices from one supplier v another. What if supplier A drops their price - is that a saving? Do you consider it a saving if supplier A stops supplying this particular lightbulb? Perhaps the bulbs from supplier B are an inferior quality and only last half as long and those from supplier A - so you buy twice as many - equivalent cost is therefore $3.00 per lightbulb 'life' - is that a saving?

    usual way to handle this is to have a standard price of say $1.60. When you buy from supplier A, you lose $0.10, and from supplier B you gain $0.10. But you can use a standard price of $1.70 if you wanted to.

    Also depends on why you are buying the lightbulbs - if to replace bulbs in the office, it is a overhead (sic) cost and as such would/should be budgeted as say 500 bulbs a year @$1.60. Over the year you actually use 400 bulbs. So you also have a volume saving of 100 bulbs @ $1.60 - you may not have bought them, but you would have budgeted to spend the money, the fact you didn't is still a saving.

    You also need to consider stock - say you go out and buy (for the purposes of illustration) 10,000 bulbs from supplier B @ $1.50 - would you really classify that as a $2000 saving if your expected usage is 500/year? As a slight aside, there is a well know story in accounting circles about an Australian company that appointed a new manager and bonused him on 'savings' along the lines you seems to be wanting to do. He bought large quantities of stock, made the 'saving', collected his bonus and the company went bust - loads of stock, no cash.

    if you are buying bulbs to sell on, and you knock $0.20 off the selling price - do you have a saving? Worse, $0.20=11.7% reduction in your cost price, if you offer 11.7% reduction on the selling price of say $3.00. that is $0.35 - your profit has changed from $1.30 to $1.15 - is this a saving?

    There can be much more to it - allowance for delivery charges, impact of lead times, impact on cashflow if payment terms are shorter/longer, minimum order quantities and the like.

    Maybe I'm making this more complicated than it needs to be for you - but you will struggle to provide your client with a meaningful 'savings' report at the end of the year without a very clear definition of what a saving is.

  5. #5
    Norbayah is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    5
    Hi Ajax,

    Thank you for your informative comments. I'll take note of the points shared.

    But currently, I require some ideas on how to design the query.
    The example of lightbulb is meant to buy in small quantity and it is for replacement purposes.
    Usually, the lightbulb specifications from any supplier should be the same.

    Looking forward to your reply.

    Thank you.

  6. #6
    Norbayah is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    5
    Hi ssanfu,

    Thank you for replying.

    Product specifications should be the same or equivalent from any supplier.
    Order (date) is not essential.
    Usually, we would not go for a higher price than what had purchased on the earlier date.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not really enough detail to provide query ideas - but assuming your productid, price, quantity and supplierID is in your orderdetails table, join the table to itself on the productid and the yearmonth of the orderdate. you can then compare prices.

    Something like

    Code:
    SELECT T1.ProductID, T1.SupplierID, T1.Price, T2.SupplierID, T2.Price, T2.Quantity, (T1.Price-T2.Price)*T2.Quantity AS Saving
    FROM tblOrderDetails T1 INNER JOIN tblOrderDetails T2 ON T1.ProductID=T2.ProductID AND format(T1.OrderDate,"yyyymm")=format(T2.OrderDate,"yyyymm")
    WHERE T1.SupplierID<>T2.SupplierID AND T2.Price<T1.Price
    This in itself is probably insufficient since it is only going to return products bought from more than one supplier in a month. but perhaps gives you an idea on a way to go.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Saving Queries in an Organized Manner
    By Russellh in forum Access
    Replies: 8
    Last Post: 01-23-2015, 04:32 PM
  2. cost comparison queries
    By josh21 in forum Queries
    Replies: 1
    Last Post: 12-02-2014, 05:16 PM
  3. Replies: 1
    Last Post: 12-06-2012, 08:16 PM
  4. Create a method of saving data
    By tomneedshelp in forum Forms
    Replies: 1
    Last Post: 03-22-2012, 04:06 AM
  5. Saving new Queries (and other objects)
    By afvaiv in forum Access
    Replies: 0
    Last Post: 01-28-2012, 02:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums