# Forecasting accounts receivable collections with Markov chains and Microsoft Excel.

As the U.S. economy has struggled during the past few years, estimating the collectibility of accounts receivable is perhaps more important than ever for a business's success. An improvement in the accounting measurement of collections, along with an improvement in cash How forecasts and budget accuracy, can make the difference between a company's survival and its failure.Using matrix algebra and a methodology known as Markov chains may facilitate forecasting collections of accounts receivable or confirming estimates made from more traditional methods. Traditional methods of estimating collections generally involve a specific review of large accounts and estimates of collection percentages by aging categories of receivables for remaining accounts. Sometimes, however, it may be easier to determine how likely receivables will transition from one aging category to the next than it would be to estimate the likelihood of specific collections.

Using the Markov chain method, which develops probability judgments about transitions of accounts receivable from one aging category to the next, may be more useful for estimating collections than historical collection methods. Markov chain probabilities of collection by period may also provide better estimates of cash flow for budgeting purposes because they facilitate computing the expected value of collections. (Expected value is generally defined as equal to the probability of collection times the amount of the accounts receivable in an aging category.) FASB's Statement of Financial Accounting Concepts (SFAC) 7, Using Cash Flow Information and Present Value in Accounting, recommends using an expected cash flow approach in accounting measurements: "The expected cash flow approach [is] a more effective measurement tool than the traditional approach in many situations ... [it] allows the use of present value techniques when timing of cash flows is uncertain." The longer the maturity of the accounts and the higher the interest rates, the more likely it is that present value methods could significantly impact the fair value of accounts receivable. (The use of matrix methods to compute present value is beyond the scope of this article.) Another useful aspect of Markov chain methods is the ability to calculate the standard deviation of collections. (A complete matrix calculation of the standard deviation of collections is also beyond the scope of this article.)

[ILLUSTRATION OMITTED]

Using Markov Chains

Russian mathematician Andrei Andreyevich Markov (1856-1922) was responsible for developing the probability models known as Markov chains. Markov chains use matrix algebra methods to fore cast outcomes (states), given a starting point and probabilities that describe the chance of transitioning from one state to another state. Markov chains have also been used to forecast the weather, brand loyalty, the decay of bridges, and the diffusion of gases, to name a few examples.

While the use of Markov chains to estimate collections of accounts receivable is not new, the ability to use Microsoft Excel to perform the needed matrix algebra calculations is. As suggested by SFAC 7, the accounting profession seems to be moving toward the expanded use of expected value techniques alongside fair value. Markov chain methods facilitate the use of expected value calculations by developing collection probability estimates and calculating the present value of collection forecasts.

Using Markov chains to estimate collections of accounts receivable used to be difficult unless a user had access to a mainframe computer. Now, however, Microsoft Excel easily manipulates matrix and vector operations and allows for enough accounts receivable aging categories to satisfy most users. Prior to Excel, manual computations would have been tedious whenever the desired aging categories were more extensive than paid in full, current, past due, late, and uncollectible. Many industries need more aging categories to create realistic scenarios.

Example

In order to make the use of Markov chains easier to understand, consider the case of a hypothetical company. Exhibit I summarizes a company's billings and accounts receivable over the past several months. It shows the month the revenue was billed and traces the collection of the revenue from the month billed through subsequent periods. Using the $1,274,424 billed in April 2009, notice that $144,324 (11.3% of the billings) was collected or discounted during the billing month (month 0), prior to becoming a receivable, and that $553,481 of the month 0 receivable (49% of the remaining balance) was collected. In practice, accountants would most likely have more confidence using the average collection percentages rather than data from one month. For example, to estimate the average amount of accounts receivable transitioning to the next month and the collection percentages, add the accounts receivable in the current column and divide by the total gross charges. This computes the percentage of accounts receivable transitioning to the next month, which may also be thought of as the probability of transition. By subtracting this calculated percentage from 1.0, an estimate of an average collection rate of gross charges in the current period can be derived. This calculation results in l-($9,847,576/$11,496,309), or 14.3%, as shown in Exhibit 1. Similar computations are done for the remaining aging categories.

