there are several ways to add dates in excel depending on format of day book in expenses. receipt book template – 15+ free word, excel, pdf format download! If you therefore switch from another accounting solution to our template, you need to recapture all your income & expenses as from the first day of the first financial. Petty Cash Book is a ready-to-use Excel template to systematically record and manage your petty or small daily gaalmapat.site2/9/0//numafezenosixiz_low__gloss_finish_sheet_vinyl_gaalmapat.site
- format of day book in excel
- Cashbook in Excel
- How to use the Basic Accounting template
- 100% UNIQUE EXCEL TEMPLATES
- Download Cash Book Excel Template
This is the first of our three accounting templates in Excel. Use the basic accounting template if you have a service-based business and do not need to issue invoices. Accommodates multiple financial years and easy to roll forward or back.
format of day book in excel
The template is easy to use and can be customized by editing the default accounts and adding an unlimited number of additional accounts. It also accommodates sales tax calculations and multiple bank accounts. After completing the initial template setup, the template can be rolled forward or back by simply changing the reporting year in a single input cell. Note: The main difference between our Basic and Service Based accounting templates is that the Service Based template also includes customer details and invoicing.
If you do not need to produce invoices for customers, the Basic accounting template should be used. The template includes the following sheets: Setup - enter your business name to change the headings on all the sheets and customize the sales tax codes and bank codes.
The first financial year, reporting year and year-end input cells determine the monthly periods that are included on the trial balance, income statement, cash flow statement and balance sheet. Groups - this sheet contains the default list of account groups that are used in this template. You can customize the descriptions of the account groups but we do not recommend changing the account group keys. Additional account groups can be added and inserted in the appropriate location on the income statement, cash flow statement and balance sheet.
TB - this sheet contains a full list of the accounts that are included in the template and a trial balance which is automatically calculated based on the transactions that are entered on the Income and Expenses sheets.
The default accounts list can be customized by editing the existing accounts or by inserting new rows for additional accounts. The period for which the trial balance is calculated is determined by the month-end period that is selected at the top of the sheet in column G. Income - all income and other receipt related transactions should be entered on this sheet.
The columns with yellow column headings require user input and the columns with light blue column headings contain formulas and are therefore automatically calculated. Expenses - all expense and other payment transactions should be entered on this sheet. We also recommend entering all journals on this sheet. IS - the monthly income statement on this sheet is automatically compiled based on the transactions that are recorded on the Income and Expenses sheets.
The 12 monthly periods that are included in the report are determined based on the reporting year that is specified on the Setup sheet. CFS - the monthly cash flow statement on this sheet is automatically compiled based on the income statement and balance sheet calculations. BS - the monthly balance sheet on this sheet is automatically compiled based on the transactions that are recorded on the Income and Expenses sheets.
All the line items are set to report account group totals but you can include individual accounts by changing the keys in column A. Bank - this sheet contains an analysis of bank account movements on a daily basis for the selected month and on a monthly basis for the current financial year.
Aside from selecting the bank code and month, no user input is required on this sheet. SalesTax - this sheet contains a monthly analysis of sales tax calculations.
All the calculations on this sheet are automated and can be used for sales tax returns. You can also display monthly values based on individual sales tax codes by selecting the appropriate sales tax code at the top of the sheet. The first step in setting up the template for your business is to enter your business name at the top of the Setup sheet.
Your business name is included as a heading on all the other sheets. The first financial year that is entered on the Setup sheet is very important because it determines the first period that will be included in the template calculations.
The year that is specified in this input cell also determines the effective date for which opening balance sheet balances should be entered in column C on the TB sheet. Note: Once you have set the first financial year that needs to be included in the template, you need to use a trial balance as at the end of the previous financial year as generated in your previous accounting solution to enter the balance sheet opening balances in column C on the TB sheet.
The total of all the balance sheet opening balances needs to be nil in order for the balance sheet to balance. This template can only be used for full financial years.
The template does not make provision for entering opening balances for income statement items and you therefore need to start with data for a full financial year. The reporting year that is entered on the Setup sheet determines the 12 monthly periods that are included on the income statement, cash flow statement and balance sheet.
The IS, CFS and BS sheets include 12 monthly periods - these periods and the calculations that are automatically performed on these sheets can therefore be amended by simply changing the reporting year on the Setup sheet to include a new month financial period in these reports. Note: The first financial year basically needs to be set once when you start using the template but the reporting year can be used to roll the month financial period that is included in the template forward or back by simply changing the year in this cell.
The template can be used for multiple financial years and all template calculations are updated automatically when you change the reporting year.
You also need to select the appropriate year-end month from an input cell on the Setup sheet. This selection is used to determine the start date and the end date of each financial year and also determines the date on which opening balance sheet balances need to be included on the TB sheet. The template accommodates an unlimited number of sales tax codes.
You can include as many sales tax codes as you want by editing the default list of sales tax codes on the Setup sheet and adding additional sales tax codes by inserting the required number of additional rows, entering a sales tax code use letters between A and Z and entering the appropriate sales tax percentage. The sales tax codes that are included in the list on the Setup sheet are automatically included in the drop-down lists in the Sales Tax Code columns on the Income and Expenses sheets and the relevant percentages are automatically used to calculate the sales tax amounts on the same sheets.
We have also included a SalesTax sheet which is automatically calculated and requires no user input. This sheet contains a monthly analysis of sales tax amounts and can be used to populate sales tax returns. The calculations can also be displayed for a specific sales tax code by simply selecting the appropriate code from the list box at the top of the sheet. When you then clear the contents of the list box, totals for all sales tax codes are displayed.
The template also accommodates an unlimited number of bank accounts. You can include as many bank accounts as you want by editing the default list of bank account codes on the Setup sheet and adding additional bank account codes by inserting the required number of additional rows, entering a bank account code and entering a description of the bank account. You also then need to copy the formulas in columns D and E indicated with light blue cell background from one of the existing bank account codes.
Note: When adding bank account codes, we recommend inserting new rows anywhere above the JC bank account code and using a two character bank code for all new bank accounts.
Cashbook in Excel
The JC bank account code is reserved for journal entries and the default code for this bank code should not be changed. Journals do not really have a cash effect but we are using the JC code to facilitate including general ledger journal type entries in the template. All the bank codes that are added to the Setup sheet are automatically included in the drop-down list boxes on the Income and Expenses sheets and are available for selection immediately after being added to the Setup sheet.
If the default number of bank account codes four including petty cash is not sufficient for your requirements and you have added additional bank account codes to the Setup sheet, you also need to add these bank account codes to the TB and BS sheets.
How to use the Basic Accounting template
Note: It is very important that you add the additional bank account codes to the TB and BS sheets otherwise your trial balance and balance sheet will not balance! The bank accounts on the TB sheet are part of the control accounts section at the bottom of the sheet.
Adding the additional bank account codes is a simple exercise - insert the required number of additional rows and copy the formulas from one of the existing bank account rows. The template will automatically display your bank account codes in the same sequence as they are included on the Setup sheet. After adding the additional bank account codes to the TB sheet, you also need to add the new codes to the BS sheet. The procedure to follow is again really simple - insert the required number of additional rows above the JC bank account code BS-BJC key in column A and copy the formulas from one of the existing rows.
You can copy the entire row to ensure that you have included the formulas in all columns. The formulas will automatically pick up the correct bank account codes. Note: If an additional bank account has been added to the Setup sheet and not included on the TB or BS sheets, the appropriate row will contain an "add! This indicates that the bank account still needs to be added to the appropriate sheet.
Note: We have also included a Bank sheet in the template which enables users to analyse bank account movements in a lot more detail especially when preparing bank reconciliations. This sheet enables users to select a bank account code and month at the top of the sheet in order to display daily and monthly bank account movements. Aside from selecting the bank code and month, the sheet requires no user input. The input error codes at the bottom of the sheet are included for information purposes only and provide users with a reason for the error codes that may be encountered when entering transactions on the Income and Expenses sheets.
These error codes are covered in more detail in the Error Codes section of these instructions. The accounts that are included on the income statement, cash flow statement and balance sheet are based on a pre-defined account structure which consists of pre-defined account groups that are used to populate the appropriate line items.
A detailed list of these account groups is included on the Groups sheet. Note: Changes to the Groups sheet do not influence any of the calculations on the other sheets and only have an effect on the account group descriptions that are included on the IS, CFS and BS sheets.
100% UNIQUE EXCEL TEMPLATES
If you want to make changes to our default account groups, we only recommend doing so if absolutely necessary and that you customize existing account groups before considering adding new account groups. Customizing similar, existing account groups which are not required is a lot easier than adding new account groups. Note: If you do not change any of the default account groups, it is not necessary to review the rest of the instructions in the Account Groups section.
The description of line items on the balance sheet can be changed quite easily by changing the description of the appropriate account group on the Groups sheet and then changing the account descriptions of the appropriate accounts which are included in the account group on the TB sheet. Account groups can accommodate up to individual accounts which are linked to the account group by the same first 5 account number characters.
The same account integration principle is applied to most balance sheet account groups where the default integration is to report account group totals as indicated by the "G" at the end of the account group keys.
Although it is not difficult to add new account group keys to the Groups sheet and to then include the new keys in the balance sheet or income statement, there is a risk of balance sheet imbalances occurring if the cash flow statement movements are not included correctly. We therefore recommend completing the following steps if you want to add new account groups to the income statement or balance sheet:.
As you can see, it becomes relatively complex when needing to add new account groups to the template and it is therefore much easier to change the descriptions of one of the existing line items in order to accommodate new or renamed account groups. Note: Refer to the Account Integration section of the instructions for guidance on how to include individual accounts on the income statement or balance sheet.
Download Cash Book Excel Template
This section is part of the main Trial Balance section below. The default chart of accounts on the TB sheet includes all the individual accounts that have been created in each of the default account groups. Account groups constitute the first 5 characters of the account number and the individual account numbers are represented by the next two characters in the account numbers. Each account group can therefore accommodate up to account numbers 00 to Note: The template has been designed based on a fixed account number format.
All account numbers start with a two character code which identifies whether an account is an income statement account IS or a balance sheet account BS followed by a hyphen, the two-digit account group as listed on the Groups sheet and a two-digit account number.
This default account number format should not be deviated from otherwise the template calculations may not be accurate. The template can be customized for your business by reviewing the accounts that are included for each account group as per the Groups sheetchanging the account descriptions in column B where required and inserting additional accounts if the number of accounts that are provided for in the default chart of accounts are not sufficient.
Additional accounts can be created on the TB sheet by inserting a new row, entering the appropriate account number, entering an account description and copying the formulas in columns E to H from one of the existing rows. We also recommend inserting the new row in the appropriate location based on the account number sequence in an ascending order.
The list boxes in the Account Number columns on the Income and Expenses sheets include the accounts in the same sequence as on the TB sheet - if account numbers are therefore not added in the correct order, it may be confusing when selecting accounts.
Note: All new accounts need to be created within the existing account group structure as per the Groups sheet otherwise the template calculations may not be accurate and your balance sheet may not balance.