Cash planning template

It fits small business companies who manage their tight cash flow where their expenses rely upon As an investor, it is important to be able to evaluate the worth of current investments and future investment opportunities.

Traditional financial statements — with categories of assets, liabilities, and profits and losses — are essential sources of information for small business owners. Calculating the present or future value of an investment is easy, as long as that investment generates even cash flows over the period of investment. But uneven or irregular cash flows present a challenge. All businesses want to operate in the black.

Doing well financially, rather, takes planning and strategy Every business owner understands the importance of tracking business expenses, income, and net cash flow. But many may not realize the value of seeing those financial results in graphical form.

Our Cash A cash flow statement is a critical tool for analyzing the current liquidity of any business venture. Average rating 4. Vote count: No votes so far! Be the first to rate this template. Is there a version or any way to increase the number of income and expenses for similar days more than 6? Highly appreciate any help. Thank you for sharing this. I need to add several line in the income section from the Payment Planner sheet. When I add these new lines, they are not taken into account in the Weekly sheet.

I tried to backtrace the algorithm and to extend the function for the new lines, but the file is locked. Could you please help me? I use it at my company, but I need more lines to fill in. How can we increase the number of income and expenses? Can you provide a template with more lines? Click to see more Click to see more Click to see more Click to see more Click to see more. Version 7' Downloads 5. Those are : Income and expenses entry data area to put all of your company financial transactions.

Five weeks financial transaction view. Daily, weekly and monthly cash flow summary. How useful was this template? Click on a star to rate it! How we can improve this template? Weekly projections for depreciation and amortization need to be calculated independently of the template and included in this section.

We unfortunately cannot include default depreciation or amortization calculations because some businesses may have very different asset bases than others with existing assets which may already have been depreciated over a number of years.

Any calculation which is based on a percentage of the balance sheet asset value may therefore not be accurate. We also realize that some users may want to include depreciation and amortization as part of their operating expenses. We have therefore provided for this in that the depreciation and amortization calculations on the cash flow statement are based on the default code which is included in column A.

All interest paid calculations are automated and based on the amortization tables on the Loans1 to Loans3 and Leases sheets.

You therefore also need to specify a repayment day as part of the loan assumptions on the Assumptions sheet and the loan repayments are included in the weekly amortization tables based on the repayment day that is specified. Opening loan balances are based on the balance sheet opening balances section on the Assumptions sheet and additional loan amounts can be entered in the proceeds from loans section of the cash flow statement and will then automatically be included in the appropriate amortization table.

You do not need to use all four loan amortization sheets - if you only need to include loans based on one set of repayment terms, you can delete the other loan amortization sheets, delete the other interest paid rows on the income statement, delete the other proceeds from loans rows on the cash flow statement, delete the other repayment of loans rows on the cash flow statement and delete the other loan balances from the balance sheet.

The template provides for four sets of loan repayment terms - the same amortization table can basically be used for all loans with the same repayment terms by adding additional loan amounts as proceeds to the cash flow statement in order to add new loans to the appropriate amortization table. If you need to add more than four sets of loan repayment terms, you will need to copy one of the amortization sheets, change it to reflect the appropriate loan terms and then change the formulas in the amortization table to be based on the correct loan repayment terms at the top of the sheet.

This means that you need to add another set of repayment terms to the Assumptions sheet and link the fields at the top of the new amortization table to the appropriate cells on the Assumptions sheet. If there is an opening balance for the required additional loan terms, you need to include a new code in the balance sheet opening balances section on the Assumptions sheet and base the opening balance calculation in the first period of the amortization schedule on this code.

You also need to add new rows to the interest paid section on the income statement, the loan proceeds section on the cash flow statement, the loan repayment section on the cash flow statement and the loan balances section on the balance sheet. The appropriate formulas can be copied from one of the existing items and the sheet reference in the copied formula can then just be replaced by the sheet name of the new amortization table that you've added.

The taxation line item on the income statement is automatically calculated based on the profit before tax and the income tax assumptions which are specified on the Assumptions sheet. This will result in no income tax being calculated. If you do want to include income tax calculations, the appropriate income tax percentage needs to be entered in the Income Tax section on the Assumptions sheet.

You can also enter a value for an assessed loss as a positive value which may have been carried over from a previous tax year which would result in income tax only being calculated after profits exceed the value of the assessed loss. You also need to specify the payment frequency in months and the first calendar month in which a payment needs to be included.

