Fundamentals of Health Care Finance
jacki814
MPA613Spring2020Midterm.xlsxDIRECTIONS PLEASE READ FIRST
| LIU |
| SCHOOL OF BUSINESS, PUBLIC ADMINISTRATION AND INFORMATION SCIENCES |
| MPA Program |
| HEALTHCARE FINANCE |
| Directions: |
| Please answer the ten problems as indicated on each numbered worksheet. |
| THIS IS AN INDIVIDUAL ASSIGNMENT. |
| Use your lecture notes, handouts and reading materials as your sources to solve the problems. . |
| You may look to outside sources if you feel they will enhance your answers, although it is NOT required. If you do, please cite references. |
| NEATNESS COUNTS. PLEASE ORGANIZE YOUR ANSWERS IN A CLEAR MANNER. |
| PLEASE...YOU MUST SHOW ALL YOUR WORK IN EXCEL FOR CREDIT |
| Good luck, |
| Prof. Levine |
Problem #1
| Problem #1 | 15 Points |
| Assume that LIU Healthcare has the following cost structure: | |
| Fixed Costs | $625,000 |
| Variable Cost per procedure | $42.50 |
| Average revenue (charge) per procedure | $130 |
| Assume the group expects to perform 3,500 procedures during the coming year | 3,500 |
| a) Construct LIU Healthcare's base case projected P&L statement | |
| b) What is LIU Healthcare's contribution margin? | |
| c) What is the breakeven point using CM? | |
| d) How many procedures must the practice do to make a $55,000 profit? | |
| e) Now assume that the practice contracts with one HMO, and the plan proposes a 30% discount from charges on 15% of its patients. | |
| Should the hospital accept the discount? YOU MUST SHOW PROFIT & LOSS STATESMENTS AND CONTRIBUTION MARGINS FOR BOTH SCENARIOS | |
Problem #2
| Problem #2 | 10 Points | |||||
| Please fill in the highlighted cells (YOU MUST USE EXCEL FORMULAS FOR CREDIT) | ||||||
| 12/31/16 | Clinic A | Clinic B | Clinic C | Clinic D | ||
| Assets | $ 12,500.00 | $ 42,000.00 | $ 85,325.00 | |||
| Liabilities | $ 5,000.00 | $ 12,500.00 | ||||
| Equity | $ 22,000.00 | $ 42,500.00 | $ 65,000.00 | |||
| 12/31/17 | Clinic A | Clinic B | Clinic C | Clinic D | ||
| Assets | $ 22,500.00 | $ 125,000.00 | ||||
| Liabilities | $ 950.00 | $ 4,200.00 | $ 300.00 | |||
| Equity | $ 12,500.00 | $ 65,325.00 | $ 88,500.00 | |||
| During 2017 | Clinic A | Clinic B | Clinic C | Clinic D | ||
| Total revenues | $ 13,500.00 | $ 32,000.00 | ||||
| Total expenses | $ 6,200.00 | $ 30,000.00 | ||||
| Net Income | ||||||
Problem #3
| Problem #3 | 10 Points | |
| Given the following financial data on Parkside Memorial Hospital, please perform a Dupont Analysis to determine the company's ROE. | ||
| Pleae also report your findings in a short paragraph to a group of community stakeholders who have never taken MPA 613…. | ||
| Net Income | $42,500 | |
| Total Revenue | $185,000 | |
| Total Assets | $8,500,000 | |
| Total Equity | $325,000 | |
3
| Problem #3 (10 Points) | ||||
| Consider MPA Medical Center's Financial Statements below | ||||
| 1) Please complete MPA Medical Center's Statement of Income and Retained Earnings, and Balance Sheet | ||||
| 2) What is the Center's approximate cash flow? | $ 174,048.00 | Dupont Analysis | ||
| 3) What is the Center's net working capital? | $ 163,842.00 | |||
| 4) What is the Center's profit margin? | 0.86 | |||
| 5) What is MPA Medical Center's debt ratio? | 1.77% | |||
| 6) What is MPA Medical Center's Return on Assets? | 2.31% | |||
| MPA Medical Center | ||||
| Statement of Income and Retained Earnings | ||||
| Year Ended December 31, 2016 | ||||
| Net patient service revenue | $ 3,163,258.00 | |||
| Other revenue | $ 123,750.00 | |||
| Total revenue | $ 3,287,008.00 | |||
| Expenses: | ||||
| Salaries and benefits | $ 1,515,438.00 | |||
| Medical supplies | $ 966,781.00 | Approximate Cash Flow | ||
| Insurance | $ 296,357.00 | $ 191,652.00 | ||
| Provision for bad debt | $ 110,000.00 | |||
| Depreciation | $ 85,000.00 | Net working capital | ||
| Interest | $ 206,780.00 | $ 163,842.00 | ||
| Total expenses | $ 3,180,356.00 | |||
| Operating income | $ 106,652.00 | Debt Ratio | ||
| Provision for income taxes | $ 31,167.00 | 0.86 | ||
| Net income | $ 75,485.00 | |||
| Profit Margin | ||||
| Retained earnings, beginning of year | $ 199,961.00 | 2.30% | ||
| Retained earnings, end of year | $ 275,446.00 | * | ||
| Return on Assets | ||||
| MPA Medical Center | 3.02% | |||
| Balance Sheet, December 31, 2016 | ||||
| Assets | ||||
| Current Assets: | ||||
| Cash | $ 105,737.00 | |||
| Marketable securities | $ 200,000.00 | |||
| Net patient accounts receivable | $ 215,600.00 | |||
| Supplies | $ 87,655.00 | |||
| Total current assets | $ 608,992.00 | |||
| Property and equipment | $ 2,250,000.00 | |||
| Less: accumulated depreciation | $ 356,000.00 | |||
| Net property and equipment | $ 1,894,000.00 | |||
| Total assets | $ 2,502,992.00 | |||
| Liabilities and Shareholders' Equity | ||||
| Current Liabilities | ||||
| Accounts payable | $ 72,250.00 | |||
| Accrued expenses | $ 192,900.00 | |||
| Notes payable | $ 100,000.00 | |||
| Current portion of long term debt | $ 80,000.00 | |||
| Total current liabilities | $ 445,150.00 | |||
| Long term debt | $ 1,700,000.00 | |||
| Total Liabilities | $ 2,145,150.00 | |||
| Shareholders' Equity: | ||||
| Common stock, $10 par value | $ 100,000.00 | |||
| Retained earnings | $ 275,446.00 | * | ||
| Total shareholders' equity | $ 375,446.00 | |||
| Total liabilities and shareholders' | ||||
| equity | $ 2,520,596.00 | |||
Problem #4
| Problem #4 | 10 Points | |||||
| Please complete the budgeting analysis by filling out the chart below - you must use excel for credit | ||||||
| Fringe benefits, (both budgeted and actual) are 45.2% of Salaries | ||||||
| Equipment is budgeted at 3% less than supplies but actual dollars spent were 4.2% less than budgeted | ||||||
| What is your observation of each line item? | ||||||
| Adopted vs Actual Budget (Fiscal Year Ended 12/31/2019) | ||||||
| LIU HEALTHCARE WALK-IN CLINIC | ||||||
| EXPENDITURES BY TYPE | ||||||
| End of Year (December 2019 Actual/Spent) | Adopted Budget in beginning of (January 2019) Year | Net Change (Variance) | Percent Change Budgeted vs. Actual | Percent of Total Expenditures Spent | Analysis/Observation | |
| Personnel Expenditures | ||||||
| Salaries | $1,855,250 | $1,655,250 | ||||
| Fringe Benefits | ||||||
| Total Personnel Expenditures | $1,855,250 | $1,655,250 | ||||
| Operating Expenditures | ||||||
| Supplies | $525,000 | $450,000 | ||||
| Equipment | ||||||
| Subtotal Operating Expenditures | ||||||
| Total Expenditures | ||||||
Problem #5
| Problem #5 | 5 Points | |
| Assume you are the owner of your own Healthcare clinic You purchased computer equipment for $32,500. | ||
| You expect to use it for 7 years and sell it at $4,500 (salvage value). | ||
| 1) What value would appear on BOTH the balance sheet and income statement at the end of 3 years, and under what names? | ||
| 2) What amount would appear on BOTH the balance sheet and income statement at the end of 1 year, and under what names? | ||
Problem #6
| Problem #6 | 10 Points |
| MPA Pediatrics currently provides 1,000 visits per year at a price of $50 per visit. | |
| The variable cost per visit (variable cost rate) is $30, and total fixed costs are $15,000. | |
| The business manager suggests that MPA Pediatrics can increase the number of visits to 1,200 per year by cutting the price per visit by 10% and increasing the fixed advertising budget by $5,000. | |
| a. Construct the base case P&L statement. Please show Contribution Margin (per unit) in your analysis. | |
| b. What is MPA Pediatrics break even? How many patients would have to visit MPA Pediatrics for them to have an economic profits of $6,500? | |
| c. What is the projected P&L statement incorporating the proposed change? Please show Contribution Margin (per unit) in your anlysis | |
| d. Based on the proposed chnges, how much would visits/volume need to increase in order for MPA Pediatrics to break even? | |
Problem #7
| Problem #7 | 5 Points | ||||
| Consider the data in the table below for three health services organization: | |||||
| Revenues | Total Variable Costs | Fixed Costs | Total Costs | Profit/Loss | |
| a) | $ 1,600.00 | $ 1,300.00 | $ 2,400.00 | ||
| b) | $ 1,150.00 | $ 1,255.00 | $ 2,300.00 | ||
| c) | $ 3,150.00 | $ 625.00 | $ (400.00) | ||
| Fill in the missing data using equations in excel | |||||
Problem #8
| Problem 8 | 15 Points |
| Assume you are in the 42.5 percent personal tax bracket. | |
| You are considering investing $50,000 in for profit Universal Health Services (UHS) bonds that carry an 11.25 percent interest rate. | |
| a) How much interest would you receive net of the tax on the investment? | |
| b) What is the after tax total amount of your investment? | |
| c) Now, consider that Not for Profit Kaiser Permanante, one of the leading California Healthcare systems, has issued tax-exempt bonds that have an interest rate of 7 percent to finance a new Women's and Children's Pavillion. | |
| With all else the same, should you buy $50,000 of the taxable UHS bonds at 11.25% interest, or 50,000 of Not for Profit Kaiser Permanante bonds? | |
| d) Based on your answer to C above, please find the point (interest rate) of indifference | |
Problem #9
| Problem #9 | 10 Points | ||||||
| Please develop TWO forecasts based on ABC Health Clinic's revenues from the previous years using: 1) a moving average two year forecast and 2) Percentage change forecast | |||||||
| Fiscal Year | Collections | Percentage Change | Fiscal Year | Collections | Moving Average | ||
| 2008 | $ 22,375.00 | 2008 | $ 22,375.00 | ||||
| 2009 | $ 22,550.00 | 2009 | $ 22,550.00 | ||||
| 2010 | $ 17,900.00 | 2010 | $ 17,900.00 | ||||
| 2011 | $ 25,225.00 | 2011 | $ 25,225.00 | ||||
| 2012 | $ 23,000.00 | 2012 | $ 23,000.00 | ||||
| 2013 | $ 24,875.00 | 2013 | $ 24,875.00 | ||||
| 2014 | $ 24,875.00 | 2014 | $ 24,875.00 | ||||
| 2015 | $ 32,500.00 | 2015 | $ 32,500.00 | ||||
| 2016 | $ 33,450.00 | 2016 | $ 33,450.00 | ||||
| 2017 | $ 32,000.00 | 2017 | $ 32,000.00 | ||||
| 2018 | $ 29,999.00 | 2018 | $ 29,999.00 | ||||
| 2019 | $ 32,500.00 | 2019 | $ 32,500.00 | ||||
| Future year (2020 | Future year (2020) | ||||||
| Future year (2021) | Future year (2021) | ||||||
2 Year Collections Forecast
Problem #10
| Problem #10 | 10 Points |
| MPA Healthcare Walk-in reported the following assets and liabilities | |
| 1) Please prepare MPA's balance sheet | |
| 2) What is MPA's net working capital? | |
| 3) What is MPA's debt ratio? | |
| Accounts Payable | 75,000 |
| Accrued Expenses | 195,000 |
| Less: Accumulated Depreciation | 500,000 |
| Cash | 125,000 |
| Short term Investment | 150,000 |
| Long term debt | 1,500,000 |
| Net patient accounts receivables | 225,600 |
| Notes Payable | 180,000 |
| Property and Equipment | 3,230,000 |
| Supplies | 87,655 |
