Collaborate on Delivery Receipt Template Excel for Accounting with Ease Using airSlate SignNow

Delivery receipt template excel for Accounting

hello and welcome to the mr spreadsheet youtube channel in this video i will show you how to create a simple bookkeeping spreadsheet for you to record your business transactions during the tutorial i will use a basic data table created with excel's table creator tool and i will incorporate a data list drop-down box and also examine excel's filtering tool to analyze the data the completed spreadsheet is very easy to use and can be expanded and adapted to your specific requirements 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 that i've used then please do leave a comment below i hope you enjoy watching we will start our bookkeeping spreadsheet by entering the word date in cell b4 in cell c4 enter the word type in cell d4 its description and in cell e4 type in value cells f4 through 204 contain the analysis details for each of our transaction lines i have entered sales materials delivery motoring admin marketing finance professional drawings and other finally i've added an additional column called notes in cell p4 now you may have different analysis types for your business but don't worry because you can always change these later now that we have our bookkeeping spreadsheet headers we can use excel's create table routine to automatically prepare a precise and well formatted table for us to use so select the range b4 through to p18 and whilst this range is highlighted select the insert ribbon and from this ribbon click on the table tool the create table dialog box will now open accept the default data range and check the my table has headers box now click ok you now have your bookkeeping spreadsheet table we just want to make a couple of changes to this check the total row box to add an extra row at the end of the table so that we can total the individual columns select a table style to your liking and finally from the page layout ribbon uncheck the view gridlines box so that we have a crisp clear background to our worksheet now every transaction needs a date so enter the 1st of march 2021 into cell b5 press enter and the column width expands to accommodate this date in c5 the type column we want this to be one of six transaction types so we need to create a list of these and then create a drop down box from which to select one of the six types let's create this list but locate it off screen navigate to s4 and enter in the word types and in the six rows directly beneath this enter your transaction types mine are bank receipt bank payment card receipt card payment cash receipt and cash payment now highlight these six transaction types and from the formulas ribbon click on the define name tool enter types in the name field of the new name dialog box and click ok now press the home key on your keyboard to return back to our bookkeeping spreadsheet with your cursor in cell c5 we can now create a drop down box selector which will allow us to choose from the transaction types that we've just created select the data ribbon and choose the data validation tool in the allow field select list and in the source field enter equals types and then click ok a drop down box now appears to the right of c5 inviting us to select one of the six transaction types please choose bank payment adjust the column width to accommodate the contents and in cell d5 enter james cook and enter the value minus 217.34 in cell e5 we will analyze this transaction to delivery so navigate to the delivery column and enter minus 217.34 in cell h5 ok we have now completed all of the essential information for this transaction now we need to add some totals we need to copy the formula in c5 such that all rows now have the drop-down function and we need to add a check routine and finally add a title and format our workbook select cell c5 the first row of the type column and simply drag down to cell c18 each row now populates with bank payment simply delete these by highlighting the range c5 to c18 and pressing the delete key on your keyboard the narratives disappear but the drop down box still remains navigate to the totals row for the value column this is cell e19 and from the drop down box select the sum option now drag this formula all the way across the worksheet to cell o19 such that we have totals for all of the columns you can delete the contents of cell p19 as we do not need these to make sure that our data entries are correct we need a check total this check total will ensure that the figure in the value total is equal to the sum of all the analysis totals so navigate to cell c19 and enter in the formula plus e19 minus sum open brackets and then select f19 through to o19 and close the brackets this value should always be zero if it is not then there is a data entry that needs correcting with your cursor in cell b2 enter in a title for your bookkeeping spreadsheet make this bold and increase the font size now highlight the range f5 through to o19 and from the format cells tool select number and set to two decimal places with the 1000 comma separator box checked and negative numbers to display in red preceded by a minus sign now click ok to complete select the range b19 to p19 and change the background color to make the totals row stand out and make this bold and increase the font size now to emphasize our check total navigate to cell c19 apply a different background color and a different font color and center the cell contents finally navigate to cell a5 and from the view ribbon choose the freeze panes tool such that when you expand the bookkeeping spreadsheet we can easily navigate upwards and downwards whilst the column headers and the title remain in view now let's test out the bookkeeping spreadsheet in the second row enter the date 7th of march 2021 and choose bank receipt from the type drop down box enter abc limited in the description field and as this is a receipt enter the positive value of 1000 in e6 analyze this to sales and then check that your entry is mathematically correct by ensuring that your check total in cell c19 is zero now as you progress you will eventually run out of rows you can simply use the insert row tool from the home ribbon to add more lines always making sure that you copy the drop down box feature in the type column to populate these new rows i have now populated the bookkeeping spreadsheet with a month's worth of data and in this final part of the video i will demonstrate the use of excel's filtering tool to further analyze our data please pause the video for a moment and copy the data on screen into your worksheet now let's say that we want to know the value of our card receipts in the month so click on the downward pointing arrow in cell c4 then uncheck the select all box and then check the card receipt box once you click on ok the bookkeeping spreadsheet will truncate showing only the card receipt transactions you can use this filtering routine across the entire spreadsheet and you can use multiple variables just click the undo tool to revert back to the full table and that completes our video tutorial on how to create your bookkeeping spreadsheet 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