Collaborate on Receipt Book Template Excel for Finance with Ease Using airSlate SignNow

Receipt book template excel for Finance

hello and welcome to another Mr spreadsheet video today I will show you how to create your own simple cashbook for you to record your business or personal bank or cash transactions the spreadsheet uses excel's table creation tool for its basic template and then I use an if statement I use the vlookup function and finally the D sum command the completed spreadsheet is very easy to use and can be expanded and adapted to your own specific requirements as usual I will explain how you can get a copy of the completed spreadsheet later on in this video and if you need any help on the Excel functions that I've used then please leave a comment below I do hope that you enjoy watching this video let's create our cach book by entering the various column headers that we'll need navigate to cell C4 and type in date cursor one to the right and enter type in E4 enter description cursor to F4 and enter ref in G4 it's analysis and finally in H4 enter value now highlight the range C4 to H9 this will become our cashbook table so from the insert ribbon select the table tool accept the values in the dialogue box that opens and then check the my table has headers box then click okay to complete add a total row by clicking the total row option and choose a table style to your liking now let's enter some initial data so on the first row of our cashbook table in cell C5 entering the date 1st of January 2021 then in the description field type in F frt and Co and finally enter the positive value of 32480 in H5 for our value now this still leaves us with D5 the type F5 the ref and G5 the analysis still to complete select cell D5 we want this to say receipt if the value of H5 is a positive or payment if the value of H5 is a negative we can use a simple if command to achieve this so enter the formula equals if Open brackets 2 H5 and then less than Z comma payment comma receipt and then close the brackets notice that the word payment and receipt are enclosed in inverted commas D5 and indeed the entire Dr row now populates with the word receipt our analysis will consist of any one of 10 analysis descriptions so let's quickly create these navigate to cell J5 and enter in the number one and in j6 enter the number two and with both J5 and j6 highlighted drag down to j14 we now have numbers 1 to 10 now copying the analysis types that I have entered in K5 through to k14 you may wish to pause the video to achieve this or indeed You may wish to actually put in your own discription descriptions to make data entry quick and easy we're going to use the vlookup command to determine which of our 10 analysis types to use on each line of our cach book we will enter a value of one through to 10 in each reference field on each line so that if we entered a one in cell F5 this would automatically retrieve sales as this is the value that corresponds to the first line of our analysis types table that we have just created let's quickly give the analysis table from J5 to k14 a name select and highlight this range and from the formulas ribbon select the Define name Tool and enter analysis table with no spaces in the name field of the dialogue box that opens and then click okay enter number one into cell F4 and in cell G5 type in the formula equals V lookup point to cell F5 comma then the word analysis table with no spaces comma the number two comma and then the word false and close the brackets this says go to the analysis table and look for the row that contains the number in cell F5 then find that number in the First Column of our analysis table and then retrieve the value in the second column of that table the variable falce is mandatory at the end of this formula but for our purposes it is not relevant once you complete the formula the value sales is automatically retrieved from the analysis table and populate cell G5 the remaining analysis Fields will return the na error which will disappear once a valid reference is entered Let's test out a new line to see if all of our formulas are working correctly in cell C6 enter the date 2nd of January 2021 and in the description field type in ABC limited type in the number two in the ref field which correctly retrieves the description materials from the analysis table finally enter 632.8 5 as a negative number in cell H6 once this number is entered the word payment shows correctly in cell D6 now you may wish to pause the video to now copy in the remaining lines as shown to complete our cashbook entries and to validate all of our commands now we need to calculate the totals for our cash book select cell H20 and from the drop-down box select the sum command the value column now has a total we would also like to calculate the total values for each of our 10 analysis types to do this I'm going to use the D sum command the DM command has three components firstly you need to select the database or data table that you are using so in our case it is the entire cach book from C4 to H2 20 the second component is the value field now this is a numeric value that represents the column number that contains the value so working from left to right across our cache book we note that the value field is in the sixth column so the second component of our D sum command is simply the number six finally the criteria is simply the range that contain contains the criteria type and the criteria value as we have 10 separate criteria values number one through to number 10 it would help us to isolate each one of these so select cell N1 and enter in our criteria field name which is ref then in cell enter enter in our criteria value for the first of our 10 analysis types so enter the value one we can now enter our first Dum formula in cell L5 to calculate the total value of sales in our cach book so enter in the formula equals D sum Open brackets then select the data table C4 to H20 then you need a c comma then the number six followed by a comma and then our criteria of N1 through to N2 and close the brackets once you press return L5 now contains the total value of all instances where reference one occurs now we simply need to repeat this process for the the other nine analysis types so in cell 01 enter ref again and in O2 enter the number two now using the D sum command again enter the formula into L6 for our second analysis once you have completed the D sum commands for each analysis type select cell l15 and using the auto sum command enter in the total for L5 through to l14 hopefully this will reconcile to the cashbook total finally let's do a bit of formatting and tidying up of our completed cashbook select cell C2 and enter in your cashbook title make bold and increase the font size now whilst holding down the control key select the ranges H5 through to H20 and L5 through to l15 and from the formatting dialogue box select numbers with two decimal places and with the 1,000 comma separated checked and negative numbers to be preceded by a minor sign make bold and increase the font size for the total fields in H20 and l15 and finally move the values in cells N1 onwards out of the way by simply extending the width column of column n and that's it we've now completed our cash book we do hope that you enjoyed our spreadsheet video and that there was lots of content that you found both both useful and informative now if you would like us to send you a copy of the 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 while not watch our accounting spreadsheet tutorial alternatively why not view our how to keep your accounts 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