Excel template to track income and expenses
Excel template to track income and expenses
This article provides details of Excel template to track income and expenses that you can download now.
Whether you manage finances at work or at home, an essential first step is to have a budget in place. A budget is needed to know what you are currently spending, to decide where you can save money, and to determine where you want to allocate your money.
Manage your monthly income and expenses with these affordable budget templates. You can configure categories to see expense items.
Microsoft Excel software under a Windows environment is required to use this template
These Excel templates to track income and expenses work on all versions of Excel since 2007.
Examples of a ready-to-use spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.
To be able to use these models correctly, you must first activate the macros at startup.
Once you have clicked the OK button, the calculations start and the results are displayed.
The file to download presents three Excel templates to track income and expenses
The income statement reflects revenues, expenses, and asset amortization, as well as gains and losses of certain assets over a period of time. The main purpose of the income statement is to indicate the income and profitability of the business over a period of time, which can be one month, three months, a year or some other time period.
The income statement has three main sections: revenue, expenses, and net income.
Income statements for farm businesses are usually prepared on an annual basis. In addition, under the Canadian Income Tax Act, farms can file income tax returns based on cash accounting systems. While this is useful for tax management purposes, income statements produced by the cash method of accounting, unadjusted for items such as prepaid expenses or inventory changes, for example, may give a false indication of the financial performance of the farm business.
For example, farm businesses usually grow crops and produce livestock on an annual basis, but the expenses and revenues associated with crops and livestock are not always incurred in the same year. There exists the potential for a mismatch of revenues and expenses. For example, feeders may be purchased in the fall, fed over the winter, and sold the next year. But the purchase costs of the animals are usually expensed in the year of purchase, and the revenue from the sale of the feeders is usually recorded in the year of sale, when each of these transactions occurs. Similarly, feed for the cattle feeder operation may be purchased and expensed in December, but fed over the next few months. This would result in inaccurate accounting information for farms with a December 31st year-end.
Accrual accounting requires that the revenues and expenses be matched to the economic events to which they are related so that the actual financial performance of the business for a specified period of time can be accurately reported. A cash income statement, without making accrual adjustments, cannot provide this information.
Another example is deferred storage tickets received for grain sold at the end of December, and using the storage ticket to purchase fertilizer in December for use next year. Cash accounting methods and Income tax rules allow revenue from deferred grain sale to recognize when the deferred ticket matures in the new year, while at the same time recognizing the fertilizer expense in December. Without making accrual adjustments, income statements prepared using the cash method does not provide a true picture of the operation’s profitability because:
- Some of the revenue from sales may represent production from the previous year or fiscal period;
- Not all of the production from the year or fiscal period may be sold in that same year or fiscal period;
- The expensing of inputs such as fertilizer may be expensed in the year or fiscal period in which it was purchased, not the year or fiscal period it is used.
Farm clients file their income tax on an annual basis under the cash accounting income and expense method. Although this cash statement does not provide an accurate reflection of the profitability of the farm operation it provides a good starting point for building the accrued income and expense statement. The cash revenue and expense amounts stated on the income tax income and expense statement, and then accrual adjustments are made to the cash income and expense amounts to calculate the accrued net income.
Figure A1-2 shows an example of an Accrued Income and Expense Statement. In this statement cash revenue is entered as a lump sum. This revenue could be broken down and shown in detail if required. The adjustments to cash revenue to calculate accrued revenue are for accounts receivable and inventories. The opening amounts for these items are collected from the opening net worth statement. The closing amounts of these accrual adjustment items are brought in from the closing net worth statement. The adjustment is minus the opening amounts and plus the closing amounts as the opening inventories and receivables were generated in the previous accounting period. The closing amounts were generated in the accounting period being reported in these statements. Once the overall accrual adjustment is calculated ($70,000) it is added to the cash revenue received ( $250,000 ) to calculate the accrued revenue for the period of $320,000.
Cash expenses are also entered as a lump sum in this statement. These cash expenses could be broken down and shown into detail if required as well. The accrual adjustments to the cash expenses are for accounts payable, supplies inventory for production, accrued interest, and depreciation. Again the opening amounts for these items are gathered from the opening net worth statement, and the closing amounts from the closing net worth statement.
The adjustment for payables and accrued interest is minus the opening plus the closing amounts for these items. Again the opening amounts were generated in the previous accounting period, and the closing amounts were generated in the accounting period being reported. For the supplies inventory for production the adjustment is the opposite. We add the opening supply inventory and subtract the closing. The opening supply inventory was used in production in the accounting period being reported whereas the closing supply inventory will be used in the production of the next year’s products.
There are two ways to calculate depreciation for accounting purposes. The first is based on allowable capital cost allowance for taxation purposes and called the “taxation” method. This amount is calculated based on the cost of the depreciable item, minus previously claimed depreciation, and times the percentage amount allowed by Canada Revenue Agency for that class of depreciable asset. The allowable depreciation amount is different for motorized and non-motorized equipment, and for buildings.
The second calculation for depreciation is called the “management depreciation” method. It is based on the current market value of a depreciable asset. The farm manager makes a decision on how often they would like to replace the piece of equipment or building and the depreciation rate is calculated accordingly. Usually for equipment a rate of 8-12 % is used which indicates that the farmer would like to completely replace his equipment line every ten years or so.