# Analyze the Attachment and answer these questions

June 4, 2016

Question

3. Examine the aging schedules as of the end of March and the end of June. What do these schedules indicate about customers’ payment patterns? Explain.

4. Analyze the uncollected balances schedules as of the end of March and the end of June. Do these schedules properly measure customers’ payment patterns? If you were to monitor one number from the uncollected balances schedule, which would you choose? Explain your answers.

5. Examine the quarterly carrying cost of receivables associated with each customer. (Assume that quarterly ending balances are equivalent to average balances for the quarter.)

a. For a given level of receivables, what factors influence the carrying costs of receivables?

b. Which customer contributes most to receivablesâ€™ carrying costs? Why does it contribute the most?
CASE27

INPUT DATA:

KEY OUTPUT:

Sales Forecast:
Month
January
February
March
April
May
June

Accounts Receivables Balance:
Gross Sales
\$100,000
250,000
400,000
600,000
450,000
300,000

End of Mar
\$353,550

End of Jun
\$336,300

Assumed Collection Pattern:
Payer
Large Retail Chain 1
Large Retail Chain 2
Regional Drug Store
Small Grocery Chain

0-30 days
35%
25%
20%
30%

Other Inputs:
Periodic (quarterly) interest rate
Contribution margin

30-60 days
50%
40%
35%
55%

28.8

80.8%
19.2%
0.0%

63.7%
36.3%
0.0%

Uncollected Balances Schedule:
January remaining rec / sales
February remaining rec / sales
March remaining rec / sales
Quarter remaining rec / sales

% of Sales
40%
35%
17%
8%

42.4

Aging Schedules:
0 – 30 days
30 – 60 days
Over 60 days

0.0%
27.1%
71.5%
47.1%

0.0%
27.1%
71.5%
24.9%

Quarterly Carrying Costs of Receivables:

Sales Mix Forecast:
Payer
Large Retail Chain 1
Large Retail Chain 2
Regional Drug Store
Small Grocery Chain

Average Collection Period (Days):

\$7,071

\$6,726

60-90 days
15%
35%
45%
15%

2.5%
20.0%

MODEL-GENERATED DATA:
Average Collection Period:

Payer
Large Retail Chain 1
Large Retail Chain 2
Regional Drug Store
Small Grocery Chain

Remaining Uncollected Balance at Period End
30 days
65%
75%
80%
70%

60 days
15%
35%
45%
15%

End of March:
Accounts receivables balance
Average daily sales
Average collection period (days)

90 days
0%
0%
0%
0%

\$353,550
\$8,333
42.4

Page 1

End of June:
\$336,300 2a.
\$11,667 2b.
28.8 2c.

CASE27
Aging Schedules:
End of March:

End of June:
Age of Accounts in Days

Payer
Large Retail Chain 1
Accts Rec
%

0-30

30-60

60-90

Total

\$104,000
87.4%

\$15,000
12.6%

\$0
0.0%

Large Retail Chain 2
Accts Rec
%

\$105,000
77.4%

\$30,625
22.6%

Regional Drug Store
Accts Rec
%

\$54,400
74.0%

Small Grocery Chain
Accts Rec
%
Total End of March
Accts Rec
%

\$119,000
100.0%

\$78,000
74.3%

\$27,000
25.7%

\$0
0.0%

\$135,625
100.0%

Large Retail Chain 2
Accts Rec
%

\$78,750
58.8%

\$55,125
41.2%

\$19,125
26.0%

\$0
0.0%

\$73,525
100.0%

Regional Drug Store
Accts Rec
%

\$40,800
54.2%

\$34,425
45.8%

\$22,400
88.2%

\$3,000
11.8%

\$0
0.0%

\$25,400
100.0%

Small Grocery Chain
Accts Rec
%

\$16,800
75.7%

\$5,400
24.3%

\$285,800
80.8%

\$67,750
19.2%

\$0
0.0%

\$353,550
100.0%

\$214,350
63.7%

\$121,950
36.3%

Sales

Accts Rec
for month

Remaining
Rec/Sales

\$40,000
\$100,000
\$160,000
Total

\$0
\$15,000
\$104,000
\$119,000

0.0%
15.0%
65.0%
39.7%

\$35,000
\$87,500
\$140,000
Total

\$0
\$30,625
\$105,000
\$135,625

0.0%
35.0%
75.0%
51.7%

\$17,000
\$42,500
\$68,000
Total

\$0
\$19,125
\$54,400
\$73,525

0.0%
45.0%
80.0%
57.7%

\$8,000
\$20,000
\$32,000
Total

\$0
\$3,000
\$22,400
\$25,400

0.0%
15.0%
70.0%
42.3%

\$100,000
\$250,000
\$400,000
Total

\$0
\$67,750
\$285,800
\$353,550

0.0%
27.1%
71.5%
47.1%

Total End of June
Accts Rec
%

0-30

Age of Accounts in Days
30-60

Payer
Large Retail Chain 1
Accts Rec
%

Uncollected Balances Schedules:
End of March:
Payer
Month
Large Retail Chain 1
January
February
March
Quarter
Large Retail Chain 2
January
February
March
Quarter
Regional Drug Store
January
February
March
Quarter
Small Grocery Chain
January
February
March
Quarter
Total End of March
January
February
March
Quarter

End of June:
Payer
Month
Large Retail Chain 1
April
May
June
Quarter
Large Retail Chain 2
April
May
June
Quarter
Regional Drug Store
April
May
June
Quarter
Small Grocery Chain
April
May
June
Quarter
Total End of June
April
May
June
Quarter

Quarterly Carrying Costs of Receivables:
End of March:

End of June:

Large Retail Chain 1
Large Retail Chain 2
Regional Drug Store
Small Grocery Chain
Total

\$2,380
\$2,713
\$1,471
\$508
\$7,071

Page 2

Large Retail Chain 1
Large Retail Chain 2
Regional Drug Store
Small Grocery Chain
Total

Sales
\$240,000
\$180,000
\$120,000
Total
\$210,000
\$157,500
\$105,000
Total
\$102,000
\$76,500
\$51,000
Total
\$48,000
\$36,000
\$24,000
Total
\$600,000
\$450,000
\$300,000
Total

CASE27

Page 3

CASE27

Age of Accounts in Days
60-90

Total

\$0
0.0%

\$105,000
100.0%

\$0
0.0%

\$133,875
100.0%

\$0
0.0%

\$75,225
100.0%

\$0
0.0%

\$22,200
100.0%

\$0
0.0%

\$336,300
100.0%

Accts Rec
for month

Remaining
Rec/Sales

\$0
\$27,000
\$78,000
\$105,000

0.0%
15.0%
65.0%
19.4%

\$0
\$55,125
\$78,750
\$133,875

0.0%
35.0%
75.0%
28.3%

\$0
\$34,425
\$40,800
\$75,225

0.0%
45.0%
80.0%
32.8%

\$0
\$5,400
\$16,800
\$22,200

0.0%
15.0%
70.0%
20.6%

\$0
\$121,950
\$214,350
\$336,300

0.0%
27.1%
71.5%
24.9%

\$2,100
\$2,678
\$1,505
\$444
\$6,726