Introducing you cash flow forecast excel template. The main purpose of cash flow forecast excel is for providing and helping you in forecasting and managing the strategy for future cash flow. Aside for forecasting, it can be used for recording the actual cash flow or accountancy journals. The cash flow excel template is perfect for small to medium company or personal use.
THE MENU
There is only one menu in cash flow forecast excel, simply called Cash Flow. In this sheet, there is 5 elements:
- Information field: for recording the information which for supporting other elements. The fields are:
- Starting Date: the first day of cash flow period.
- Cash Balance Alert Minimum: to alert the field on other elements which amount is below or less than the amount in this field.
2. Cumulative Cash Flow field: display the cash flow amount in cumulative way from previous month. The cash flow projection is for 12 months or a year. So, there will be 13 columns including beginning amount column. There is two field: the cash on hand in beginning month and cash on hand in ending month.
3. Cash Receipt table: the table to recording the income amounts. There are 14 columns for beginning amount column, amount for 12 months, and total amount column for each row or income name. (in this picture, some columns are hidden to see the Total field).
4. Cash Paid Out table: the table to recording the expense amounts. Like Cash Receipt, it has 14 columns. The Cash Paid Out table consist of 2 types separated by subtotal and total rows:
- Company expenses: for recording company’s operational expenses.
- Other liabilities: for recording other cash out that not from company’s operational, owner personal expense, or company’s liabilities.
- Other Operating Data: the table to recording the gain or loss amount of asset. However, the gain or loss are not require cash out or cash in. For example: depreciation, account receivable balance, etc. The amount in this table will not summarized with other tables.
HOW TO USE
To use the projected cash flow statement format in excel, simply enter the provided fields.
On the header, enter the company name and recording date or period. You can enter the company contact details too.
Enter the Starting Date and Cash Balance Alert Minimum field. The Starting Date can be any date. However on the rest of the tables, the starting period will be shifted into the first day of the entered month.
On Cumulative Cash Flow field, you may notice a white field, while the rest are grey. It notify you that the grey fields are all auto-generated by the formula. Enter the Cash on hand amount on Beginning column.
Now, enter the field on all tables, on all rows and columns. Remember to only enter the white fields.
On Cumulative Cash Flow field, if the amount drop below the Cash Balance Alert Minimum amount, the text fields will turned red.
MODIFY
This template using waterfall forecasting in excel. If you want to change the forecasting method, you may need specific knowledge to edit.
The cash flow forecast excel is easy to modify. The formula used is simple summary or subtraction formula.
The beginning field is supposed for recording the amount before the Starting Period. However if you think it is unnecessary, you can delete it. Deleting the column will only affect the Cumulative Cash Flow field, which easy to fix. Simply replace Cash on hand (beginning of month) row in first month column with actual beginning amount. You can turn the 12 months into 3 year cash flow projection template simply by adding new columns.
If you want to change currency, go to Home tab. On dollar ($) icon, click it and select More Accounting Format. Select desired currency symbol on dialog box.
CONCLUSION
The cash flow forecast excel template is easy to use and modify. You only need to replace the provided fields. You can download cash flow forecast excel template for free after you click one of these buttons: