First, download a free invoice template for Excel , or get one for Google Sheets. Then, add a new sheet to your Income Statement Template. Copy and paste the data from the invoice template you downloaded into the new sheet. Keep the tab for your invoice tracker to the right of the Transactions sheet, but to the left of your monthly Income Statements.
However, for your own personal planning, you can still plan your cash flow month by month in a simple spreadsheet. Copy and paste it into a blank sheet next to your Transactions. Then you need to categorize it.
When you use Bench , your transactions are automatically imported and categorized for you. Get a record of the transaction. That could be a credit or bank card statement, info in your Paypal account, or receipt from a cash payment. Categorize the transaction. Enter its relevant account under the Category column. Be sure to type it exactly as it appears on the Chart of Accounts. The Income Statement will look at the data you enter in order to figure out how the transaction will be categorized.
By the end of every month, the Income Sheet should have all the data it needs to summarize your bookkeeping for that period. Make sure to keep the Income Sheets separated, organized by month. You may want to make a copy of each one at the end of the month, and either save it or upload it to the cloud. This excel template is unlike our Cash Book, where we had two different half to record debit and credit transactions.
Accounts Payable Accounts Payable Accounts payable is the amount due by a business to its suppliers or vendors for the purchase of products or services. It is categorized as current liabilities on the balance sheet and must be satisfied within an accounting period. Every vendor requires different TDS percentages, so you need to enter the TDS percentage based on the vendor category. Accounts Receivable Accounts Receivable Accounts receivables is the money owed to a business by clients for which the business has given services or delivered a product but has not yet collected payment.
They are categorized as current assets on the balance sheet as the payments expected within a year. AR is the blood of the business because you need money to run your business, and based on the funds available; the proprietor decides the Accounts Payable dates irrespective of the due date.
If there is money, then how you do pay even if the due date is tomorrow, and that is where the Accounts Receivable team plays a major role in pushing the clients to make the payment on time.
One of the thumb rules in accounts Rules In Accounts Accounting rules are guidelines to follow for registering daily transactions in the entity book through the double-entry system. Keeping that in mind, we need to create an aging schedule breakup the total receivable amount into different time slabs. For accounting purposes, the exclusive amounts are allocated to the income statement, the sales tax amounts are allocated to the sales tax control accounts and the inclusive amounts are allocated to trade creditors or bank depending on whether the amounts have been paid or not.
Sales Tax 1 Amount - this column contains a formula which calculates the national or federal sales tax amounts based on the tax codes selected in column F. Sales Tax 2 Amount - this column contains a formula which calculates the state sales tax amounts based on the tax codes selected in column G. Inclusive Amount - this column is based on the tax inclusive amount which the user enters in column E. All the transactions that are entered on the Expenses sheet are automatically updated to the other sheets in this template.
Example: If you only want to display the transactions for a particular invoice or payment month, you can filter the data based on the Document Date or Payment Date column by selecting the appropriate date filter from the filter menu which is displayed after clicking the selection arrow.
If you want to view the list of expenses which make up any of the monthly trade creditor balances on the balance sheet, simply enter the appropriate month-end date as the balance date in cell K2 and then use the filter arrow next to the column heading in the outstanding balance column to deselect all zero values. All the expenses with balances at the appropriate month-end date will then be displayed.
If you only require one sales tax type and you would like to delete the second sales tax type, you will be able to do so without affecting the template formulas by simply deleting the columns for Tax 2 Code column G and Sales Tax 2 Amount column N.
When funds are transferred between two bank accounts, both the deposit and withdrawal entries need to be recorded. We recommend recording both entries on the Expenses sheet. The deposit entry should be recorded by selecting the bank account into which the funds are transferred and entering the transfer amount as a negative value.
The withdrawal entry should be recorded by selecting the bank account from which the funds are transferred and entering a positive amount. All transfers between bank accounts should be allocated to the Cash Transfer Control account BS By using this default account for all bank transfers, you can easily determine when only one of the entries deposit or withdrawal has been recorded.
Note: You can check whether the balance of the Cash Transfer Control account is nil by selecting the appropriate month end from the list box in cell G3 on the TB sheet and checking whether the balance for account BS is nil. If the balance is not nil, the transactions for the appropriate period should be reviewed in order to determine why the allocations to the Cash Transfer Control account do not result in a nil balance.
Note: Petty cash transactions should be treated in the same way as transactions that are processed through a bank account. This means that petty cash reimbursements should also be treated as transfers between bank accounts and allocated to the Cash Transfer Control account. Both the withdrawal out of the appropriate bank account and the "deposit" of cash into the petty cash account should therefore be recorded. We recommend that all general ledger journal type entries are also recorded on the Expenses sheet.
We have basically created a specific bank code for the recording of journal entries so that these transactions will not have any cash effect when recorded incorrectly. The debit part of the journal entry should be recorded as a positive value and the credit part should be recorded as a negative value on the Expenses sheet.
Both entries should be allocated to the JC bank code. Note: If the debit and credit parts of all journal entries balance to a total of nil, the JC bank account should always have a nil balance. If you therefore notice that the JC bank account has a balance, it means that you have made an error in recording some of your journal entries. You can then filter the Expenses sheet for all transactions with a bank code of JC and filter the document dates for the appropriate month in order to review all the journal entries for the particular month.
The following error codes may result from inaccurate input on the Income and Expenses sheets and will be displayed in the Error Code columns. The heading of the affected input column will be highlighted in orange:. Note: Input errors may result in inaccurate template calculations and it is therefore imperative that all errors are resolved before reviewing the income statement, cash flow statement and balance sheet.
These errors may in fact also cause the balance sheet not to balance. All the transactions recorded on the Income and Expenses sheets are automatically updated to the monthly income statement which is included on the IS sheet. All of these income statement accounts are reflected exclusive of any sales tax which may be applicable to the source transactions. The income statement includes 12 monthly periods, an annual total and a column for comparison to a prior year.
The periods are based on the reporting year which is specified on the Setup sheet. If you want to amend the 12 month reporting period, you only need to change the reporting year on this sheet in order for the reporting period and all calculations on the IS sheet to be updated. All other account groups are integrated on a group basis which means that all the accounts that are included in the appropriate account groups are included in one total line on the income statement.
Note: You can easily identify a group integration on the income statement, cash flow statement and balance sheet by the key in column A ending in a "G". If the key does not end in a "G" but includes complete account numbers, it means that the appropriate lines are integrated on an individual account basis. When an account group is integrated on an individual account basis and you make changes to the account numbers on the TB sheet or if you add new accounts to the account group, you basically also need to make the same changes to the income statement or balance sheet.
This means that you either need to update the key in column A if the account numbers have been changed or that you need to insert additional lines for new accounts, copy the formulas from one of the existing lines and edit the key in column A to reflect the new account number s. The same process can be followed if you want to change the integration of an account group from account group reporting with the "G" at the end to individual account reporting. Additional lines need to be added for all the account numbers that form part of the appropriate account group, the formulas in all columns on the sheet can be copied from the existing line and the keys in column A then need to be replaced by the full account numbers.
It may also be necessary to update the appropriate total or profit line which the account groups forms part of. Note: If you change the reporting of any account group from group to individual reporting and you do not include all account numbers on the income statement, your balance sheet may not balance.
It is therefore imperative that you add all individual accounts to the appropriate account group if it is set up to report on an individual account basis. Refer to the TB sheet and more specifically the Status column where all integration errors will be highlighted in red. Note: If you are not going to use any of the default account groups which have been included on the income statement, we suggest hiding the rows instead of deleting them.
If you do decide to delete the rows containing the affected account groups, you may have to fix some of the total or profit calculations. After deleting the account groups, you should also delete the individual accounts on the TB sheet linked to the appropriate account groups so that no transactions can be allocated to these groups.
All the transactions recorded on the Income and Expenses sheets are also automatically updated to the monthly cash flow statement which is included on the CFS sheet.
No user input is required on this sheet. Note: The same principles with regards to account group integration as included in the Income Statement section applies to the cash flow statement. We do not however recommend changing the default account group integration on this sheet. Most account groups are set to report on an account group total basis and there is no real benefit in switching to individual account integration on the cash flow statement.
Note: The only changes that may be required are the addition of new lines if you have added any new account groups to the template. You then basically need to copy a line of a similar nature which has a green key in column A and change the key in column A so that the monthly amounts are calculated based on the new account group.
Note: All the keys in column A which are reflected in orange contain custom calculations relating to specific control accounts which should not be copied for any new account group lines. All the transactions recorded on the Income and Expenses sheets are also automatically updated to the monthly balance sheet which is included on the BS sheet.
The balances sheet account integration is set up to report account group totals for most of the balance sheet items. The exception is the individual bank accounts in the current assets section which all report individually. If you therefore add additional bank codes to the Setup sheet, you also need to add additional rows in this section to include the new bank codes in the balance sheet.
You only need to insert the required number of additional rows and copy the formulas from one of the existing bank account lines to complete this process. The formulas will automatically pick up the correct bank codes. If you want to change the account integration for any line on the balance sheet from group reporting to individual reporting, you again need to insert the required number of additional rows, copy the formulas from the existing line and replace the account group key in column A with the full account numbers.
If you do not add all the accounts that form part of the appropriate account group, your balance sheet may not balance. Note: If you convert one or more account groups from account group reporting to individual account reporting, you may also need to update the appropriate total column on the balance sheet.
Note: Only the balance sheet items with green account keys can be changed to individual account reporting. The items with orange keys are control accounts which have unique formulas which cannot be used for any of the other balance sheet items. We have included two rows below the balance sheet that contain control totals which indicate whether the balance sheet is in balance. Both control totals should always display an "ok" text string but if there is a problem with the appropriate control total, an "error" message which is highlighted in orange will be displayed.
This error message means that there is an imbalance in the template and the value of the imbalance will be displayed above the error message for balance sheet errors and below the error message for cash flow statement errors. If you've completed all of the above steps and still cannot resolve the imbalance on the balance sheet, contact our Support function for assistance. You probably need to send us your version of the template so that we can determine what the cause of the imbalance is.
We have included the Bank sheet to enable users to analyse bank account movements for any bank account on a daily and monthly basis. The only user input that is required on this sheet is to select the appropriate bank code and monthly period from the drop-down lists at the top of the sheet. Note: If you want to display the bank account movement totals for all bank codes, simply clear the contents of the bank code input cell.
If no monthly period is selected, the calculations will default to the last month of the current financial year. Note: Negative values that have been included on the Expenses sheet are deemed to be Income and therefore included in the Income columns. Transactions that have been allocated to the JC general journal bank code are not included in the calculations on this sheet.
We have also included an additional sheet which enables users to analyse sales tax movements on a monthly basis. The SalesTax sheet breaks the movement in the sales tax control accounts BS and BS down into individual components which makes it easier to calculate sales tax return and paid amounts.
The main components of the sales tax control accounts are output tax, input tax and payments. The output tax totals are calculated based on the Sales Tax Amount columns on the Income sheet, the input tax totals are calculated based on the Sales Tax Amount columns on the Expenses sheet and the Payment column includes all transactions on the Income and Expenses sheets which have been allocated to account BS or BS Note: All sales tax payments should be recorded on the Expenses sheet and allocated to the appropriate sales tax control account while all sales tax refunds should be recorded on the Income sheet and allocated to the sales tax control accounts BS for national or federal sales tax or BS for state sales tax.
The Inclusive section provides the monthly totals inclusive of sales tax - as per the Income sheet for the Output column and as per the Expenses sheet for the Input column. These totals are usually required when completing sales tax returns. The net monthly column is calculated by deducting the monthly input tax value from the monthly output tax value and therefore represents the net monthly sales tax liability or asset if the input exceeds the output. The effective monthly sales tax percentages are also calculated and included on the sheet.
If cell D2 is empty, the both option is assumed and the calculations are reflected for both sales taxes and therefore both sales tax control accounts.
Note: You can also select individual sales tax codes from the list boxes in cell E2 and F2 at the top of the sheet to display the calculations based on any of the tax codes which have been included on the Setup sheet. The content of these list box can then simply be cleared to display the values for all sales tax codes.
Note: The template provides for two sales tax types namely national or federal sales tax and state sales tax. If you only require one sales tax type and you would like to delete the second sales tax type, you will be able to do so without affecting the template formulas by simply deleting the columns for Tax 2 Code and Sales Tax 2 Amount on the Income and Expenses sheets. If you do not require any sales tax calculations, you can leave the sales tax 1 codes blank and delete the sales tax 2 columns as indicated above.
After specifying the first financial year on the Setup sheet and including the appropriate opening balances in column C on the TB sheet, the template can be rolled forward for subsequent financial years quite easily.
All you need to do in order to roll the template forward for subsequent financial years or back for previous financial years is to amend the reporting year on the Setup sheet. When you change the reporting year, the monthly reporting periods on all the sheets that are included in the template are automatically changed and all calculations are automatically updated.
You therefore do not need to save a separate version of the template for each financial year - you simply continue adding transactions to the Income and Expenses sheets and use the reporting year setting on the Setup sheet to navigate between financial years. How to use the Basic Accounting template Download the sample or trial version when reviewing these instructions. Setup The first step in setting up the template for your business is to enter your business name at the top of the Setup sheet.
Sales Tax Codes The template accommodates an unlimited number of sales tax codes. Bank Accounts The template also accommodates an unlimited number of bank accounts. Input Error Codes 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.
Account Groups 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. We therefore recommend completing the following steps if you want to add new account groups to the income statement or balance sheet: Scan the Groups sheet for similar items which you are not going to use.
For an item to be similar, it needs to be of the same nature as the item that you want to add. For example, current assets can only be replaced by other current assets and liabilities can only be replaced by other liabilities. If the items differ in nature, you should add a new account group instead of using an existing one. If no suitable existing item can be found, insert a new row and add a new account group by entering the appropriate account group key and a description for the account group.
When adding keys, you need to stick to the principle of a five character key which is unique and account numbers that need to be linked to this key, need to also start with the same five characters followed by a number between 00 and When adding new account groups, we suggest using the next available account group number after the "BS-". The default balance sheet account groups end after "BS" which means that the first available account group is "BS".
The default income statement account groups end after "IS" which means that the first available account group is "IS". After adding the appropriate account group to the Groups sheet, go to the TB sheet and add the required individual accounts which need to be included in the account group total on the balance sheet.
Remember that the appropriate account numbers all need to start with the same first five characters as the account group and refer to the New Accounts section of these instructions for guidance on adding new accounts to the TB sheet.
Accounting Journal Template. See how Smartsheet can help you be more effective. Accounts Payable Template. Accounts Receivable Template. Bill To Invoice Template. Bill of Lading Template. Billing Statement Template. Cash Flow Statement Template. Cash Flow Forecast Template. Expense Report Template. Income Statement Template. Payment Schedule Template. Simple Balance Sheet Template.
0コメント