hi guys and welcome to the expense report template now the expense report template
is a super easy one and what it allows you to do is track all of the different expenses that you
may incur on say a business trip so you can easily track and categorize your expenses when traveling
we have some predefined categories such as transportation accommodation
and others so you can just list them up and the reimbursement will be calculated automatically
and on top of that for the people responsible for consolidating these expense reports
you also have a great way of putting all of this together and really seeing where the money
is being spent so let's take a quick look at the different sheets again we have two support cheats
employee data and consolidation and two analysis so let's work with the employee data sheet first
just two columns enter in the employee names and their employee id basically
you can maintain the entire range that is here marked in green so it should be enough
so later this will be a drop down list and then you can pick from this list right here
on the expense report tab you can put in a travel purpose which is just still manufacturing congress
you put in the name coming from the employee data tab so let's say we take dennis employee id will
update automatically because it's just a vlookup and then that particular person can enter in the
date a description of the expense so in this particular case they probably took a train
or they were using their own car so transportation wise reimbursement for the 31st of july is 300 in
terms of accommodation they spend 400 on a hotel and 30 on food then they may have had some other
expenses such as entertainment or other of course feel free to change these no problem about that
and then you're calculating a grand total at the end and over the course of these days you arrive
at a grand total which is roughly 3.2 k to be reimbursed then of course the person shouldn't
forget to attach receipts and there's someone in the company that needs to prove it on a particular
date so what you can do is you can actually print this one out printing area is already fine so it's
going to come nicely on a regular sheet of paper and then you can just hand that over to the person
for approval and that's it now for the person having to consolidate this you actually need
to look at the consolidation support sheet now let's say you get a whole bunch of these different
expense reports right and each and every time what you have to do is just copy paste
this piece right here and bring it here down below so you want to have like a list
following the exact same order every single time now in this particular case it's linked but let's
say if you get another expense report you want to add that expense report here at the bottom
once you've updated all of this all you need to do is go over to the consolidation people sheet
refresh the pivot table and it's going to come in super nicely so you see that for this particular
steel manufacturing congress which is our travel purpose you have dennis who incurred different
expenses on different days so you can actually analyze this data and see that for example total
transportation reimbursement for that congress was 546 and it split into the 31st of july
some right here in august and you also have accommodation that you reverse the person for and
some other expenses now the pivot table allows you to now in this case we only have one person who's
traveled right but let's say if we had additional down here at the bottom because we're putting them
right here we would have the chance to just collapse the entire field
and just see the actual dates this is probably a nice review if you want to look at
the individual spend on each and every day and if you need some more details
you just open them up and you see the person who has spent that money now that is it for the
expense report i hope that you're going to be using this because it's a really helpful tool
and it'll allow you to analyze your expenses and your reimbursements at just a glance