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

Receipt book template excel for Accounting

hello let's start off our accounting spreadsheet by entering the various columns that we will need to describe and to analyze our data so in cell b7 type in date and then cursor one to the right and in c7 type in transaction in d7 enter description and in east seven enter analysis f7 contains our reference whilst finally enter value in g7 now that we have these headings let's quickly adjust the various column widths so that we can see their entire contents and that when we come to enter our data we have plenty of room we'll continue the design of our accounting spreadsheet by entering a heading in cell b2 so enter your business name followed by a description of your accounting period in Box B 3 enter start date and alongside in c3 and to the first date of your accounting period do the same thing in cells b4 and b5 for the final dates now we're going to reserve the top right hand side of our spreadsheet to record our bank details and balances so in cell f2 enter open balance and in cell f3 enter the name of your bank followed by the word receipts in f4 it's your bank name followed by the word payments box f5 contains your closed balance and finally navigate to cell g2 and enter an opening balance value of 100 in this section we're going to create a list of our income and expenditure types on a separate worksheet so double click on the sheet 1 tab and change the sheet name to read accounts then click on the plus side alongside to add a new worksheet and change that worksheets name to tables now whilst in the tables worksheet navigate to cell d2 and then enter equals and point to cell b2 on the accounts worksheet and then press Enter this retrieves our heading from the accounts worksheet and copies it into the tables worksheet now while still in the tables worksheet select cell d4 and type in expense types and in e4 type in totals now select c5 and then to the number 1 and in cell c6 enter the number 2 now select both of these cells numbers 1 and 2 and drag copy them down until it reaches C 19 such that you now have numbers 1 to 15 okay now let's enter our in income and expenditure names in the boxes d5 through to D 19 now you can use your own description or copy the values already entered these are sales a and sales be for your incomes followed by materials subcontract telephone internet advertising repairs travel costs subsistence entertaining fuel costs bank charges accountancy and sundries for your expense types now don't worry if these descriptions are not suitable for your biz yes as you can change them once the accounting spreadsheet has been completed so let's now move on to the next section where we name this range so that Excel can identify easily with our list of income and expenditure types we're going to give the range defy through 2d 19 and name just select this range and from the formulas ribbon selected define name and in the dialog box name enter expense types with no spaces and click OK now that we've created our income and expenditure list and called it expense types we can move back to the accounts worksheet and create a new list for our transaction types we want the only options to be either our bank receipts or our bank payments so highlight the range F 3 to F 4 and from the formulas ribbon select once again the define name command and then in the dialog box that opens enter transaction types with no spaces and click OK now navigate to Box c8 and from the data ribbon select the data validation tool select list from the drop-down box in the allow field and enter equals transaction types with no spaces in the source field and then click ok now select e8 and repeat the process with the source field now reading equals expense types with no spaces both the transaction and the analysis cells now contain drop-down boxes for you to select your transaction type and your expense type now that should make data entry quite easy navigate to box b8 and enter any date and type 'abc limited into d8 for your analysis select sales a and type in any reference in f8 finally enter 250 in the value box g8 go back to b8 and right click your mouse and select format cells in the category box select date and accept the default format the next 4 boxes are ok but we want the reference box to align to the left and finally we want to change the value box g8 so highlight this cell right click your mouse select format cells and this time select number from the categories section change the decimals to to click the 1000 comma separator box and choose the red with a minus sign for displaying negative numbers we can now highlight the entire first row b8 through to g8 and drag and copy the contents and formulas down to row 19 once we've done this we can delete the data copied into the range b9 2g 19 and then to indicate the end of the data section type the word end in Box B 20 we now want to return to our tables worksheet and enter the formula to calculate total values for each of our expense types this is best performed by using the sum if command now to make this routine easier it would help us to give some names to various ranges within our accounts worksheet so select the range e8 to eat when T on the Accounts worksheet and from the formulas ribbon select define names and call this the analysis range with no spaces and then click OK do the same for the range g8 to g20 and call this value range again with no spaces now we're ready to enter the summit command to calculate our expense totals we can now return to the tables worksheet and enter us some if command simply select cell e5 and enter in the following command equals some if open brackets analysis range comma D 5 comma value range and close the brackets this formula will add together all of the data elements on the Accounts worksheet where the data contained within this range is the same as that in D 5 on the tables worksheet now once we have the formula successfully entered into a 5 we can simply copy and drag the formula down to cell b9 teen to get the total values of all of the expense types let's tidy up and make clear our tables worksheet first of all from the page layout ribbon uncheck the view gridlines box to give our worksheet a nice crisp and clear white background now select d2 and using the home ribbon make bold and increase the font size select the range d4 to e4 and change the background color to blue and change the font color to white and using the borders tool select all borders now highlight the range c5 to 19 and with the borders tool again select all borders play around with the column widths to ensure that you can easily see all of the data in the totals we want the number to be to two decimal places with the one thousand comma separators in and negative values to show as red preceded by a minus sign now with the range 'if I've 219 highlighted select the conditional formatting tool and then choose highlight cell rules and then the equal to option enter the value 0 and select custom format from the drop-down box on the right hand side from the font menu select the color palette and choose white now click OK and ok again now only the values other than 0 will be displayed returning to the accounts worksheet you'll recall the earlier we defined and named the value range g8 to g20 we now need to do the same for the range c8 to see 20 so highlight this range and from the formulas ribbon select the define name tool and in the dialog box enter the name transaction range with no spaces now navigate to Box g3 and we can enter another sum if command to get the totals of all bank receipts in our data range so enter the formula equals sum if open brackets transaction range comma F 3 comma value range and then close the brackets this formula says calculate the total of all the instances where the value in f3 appears in the transaction range and then from the value range add these totals up now with your cursor still in g3 simply copy and drag down one line so the formula is also applied to the payments line so we now have two formulas to calculate both our Bank receipts and our Bank payments we also have entered our opening bank balance of 100 in cell g2 we can now complete this section by entering a simple formula of + g2 plus G 3 - g4 in the closing bank balance cell of g5 now to complete our accounting spreadsheet we can apply some formatting have a general tidy up and make the finished template look a little more professional so from the page layout ribbon uncheck the view gridlines box now highlight box b2 and increase the font size and make it bold we'll change the font colors of the to date fields c3 and c4 to dark gray and do the same for the bank narratives in f2 to f5 let's format the bank values in cells g2 to g5 such that they now have two decimal places that the 1000 comma separator is checked and that negative numbers display as red preceded by a minus sign now highlight the headers range b7 to g7 and change the background color and the font color and make it bold with the entire data area b7 to g20 selected use the border tool and select all borders and finally highlight the endline b22 g20 and change the background color and that finishes our design and development work in the next section we can test it all out in this section we are going to fully test out our accounting spreadsheet so let's enter some data into line to enter a date choose bank payments from the transaction drop-down box enter a reference and choose one of the analysis types put in a reference of your choice and finally enter a value we can see that the bank balances have correctly been updated and that if we switch to the tables worksheet we can confirm that the analysis values have also been successfully updated returning to the accounts worksheet enter a new payments line such that the total value will put the bank balance into overdrawn which should show correctly as a negative go backwards and forwards between the two worksheets and fully test each line if you need extra lines or indeed extra analysis types then by using the techniques demonstrated earlier you can easily increase the accounting spreadsheets capacity we do hope that you found the contents of this video both instructive and to be of use to you and your business thank you very much for watching thank you for watching our spreadsheet video we hope that you enjoyed it and there was lots of content that you found useful now if you'd like us to send you a copy of the completed spreadsheet then all we ask is that you subscribe to one of our channels so you can like via the mr. spreadsheet Facebook page you can follow on Instagram where it's at mr. spreadsheet or follow us on Twitter where it's at mr. underscore spreadsheet oh and yes please do subscribe to our youtube channel where there are many other spreadsheet solutions for you to watch now if you're a small business then why not watch our how to keep accounts in Excel video it's a great inexpensive solution for your accounts and bookkeeping needs and this will play automatically once this video ends many thanks for your attention