Inventory management in Excel
                     Inventory management in Excel
Tracking your inventory can become a complicated task if it’s not managed  
correctly. Spreadsheets are the simplest way to manage your inventory and 
 related data like stock, sales, purchases, orders, suppliers, and basic level  
reports for free!
It is pre-known that Excel spreadsheets have various limitations, but it can  
work for small businesses and start-ups in the beginning. Excel is a
low-cost inventory management solution but not the best one.  
Want to eliminate the limitations of Excel? Take up a Free Trial of
 Inventory management software.
Click the image to Download the Free Excel inventory template with  
formulas.
We’ve done the back-hand formulating and linking of the datasheets. The 
 sheet is ready for you to directly enter data so, Get started!
Let’s learn how to use the Excel template efficiently to get the most out of it  
Divide and enter all the standard information related to Products,  
Orders, and Purchases in their respective Sheets, that are already  
categorized for you. Enter the information like Product Name, SKU, Stock  
location, Supplier, Current Stock level, Reorder Point, Sales Channel,  
Purchase Order, Purchase Quantity, etc.
Our inventory management excel template uses pre-installed formulas in 
 the Excel that will auto-fill or calculate - the total amount of Order,  
Purchases quantities to be bought, Stock location, and much more values  
automatically.
We’ll further show how this is done.
Update your Product sheet daily to record available current Stock  
level or when there are any changes in standard information like SKUs or  
suppliers or on the addition of new product(s).
Update your Order sheet when a new order comes in and gets  
confirmed on any of your sales channels. This could be done on a daily or  
weekly basis, depending on your business type.
Update your Purchase sheet when a particular product reaches near 
 Reorder point and is needed to be restocked. At his moment, a new  
purchase order is placed. This could be done biweekly or more, depending  
on the business.
One of the most significant drawbacks of using excel spreadsheets is that  
you have to manually enter all of your products and related information,  
time and again. This is not the case with Free inventory management  
software available, which is more straightforward than a spreadsheet. It will  
automate your processes easily to increase inventory management  
efficiency.
Uses of Excel Spreadsheets
You’ll find all of the below-mentioned formulas and applications pre-loaded  
in the Excel inventory template with formulas.
Interlink the information
Certain information is dependent on other parameters which can be  
interlinked to cancel out manual calculation every single time. For  
inventory management using Excel, Selling Price is dependent on the Cost  
price and mark-up price. The mark-up price remains constant, but the Cost  
Price for each product varies due to factors like Vendor, Demand of
product, etc. Using the ‘SUM’ or ‘SUMIF’ Formulas in Excel, we have  
interlinked these three parameters to get the calculated Selling Price on its  
own. For every product:
Selling Price = Cost Price + Mark-up Price.
Mark-up price is pre-recorded.
Using SUM Formula, you get a calculation for Selling Price automatically,  
every time you enter the Cost Price.
Formula: “=SUM(G3,H3)”
Syntax: SUM(number1, [number2, …number_n]) 
 Refer information
While keeping a record, the same information gets referred to multiple  
times. For inventory management, information like ‘Stock location’- which  
is attached to the SKU- is needed while dealing with 1. Purchasing- to know  
where to put that Stock once it arrives 2. Picking- to know where the  
product is lying 3. Returns- to know where to put the product back. When  
you have 1000+ products on hand, it is infeasible to record two or five or  
more information like the stock location for each SKU, again and again.
Using formulas like ‘VLookUp’ or ‘HLookUp’ in Excel, we help you 
 duplicate the information as and when needed.
Example:
You want Supplier names from your Product page to the Purchase page, we  
have used SKU as the foreign key to look-up between the two pages/sheets  
and get the Supplier’s name displayed on the Purchase page, the very  
second you type the SKU!
Formula: “=VLOOKUP(D3,PRODUCTS!$D$3:$F$32,3,0)”
Syntax: VLOOKUP(value, table, index_number, [approximate_match])” 
 Pictorial representation of data
Analyzing data using only numbers and codes can make you go crazy! Excel  
provides various options for the pictorial representation of your data in the  
form of graphs, charts, diagrams, Smart Art, etc. This makes the analysis of  
the data much more organized and easy to interpret.
Highlights when to act upon
At times your products run out of stock, and you are caught off guard! We  
have fed conditions into inventory Excel template using ‘Conditional  
Formatting’, which signifies an alert for you when a stock count reaches or  
crosses the Reorder point. This helps in alarming you before the stock runs  
out without the need for you to go through hundreds of cells each time and  
avoid any mishap. You can also differentiate columns and rows by colors or  
gradients based on distinguishing features.
Conditional formatting can be done for complex tasks and interventions  
also.
Forecasting
Excel allows trend lines to be extended beyond the graph to offer  
predictions of future activity – and such forecasts can help businesses 
 develop their future strategy.
Formula based forecasting can also be done using Excel’s ‘Forecast’ 
 formula.
Forecasting for a y-value dependent on an x-value:
Formula: “=FORECAST(700,J3:J9,I3:I9)”
Syntax:“=FORECAST(x-value, known_y_values, known_x_values)” 
 You get y-value displayed.
But is the Inventory Excel template excellent?
Excel has efficient and easy-to-use formulas that do the job to a basic level.  
It only works well for small businesses, start-ups, etc. or with less business  
in hand, but what post that? We’re sure you’re growing your business into a 
 perpetual one, would you really want to limit to simple and basic tools?
Growing businesses like yours can’t afford to manage inventory using just 
 Excel! They need something more, which is a Cloud-based Automated  
inventory management software that is quick, reliant, and supportive!
Orderhive is one such software that manages your inventory data and  
automates your inventory functions in real-time, fitting all business sizes.  
Learn more about the benefits.