Simplify Your Finances with Our Expense Invoice Template for Businesses

Expense invoice template for businesses

would you like to use an excel spreadsheet to easily keep track of your business expenses and incorporate a permanent pdf link to your underlying receipts if so this business expenses spreadsheet presentation will show you how hello and welcome to another mr spreadsheet tutorial in this short video we will create a business expenses spreadsheet template using excel the business expenses spreadsheet has been designed to help you easily record your business expenses split into 10 different analysis categories and facilitates a tax calculation we have included a document link within the template that allows you to view the underlying expenses receipts from within the excel file the completed template is easy to use and can be easily adapted to suit your business or personal requirements the completed template uses many of excel's functions commands and techniques we will name ranges and use the table creation tool we create and use drop down boxes we employ the sumif command and we use the insert link function all of these routines are explained within the presentation if you'd like to get a copy of the completed template then all we ask is that you subscribe to our channel and give this video a big thumbs up i hope you enjoy watching okay let's get started in a new workbook pick up the page layout ribbon and uncheck the view gridlines box navigate to cell c4 and copy in the headings as shown on screen all the way across the worksheet to cell j4 in cell c5 enter in a date and now move to cell d5 and enter in the description for our first entry this is a telephone bill so enter this in the details box and expand the width of the cell to fully accommodate this later on in this presentation we are going to insert a link to a scanned copy of this document in cell e5 but for the moment let's complete the expenses spreadsheet in cell f5 we want to select one of ten different analysis types so let's quickly create these in a separate table move along to cell l5 and enter in the first expense type of telephone now copy in the other nine expense types as shown or enter narratives that may be more appropriate to your business let's give this range a heading of analysis type with no spaces in cell l4 now highlight the range l4 through to l14 and then from the formulas ribbon choose the create from selection option from the define name section this automatically creates the named range for us we can check this by viewing the contents of the name box to the left of your worksheet navigate back to cell f5 and from the data ribbon pick up the data validation tool from the allow field select list and in the source field enter equals analysis type with no spaces and then click ok to save and close a drop down box now appears to the right of cell f5 from this please select telephone enter in the value of 120 in cell g5 and now move across to cell h5 we want to calculate the tax content of the 120 in cell g5 now wherever you are in the world it is quite likely that you will have differing tax rates and maybe you will have multiple tax rates so let's create a tax rate table with four separate tax percentage rates in the range 05 through 208 enter in the four percentage values as shown and format these to two decimal places now with the range 05 through 208 highlighted pick up the formulas ribbon and then select the define name tool in the dialog box that opens enter in the name tax rates with no spaces and then click ok to save and close select cell h5 and from the data ribbon pick up the data validation tool select list from the allow field and enter equals tax rates in the source field with no spaces now from the drop down box select 20 percent we now need a formula to calculate the tax element of the 120 gross value in cell g5 in cell i5 enter in the formula plus g5 minus g5 divided by open brackets one plus h5 and close the brackets and press enter the correct value of 20.00 is returned in fact this formula will return the correct tax element for any of our tax rates that we have in the tax rate table now move along to cell j5 and enter in the formula plus g5 minus i5 to calculate our net total quickly format the contents of the value fields to two decimal places and format the tax rate cell h5 to a percentage format again with two decimal places that's fine we now have all the formulae working for our first line on our business expenses worksheet now let's increase the size of the expenses table higher the range c5 through to h5 and use the auto fill tool to copy and drag down these formulae to row 18. now select the range c6 through to h18 and delete the contents these rows are now all blank but importantly the underlying functions and commands remain select the range i5 through to j5 and use the auto fill tool to copy these two formulae down to the end of the table on row 18. now copy in the few lines of data that i've entered into the table and then format the table as shown on screen or choose a format style to your liking now test out each line to ensure that your formulae are working correctly please pause the video whilst you complete this process we are now going to use the sum if command to calculate the cumulative values for all of our expense analysis types to make our use of the sum if command easier we are going to name the ranges that the sumif command will call upon this range is the net value range of j4 through to j19 so highlight this range and from the formulas ribbon choose the create from selection tool make sure that the top row is the only box checked and then click ok to save and close the some if command will also call on the analysis column let's create a name for this range using a different method highlight the range f5 through to f18 and now choose the name manager tool from the formulas ribbon from the dialog box that opens click on new and enter in the new name of analysis and now click ok to save and close navigate to cell m5 and enter in the formula equals sum if open brackets analysis comma 0.2 cell l5 comma net value and close the brackets and press enter this should return the value of 100 which if you check is the cumulative value of all instances in our business expense table where analysis is telephone with cell m5 still active copy and drag the formula down all the way to cell m14 the formulae are working fine in cell m15 use the autosum tool to add a total for our table we can now use the sumif command to calculate the tax values in the tax type tables range 05 through 208 firstly though we will name the ranges that our sum if command will use highlight the range h4 through to i19 and from the formulas ribbon select once again the create from selection tool make sure that the top row is the only box checked and then click ok to save and close select cell p5 and enter in the formula equals sum if open brackets tax rate comma 0.205 comma tax value and close the brackets once completed the cell should return the value of 60 which if you check in the business expenses table is the cumulative value of all instances where the tax rate percentage is set to 20 percent now with p5 still active copy and drag down the contents in p5 through to p8 such that all the tax rates are calculated you can now add totals in cells m15 and p9 and now add the narratives and the formatting as shown on screen please pause the video whilst you complete this process finally we will create links to the documentation supporting each expense line navigate to cell e5 we are going to insert a link to a pdf of the telephone bill so from the insert ribbon pick up the link tool from the dialog box that opens click the insert link option this opens up a new insert hyperlink dialog box now our document is stored on a usb drive your documents may be stored on a local file a cloud location such as onedrive or maybe your organization's computer network wherever your file is located the routine i'm about to show you will be the same in the link to section make sure that the existing file or web page option is highlighted then in the look in section navigate to your location mine is a usb drive select your file location and pick up the correct file once done click ok to save and close check that the link works by clicking on it from within the business expenses table it should take you directly to the document now tidy up the worksheet formats and make sure that you adjust your column widths to ensure that all contents are visible and that's it we have now completed our business expenses template we do hope that you enjoyed watching this video and that there was lots of content that you found both useful and informative if you would like a copy of this spreadsheet template then please do subscribe to our channel and give this video a big thumbs up you can also visit us on one of our channels on facebook instagram or twitter now if you are a small business and you want to keep your accounting records using a spreadsheet then why not watch our how to keep your accounts in excel video and if you would like to get a copy of this spreadsheet then please watch our free mr spreadsheet templates video for full instructions thank you once again for watching