This assignment consists of a set of problems that are widely used in healthcare industry. You should use Excel formulas for all calculations as required and add the two tables of the analyses in one Excel spreadsheet. You should use Excel formulas if calculation is required. 1. Apple hospital has the following receivables amounts, listed by age (Use Excel formulas in Excel spreadsheet). - Complete the aging schedule by filling the total value of account and the percentage of total value column. - Interpret your results. - Using these data, estimate the hospital's ACP (Hint: Use the average days (e.g., 15 days for 0-30 days). 2. Banana hospital has annual patient service revenues of $48,253,000. It has two major thirdparty payers, and some of its patient are self-payers. The hospital's patient accounts manager estimates that 15% of the hospitals' billings are paid (received by the hospital) on day 30,55% are paid on day 60 , and 30% are paid on day 90 . - What is Banana hospital's average collection period (ACP)? - What would be the hospital's new receivables balance if a newly proposed electronic claims system resulted in collecting from third-party payers in 15,45 and 75 days, instead of 30,60 2. Banana hospital has annual patient service revenues of $48,253,000. It has two major thirdparty payers, and some of its patient are self-payers. The hospital's patient accounts manager estimates that 15% of the hospitals' billings are paid (received by the hospital) on day 30,55% are paid on day 60 , and 30% are paid on day 90 . - What is Banana hospital's average collection period (ACP)? - What would be the hospital's new receivables balance if a newly proposed electronic claims system resulted in collecting from third-party payers in 15, 45 and 75 days, instead of 30,60 and 90 days? 3. Cherry hospital is opening a new clinic in Cherry City on November 1st.. Based on its old clinic in Banana City, its finance department estimates; the ACE for Cherry hospital is 25 days. The hospital's daily billing is $800. They will be able to borrow money from Cherry Bank at 4.5% interest rate. - Estimate the amount of billing outstanding (or how much they need to borrow from the bank) before they start the business. - Given the interest rate, calculate the monthly cost of financing. Grape hospital has the following annual financial data and operation metrics (Use Excel formulas in Excel spreadsheet). - What is the hospital's profit per discharge? - What are the hospital's total revenues from Medicare patients? - What is the total number of inpatient days? - What is the hospital's occupancy rate? Search a dashboard that shows critical volume metrics in healthcare industry using a search engine on the web. Provide an image of the dashboard and explain why you choose it and why it is important in healthcare financing