Printer Friendly

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.
COPYRIGHT 2010 New York State Society of Certified Public Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2010 Gale, Cengage Learning. All rights reserved.

Article Details
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:

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters