School Book Shop Inventory Management System – MS Excel File
In this article we will discuss Inventory Management System and will cover two things; Quantity and its Price (In and Out). In this version we will cover the following items,
- Items detail
- Purchase; Items Received
- Sale: Items Issued
- Stock
- Invoice
MS Excel users have greate advantage that they dont need any software or other application to maintaint their account or other records. Now lets start how to construct different types of sheets to maintain School book shop inventory system but before it, Download and install legal copy of Microsoft Office if you dont have before.
Create five sheets with titles as given below in picture.
1. Items
In this sheet we will create 5 columns for Item Code, Items description, parchse price, profit margin and finally sale price. Here we will determine all these things as we will use them in other sheets.
Write code of an article in first colums and its descriotion in second column. Please keep code and descrioption wording in a symmetric manner so that we can use them without any error. I have taken Female dress as item but of diffent sizes. You can see their code number are unique and have a symmetry.
In third column, write purchase price of each item.
Now In fourth column we will insert formula to calculate profit margin in cell E4. In this sheet I have taken 15% margin of each prchase price.
=D4*ProfitMargin/100
In above formula, ProfitMargin is the name given to profit value located in cell H3.
For more information about how to give name to any single cell or range, must read this article (Name the Range).
now we have inserted formula in Cell E4. Now see right botton of cell E4, and pick the dot then drage to thw whole column to paste the fomula to the whole column.
Now in Fifth colum, we need to insert Addition Formula in cell F4 to calculate Sale Price. See below
=D4+E4
Sale price formula has been entered and now agains perform same activity; drag the dot to the whole column to paste this formula.
Now scroll down the page and see at the end. you will find the same columns mentioning different sets of books of different classes. Here is a difference, in above mentioned example you need to decide profit margin, but here in book you can find sale price already mentioned on it.
In this case, you can not determind the sale price. The publisher will tell you discount rate as in this case publisher sale man offer me to give a book set at 25% discount. Now we need to calculate purchase price. lets start.
I have given “Discount” name to discount rate (25%). Now in Cell D41 calculate of a purchase price of which 25% profit and total sale price is Rs. 600. On the other hand Saleman will tell you discount margin as well as purchase price but how will you calculate in excel file. Lets construct formulat for this,
If sale price of a product is Rs. 125 then its purchase price is Rs. 100 at 25% rate.
If sale price is Rs. 600, then how much will be purchase price; solution is: 600*100/125. now try to understand the following formula,
=100*F41/(100+Discount)
Now in cell E41 determing the profit on purchase price or discount on sale price offered by publishing company.
=F41-D41
Now Item seheet is almost done. One thing left which is Books table that i have created. See below
See above book table and at the end total price is also mention. pick these value direct to sale price column. you can customise this sheet according to your needs as well as your items.
2. Purchase; Items Received
In this sheet we will create columns to maintain data records of intems purchased. Now we will create Code, description, date and number of items received as shown below.
Now when we purchase an item from company we insert the code number of that item that fetch description in second colum automatically. In items sheet, Select the whole table ot items and name it as “iTable” so that can can pick description and purchase price from that table. See below.
Now in code colum we will insert code and in second colum we will use vlookup formula to fetch description. Use the following formula in cell B5.
=VLOOKUP(A5,’School Book Shop Inventory.xlsx’!iTable,2,FALSE)
Learn more about how to use vlookup formula in MS EXCEL, see Microsoft Excel introduces powerful XLOOKUP function.
In third column, inserrt receving date manualy, simply press control+; button. in forth column enter number of items you received. See below
Now we have list of items that we received in whole of the month. Name code column range as “rCode” and Units column as “rUnits”. One items could be received multiple time in a month. Now we will accumulate the list item-wise. For this purpose we will create 5 more columns.
See above picture, description detail has been fetched on the basis of Code. Now in cell i5 insert the following formulla to pick accumulate number of unit of 20F (Female dress of sezi 20″) from rUnit column on the basis of code from rCode.
=SUMIF(rCode,G5,rUnits)
This formula will give you whole number units of 20F size purchsed in whole month. Now use the following formula to fetch unit purchase price from iTable located in Items sheets.
=VLOOKUP(G5,’School Book Shop Inventory.xlsx’!iTable,3,FALSE)
And in the last column, une multiplication formula to caculate purchase price of all the items of 20F size purchase in the whole month.
=I5*J5
As done above, revise agains to drag the formula down to the whole colum. Done. Purchase sheet is ready.
3. Sale: Items Issued
In this sheet we will take review about items which have been sold or issued. This is the same as purchase but a little difference in formula.
In previous Purchase sheet we have given name rUnit for units column and rCode for Code columns.
While creating Sale Sheet. now make a little difference in the name of ranges. Create from iUnit for units column and iCode for Code column.
Now its your working to change the formula according to new Range Names.
If Done, Sheet is ready.
4. Stock
Now its time to calculate result and stock left over. In this sheet we have constructed 5 columns; Code, Descrption, Unit purchased, Unit Sold and Stock.
First two column are same as we have created in last two sheets (Prcuase and Sale). Third colum is also near about same. We will pick units received (purchased) from Purchase sheet by inserting the following formula in Cell D5.
=SUMIF(‘School Book Shop Inventory.xlsx’!rCode,Stock!B5,’School Book Shop Inventory.xlsx’!rUnits)
In Fourth colum, We will pick units issued (Sold) from Sale sheet by inserting the following formula in Cell E5.
=SUMIF(‘School Book Shop Inventory.xlsx’!iCode,Stock!B5,’School Book Shop Inventory.xlsx’!iUnits)
In fifth column, calculate the difference of these two and find the stock left in your hand.
=D5-E5
Now drag these formulas down to the required rows.
Done, but now optionally we will also create another column to check which stock item is ready to be placed for oder.
Now design two cells for ALERT heading and its required value. Click on required value (arrow 1) and give it a name (arrow 2). Now in order column, in cell G5 we need to insert IF formula; If Stock value F5 is less thank alert value H4, highlight this by showing “Place an order” text in colum G5 Order. If Stock is greater than alert value, means we have enough stock left in hand, then there will be no text here in G5. see the formula below.
=IF(F5<=Alert,”Place an order”,” “)
After inserting, drag the formula cell down to required rows. Now insert order stock value in alert value. In my case, I am going to insert 10 value that means below this value I will place an order for further purchasing. See below
Done. Try this
5. Sale Invoice
Finaly I have created a very simple invoice Excel sheet for customers. Now i am sure that you know how to fetch information and values on the basis of code. write number of unit of items that customer has purchased. See below.
In Total Price column, Pick the sale price on the basis of code and multiply it with No. of Unit. See the formula below.
=(VLOOKUP(A11,’School Book Shop Inventory.xlsx’!iTable,5,FALSE))*C11
Drage all the formula down to your required rows. Below is the final invoice that I have tested. Date and time is automatic update.
Done. Do this try at home or download this file.
Post a Comment
0Comments