Forecasting Accounts Receivable Collections with Markov Chains and Microsoft Excel

Article excerpt

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 flow 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 [isj 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 die higher the interest rates, the more likely it is that present value methods could significantiy impact the fair value of accounts receiv(The use of matrix methods to compute present value is beyond the scope of article.) Another useful aspect of chain methods is the ability to calthe standard deviation of collections. complete matrix calculation of the standeviation of collections is also beyond scope of this article.)

Using Markov Chains

Russian mathematician Andrei Markov (1856-1922) was for developing the probability known as Markov chains. Matkov use matrix algebra methods to forecast 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.


In order to make the use of Markov chains easier to understand, consider the case of a hypothetical company. …