# Forecasting Accounts Receivable Collections with Markov Chains and Microsoft Excel

By Saibeni, August A. | The CPA Journal, April 2010 | Go to article overview

# Forecasting Accounts Receivable Collections with Markov Chains and Microsoft Excel

Saibeni, August A., The CPA Journal

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.

Example

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

If you are trying to select text to create highlights or citations, remember that you must now click or tap on the first word, and then click or tap on the last word.
One moment ...
Default project is now your active project.
Project items

Highlights (0)
Some of your highlights are legacy items.
Citations (0)
Some of your citations are legacy items.
Notes (0)
Bookmarks (0)

Project items include:
• Saved book/article
• Highlights
• Quotes/citations
• Notes
• Bookmarks
Notes

#### Cited article

Style
Citations are available only to our active members.

(Einhorn, 1992, p. 25)

(Einhorn 25)

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

#### Cited article

Forecasting Accounts Receivable Collections with Markov Chains and Microsoft Excel
Settings

#### Settings

Typeface
Text size Reset View mode
Search within

Look up

#### Look up a word

• Dictionary
• Thesaurus
Please submit a word or phrase above.

Why can't I print more than one page at a time?

Help
Full screen

### How to highlight and cite specific passages

1. Click or tap the first word you want to select.
2. Click or tap the last word you want to select, and you’ll see everything in between get selected.
3. You’ll then get a menu of options like creating a highlight or a citation from that passage of text.

## Cited passage

Style
Citations are available only to our active members.

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn, 1992, p. 25).

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn 25)

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences."1

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

## New feature

It is estimated that 1 in 10 people have dyslexia, and in an effort to make Questia easier to use for those people, we have added a new choice of font to the Reader. That font is called OpenDyslexic, and has been designed to help with some of the symptoms of dyslexia. For more information on this font, please visit OpenDyslexic.org.

To use OpenDyslexic, choose it from the Typeface list in Font settings.

## Thanks for trying Questia!

Please continue trying out our research tools, but please note, full functionality is available only to our active members.

Your work will be lost once you leave this Web page.