Cash Flow Matrix

Cashflow Matrix

This application is a Microsoft Excel spreadsheet. It analyzes twenty-four months of cash flow from accounts receivable, based off percentages entered by the user. The user inputs the beginning date of the period to be analyzed, in the cell under “Ref Date.” The user then inputs the percentages of accounts receivable to be considered as cash.

The matrix allows up to seven entries, and they should total 100%. The user then enters the monthly accounts receivable in column C. The spreadsheet looks in each cell of column C for a non-zero amount. If an amount is found, the spreadsheet applies the percentages in the matrix, starting with the month after the non-zero amount, and carrying it out for the number of non-zero percentages entered in the matrix. This process is carried out, for each non-zero month of sales, for up to twenty-four months.

The sheet is designed to allow a user to forecast cash revenues from accounts receivable, for up to seven months after the sale. The sheet allows the forecast to run for a period of twenty-four months of sales, but will only forecast if a non-zero amount is entered for a given month. For example:

Reference Date: 12/15/1999

Percentages:

1

2

3

4

5

6

7

10%

40%

20%

15%

10%

5%

0%

A matrix is established of twenty-four months, with the first two columns being the year and month, defined by the reference date for the first entry, and the remaining twenty-three values are calculated by the sheet. The third column of the matrix is the accounts receivable for the month.

The spreadsheet looks at each possible column in the matrix, and applies the percentages based off accounts receivable greater than zero, and applying the non-zero percentages to the month of receivables plus one, for each non-zero percentage entered.

With the date entered in this example, the spreadsheet starts with December, 1999 as its first month, and then calculates the date for the remaining twenty-three. The time period covered would be December, 1999 through November, 2001.

The spreadsheet is simple and straightforward, and the user should have no difficulty with the data entry, and receives a reliable forecast.

download-buttom

Related spreadsheets:

This post was published on Administrative and under following tags , , , , ,

Leave a Reply