EXHIBIT 1 Example Company Accounts Receivable Collection Analysis Accounts Receivable Balance Current Month Billed Gross Charge (Month 0) Month 1 Month 2 Month 3 Apr 09 $1,274,424 $1,130,100 $576,619 $455,564 $359,467 May 09 $1,387,825 $1,132,382 $603,223 $442,187 $359,550 Jun 09 $1,024,396 $ 885,407 $356,613 $220,366 $157,132 Jul 09 $1,223,085 $1,091,158 $411,650 $298,091 $222,023 Aug 09 $1,243,650 $ 975,022 $589,493 $284,433 $223,915 Sep 09 $1,410,094 $1,218,965 $449,164 $306,446 $236,335 Oct 09 $1,522,647 $1,348,218 $594,380 $376,814 Nov 09 $1,048,894 $ 886,955 $348,167 Dec 09 $1,361,294 $1,179,369 Probability 14.3% 54.7% 33.4% 22.4% 11.8% of Collection Month Billed Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Apr 09 $284,467 $245,356 $198,974 $168,555 $149,424 0 May 09 $404,534 $275,147 $238,686 $217,250 Jun 09 $141,057 $113,609 $ 95,570 Jul 09 $149,225 $162,687 Aug 09 $186,709 Sep 09 Oct 09 Nov 09 Dec 09 Probability 18.6% 15.9% 11.8% 11.4% 0.0% of Collection Date Gross Charge Current (Month 0) Difference Collection % Apr 09 $ 1,274,424 $1,130,100 $ 144,324 11.3% May 09 $ 1,387,825 $1,132,382 $ 255,443 18.4% Jun 09 $ 1,024,396 $ 885,407 $ 138,989 13.6% Jul 09 $ 1,223,085 $1,091,158 $ 131,927 10.8% Aug 09 $ 1,243,650 $ 975,022 $ 268,628 21.6% Sep 09 $ 1,410,094 $1,218,965 $ 191,129 13.6% Oct 09 $ 1,522,647 $1,348,218 $ 174,429 11.5% Nov 09 $ 1,048,894 $ 886,955 $ 161,939 15.4% Dec 09 $ 1,361,294 $1,179,369 $ 181,925 13.4% Total $11,496,309 $9,847,576 $1,648,733 14.3% Date Current (Month 0) Month 1 Aging Difference Collection % Apr 09 $1,130,100 $ 576,619 $ 553,481 49.0% May 09 $1,132,382 $ 603,223 $ 529,159 46.7% Jun 09 $ 885,407 $ 356,613 $ 528,794 59.7% Jul 09 $1,091,158 $ 411,650 $ 679,508 62.3% Aug 09 $ 975,022 $ 589,493 $ 385,529 39.5% Sep 09 $1,218,965 $ 449,164 $ 769,801 63.2% Oct 09 $1,348,218 $ 594,380 $ 753,838 55.9% Nov 09 $ 886,955 $ 348,167 $ 538,788 60.7% Total $8,668,207 $3,929,309 $4,738,898 54.7%

After calculating these transition and collection probabilities, one can estimate the collections of accounts receivable at a given balance sheet date. The first step is to set up a matrix of transition probabilities. Exhibit 2 shows that the probability of a collected amount transitioning to a state of being collected in the subsequent month is 1.0. Using similar reasoning, the probability of an account that has already been written off transitioning to a state of being written off is 1.0. Both of these states are called absorbing states. The other states are called transient states, because they may either be entered or exited. Accounts receivable move through the transient states until the account is either collected or written off.

EXHIBIT 2 Matrix of Transition Probabilities Collected Month 0 Month 1 Month 2 Month 3 Month 4 Collected 1 0 0 0 0 0 Month 0 0.55 0 0.45 0 0 0 Month 1 0.33 0 0 0.67 0 0 Month 2 0.22 0 0 0 0.78 0 Month 3 0.12 0 0 0 0 0.88 Month 4 0.19 0 0 0 0 0 Month 5 0.16 0 0 0 0 0 Month 6 0.12 0 0 0 0 0 Month 7 0.11 0 0 0 0 0 Write-off 0 0 0 0 0 0 Month 5 Month 6 Month 7 Write-off Collected 0 0 0 0 Month 0 0 0 0 0 Month 1 0 0 0 0 Month 2 0 0 0 0 Month 3 0 0 0 0 Month 4 0.81 0 0 0 Month 5 0 0.84 0 0 Month 6 0 0 0.88 0 Month 7 0 0 0 0.89 Write-off 0 0 0 1 Note: Calculation of collection and transition percentages (estimates of probabilities) Current Month 1 Collected % % (month 0) A/R Collected Transitioning Totals $8,668,207 $3,929,309 $4,738,898 54.7% 45.3% April to November (Months 0-7) (Note: Percentages rounded above)

