I need help with questions 4 ( Average Collection Periods (ACP) for each quarter of operation) and 5 (Aging Schedules for the end of each quarter). Please show calculation in excel. I need more than just the answers. I need to know how you arrived at the answers.
able[[9/1/14,UNDERSTANDING HEALTHCARE FINANCLAL MANAGEMENT,,],[,Chapter 15 - Revenue Cycle and Current Accounts Management,,],[,Mini-Case,,],[,]]
new prosthetic/orthotic supply division of the company. The division would sell primarily to rehab
patients, some of whom pay for the devices themselves and some of whom have the bill paid by a third
party insurer. Sales would be slow during the first few months, rise during the middle of the year,
and then level off by the end of the year. Sales estimates for the first year of operation are shown in
the table below (in thousands of dollars). Rich estimates that 30 percent of the division's customers
will pay in the month of sale, 50 percent will pay in the month following sale, and the remaining 20
percent will pay in the second month following the sale. Calculate the division's expected:
Accounts receivables (A/R) at the end of each month
Total sales for each quarter of operation
Average daily sales (ADS) for each quarter of operation
Average collection period (ACP) for each quarter of operation
Aging schedules for the end of each quarter
ANSWER
End of March
A/R
S0
Quarterly Report
Sales
ADS
ACP _(())
$0
$0.00
$0.00
$0
$0.00
$0
$0.00
0.0
End of June
End of December
A 9/1/14
B
?
D
E
F
G
H
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 15--Revenue Cycle and Current Accounts Management
Mini-Case
Rich Jacksdn,a recent MHA graduate, has been hired by Tarheel Rehabilitation Clinic to manage a new prosthetic/orthotic supply division of the company. The division would sell primarily to rehab patients,some of whom pay for the devices themselves and some of whom have the bill paid by a third party insurer. Sales would be slow during the first few months, rise during the middle of the year, and then level off by the end of the year. Sales estimates for the first year of operation are shown in the table below (in thousands of dollars). Rich estimates that 30 percent of the division's customers 3 will pay in the month of sale, 50 percent will pay in the month following sale, and the remaining 20 + percent will pay in the second month following the sale. Calculate the division's expected: 5 - Accounts receivables (A/R) at the end of each month 6 - Total sales for each quarter of operation - Average daily sales (ADS) for each quarter of operation 8 -Average collection period (ACP)for each quarter of operation - Aging schedules for the end of each quarter 0 ANSWER 2 Assumed collection pattern Collected Remaining + Month of sale 0% 0% 5 One month after sale 0% 0% Two months after sale 0% 0% 7 8 Monthly Report Quarterly Report 6 Month Days Sales A/R Sales ADS ACP C January 31 $100 $0 February 28 $200 $0 2 March 31 $300 $0 $0 $0.00 0.0 3 April 30 $400 $0 May 31 $500 $0 5 June 30 $600 $0 $0 $0.00 0.0 6 July 31 $675 $0 7 August 31 $725 $0 8 September 30 $750 $0 $0 $0.00 0.0 6 October 31 $750 $0 0 November 30 $750 $0 1 December 31 $750 $0 $0 $0.00 0.0 2 3 End of March End of June Age of account A/R % A/R % 5 0-30 days $0 %0'0 $0 0.0% 6 30-60 days $0 0.0% $0 0.0% 7 60-90 days $0 0.0% $0 0.0% 8 Total $o 0.0% $0 0.0% 9 0 End of September End of December 1 Age of account A/R % A/R % 2 0-30 days $0 0.0% $0 0.0% 3 30-60 days $0 %00 $0 %00 + 60-90 days $0 0.0% $0 0.0% 5 Total $0 0.0% $0 0.0% 6