The template automatically provides for income tax based on what is due and includes the income statement amount and a provision for taxation on the balance sheet. The payment frequency and month of payment assumptions are then used to determine when the income tax liability will be settled which will result in the appropriate cash outflow being recorded on the cash flow statement and the provision for taxation being reduced.

The template can accommodate income tax calculations based on current and subsequent month payments. If you select the Current option, the income tax payment amount will be calculated based on all amounts that have accrued up to and including the month of payment.

If you select the Subsequent option, the income tax payment amount will only be calculated based on all amounts which have accrued up to the previous month end. Example: If you select the Current option in the Income Tax section of the Assumptions sheet, all income tax amounts up to and including the current month will be included in the income tax payment amount.

This means that the provision for taxation at the end of the particular month will be nil. The Current setting is therefore usually appropriate for provisional taxpayers. Example: If you select the Subsequent option, all amounts up to and including the previous month end will be included in the income tax payment amount.

The provision for taxation balance on the balance sheet will therefore not be nil at the end of the month of payment and include the current month's income tax charge. Note: Income tax payments are included in the appropriate weekly period based on the payment day that is specified on the Assumptions sheet.

The template also includes automated dividends calculations. If you do not want to include any dividends in your cash flow projections, you can simply specify a dividend percentage of zero percent. If you want to include dividend calculations, you need to specify a dividend percentage which will be applied to the profit for the period in order to calculate the dividend value. You also need to specify the frequency in months of dividend payments and the first payment month.

The frequency of dividends determines when the dividends are included on the income statement and the first month of payment determines when the dividend payment is included on the cash flow statement only has an effect if the dividend payment option is Subsequent.

You can also specify whether the dividend is paid in the month of calculation Cash option , the month after calculation Next option or in a subsequent month. When you elect the subsequent month option, the payment of the dividend will be included based on the relative position of the first month of payment in relation to the year-end period which is determined based on the template start date at the top of the Assumptions sheet.

Example: If you want to include a dividend in the last month of each financial year, select a payment frequency of 12 months and month 12 as the first payment month. Then select the Cash option in order to include both the dividend on the income statement and the payment in the last month of the year. Example: If you want to include a dividend in the last month of each financial year but delay payment to the first month of the next financial year, select a payment frequency of 12 months and month 12 as the first payment month.

Then select the Next option in order to include the dividend on the income statement in the last month of the financial year and the payment in the first month of the next financial year. A dividend payable amount will then automatically be included on the balance sheet at year-end.

If you need to compile cash flow projections for an existing business, you will need to include the opening balance sheet balances at the start of the cash flow projection period. The opening balances that are entered here are included in the first column on the balance sheet. You can use the trial balance as at the end of the period immediately before the start of the cash flow projection period for this purpose.

The opening balances should also balance to a total of nil as with any accounting system trial balance. If you enter balances and the total of all balances is not nil, the entire opening balances section on the Assumptions sheet will be highlighted in orange. You then need to fix the imbalance by adjusting the opening balances so that the total comes to a total of nil. The orange highlighting will then be removed automatically. Also note that the cash flow projection balance sheet cannot balance if the opening balances do not balance.

Note: If you are preparing a cash flow projection for a new business, you can include zero balances for all the balance sheet items in the opening balances section. Intangible assets balances are calculated in much the same way by adding the purchases of intangible assets as per the cash flow statement and deducting the amortization charges which need to be entered on the income statement. The calculation of the investments balances on the balance sheet is a bit simpler in that only the purchases of new investments as per the cash flow statement is added to the previous month's balance and there is no depreciation or amortization on investments.

The inventory balances on the balance sheet are calculated based on the inventory days assumption which is specified on the Assumptions sheet. The number of days that are entered here is applied to the weekly cost of sales in order to calculate the appropriate inventory balance.

This calculation is based on the number of days in a week and the difference between the days in the assumption and full week days. Example: If you enter an inventory days assumption of 30 days, the entire cost of sales value for four weeks will be included in the inventory balance. After including the four weeks, there is a difference of 2 days between the 30 days assumption and the total days in four weeks.

The week 5 cost of sales balance will therefore be used, divided by 7 days and multiplied by the 2 remaining days. Note: The above calculation principle is applied regardless of the number of days which are entered as the inventory days assumption on the Assumptions sheet even if the value of the inventory days assumption requires the inclusion of multiple weeks. This method of calculation is the most accurate way of projecting inventory balances even for businesses where there is significant sales volatility.

Note: If your business does not carry inventory, you can simply enter a nil value in the inventory days assumption on the Assumptions sheet. The inventory line on the balance sheet will then also contain nil values. If you want to include variable weekly inventory days, you can do so by changing the inventory days assumption in the Workings section of the balance sheet which has been included below the section with the ratios. Simply replace the formula which links the inventory days assumption to the value on the Assumptions sheet by overwriting it with the appropriate inventory days value.