Before beginning matrix manipulations with Excel, one must rearrange Exhibit 2 so that the absorbing states "collected" and "write-off" are the first two rows and first two columns (see Exhibit 3). After laying out the rearranged row and column headings, transfer the probability values. The only exception to this convenience is the need to expand the identity matrix as described below. This enables the user to change the values in Exhibit 1 and have them flow through to the subsequent matrix and vector calculations. As stated above, the probability that revenue generated in the current month will be collected or discounted in the current month is .143 (14.3%). Stated another way, on average, 14.3% of the revenue will be collected or discounted prior to becoming a receivable. The next step is to partition the matrix into standard format of the form:

I O R Q

The identity matrix, I, is the equivalent of 1.0 in nonmatrix calculations. Identity matrices have ones on the diagonal proceeding from the upper left-hand corner and to the lower right-hand corner. The remaining entries are zeros. O is a zero matrix. All entries of O are zero. R is an s-by-r matrix and Q is an s-by-s matrix, where the first r states are absorbing and the last s states are nonabsorbing.

EXHIBIT 3 Rearranged Matrix Collected Write-off Month Month 1 Month 2 Month 3 0 Collected 1 0 0 0 0 0 Write-off 0 1 0 0 0 0 Month 0 0.55 0 0 0.45 0 0 Month 1 0.33 0 0 0 0.67 0 Month 2 0.22 0 0 0 0 0.78 Month 3 0.12 0 0 0 0 0 Month 4 0.19 0 0 0 0 0 Month 5 0.16 0 0 0 0 0 Month 6 0.12 0 0 0 0 0 Month 7 0.11 0.89 0 0 0 0 Month 4 Month 5 Month 6 Month 7 Collected 0 0 0 0 Write-off 0 0 0 0 Month 0 0 0 0 0 Month 1 0 0 0 0 Month 2 0 0 0 0 Month 3 0.88 0 0 0 Month 4 0 0.81 0 0 Month 5 0 0 0.84 0 Month 6 0 0 0 0.88 Month 7 0 0 0 0

Next, calculate the "fundamental matrix" [[1-Q].sup.-1]; that is, the inverse of the matrices [I-Q]. Both matrices I and Q are shown in Exhibit 4. In order to subtract Q from I, add another six rows and another six columns to I, because I and Q must have the same number of rows and columns. Thus the identity matrix I will have ones on eight diagonals and zeros elsewhere. Using Excel functions, subtract each element of matrix Q from the identity matrix I. After completing this operation, shown in Exhibit 5, select a block of eight rows and eight columns and copy them into a new matrix. Use the Excel function "Minverse" to turn this copy into an inverse of the matrix, that is, [[I-Q].Sup.-l]. This result--the fundamental matrix--is shown in Exhibit 6.

EXHIBIT 4 Matrices I and Q Identity Matrix I 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 I-Q 0 0 0 1 0 0 0 0 - 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 Matrix Q 0 0.45 0 0 0 0 0 0 0 0 0.67 0 0 0 0 0 0 0 0 0.78 0 0 0 0 I-Q 0 0 0 0 0.88 0 0 0 0 0 0 0 0 0.81 0 0 0 0 0 0 0 0 0.84 0 0 0 0 0 0 0 0 0.88 0 0 0 0 0 0 0 0 EXHIBIT 5 Matrix I-Q -0.45 0 0 0 0 0 0 0 1 -0.67 0 0 0 0 0 0 0 1 -0.78 0 0 0 0 0 0 0 1 -0.88 0 0 0 I-Q 0 0 0 0 1 -0.81 0 0 0 0 0 0 0 1 -0.84 0 0 0 0 0 0 0 1 -0.88 0 0 0 0 0 0 0 1 EXHIBIT 6 Fundamental Matrix [[I-Q].sup.-1] 1 0.45 0.30 0.23 0.21 0.17 0.14 0.12 0 1 0.67 0.52 0.46 0.37 0.31 0.27 0 0 1 0.78 0.68 0.56 0.47 0.41 Inverse 0 0 0 1 0.88 0.72 0.60 0.53 I-Q 0 0 0 0 1 0.81 0.68 0.60 0 0 0 0 0 1 0.84 0.74 0 0 0 0 0 0 1 0.88 0 0 0 0 0 0 0 1

