The dataset provided with this chapter includes data for the allowance for doubtful account receivable accounts of six members of the Dow 30 from 2016 to 2018. For each company and year, the data starts with the beginning balance in the allowance for doubtful accounts, adds bad debt expense, subtracts write-offs, and then adjusts for amounts charged to other accounts.

William R. Pasewark and Mark E. Riley (Journal of Accountancy, September 2009, pages 40-44) discuss three techniques for analyzing estimates of uncollectible receivables. Pasewark and Riley note that all of the benchmarks (referenced below) are based on their opinions and might differ based on specific circumstances. You will use these techniques to assess estimates made by Chevron, Cisco, Coca-Cola, Dow, Microsoft, and Verizon during the period 2016 to 2018.

1. The first technique compares cumulative bad debt expense recorded over a multi-year period to write-offs over that same period. Pasewark and Riley state that 0 is a general benchmark for this ratio. Compute this ratio for the 2016-2018 period for each company. Plot the ratios using a histogram. Comment on any differences you note among the companies ratios and on what they might reflect about the companies estimation processes. Why might a company record negative bad debt expense (i.e. a credit to expense)?

2. The second technique compares each years beginning allowance for doubtful accounts balance to write-offs for the same years. Pasewark and Riley note that a figure between 1.0 and 0 is a benchmark for a typical year. Compute this ratio for each company and year. In other words, you will compute three ratios for each company. Again, use a histogram to represent the data and comment on any differences you note among the companies ratios. Are there patterns emerging that are consistent between the two techniques employed thus far? What do the patterns indicate about the companies tendencies in estimating their allowances for doubtful accounts?

3. The third technique employs the allowance exhaustion rate. Pasewark and Riley suggest a company should typically exhaust its beginning allowance within one to two years. You will slightly modify the method recommended by Pasewark and Riley. Using 2017 as year t, adopt the same method Pasewark and Riley recommend if the entire 2017 beginning allowance is used in the form of write-offs by the end of 2017 or 2018.

• If the entire 2017 beginning balance is used in the form of write-offs by the end of 2017, compute the exhaustion rate by dividing the beginning allowance by total 2017 write-offs.
  • For example, if a companys beginning 2017 allowance balance was 100 and write-offs during 2017 totaled 150, the exhaustion rate would equal 100 / 150 = 0.67. This calculation would indicate that it would take two-thirds of a year to use the allowance in the form of write-offs.
• If the entire 2017 beginning balance is not used in the form of write offs by the end of 2017, but is used by the end of 2018, compute the unused portion of the allowance at the end of 2017 and divide that unused amount by 2018 write-offs. Add one to the result.
  • For example, assume a company had a beginning allowance for doubtful accounts of 100 in 2017, with write-offs of 75 in 2017 and 50 in 2018. At the end of 2017, 25 (100 beginning balance 75 write-offs) of the companys beginning allowance had not yet been utilized. Therefore, you would take the remaining allowance of 25 and divide that amount by the 50 written off in 2018, to obtain a result of 0.50. Adding that to 1, provides a result of 1.50. In other words, it took one-and-a-half years for the company to utilize its beginning 2017 allowance in the form of write-offs.
• If a company does not utilize its beginning 2017 allowance in the form of write-offs by the end of 2018, you will modify Pasewark and Rileys method. You will assume the write-off pattern for 2017 and 2018 is indicative the pattern for future years. You will, therefore, divide the beginning 2017 allowance for doubtful accounts by average write-offs for 2017 and 2018 to compute a projected allowance exhaustion rate.
  • For example, if a companys beginning 2017 allowance balance totaled 100 and its write-offs for 2017 and 2018 were 22 and 18, respectively, you would perform the following calculation of its exhaustion rate: 100 / average(22,18) = 5. In other words, based on average write-offs of 20 per year, this companys projected allowance exhaustion rate for its 2017 beginning allowance for doubtful accounts is 5 years. This is, of course, an imperfect method, as it is a projection based on only two years of write-off data. There might be other methods that are preferable.

HINT: In order to efficiently calculate the exhaustion rates for all six companies, using a formula that you can copy and paste from company to company, you should use nested if statements that consider all three possible scenarios discussed above.

REQUIREMENT: As with questions 1 and 2, use histograms to graphically represent the exhaustion rates you computed for each company.

Are the exhaustion rates for any of the companies surprising? What incentives might drive some of the more extreme rates?

4. Compare the ratios you computed above for each company. Do you notice trends in specific companies estimation patterns? Do some companies seem to be closer to Pasewark and Rileys benchmarks than others? Could a company have legitimate reasons for falling well outside these benchmarks? Based on the data you have encountered and evaluated, which companys estimation process would you deem least accurate? Why? Which companys estimation process would you deem most accurate? Why?

5. ChevronChevronChevronCiscoCiscoCiscoCoca-ColaCoca-ColaCoca-ColaDowDowDowMicrosoftMicrosoftMicrosoftVerizonVerizonVerizon

   Year201820172016201820172016201820172016201820172016201820172016201820172016

   Beginning Balance 606 487 429 211 249 302 477 466 352 127 110 94 361 409 289 1,199 1,146 1,037

   Additions (Bad Debt Expense) 379 128 76 (45) 27 (26) 29 32 126 114 43 31 134 58 175 776 1,167 1,420

   Deductions (Write-offs) (5) (9) (18) (37) (61) (28) (4) (10) (10) (54) (29) (15) (98) (106) (55) (1,261) (1,319) (1,461)

   Charged to other accounts - - - - (4) 1 (13) (11) (2) 4 3 - - - - 216 205 150

   Ending Balance 980 606 487 129 211 249 489 477 466 191 127 110 397 361 409 930 1,199 1,146