The trade receivables balances on the balance sheet are calculated based on the debtors days assumption which is specified on the Assumptions sheet. The debtors days number can be determined based on the average trading terms which has been negotiated with customers. The debtors days is applied to the weekly turnover in order to calculate the appropriate trade receivables balance. Example: If you enter a debtors days assumption of 30 days, the entire turnover value for four weeks will be included in the inventory balance.

The week 5 turnover balance will therefore be used, divided by 7 days and multiplied by the 2 remaining days. Note: The above calculation principle is applied regardless of the number of days which are entered as the debtors days assumption on the Assumptions sheet even if the value of the debtors days assumption requires the inclusion of multiple weeks. This method of calculation is the most accurate way of projecting trade receivable balances even for businesses where there is significant sales volatility.

Where sales tax is applicable, the appropriate sales tax value relating to weekly turnover will be added to the trade receivables balance. Sales tax codes are defined on the Assumptions sheet and the codes in column A next to the turnover amounts on the income statement are used to determine the appropriate rate of sales tax to be used. The trade receivables calculation will also only include lines that are coded with a sales tax rate code in the first two characters and a "C1" at the end of the code.

The C1 part of the code refers to credit sales while the inclusion of a C0 code at the end refers to cash sales. Cash sales do not need to be included in the trade receivables calculation and turnover lines with C0 or no code in column A are therefore ignored when calculating trade receivable balances. Example: If the standard rate sales tax code is V1 and the appropriate turnover line needs to be included in the calculation of trade receivables, the code V1C1 needs to be added in column A of the appropriate turnover line on the income statement.

Example: If you do not want a particular turnover line to be included in the trade receivables calculation, you can include any sales tax rate followed by C0 in order to exclude the line in the trade receivables calculations. For example, a turnover line with a code of V1C0 would not form part of the trade receivables calculations. Note: If your business has no trade receivables, you can simply enter a nil value in the debtors days assumption on the Assumptions sheet.

The trade receivables line on the balance sheet will then also contain nil values. If you want to include variable weekly debtors days, you can do so by changing the debtors days assumption in the Workings section of the balance sheet which has been included below the section with the ratios. Simply replace the formula which links the debtors days assumption to the value on the Assumptions sheet by overwriting it with the appropriate debtors days value.

If you therefore want to increase or decrease these balances, you need to add the amount of the increase or decrease to the line with a matching description on the cash flow statement under the changes in operating assets section.

If you therefore want to increase or decrease these balances, you need to add the amount of the increase or decrease to the line with a matching description on the cash flow statement. Note: The shareholders contribution line on the cash flow statement can be found under the cash flow from financing activities and the reserves line on the cash flow statement under the non-cash adjustments. The retained earnings balances on the balance sheet are linked to the retained earnings for the year which is calculated on the income statement.

Loans with the same repayment terms can be grouped together in the appropriate line item. There is no difference between the treatment of loans 1 to 3 and leases.

If you do not have finance leases and have loans with 4 different sets of repayment terms, you can use the Leases sheet and rename the appropriate line items accordingly. Note: The loan repayment period in years is limited to a maximum period of 30 years. If you want to include a loan repayment period which exceeds this period, you need to change the data validation settings in the appropriate input cell by selecting the data validation feature from the Data tab on the Excel ribbon and editing the maximum value of 30 which has been set in the loan repayment period cells.

Each of the loan repayment terms can be specified in the Loan Terms section on the Assumptions sheet. The loan terms include the annual interest rate, loan repayment period in years and a selection field which can be used to indicate interest-only loans.

These loan repayment terms are then included at the top of the appropriate loan amortization sheet on the Loans1 to Loans3 and Leases sheets. Note: A set of loan terms can be specified as interest-only by selecting the "Yes" option from the interest-only drop-down list in the appropriate loan terms on the Assumptions sheet.

If this selection is made, the loan will be interest only and not include any loan repayments. All the calculations on the amortization sheets are fully automated.

The loan terms are taken from the Assumptions sheet and the opening balances in the first row of the amortization table are based on the opening balances that are entered in the balance sheet opening balances section of the Assumptions sheet. The loan repayments, interest charged and capital repayments are calculated based on the outstanding balances at the beginning of each period. Additional loans can be added to the appropriate amortization table by entering the appropriate values in the proceeds from loans section on the cash flow statement under the cash flow from financing activities section.