Based on the information and financial statements below, calculate the following financial ratios in an Excel spreadsheet:
• Current Ratio = Current Assets / Current Liabilities • Days of Cash on Hand = Cash / (Operating expenses / 365 days) • Age of Accounts Receivable = Accounts Receivable, Net / (Net Patient Service Revenue / 365) • Age of Physical Plant = Accumulated Depreciation / Depreciation Expense • Debt to Equity Ratio = [Long Term] Debt / Net Assets • Debt to Assets Ratio = [Long Term] Debt / Assets • Collection Rate = Net Patient Service Revenue / Gross Patient Service Revenue • Operating Margin Ratio = Gain or (Loss) from Operations / Net Patient Service Revenue Note: For those Assignments in this course that require you to perform calculations you must: • Create an Excel spreadsheet containing the information provided. (For week 3 an Excel spreadsheet is provided) • Show all your calculations in the spreadsheet with Excel formulas. A title and reference page are NOT needed in this assignment. Put your name and assignment at the top of the Excel spreadsheet. For those not comfortable with the use of Microsoft Excel, this week’s Optional Resources suggest several tutorials. To prepare: • Review the information in this week’s Learning Resources (including the Media) dealing with ratios and how they are calculated. • Examine the information in the scenario below and consider how it will be used to calculate the ratios assigned. This Assignment is due by Day 7 of this week. The ABC General Hospital Statement of Revenues and Expenses June 30, 20X2 Assets Current Assets Cash $1,985,000 Accounts Receivable, Less Allowance for Doubtful Accounts 3,720,000 Inventory 550,000 Total Current Assets $6,255,000 Fixed Assets Plant and Equipment $10,000,000 Less Accumulated Depreciation -3,770,000 Total Fixed Assets $6,230,000 Total Assets $12,485,000 Liabilities and Net Assets Current Liabilities Accounts Payable $1,900,000 Accrued Accounts Payable 850,000 Total Current Liabilities $2,750,000 Long Term Debt 2,800,000 Pension Liability 1,250,000 Total Liabilities 6,800,000 Net Assets Balance at June 30, 20X1 4,775,000 Gain or (Loss) from Operations 910,000 Balance at June 30, 20X2 $5,685,000 Total Liabilities and Net Assets $12,485,000 Gross Patient Service Revenue Inpatient Revenue $8,250,000 Outpatient Revenue 2,275,000 Total $10,525,000 Less: Discounts -1,455,250 Total Patient Service Revenue $9,069,750 Operating Expenses Salaries & Benefits $5,110,000 Supplies and Services 2,829,750 Interest 100,000 Depreciation 120,000 Total Operating Expense $8,159,750 Gain or (Loss) from Operations $910,000 |
 |
