How to manage inventory in Excel
| How to manage inventory in Excel: Managing
inventory is an important aspect of most businesses. Whether you are a
manfacturing unit, a trading company or a small store, you need to work
with and manage inventory. Managing your raw materials and finished
products can lead to substantial savings in your business. We feel that
you can create a robust inventory management system using Excel. You
need to have an in-depth understanding of flow of goods in your company
including seasonal and unexpected variations and a basic knowledge of
Excel. In our example shown in the video below, we
discuss how a store selling blue silk shirts could manage its
inventory. We have made assumptions about the maximum sales per day and
the average sales per day. The time taken for delivery of the goods has
also been mentioned. Based on these parameters we have calculated the
'variance' between the maximum expected sales and the average sales per
day. Based on the variance we have assumed a calculated 'safety
margin'. The total of the safety margin and time taken to deliver the
goods gives us a good estimate of our re-order levels. Based on the
stock-in-hand and the calculated reorder level we can calculate the
reorder quantity.
|
|