hello and welcome to the mr spreadsheet
youtube channel
if your business needs to keep track of
inventory
then why not create your own inventory
listing
and inventory movement schedule using an
excel spreadsheet
in this short video i will show you how
to do both of these things
and provide you with an easy to use
template
that you can adapt and expand to suit
your business needs as usual i will show
you how to get a copy of the final
template
later on in this video and
if you need any help on the excel
functions i've used
then please do leave a comment below
i hope you enjoy watching
open up a new workbook and we will
create the inventory list
the first of our two tables in cell
b2 type in inventory list in capitals
in cell c2 type in code
in c3 it's product in d3
its cost in e3 its quantity
and finally in f3 type in value
now select the range b2 through to f2
and from the home ribbon select and
click
the merge and center tool choose a
background color of green
a font color of white make bold
and increase the font size and now
select the range b3 through to f3
make bold and change the background
color
to light green we will now add a border
to the inventory list table so
with the range b2 through to f19
highlighted select the all borders
option from the borders tool
on the home ribbon now let's enter in
our first product
with a code of w101 a description of
widgets gray and a cost of 21.50
you can now enter the next 12 products
or indeed
you can choose values more suited to
your business needs
please pause the video whilst you
complete this routine
our second table is the schedule of
inventory movements
now for convenience we're going to place
this alongside
the inventory list table so select cell
h2 and type in inventory
in and out schedule
in cells h3 through to n3
type in the inventory schedule headings
of date
type code
product cost
quantity and finally value
select the range h2 through to n2
and merge and center the contents change
the background color to green
the font color to white make bold
and increase the font size
select the range h3 through to n3
and change the background color to light
green and make bold
now select the range h2 to n19
and apply the all borders tool
and finally from the page layout ribbon
uncheck the view gridlines box
okay so we've now set up our basic
layout
now in the inventory movements table we
actually need to define
the type of movement we want valid
entries to be either
sales or receipts
or adjustments so let's type these
values into cell
r3 through to r5
r3 becomes sale r4 becomes receipt
and r5 becomes adjustment
and with these three cells highlighted
select the formulas ribbon and choose
the
define name tool in the dialog box that
opens
enter the word type in the name field
and then click
ok now we can enter a drop down box in
cell
i4 so with i4 selected
choose the data ribbon and then the data
validation tool
in the allow box choose list
and in the source field enter equals
type and then click ok
a drop down box will now appear to the
right of cell
i4 from which you can select one of the
three options
from the type list now
with i4 still active drag and copy this
formula down to the last cell in the
column
such that all the rows for the type
column
contain the type list drop down box
now we will create a similar drop down
box routine
to select the code in cell j4
the list we need to create is actually
the range
b4 to b19 in the inventory list
table so highlight this range and from
the formulas ribbon
choose the define name tool and
change the name field to read code and
then click
ok navigate back to j4
and from the data ribbon select once
again
the data validation tool choose list
for the allow field and enter equals
code in the source field
click ok to save and close the data
validation tool
as before a drop down box appears to the
right of the cell
j4 inviting you to select from the code
list
with j4 still active copy
and drag down the formula to j19
at the end of the code column
now that we can select a valid code we
need to enter the product name
and the cost into cells k4
and l4 but before we do that
let's quickly populate the first three
fields
of line one of our inventory movement
schedule
enter a date in h4
select receipt from the drop down box in
cell
i4 and select code w101
from j4
now we can easily use excel's vlookup
function
to automate this routine
but first we need to name the range b4
to d19 which will make our use of the
vlookup function
much simpler so with b4 to b19
highlighted
select the define name tool from the
formulas ribbon
change the name field to read details
and then click
ok navigate back
to k4 and enter in the following formula
equal vlookup open brackets
point to cell j4 enter a comma
then enter the lookup range name of
details
then another comma then enter 2
then a comma then the word false and now
close the brackets
the 2 in this formula represents column
2
of the details range
you'll now find that cell 4 populates
correctly
with the value in the second column of
the details range
we can use the same vlookup routine to
populate cell
l4 our formula simply becomes
equals vlookup open brackets
point to j4 then a comma
then details then another comma
then three then comma then
false and close the brackets
the three actually equates to the third
column
in our details range and you'll find
that l4
correctly populates with the value of
21.50
let's complete this first line by
entering a quantity
of 15 in cell m4
the value in n4 is simply the quantity
in
m4 multiply by the cost in l4
so enter the formula plus m4
times l4 to achieve this
and that completes the first line
but wait if we delete the code for this
line
then excel will return an error message
in k4 l4 and n4
this is simply because our formulas in
these cells
are totally dependent on there being a
correct code
in the field j4
let's demonstrate this another way
select the cell
i4 and copy and drag down this formula
to the end of column k19
the error message appears in each row
where there is not
a valid code we want to slightly adjust
our vlookup command
such that if there was no value in the
code field
in column j then the formula result
should simply be
a blank entry
select k4 again the formula is shown in
the function command section
and we're going to add an if statement
to the command
so after the equal sign and before the v
of vlookup enter in the following
if open brackets
point and click on j4
equals inverted comma
inverted comma comma
then inverted comma and inverted comma
again
followed by a comma and then
enter a final bracket at the end of the
formula
once the formula is complete press enter
and now if you delete the code value in
cell
j4 you do not get the na
error message now copy the formula
all the way down to k19
we can insert this same if command
to tidy up the cost column and the value
column
with cell l4 selected locate your cursor
after the equal sign in the command line
and before the v
a vlookup and enter
if open brackets
point to cell j4
equals inverted comma inverted comma
comma inverted comma inverted comma
and comma again and then finally
navigate to the end of the formula and
enter in a closing bracket
once you press return you can copy and
drag down the formula
to l19
the formula in n4 now becomes
equals if open brackets
j4 equals inverted comma inverted comma
followed by a comma
then inverted comma and inverted comma
again followed by a comma
and then m4 multiplied by l4
and close the brackets once again
copy and drag this formula down to the
end of the column
okay that's most of the work done in the
inventory movement schedule
let's add a couple of totals for
quantity and value
and change the number formatting
select the range l4 to l19
and by holding down the control key on
your keyboard
select also the range n4 to n19
with both of these ranges highlighted
right click your mouse
and select the format cells tool
choose the number category and set to
two decimal places
and set negative numbers to be in red
preceded by a minus sign
do the same for the quantity fields
except set the decimals
to zero
navigate to m4 and enter in the formula
equals subtotal open brackets
9 comma and select the range
m4 through to m19 and close the brackets
do the same in n4 to get the value
totals
so it's equals subtotal open brackets
9 comma n4 through to
n19 and close the brackets
now click anywhere in the inventory
movements table
and from the home ribbon select the sort
and filter tool
and choose the filter option
now change the background color of the
two total fields
to a mid-gray and format the numbers
as before
let's quickly enter a few lines of data
to test out the inventory movements
table
select cell h5 and enter a date
select receipt and then w106
in the code field our product
and cost fields populate correctly
navigate across to m5 and enter 10
as the value the whole line works
properly
populate the next two lines as follows
making sure that any sale item is
recorded
as a negative quantity
let's now return to the inventory list
and enter a formula to automatically
calculate the running or the cumulative
totals
for each of our products together with a
valuation
for each product line to do this
we'll use the sumif command which will
add up
both the positive and the negative
values for each product
based on the movements in the inventory
movements schedule
to help us with the sum if command it is
advisable
to name the ranges that the sumif
command uses
select the range j4 to j19
and from the formulas ribbon select the
define
name tool and in the name box enter
in out code with no spaces
and then click ok
now select the range m4 to m19
and using the define name command again
enter the name in out quantity again
with no spaces
and click ok
our sum if command in cell e4 will call
on both of these ranges
to calculate the cumulative or the
running balances
for the first product so in cell
e4 enter in the formula
equals sum if open brackets
in out code comma 0.2 b4
comma and then in out quantity
and close the brackets e4 populates
correctly
with 35 which is the cumulative total
for that product
the inventory value for w101
is simply the quantity times the cost
so enter the formula plus d4
times e4 in cell f4
now we can copy these two formulas down
to the end of the inventory list
so highlight both cells and drag and
copy down
to complete this
let's enter a total in cell f20
to give us a complete inventory
valuation
select f20 and simply double click the
auto sum tool on the home ribbon
now from the borders tool select all
borders
and make bold and change the background
color
to mid gray select the ranges
f4 and f20 together with
d4 and d19 and from the format tool
set the number to two decimal places
with the 1000 comma separator
checked and last but not least
we will apply some conditional
formatting to the range
e4 to f19 such that
zero values are not displayed
so select the range e4 to f19 and from
the home ribbon
choose the conditional formatting tool
select the highlight cell rules option
and then the
equal to option enter a zero
in the value field and then from the
with field
select custom format from the drop down
box
and from the font color palette choose
white
and then click ok
please fully test all of the transaction
rows
and the filtering routines to ensure
that all the formulas
are functioning correctly it is most
likely
that you will need many more transaction
lines than shown
but by using the techniques displayed
you can easily expand
the data tables
and that completes our design work we
now have a fully functioning inventory
control spreadsheet
that will hopefully be of value to you
and your business
we do hope that you enjoyed our
spreadsheet video
and that there was lots of content that
you found both
useful and informative
now if you would like us to send you a
copy of this spreadsheet file
then please subscribe to our channel and
give this video a big
thumbs up alternatively please
visit one of our channels on facebook
instagram or twitter
now if you are a small business and want
to keep your bookkeeping records in
excel
then why not watch our accounting
spreadsheet tutorial
alternatively why not view our how to
keep your account in excel video
this is our easy to use and inexpensive
solution
for your small business bookkeeping
needs
thank you once again for watching our
spreadsheet video
and oh yes please do subscribe
to our channel