hi I'm Aimee from Razor Accounting the sharp solution for your finances.
this is the second tutorial in an Excel series called Excel for Small Business
this tutorial is called Excel for Small Business Expense Spreadsheet in this
tutorial you will learn how to create a basic expense spreadsheet in Excel which
will help you keep track of your business expenses in an organized and
efficient manner. If you haven't already please hit that subscribe button below
and also hit that bell next to subscribe that's going to give you a notification
every time I post something new to this channel.
So I'm going to start building
my expense spreadsheet in the top left corner in row 1 column a and the first
category that I'm going to put in is the date moving over I'm going to put in the
source moving over again I'm going to put in the reference and moving over
again total now before I move along to the rest of this tutorial I'm going to
give a quick explanation of how to use these four categories so the date column
is going to be used to fill in the transaction date or the purchase date of
your expense and you would find that on your receipt on your invoice on your
bank statement or on your credit card statement wherever you're getting your
information from.
Moving over the source would be where you are getting your
information from which would be your source document that could be a receipt
that you received from a store it could be an invoice that was issued to you
from a supplier or someone else it could also be a bank statement
a credit-card statement so put in your source in the source column and then
moving along we have reference now this is going to be how you reference this
expense spreadsheet back to your source document and the way that I do it is
with numbers so start with number one for your first expense you're going to
put number one in the reference column and then on your source document next to
the total amount that was paid you're going to put the number one that way you
can reference back and forth to find your expense easily
I also recommend one thing stay organized with your source documents put
them in a way that you can find them easily when you need to look for them.
Moving along to total that would be the total amount paid in the transaction or
at the total purchase price of the item or expense.
I filled in my expense
spreadsheet with some common expense categories you may use the same
categories if they apply to your business but if they don't apply to your
business do not put them in put in whatever expense categories you need. If
you're not sure how to categorize your expenses for your business you can leave
me a comment on this video and I'd be happy to help you out with that you may
have noticed that I put my expense categories in alphabetical order
starting with a and working towards t I suggest that you also use alphabetical
order to organize your expenses to keep your expense spreadsheet looking nice
and tidy you may have noticed that the last
column is a check column that is to ensure that there are no data entry
errors in the expense spreadsheet. I'm going to give you the formula for the
check column shortly.
So here's my spreadsheet with some data filled in.
You'll notice that I've filled in my stores column with a little bit of
information on what the actual Spence is so I have the source document
names so I have invoice and I also have a description of what the invoice was
for so it says invoice and tax bill for my first expense and in my second
expense it says bank statement as the source and I
put bank fee as a description. You may want to follow along with what I did to
make things a little bit easier to remember when you're looking back at
this spreadsheet and wondering what you spent the money on.
One thing that I do
like to do when I have any type of spreadsheet is to freeze the top row and
that will allow you to move down your page and make your spreadsheet as long
as you want to and not lose your categories so to freeze the top row what
you want to do is highlight the top row first by clicking in the number one on
the side here on the left you'll notice that that highlights the whole row and
then you want to go up to your top menu go under the tab that says view and
select freeze panes and then in here select freeze top row now you'll notice
you can move down your page as far as you want and you always have your
categories here in Row 1 as I mentioned before the check column is used to make
sure that there are no data entry errors in the table so I'm going to show you
the formula for the check column right now so what you want to do is click in
Row 1 of your check column then put in the equal sign and then we're going to
go to Row 1 again and the total column and click on that then put in the minus
sign and s u m for sum and a bracket now we're going to highlight the
expenses in Row one so highlight all of your expenses and then put a closing
bracket in there and select enter on your keyboard and you'll see that it
says zero in my check column that means I don't have any data entry errors in
this row. Now I'm going to fill this down so I'm going to select the cell that has
the formula in it and I'm going to go into the bottom right corner and you'll
see that there's a little cross there and I'm going to drag downwards right to
the bottom of where I have my data and then let go and there you have that all
of your formulas are filled in and as you can see they're all zeros which
means there are no data entry errors. But if you want to test it out go ahead and
test it out so I'm just going to put in a little bit of extra numbers into the
repairs column and see what happens in the check column. So there you have it
it's working so I filled in some extra information in the repairs column and
you'll see that it showed up in the check column to let us know that there
is an error in that row so I'm just going to delete that and select enter
and now the check column has all zeros again so we know that there are no data
entry errors the next thing to view would be to sum up all of the totals of
the expenses to sum up all of the data in the total column start with
highlighting it so click in the top row and then drag down so you have all of
the data in that column highlighted and then you want to go up to your top menu
under home you want to go over to the far right side and then select Auto sum
now that has everything in the Total column and
instead of doing that for every single column in the expense spreadsheet I'm
just going to drag this formula across so I'm gonna get that little + or the
cross in the bottom right corner of my cell where the formula is filled in and
then I'm going to drag to the right and that's gonna fill everything in with
formulas so I don't have to actually do the whole formula again for every single
column once I've stumped up my data in the spreadsheet I like to put the upper
and bottom double lines and so I know that the data is thumbed up so I'm going
to highlight the thumbed up data here and then I'm going to go up to the top
menu and we're under the Home tab still and we're going to go over to where you
see this little box and there's a drop-down so click on that drop-down and
select top and double bottom order if you'd like to make your expense
spreadsheet look a little bit more interesting by adding some color you can
always do that by highlighting the cells that you like to add color to and then
go up to your top menu under the Home tab you'll see that there is an option
to add color here and you can click on the drop-down and select lots of
different colors that they have available or if you want to you can make
a custom color under this selection that says more colors so that's it for
creating a basic expense bread sheet in Excel to keep your business expenses
organized in an efficient way if you do have any questions about this please
leave me a comment on this video and I'll be happy to answer your questions
as quickly as I can and if you like this video if you found it helpful please
leave it a like and don't forget to subscribe to the channel thank you for
watching