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.

The rest of this article is only available to active members of Questia

Sign up now for a free, 1-day trial and receive full access to:

  • Questia's entire collection
  • Automatic bibliography creation
  • More helpful research tools like notes, citations, and highlights
  • Ad-free environment

Already a member? Log in now.

Notes for this article

Add a new note
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 ...
Project items

Items saved from this article

This article has been saved
Highlights (0)
Some of your highlights are legacy items.

Highlights saved before July 30, 2012 will not be displayed on their respective source pages.

You can easily re-create the highlights by opening the book page or article, selecting the text, and clicking “Highlight.”

Citations (0)
Some of your citations are legacy items.

Any citation created before July 30, 2012 will labeled as a “Cited page.” New citations will be saved as cited passages, pages or articles.

We also added the ability to view new citations from your projects or the book or article where you created them.

Notes (0)
Bookmarks (0)

You have no saved items from this article

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

Cited article

Style
Citations are available only to our active members.
Sign up now to cite pages or passages in MLA, APA and Chicago citation styles.

(Einhorn, 1992, p. 25)

(Einhorn 25)

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.

Cited article

Forecasting Accounts Receivable Collections with Markov Chains and Microsoft Excel
Settings

Settings

Typeface
Text size Smaller Larger
Search within

Search within this article

Look up

Look up a word

  • Dictionary
  • Thesaurus
Please submit a word or phrase above.
Print this page

Print this page

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

Full screen

matching results for page

Cited passage

Style
Citations are available only to our active members.
Sign up now to cite pages or passages in MLA, APA and Chicago citation styles.

"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.

Cited passage

Welcome to the new Questia Reader

The Questia Reader has been updated to provide you with an even better online reading experience.  It is now 100% Responsive, which means you can read our books and articles on any sized device you wish.  All of your favorite tools like notes, highlights, and citations are still here, but the way you select text has been updated to be easier to use, especially on touchscreen devices.  Here's how:

1. Click or tap the first word you want to select.
2. Click or tap the last word you want to select.

OK, got it!

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.

For full access in an ad-free environment, sign up now for a FREE, 1-day trial.

Already a member? Log in now.