Monthly Cash Flow Template Excel Free Download
Bridgette Kubis
The Cash Flow Statement, or Statement of Cash Flows, summarizes a company's inflow and outflow of cash, meaning where a business's money came from (cash receipts) and where it went (cash paid). By "cash" we mean both physical currency and money in a checking account. The cash flow statement is a standard financial statement used along with the balance sheet and income statement. The statement usually breaks down the cash flow into three categories including Operating, Investing and Financing activities. A simplified and less formal statement might only show cash in and cash out along with the beginning and ending cash for each period.
To perform a cash flow analysis, you can compare the cash flow statement over multiple months or years. You can also use the cash flow analysis to prepare an estimate or plan for future cash flows (i.e. a cash flow budget). This is important because cash flow is about timing - making sure you have money on hand when you need it to pay expenses, buy inventory and other assets, and pay your employees.
A cash flow analysis is not the same as the business budget or profit and loss projection which are based on the Income Statement. However, for a small uncomplicated business operating mainly with cash instead of credit accounts, there may seem to be little difference.
This cash flow statement was designed for the small-business owner looking for an example of how to format a statement of cash flows. The categories can be customized to suit your company's needs. If you don't want to separate the "cash receipts from" and the "cash paid for" then you can just delete the rows containing those labels and reorder the cash flow item descriptions as needed.The spreadsheet contains two worksheets for year-to-year and month-to-month cash flow analysis or cash flow projections.
This worksheet is for people who don't like the word budget but still want to get a grip on their finances. Basic personal finance is mostly about managing cash flow which means tracking and planning how money is entering and leaving your real and virtual pockets. This worksheet can be used for tracking your spending as well as creating a budget.
Creating a Monthly Budget that is the same each month requires that you use averages for variable expenses (fuel, food, etc.) and periodic expenses (insurance, tuition, subscriptions, etc.). A yearly cash flow analysis like this one can help you figure out what those averages are.
A cash flow analysis or budget can be defined over any period that you want. The dates at the top of this worksheet don't affect any of the other numbers in the worksheet, so you could change these dates to whatever you want them to be.
If you wanted to add columns for weekly or biweekly cash flow reports, you could copy and insert copied columns, but you'd need to insert them BEFORE the current 12th column so that the Total and Average formulas remain correct (If you insert them immediately before the Total column, the Total and Average won't include your inserted columns). You'd also need to fix the formulas in the summary rows at the top of the worksheet (copy the formulas used for the 2nd column to the right).
What is NOT a spending account? Your Retirement Fund, Emergency Fund, College Fund, etc. Do NOT include the balance in any of the categories listed under "Allocations" in the Outflows section. Why? Because we are treating transfers to these types of savings as Outflows from cash accounts. If you included your Retirement Fund in the cash balance, then it wouldn't make sense to include "Retirement Fund" as an Outflow. Note again that this is meant to show how much cushion your spending account has.
Is a credit card account a spending account? If you are paying the balance off each month, then yes. You would monitor your credit card statement and allocate each transaction to specific expense categories. The transfer of money from your checking account to your credit card account would NOT show up in the cash flow statement in this case.
Prefer to do things yourself? This Excel template can help you track your monthly budget by income and expenses. Input your costs and income, and any difference is calculated automatically so you can avoid shortfalls or make plans for any projected surpluses. Compare projected costs with actual costs to hone your budgeting skills over time.
Looking for the easiest way to accomplish the following: In a calendar format (to capture the monthly cash flow), schedule monthly credits and debits on their due dates without having to input every individual credit and debit entry for the entire year, while being able to entire unscheduled expenses and incomes. Help?
While large, publicly-traded companies will certainly have their own set of internal models updated constantly on a daily (or weekly) basis, our post will focus on providing a basic overview of monthly cash flow models.
But at the same time, monthly cash flow forecast models are not meant to manage urgent liquidity requirements, as is the case for the thirteen-week cash flow model (TWCF) used in the restructuring of distressed companies.
As for cash disbursements, the expected monthly expenses are $90,000. However, in the months when taxes are due, cash expenses increase to $175,000. Note that even for small businesses, this sort of tax treatment is a simplification and is NOT meant to reflect reality by any means (i.e. different rules by jurisdiction, local/regional taxes, real estate taxes, etc.).
Regarding the cash outflows, the higher disbursements directly connected to higher revenue generation (i.e. variable costs) like inventory purchases, CapEx, and employee wages, which were 20% higher than anticipated.
Essentially, I have a table of transactions - with a field showing which year / month (yyyy-mm) the invoice will be paid. I need to show our Finance Director a cash flow report (very easy in Excel!) with a column for each month over the next 5 years.
This template enables users to automatically compile a complete cash flow statement by simply entering basic income statement and balance sheet information. The template includes a current and comparative financial period and detailed instructions on the calculation of the line items that are included on the cash flow statement. The template includes statements of cash flow that have been compiled based on both the direct and indirect methods.
The following sheets are included in this template:
Info - this sheet includes all the input cells that are used in compiling the cash flow statement. User input consists of an income statement and a balance sheet as well as some additional information that is required in order to produce a cash flow statement.
Direct - this sheet includes a complete cash flow statement based on the direct method that is automatically calculated from the information that is entered on the Info sheet. No user input is required on this sheet.
Indirect - this sheet includes a complete cash flow statement based on the indirect method that is automatically calculated from the information that is entered on the Info sheet. No user input is required on this sheet.
This section of the instructions provides more information on the calculations that are performed for each line item that is included in the cash flow statements. Note that both cash flow statements are calculated automatically and no user input is therefore required on either the Direct or Indirect sheets.
A detailed calculation of this amount is included below the cash flow statement on the Direct sheet and at the top of the cash flow statement on the Indirect sheet. The calculation starts with the profit before taxation and all non-cash income & expenses and items that are included in other line items on the cash flow statement are then added back.
Investment income and interest expenses are added back because these items are included separately on the cash flow statement. The depreciation and profit or loss from sale of property, plant & equipment amounts are non-cash items and are therefore also added back.
The net effect of all working capital balances are then deducted in the calculation because these items also form part of operating cash flow. Note that an increase in accounts receivable and inventory results of an outflow of cash and should therefore be deducted. An increase in trade payables, however, results in an inflow of cash (because less payments are made to trade creditors) and should therefore be added in the calculation. A decrease in trade payables is therefore an outflow of cash and should be deducted.
These amounts are all calculated by adding the appropriate income statement amounts to the opening balance of the appropriate provisions (liabilities) and deducting the closing balance of the appropriate provisions from this calculation. All three amounts are negative because all three items result in an outflow of cash.
Note that the calculated amount is a cash outflow and should be included on the cash flow statement as a negative amount and that financial leases do not result in an immediate outflow of cash and should therefore be excluded from this line item. Payments of financial leases should be included under the financing activities section of the cash flow statement.
This amount is included in this section of the cash flow statement because it does not form part of operating cash flows. Note that investment income is deducted in the cash generated from operations calculation because it is included in the profit before tax and it is included as a separate line item on the cash flow statement.
The calculation should be included on the cash flow statement as a negative amount because the payment of long term debt results in an outflow of cash. The finance lease acquisitions should be excluded because this amount is set off against the property, plant & equipment acquisitions and the long term debt raised is deducted because it is included in a separate line item on the cash flow statement.
760c119bf3