Use excel and specific formulas

1 Problem #10 10 Points 2 MPA Healthcare Walk-in reported the following assets and liabilities 3 1) Please prepare MPA's balance sheet at December 31, 2020 4 2) What is MPA's net working capital? 5 3) What is MPA's debt ratio? 6 7 Accounts Payable $65,000 8 Accrued Expenses $195,000 9 Accumulated Depreciation $500,000 10 Cash $130,000 11 Short Term Investment $150,000 12 Long term debt $1,500,000 13 Net patient accounts receivables $225,600 14 Notes Payable $180,000 15 Property and Equipment $3,230,000 + 16 Supplies $87,655 17A Problem #7 (5 Points) 2 Consider the data in the table below for three health services organization: 3 Total Variable 4 Revenues Costs Fixed Costs Total Costs Profit/Loss 5 a ) $ 1,600.00 $1,200.00 $2,350.00 6 b) $1,150.00 $ 1,255.00 $ 2,100.00 7 c ) $ 3,150.00 $ 625.00 $ (400.00) 8 9 10 Fill in the missing data using equations in excel + 11A B C D E F G H K Problem #6 (15 Points) 2 MPA Healthcare 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. 4 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. 5 a. Construct the base case P&L statement. Please show Contribution Margin in your analysis. 6 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? 7 c. What is the projected P&L statement incorporating the proposed change? 8 d. How much would visits/volume need to increase in order for MPA Pediatrics to break even with the proposed changes? 9 10 11 12 13 14 15 16 17A B C G H K Problem #9 (10 Points) N 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 8 2009 $ 21,900.00 2009 9 2010 $ 16,500.00 2010 10 2011 $ 25,225.00 2011 11 2012 $ 23,200.00 2012 12 2013 $ 25,000.00 2013 13 2014 $ 24,300.00 2014 14 2015 $ 16,500.00 2015 15 2016 $ 33,450.00 2016 16 2017 $ 33,000.00 2017 17 2018 $ 34,000.00 2018 18 2019 $ 32,500.00 19 2020 $ 33,000.00 2019 20 Future year (2021) 2021 21 Future year (2022) Future year (2021) 22 Future year (2022) 23 24 25 261 Problem 8 (10 Points) 2 3 Assume you are in the 37.5 percent personal tax bracket. 4 You are considering investing $75,000 in for profit Universal Health Services (UHS) bonds that carry an 9.75 percent interest rate. 5 a) How much interest would you receive net of the tax on the investment? 6 b) What is the after tax total amount of your investment? c) Now, consider that Not for Profit Kaiser Permanente, one of the leading California Healthcare systems, has issued tax- 7 exempt bonds that have an interest rate of 6.5 percent to finance a new Women's and Children's Pavillion. 8 With all else the same, should you buy $75,000 of the taxable UHS bonds at 10.75% interest, or 75,000 of Kaiser Permanente bonds? 9 d) Based on your answer to C above, please find the point (interest rate) of indifference. (Please explain) 10 (PLEASE SHOW PROOF) 11 12 13 14