Inventory Balance Calculation

Posted on 2011-05-07
Medium Priority
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
  • 2

Accepted Solution

ComputerAidNZ earned 2000 total points
ID: 35713545
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.

Expert Comment

ID: 35715296
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

Expert Comment

ID: 35715308

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:


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

850 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