Inventory Balance Calculation

Posted on 2011-05-07
Last Modified: 2012-05-11
Inventory Balance Calculation

I am running into a situation where we're trying to re-organize our inventory supply to satisfy our inventory demand.  The demand changes frequently, so we need a quick way to re-align supply accordingly.  At present, this is a tedious task when you consider we manufacture about 10K active parts.  Please see the attached sheet for more detail and examples.  This one should be a challenge.

edm iv-mrp-part.xlsx
Question by:emiller1680
    LVL 2

    Accepted Solution

    IMHO, what you are trying to achieve is beyond the scope of Excel.  My persoanl choice for a low-ish cost solution is to use MS Access and create a DB of the inventory.  You can do FAR more than what you require and if your business is growing, then you will be ready to move to the next step.  Although I am no way saying it will happen to your business, i have in the past encountered large inventories like this in Excel that have been a nightmare for the usres to manage and data duplication and other unwanted issues.  Since your request states you "need a quick way to realign" and it is currently "tedious" as well as this seems to be a LIVE system, I can only suggest this is the time to transition to a good reliable DB that will help you grow your business and give you information at the touch of a button, rather than having hurdles like you have now!
    Just my opinion - I am not usually so bold, but you have a business to run and a business is more important than a home user who insists on using an inadequate programme.
    Excel is good; but no that good!!  If you want guidance or direction in what is required or even someone to create a DB for you, let us know, we're all here to help and guide as well as just answer your question directly.
    If you feel you still need to solve it your original way, let us know too, and we'll all try our best to help you out.
    LVL 5

    Expert Comment

    Hi Emil,

    Check this articles , they explain in details how to use Excel to calculate the Inventory.

    Also here you can find some Excel templates of Intevntories to get the ideas ..
    Download them free :)
    Good luck
    LVL 5

    Expert Comment


    There is also a tool called : EOQ Inventory Calculator for Excel tool
    to calculate the inventory you just input required numbers of holding cost, units sold, ordering cost, and this EOQ Inventory Calculator for Excel tool will automatically calculate all inventory metrics and create charts.
    Here is the link to download a trail or buy the full version.

    Here are some other free templates for Orders and inventory calc:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Article by: Nadia
    Suppose you use Uber application as a rider and you request a ride to go from one place to another. Your driver just arrived at the parking lot of your place. The only thing you know about the ride is the license plate number. How do you find your U…
    Article by: Nadia
    Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now