12 month cash flow projection template excel free

12 month cash flow projection template excel free

12 month cash flow projection template excel free
12 month cash flow projection template excel free

12 month cash flow projection template excel free download

The attached worksheet is a template for projecting an organization's cash flow across the fiscal year, identifying in advance any potential cash shortfalls that may need to be addressed.

The instructions below explain, step-by-step, how to use the cash flow projection worksheet.

Note on cell protection:
"The formula (gray) cells in these worksheets are protected to prevent accidental modification that could impair the functionality of the worksheets. For an expert user wishing to override these protections in order to modify formulas or the structure of the template, the process is outlined below. Please be aware that such modifications may lead to errors or inaccuracies if not done appropriately and checked carefully. 

To remove cell protections: 
  • Click on the cell or cells that you wish to unlock
  • Under the “Data” menu, select “Data Validation” 
  • A dialog box will open—click “Clear All” at the bottom of that box (if you wish to unlock all of the cells on that worksheet, also check the box that says “Apply these changes to all other cells with the same settings”)"
Cash Flow Projection tab

INPUTS

1-Fiscal Year Begins (month): Select the first month of the organization's fiscal year from the drop-down menu. This will automatically populate the 12 months of the organization's fiscal year across the header of the projection table.

2- Actuals Through: When updating the worksheet with monthly actuals (see "Monitoring" section below), select the month through which actual results are in the spreadsheet from the drop-down menu.

3-Beginning Cash Balance: Enter the organization's cash balance as of the beginning of the fiscal year.

4-Current Year Budget: Enter the total budget for the fiscal year for each of the line items under "Cash Receipts," "Cash Disbursements," and "Capital and Financing." (Note: do not include non-cash items such as depreciation.) To add individual or additional line items under a category, click the "+" buttons in the left margin of the spreadsheet.

5-Prior Year Carryover: Enter any prior year's receivables or payables/accruals carried forward into the current year.

6-Spread Evenly?: For any item that will be disbursed or received in equal installments across the 12 months of the year, select "Yes" from the "Spread Evenly?" drop-down in Column F. This will automatically distribute the total budgeted amount for that item across the 12 months. If the amount of a line item that will be received or disbursed varies from month to month, select "No" or leave blank and manually enter the amount to be paid or disbursed in each month.
 
7-Spread: For each line item, enter the amount expected to be received or disbursed in cash in each month in the column corresponding to that month. (Note, if some or all of a budget item is not expected to be received or disbursed as cash during the current year, enter any cash expected to be received or disbursed in subsequent year(s) in column S ("Thereafter")).

OUTPUT
 
1-Net Cash Excess (Shortfall): Shows the cash-basis surplus or deficit for the individual month.

2-Rolling Cash Balance: Shows the cumulative cash position as of that month (i.e. the beginning cash balance for the month plus or minus that month's surplus or deficit). If this amount is negative or only narrowly positive, it indicates a potential cash shortage during the month. Steps should be taken to address this shortage in advance (e.g. accelerating receipts, delaying disbursements, securing a loan or line of credit).

MONITORING

1-Updates: In order to make this tool an effective basis for ongoing planning and decision-making, it is important to update the spreadsheet each month based on actual results. On a monthly basis, replace the originally projected receipts and disbursements for each line item with the actual amount received or disbursed for that item. 

For any differences between original projections and actual results that are due to a timing discrepancy ( a receipt/disbursement originally expected in March has been postponed until May), adjust the projected amount for the relevant future month to reflect the new assumption on timing of receipt or disbursement. 

For any differences between original projections and actual results that are due to a permanent change ( a receipt/disbursement originally expected in March will not happen at all), adjust the projected amount for the relevant month and the difference will appear in the "Projected Variance" column (X).

2-Actuals Through (cell D4): Once you input the actuals for a month, select the month through which actual results have been entered from the drop-down box in cell D4. The columns including the months with actuals will be shaded gray to represent the completed portion of the year.

3-Year-to-Date: This column will show the year-to-date total of cash receipts and cash disbursements for each line item, based on the month through which actual results have been updated. (Note: if no month is selected in the "Actuals Through" cell (D4), this column will report "FALSE.")

4-Remaining to Budget: This column will show the difference between the year-to-date actual results and the total budget for the year for that line item, representing the amount of cash remaining to be received or disbursed for the year.

5-Projected Variance: This column will show any projected variance for each line item, based on the difference between the original budgeted amount (in column E) and the total of actual and projected receipts or disbursements over the 12 months of the year.

Graphs tab

The Graphs tab provides a visual representation of the monthly cash balance and monthly cash receipts and disbursements. This populates automatically based on the inputs and results of the Cash Flow Projection tab.

Download also:

Post a Comment

Previous Post Next Post