馃攳
Stock Control Sheet In Excel | Inventory Management | Inventory Control Sheet In Excel - YouTube
Channel: Brain Behind Business
[0]
hello guys welcome back to the channel and in聽
this video we will learn how to make a stock聽聽
[4]
control sheet for a retail shop so let's start聽
so we'll write our title here which is stock聽聽
[14]
control sheet for retail shop
[22]
then we will select at least聽
8 cells then we will merge it聽聽
[26]
and we'll do some formatting so we'll select聽
these and increase some space and then from here聽聽
[34]
we'll select the column with the title along with聽
the line below it and then we will drag these down聽聽
[42]
that will make these bold and middle align then聽
here we will write headings which is product聽聽
[54]
then unit price, quantity in hand, quantity sold,
[66]
inventory value,
[70]
sales value, available stock
[78]
status then enter
[81]
then we'll select these and center wrap text聽
I will do some other formatting like this
[95]
i'll select this and then from here i'll聽
choose this then from here i'll select聽聽
[107]
and table all border then i'll聽
select this and heading separately
[122]
i'll increase the size of text here then from here聽
as well a little bit and then let's start here聽聽
[132]
for a retail shop we'll use examples of chocolate
[148]
here we write sales prices
[156]
and quantity in hand here
[162]
quantity sold here
[166]
and here we will use formula
[172]
we'll select these both and聽
then enter i will drag this down
[182]
here we'll use same formula
[185]
product and then we'll select this
[191]
then unit price, quantity sold bracket聽
over and enter and we will select this
[202]
drag till here now we have quantity聽
in hand out of which we have sold this聽聽
[211]
then we will use our formula here
[214]
equals to quantity in hand minus quantity聽
sold so we have this quantity available聽聽
[226]
so now we know the status of quantity聽
available so we'll drag this down
[233]
then here we'll use "if" formula because to聽
if the logical test if quantity available is聽聽
[242]
less than or equals to 20 and "comma"
[249]
then "reorder" quotation mark over then聽
comma and then quotation mark again聽聽
[257]
"available" quotation mark over and聽
enter and we will drag this formula down
[268]
and now we will fix our formatting files聽
like this and then we will use this center聽聽
[275]
now you can see this "Reorder" you can聽
now improve this by conditional formatting聽聽
[283]
so you will select this row and聽
then from conditional formatting聽聽
[288]
then you rule then format only cells that聽
contain and then from here you will choose聽聽
[295]
a "Specific text" then here you will聽
write "reorder" and then from format
[304]
you will go to fill and i will聽
select red color and then okay聽聽
[311]
then again okay you can see this now all. right?
[318]
if we increase the quantity in hand by 50 which聽
will make it 450 then you can see this available聽聽
[327]
and if i make this 100 then you can see this聽
reorder status which is highlighted in red聽聽
[337]
and much visible I hope you have learned how to聽
make a stock control sheet in microsoft excel聽聽
[343]
we have made a mistake here and聽
there are spelling of retail聽聽
[348]
ei enter please hit that like button if this聽
video was helpful for you comment down if you聽聽
[355]
have any questions or suggestions share with聽
your friends and don't forget to subscribe
Most Recent Videos:
You can go back to the homepage right here: Homepage