Mid-Year Monitoring

Cash Flow Monitoring WorksheetSomehow, we are already halfway through the year.  Periodic monitoring of projections that worked earlier this year may prevent surprises or problems later.  Now is a great time to use the cash flow monitoring worksheet to review how the monthly cash flow projection you completed compares to what has actually happened.  This tool is available for both of the monthly projection versions, with or without budgets.  Generating the cash flow monitoring worksheet is done in the cash flow plan that you wish to monitor.  Using this feature allows for timely monitoring of customer progress during the year.  And, if needed, adjustments to the cash flow plan can be made based on the actual data received.  

Generating the Monitoring Worksheet

Microsoft ExcelThe Microsoft Excel icon found on the toolbar in the cash flow plan is used to generate the monitoring worksheet as a spreadsheet.  When this icon is clicked, a pop-up dialogue box will appear asking for a few additional details.  Here the ending month for monitoring purposes is selected and details of where the generated Microsoft Excel spreadsheet should be saved are noted.  Once these details are entered, select “OK” to create the monitoring worksheet.  This will be saved in the previously designated location.

The monitoring worksheet can also be created using the Print feature.  Here the second tab is selected on the lower half of the dialogue box titled, “Monitoring Worksheet”.  This allows you to generate a worksheet with a quarterly format or one going through a certain month.  This option generates a worksheet copy, not an active Microsoft Excel spreadsheet.

Print Monitoring Worksheet

How to print the Monitoring Worksheet

Using the Monitoring Worksheet

Generating the Microsoft Excel monitoring worksheet will allow you to compare budget-to-actual details for the cash flow.  Here planned cash inflows and outflows for operating, financing, and investing activities within the designated timeframe of the plan are displayed.  You will need to gather actual year-to-date information to compare to the projections.  The actual column is used to enter the customer’s actual inflows and outflows to date for the year.  The spreadsheet will then calculate the dollar value of each item is ahead or behind for the year.  The amount this item is off, compared to the budget, is also shown on a percentage basis.

Sample Monitoring Worksheet

A sample of the Cash Flow Monitoring Worksheet

In Summary

Using the monitoring worksheet can be a powerful tool to assist in the monitoring activities of a monthly cash flow projection.  The ability to generate a Microsoft Excel budget to actual spreadsheet provides an easy, user-friendly way to monitor the progress of customers throughout the year.  Mid-season monitoring requires a bit of work now but may give you more time to work on solutions to potential problems and avoid surprises or larger problems later in the year.