User Question on
inventory management using a simple method:
I deal in 150 types of items in a shop. For example if I know I have 70
pieces of an item in stock and I take out 20 and add 30, I wish to have
the recalculated inventory quickly. I wish to use a single column for
incoming and outgoing stock and wish to track the daily changes by
date. I want a very simple system that gives me a base count and a
method to show what is going out and what is coming in and a current
total.
We can setup Microsoft Excel to perform this task as follows:
We enter the date, item and purchase or sale quantity
The sale and purchase or buy quantities are enterd in the
same column
Purchase entries are positive. Sales entries are negative.
We format the sales entries in such a way that the data is
red colored without the negative sign
We leave a column or two and enter our items in one column
In the next column we use the sumif function to find out
the current stock quantity
In this manner we have our daily sales and purchase and our
current stock.
Watch the video below to see how you can implement a simple
straightforward inventory management system using the sumif function
and no macros: