Create a spreadsheet to address the given problem:
Problem 1. A hospital has an average accounts receivable balance of $20,000,000 and a collection period of 60 days.
Problem 2: 60% of receivables are not collected on time. The bills for those receivables must be reworked by the patient billing department and resubmitted to insurance companies for payment. Assume that the insurance companies did not pay the first submission because of data errors in the bills. The reworked bills adversely impact the average accounts receivable collection period.
Problem 3: The receivables that are not reworked (40% of the total) are all processed consistently without problems.
Problem 4: Fairview Health sells a system that will gradually reduce the number of data errors in the billing process. Assume that the system will have no impact in year 1 (during the implementation period), and that data errors are projected to be reduced by 25%, 50%, 70%, and 90% in years 2 through 5 respectively. The system will cost the hospital $2,000,000 in year 1 and $300,000 per year in years 2 through 5.
Problem 5: How would you project the financial benefits that the system could deliver to the hospital over 5 years and the return on investment for the system? If you need some additional information, identify what you need and then assume a value so that you can arrive at answers.