+ Reply to Thread
Results 1 to 8 of 8

Days of Inventory on Hand Calculation

  1. #1
    Registered User
    Join Date
    07-12-2016
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2016
    Posts
    11

    Days of Inventory on Hand Calculation

    Hey all,

    Thanks for taking a look at this, hope I'm able to explain what I'm trying to accomplish well enough to come to a solution.

    Right now we have a column F that shows the current inventory available - it goes down 27 rows for each item so F2:F27. We also have historic sales numbers from 2014-December 2016. This equates to G:AP and also has the same range G2:G27 through AP2:AP27. Right now we have forecasted numbers through 2017, which is AQ:BB and want to find out at what time, using the 2017 forecasted sales, our product will go out of stock based on the current inventory available (F2:F27) in 2017 and how many days left that equals.

    Let me know if you need the actual data to do this. Right now I'm using this:

    =(IF(AP2>F2, 30, IF(SUM(AP2:AQ2)>F2, 60, IF(SUM(AP2:AR2)>F2, 90, IF(SUM(AP2:AS2)>F2, 120, IF(SUM(AP2:AT2)>F2, 150, IF(SUM(AP2:AU2)>F2, 180, IF(SUM(AP2:AV2)>F2, 210, IF(SUM(AP2:AW2)>F2, 240, IF(SUM(AP2:AX2)>F2, 270, IF(SUM(AP2:AY2)>F2, 300, IF(SUM(AP2:AZ2)>F2, 330, IF(SUM(AP2:BA2)>F2, 360, IF(SUM(AP2:BB2)>F2, "390+")))))))))))))-15)

    Which basically just says if the sum of the first month (Jan 2017) is greater than the current inventory available, say 30 and if the sum of the first two months (Jan-Feb 2017) is greater than the current available, say 60... etc. This is ok for it but it rounds something like 31 days left into 60 which could be dangerous so I'm looking for a better solution!

    Thanks.

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Days of Inventory on Hand Calculation

    can you upload a desensitized file?

  3. #3
    Registered User
    Join Date
    07-12-2016
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Days of Inventory on Hand Calculation

    Sure. Please see attachment.
    Attached Files Attached Files

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Days of Inventory on Hand Calculation

    Dear njsdca : How to calculate number of days inventory on hand calculation as per below.

    inventory turnover ratio= cost of goods sold/average inventory
    cost of goods sold=Opening inventory+purchase-sold inventory
    average inventory=Opening inventory+sold inventory/2
    cost of goods sold/average inventory= inventory turn ratio
    In a year number total days/inventory turn ratio


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Registered User
    Join Date
    07-12-2016
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Days of Inventory on Hand Calculation

    Thanks for the explanation.

    I'm looking for a way to keep a running # of days of inventory on hand.

    Is there a way to calculate it from the data I provided?

    Thanks.

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Days of Inventory on Hand Calculation

    If column "B" contains the total inventory on hand and column "C" to "N" contains the monthly consumption qty, then in another cell:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Year of days = 365
    Total Inventory = "B" column
    Yearly consumption = column "C" to "N"
    Refer attach highlighted columns.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-06-2021
    Location
    saudi arabia
    MS-Off Ver
    windows 10
    Posts
    1

    Smile Re: Days of Inventory on Hand Calculation

    Hello there,
    i dont know if anyone is here still to reply.

    your sheet is very good with an example.
    can you explain to me the column P formula ( or the concept )

    Plus if i need to go by changing a scenario by comparing the EOH with the Budgeted per month.
    will the formula change ?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Days of Inventory on Hand Calculation

    Administrative Note:

    Hello syedatif and Welcome to Excel Forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 11-23-2015, 01:49 PM
  2. Trying to create a calculation for days in inventory
    By excelhelp243 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2015, 11:27 AM
  3. Calculating Production Quantities for equal Days on Hand
    By PJM1981 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2013, 09:11 AM
  4. Replies: 6
    Last Post: 07-06-2013, 10:02 PM
  5. [SOLVED] Difference between Excel average and hand calculation?
    By Mase1 in forum Excel General
    Replies: 7
    Last Post: 05-22-2012, 01:04 PM
  6. Code for Inventory spreadsheet to add/subtract "on hand" amounts
    By DKS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2011, 02:49 PM
  7. [SOLVED] Please Help With Days Elapsed And Days Remaining Calculation
    By Scoooter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-14-2006, 12:15 PM

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