The eight rows of the fundamental matrix represent the transient months or states, month 0 through month 7. A receivable in month 0, the month the revenue was billed, will spend an average of one month in month 0, 0.45 months in month 1, 0.30 in month 2, and so on, through month 7. Adding the rows of the fundamental matrix provides additional information. This can be done in two ways. One way is to use the Excel "auto sum" function. The other way is to multiply the fundamental matrix by a column vector consisting of eight 1.0s. To do this, copy the fundamental matrix, select a blank column of eight rows, and then follow the instructions for Excel's "Mmult" function in order to multiply the matrix by a column vector. The result, shown in Exhibit 7, shows the average number of months that a receivable will remain in a transient state prior to transitioning to another transient state before being absorbed. For example, a receivable in month 0 will either be collected or written off in 2.6 months, on average.

EXHIBIT 7 Fundamental Matrix Column Row Sums (see Vector below) 1.00 0.45 0.30 0.23 0.21 0.17 0.14 0.12 1 2.6 0 1.00 0.67 0.52 0.46 0.37 0.31 0.27 1 3.6 0 0 1.00 0.78 0.68 0.56 0.47 0.41 1 3.9 0 0 0 1.00 0.88 0.72 0.60 0.53 1 3.7 0 0 0 0 1.00 0.81 0.68 0.60 1 3.1 0 0 0 0 0 1.00 0.84 0.74 1 2.6 0 0 0 0 0 0 1.00 0.88 1 1.9 0 0 0 0 0 0 0 1.00 1 1 1.00 x 1 = 1.00 0.45 x 1 = 0.45 0.30 x 1 = 0.30 0.23 x 1 = 0.23 0.21 x 1 = 0.21 0.17 x 1 = 0.17 0.14 x 1 = 0.14 0.12 x 1 = 0.12 Total (rounded) = 2.6

In order to forecast future collections, the first step is to calculate the probability matrix P. The P matrix is calculated by multiplying the fundamental matrix by the R matrix described earlier. Exhibit 8 shows these calculations. Each row of Exhibit 8 represents the transient states, and each column represents the absorbing states. Notice that a receivable in month 0 has an 89% probability of collection and an 11% probability of write-off. By multiplying an aged accounts receivable as of a given date in the format of a row vector times the P matrix, a user can estimate how much of the aged accounts receivable will be collected and how much will be written off. Exhibit 9 shows that of the total accounts receivable of $2,522,313, the estimated collections will be $1,694,784 (67%), while the estimated write-offs will be $827,529 (33%).

EXHIBIT 8 Calculating the Probability Matrix [[I-Q] sup.-1] 1 0.45 0.30 0.23 0.21 0.17 0.14 0.12 0 1 0.67 0.52 0.46 0.37 0.31 0.27 0 0 1 0.78 0.68 0.56 0.47 0.41 0 0 0 1 0.88 0.72 0.60 0.53 x 0 0 0 0 1 0.81 0.68 0.60 0 0 0 0 0 1 0.84 0.74 0 0 0 0 0 0 1 0.88 0 0 0 0 0 0 0 1 Probability of R Collection Write-off 1 0.45 0.30 0.55 0 Month 0 0.89 0.11 0 1 0.67 0.33 0 Month 1 0.76 0.24 0 0 1 0.22 0 Month 2 0.63 0.37 0 0 0 0.12 0 Month 3 0.53 0.47 0 0 0 0.19 0 Month 4 0.47 0.53 0 0 0 0.16 0 Month 5 0.34 0.66 0 0 0 0.12 0 Month 6 0.22 0.78 0 0 0 0.11 0.89 Month 7 0.11 0.89

