+ Reply to Thread
Results 1 to 4 of 4

Calculating future stock values...

  1. #1
    Registered User
    Join Date
    07-25-2008
    Location
    London
    Posts
    2

    Calculating future stock values...

    I deal with fast moving consumer products - the value of which changes on a weekly basis.

    I am trying to work out a way to calculate what the future average unit cost of a product will be as a mix between the goods I have in stock now, and the goods that will arrive on the next shipment.

    For example:

    If I have 100pcs of Product X with an unit cost $10, and I order in another 100pcs with a unit cost of $9. The average unit cost of the product will full 200pcs will be $9.50 (assuming I don't sell any of the original 100 before the next 100 arrives).

    Anyway, I do have a working solution for this - just don't think it's particularly elegant - and I'm sure that Excel geniuses out there could simplify my formula or utilise a function command.. Formula is as follows:

    =sum(Quantity in stock x Current unit cost)+(quantity on order x landed cost for the new stock)/(quantity in stock + quantity on order)

    Can anyone offer any suggestions to improve the above? Or even better, work out a way for me to do the work out the future average unit cost taking into account if i sell X pieces of current stock between now and when the new stock arrives...

    Apologies for the long first post - but appreciate any help...

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676
    Assuming you're doing first-in, first-out costing, then what you have looks correct aside from some parentheses:

    =(stockQty * stockUnitCost + orderQty * orderUnitCost) / (stockQty + orderQty)

  3. #3
    Registered User
    Join Date
    07-25-2008
    Location
    London
    Posts
    2
    Quote Originally Posted by shg
    Assuming you're doing first-in, first-out costing, then what you have looks correct aside from some parentheses:

    =(stockQty * stockUnitCost + orderQty * orderUnitCost) / (stockQty + orderQty)
    Thanks for your reply shg - I don't believe we're using FIFO - when new stock arrives, the total value of the new inventory gets booked into stock - creating an average unit cost blend between the new and old stock... ....and it's this future average unit cost that i'm trying to predict.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676
    Computing the cost of a unit sold and the average cost of remaining inventory depends on whether you consider yourself to be selling (irrespective if the units are fungible, e.g., no expiration dates):
    • the oldest unit in stock (FIFO)
    • the newest unit in stock (LIFO)
    • the most expensive unit in stock
    • the least expensive unit in stock

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1