One 6 Page Answer
Mardy87
XYZHealthCareOrganizationsFinancialStatements.xlsxInstructions
| XYZ Health Care Organization's Financial Statements |
| How to use this document: This Excel spreadsheet document contains the following financial statements: XYZ's income statement on sheet 2, XYZ's balance sheet on sheet 3, and XYZ's statement of cash flow on sheet 4. This document also includes Avalon's strategic plan with opportunities for increasing net income (up-front costs) on sheet 5. Use the three financial statements (income statement, balance sheet, statement of cash flows), and the up-front costs of the strategic plan located inside this Case Study Excel document to create a visual element, such as a chart, table, graph, etc., to support the claims you make in the Avalon Health Care Organization Case Study paper. Visual elements are commonly used to convey information to financial and non-financial people, such as upwards or downwards trends, cash flow, liquidity, expenses, reserves, etc. For support with finding tools with which to create your visualization, you may wish to visit the Digital Toolbox linked in the assignment directions. You may use any of the tools listed in the Digital Toolbox to create your visualization or the graph tools in the INSERT tab here in Excel as long as you can paste it into your Avalon Health Care Organization Case Study paper and the total file size for your assignment submission to Waypoint does not exceed 25MB. |
XYZ's Income Statement
| XYZ Health Care Organization's Income Statement | |||
| 12/31/20X0 | 12/31/20X1 | 12/31/20X2 | |
| Total Revenue | $11,275,000 | $9,240,500 | $8,195,000 |
| Cost of medical services | $5,195,000 | $4,500,000 | $3,785,000 |
| Gross profit | $6,080,000 | $4,740,500 | $4,410,000 |
| Operating expenses | $3,192,000 | $3,185,000 | $2,898,000 |
| Income before tax | $2,888,000 | $1,555,500 | $1,512,000 |
| Income taxes | $577,600 | $311,100 | $302,400 |
| Net income or (Loss) | $2,310,400 | $1,244,400 | $1,209,600 |
XYZ's Balance Sheet
| XYZ Health Care Organization's Balance Sheet | |||
| Assets | 12/31/20X0 | 12/31/20X1 | 12/31/20X2 |
| Cash and cash equivalents | $450,000 | $336,000 | $264,000 |
| Accounts receivable | $3,472,000 | $3,760,000 | $1,459,000 |
| Inventory | $275,000 | $275,000 | $275,000 |
| Land | $1,200,000 | $1,250,000 | $1,300,000 |
| Building | $10,850,000 | $10,489,000 | $10,127,000 |
| Equipment | $4,500,000 | $3,600,000 | $2,700,000 |
| Total Assets | $20,747,000 | $19,710,000 | $16,125,000 |
| Liabilities | 12/31/20X0 | 12/31/20X1 | 12/31/20X2 |
| Accounts payable & accrued expenses | $8,750,000 | $10,432,000 | $12,890,000 |
| Accrued salaries | $987,400 | $1,112,000 | $1,340,000 |
| CMS Fine | $0 | $0 | $180,000 |
| Total Liabilities | $9,737,400 | $11,544,000 | $14,410,000 |
| Total Equity | $11,009,600 | $8,166,000 | $1,715,000 |
| Total Liabilities and Equity | $20,747,000 | $19,710,000 | $16,125,000 |
XYZ's Cash Flow
| XYZ Health Care Organization's Cash Flow Statement | |||
| Cash Received | 12/31/20X0 | 12/31/20X1 | 12/31/20X2 |
| Services/Sales | $9,166,000 | $7,134,500 | $6,320,910 |
| Collections | $102,500 | $98,000 | $88,000 |
| Investments | $95,500 | $75,989 | $85,890 |
| Payer Mix | $882,000 | $877,011 | $690,900 |
| Total Cash Received | $10,246,000 | $8,185,500 | $7,185,700 |
| Cash Paid Out | 12/31/20X0 | 12/31/20X1 | 12/31/20X2 |
| Staff salaries & benefits | $887,800 | $912,500 | $1,025,000 |
| Utilities | $536,000 | $525,000 | $524,000 |
| Medical supplies | $714,500 | $612,000 | $819,500 |
| Equipment depreciation | $65,800 | $75,200 | $75,200 |
| CMS fines | $0 | $0 | $180,000 |
| In-service training | $6,500 | $12,500 | $55,000 |
| Marketing | $950,000 | $835,000 | $0 |
| Technology Updates | $750,000 | $800,000 | $225,000 |
| Insurance | $412,000 | $414,000 | $514,500 |
| Total Cash Paid Out | $4,322,600 | $280,450 | $3,418,200 |
| Net Cash Flow | $5,923,400 | $7,905,050 | $3,767,500 |
Avalon's Strategic Plan
| Avalon HCO's Stategic Plan - Opportunites for Increasing Net Income (Up-front Costs) | |
| Instructions: To determine opportunities for increasing the net income for Avalon HCO review the following up-front costs. New and re-establishing services (cells A3 through B6), expanding locations (cells A8 through B9), new birthing centers (cells A11 through B12), hiring one gerontologist (cells A14 through B15), hiring three infection control nurses (cells A17 through B18), hiring other new positions to increase staff and patient support services (cells A20 through B26), and new marketing plan (cells A28 through B29). Lastly, use cells A31 through B31 to determine Avalon HCO total up-front costs. | |
| Offer New and Re-establish Services | Budget |
| Pain Clinic | $1,600,000 |
| Telehealth | $750,000 |
| Behavioral Health Care | $240,000 |
| Expanding Locations | |
| Budget for (10) Satellite Offices (lease, supplies/equipment, hiring, and malpractice insurance) | $10,000,000 |
| New Birthing Centers (each hospital location) | |
| Purchase/build new structures | $50,000,000 |
| Hire (1) Gerontologist | |
| Salary/benefits and malpractice insurance | $300,000 |
| Hire (3) Infection Control Nurses | |
| Salary/benefits and malpractice insurance | $450,000 |
| Hire New Positions (Increase staffing and patient support services) | |
| Budget for (10) Nurses | $1,050,000 |
| Budget for (4) Hospitalists | $1,500,000 |
| Budget for (3) Respiratory Therapists | $240,000 |
| Budget for (4) Case Managers | $600,000 |
| Budget for (4) Social Workers | $360,000 |
| Budget for (20) Other Support Positions | $2,500,000 |
| Marketing Plan | |
| Budget for New Marketing Plan | $1,500,000 |
| Total Up-front Costs | $71,090,000 |