Notice that many of the complex matrix calculations can be performed with a single spreadsheet. Exhibit 10 uses the collection percentages from Exhibit 1 to forecast the future collection of the accounts receivable shown in Exhibit 9. By performing simple arithmetic operations, a user obtains the same collection estimates as shown in Exhibit 9. According to the balance sheet, the accounts receivable aging at month 0 shows $815,934. Multiply $815,934 by .5467 (shown as rounded to .55 in the exhibit) to estimate the amount of accounts receivable ($446,070) that will be collected from month 0's accounts receivable. Similar calculations can be performed for the remainder of the spreadsheet.

EXHIBIT 10 Accounts Receivable Collections Spreadsheet Method Probability of Collections by Aging Category A/R Aging A/R Balance 0.55 0.33 0.22 0.12 Month 0 $ 815,934 446,070 123,652 55,038 22,571 Month 1 615,263 205,694 91,555 37,547 Month 2 344,428 76,994 31,575 Month 3 214,456 25,320 Month 4 182,232 Month 5 150,000 Month 6 125,000 Month 7 $ 75,000 Total $2,522,313 $446,070 329,346 223,587 117,014 A/R Aging 0.19 0.16 0.12 0.11 Total Collected Month 0 31,418 21,825 13,668 11,542 $ 725,785 Month 1 52,263 36,305 22,737 19,200 $ 465,301 Month 2 43,951 30,531 19,120 16,146 $ 218,317 Month 3 35,244 24,483 15,333 12,948 $ 113,328 Month 4 33,958 23,589 14,773 12,475 $ 84,795 Month 5 23,864 14,945 12,620 $ 51,429 Month 6 14,810 12,507 $ 27,317 Month 7 8,513 $ 8,513 Total 196,835 160,597 115,386 105,950 $1,694,784 A/R Aging Percent Collected Month 0 89.0% Month 1 75.6% Month 2 63.4% Month 3 52.8% Month 4 46.5% Month 5 34.3% Month 6 21.9% Month 7 11.4% Total 67.2%

Using Markov Chains

With some practice, accountants can use the steps above to perform what-if analyses by simply changing the collection and transition probabilities. Markov chains could be useful in performing sensitivity analyses of budget assumptions or estimating monthly accounts receivable collections. This method may also be used to estimate the value of accounts receivable pledged as a loan guarantee or to estimate the value of accounts receivable acquired as part of a business acquisition.

Caveats

An exhaustive list of limitations and restrictions pertaining to the use of Markov chains is beyond the scope of this article. Nevertheless, one important limitation is that future transition probabilities must be the same or nearly the same as those used to develop the forecasts, otherwise the forecast will be wrong. Because using Markov chain analysis for accounts receivable collections depends upon past observations of collection percentages, the question arises as to how useful past collection percentages are if transition probabilities change, such as due to deteriorating economic conditions. Optimal use of Markov chains requires that transition probabilities should be stable; however, comparing older data to more recent data should give a user a sense of whether transition probabilities are changing.

Because Markov processes are time-series processes, one may be able to use time-series methods to improve the forecasting accuracy of collections. One such method is to use a moving average calculation of transition probabilities; another potential method is to update transition probabilities with geometric smoothing. Both of these techniques are beyond the scope of this article, although Excel supports both. Using Excel with Markov chains allows recent past transition probabilities to be modeled, and it also allows forecasters to change transition probabilities in order to simulate various scenarios of anticipated cash collections.

One final complication of using Markov chains to forecast collections of accounts receivable is partial payments. By using historical data, partial payments are included in the historical transition and collection probabilities. Even considering this and other limitations of Markov chains in making future events clear, the technique may make the probabilities of the steps to future events more readily discernible. The unpredictability of the future may actually make the Markov chain analyses demonstrated above even more useful.

August A. Saibeni, MS, CPA, is an adjunct professor at Cosumnes River College, Sacramento, Calif.

Printer friendly Cite/link Email Feedback | |

Title Annotation: | the cpa & the computer |
---|---|

Author: | Saibeni, August A. |

Publication: | The CPA Journal |

Geographic Code: | 1USA |

Date: | Apr 1, 2010 |

Words: | 3936 |

Previous Article: | The CPA Exam Content Specifications: missed opportunity to incorporate academic advice and guidance for candidates. |

Next Article: | Website of the month: Financial Accounting standards board. |

Topics: |