complete the following Excel document to determine what each patient owes the clinic and how many days has it been since they utilized the clinic. You will then also calculate the average number of days in accounts receivable. This information will be used to start a process improvement to reduce the number of days in accounts receivable which will have a positive financial impact on the clinic.
Ferris State Clinic
Aged A/R Schedule
5/31/2017
Days outstanding
Patient DOS Billed Paid Balance 0-30 31-60 61-90 91+ total number of days
1 Dave Smith 2/2 125 90
2 John Doe 5/5 1,850 1,350
3 Mary Jones 4/5 600 450
4 Bill Hamm 1/6 200 100
5 Steve Ward 4/3 700 660
6 Mallory Fox 2/5 170 –
7 Tim Flynn 3/15 4,600 3,500
8 Bobbie Watson 3/10 2,200 3,200
9 Tom Norman 1/15 28,000 25,600
10 Stephanie Jackson 1/12 5,250 4,500
11 Tyler Ames 12/15 10,000 8,000
12 Topher Easton 11/14 3,500 1,500
13 Amy Green 1/8 2,250 800
14 Kelly Trout 2/24 12,670 7,800
15 Sam Stout 4/23 4,560 2,200
16 Bonnie Warner 3/1 200 100
17 Jim Cooter 9/12 3,800 3,600
18 Nate Jefferson 12/30 25,500 10,000
19 Phil Wall 5/12 1,200 900
20 Sheila Tripp 4/9 780 250
Summary Average number of days
Total amount 108,155
Percentage xxxxxxxxx xxxxxxxxx 100%
*
