Use excel and specific formulas

A B C D m F G H Problem #1 15 Points Assume thatKeiser 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 a) Construct the group's base case projected P&L statement b) What is the group's contribution margin? c) What is the breakeven point ? 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 STATEMENTS AND CONTRIBUTION MARGINS FOR BOTH SCENARIOS +Problem #6 (5 Points) N 3 Assume you are the owner of your own HC clinic. You purchased a fleet of vans for $52,750. You expect to use it for 5.5 years and sell it at $3,500 (salvage value). 5 1) What value would appear on BOTH the balance sheet and income statement at the end of 4 years, and under what names? 6 2) What amount would appear on BOTH the balance sheet and income statement at the end of 1 year, and under what names? 7 8 9 1) What value would appear on BOTH the balance sheet and income statement at the end of 4 years, and under what names? 10 11 Amount Name 12 Balance Sheet 13 14 Income Sheet 15 16 2) What amount would appear on BOTH the balance sheet and income statement at the end of 1 year, and under what names? 17 18 Balance Sheet 19 20 Income Statement 21 22 23 YOU MUST SHOW HOW YOU DERIVED YOUR ANSWER (DO NOT JUST PUT IN A NUMBER) 24A B Problem #4 (10 Points) 2 Please complete the budgeting analysis by filling out the chart below - you must use excel for credit 3 Fringe benefits are 35.2% of Salaries 4 Equipment is budgeted at 2.5% less than supplies but came in at 4.2% less than budgeted 5 What is your observation of each line item? 6 Adopted vs Actual Budget (Fiscal Year Ended 12/31/2020) 7 LIU HEALTHCARE WALK-IN CLINIC 18 EXPENDITURES BY TYPE End of Year Percent Percent of Actual/Spent Adopted Budget in Change Total (December beginning of Year Net Change Budgeted Expenditures 2020) (January 2020) (Variance) vs. Actual Spent Analysis/Observation 10 PERSONNEL EXPENSES + 11 Salaries $1,855,250 $1,655,250 12 Fringe Benefits 13 Total Personnel Expenditures 14 OPERATING EXPENSES 15 Supplies 425,000 S 450,000 16 Equipment Subtotal Operating 17 Expenditures 18 TOTAL EXPENSES1 Problem #2 (5 Points) 2 3 Please fill in the highlighted cells (YOU MUST USE EXCEL FORMULAS TO RECEIVE CREDIT) 4 5 12/31/2019 Clinic A Clinic B Clinic C Clinic D 5 Assets $10,000.00 $42,000.00 $85,325.00 7 Liabilities $4,200.00 $12,500.00 8 Equity $22,000.00 $42,500.00 $65,000.00 9 10 12/31/2020 Clinic A Clinic B Clinic C Clinic D 11 Assets $22,500.00 $125,000.00 12 Liabilities $450.00 $4,200.00 $300.00 13 Equity $12,250.00 $65,325.00 $88,500.00 14 15 During 2020 Clinic A Clinic B Clinic C Clinic D 16 Total revenues $9,250.00 $39,225.00 17 Total expenses $5,300.00 $22,000.00 18 Net Income 19 20 21Problem #3 15 Points MPA Outpatient Surgery Center is developing an operating budget for the month ending April 2021. The Center expects to perform 80 surgical procedures during the month. the Surgery Center's average charge (price) per surgical procedure is $2,500. The cost of disposable surgical supplies is $300 per surgical procedure. MPA Outpatient Surgery Center also contracts with orthopedic surgeons at a fee of $1,500 per surgical procedure. The monthly salaries for the Center's receptionist, bookkeeper, and two surgical nurses total $10,500. The Center's occupancy costs, which include space rental, insurance, and all utilities, are $8,200 per month. Average monthly communication costs are $1,200. Office and operating room equipment was installed at a cost of $240,000. The equipment is expected to have a 5-year life and has no salvage value, so the monthly depreciation is $4,000 per month. Please prepare MPA Outpatient Surgery Center's Operating Budget for the month of April 2021 based on given budget Inputs (e.g. revenues Operating Budget - Month Ending April 30, 2021 Budget Inputs Number of Surgeries 80 Average Charge per Surgery 2,500 EA to Surgical Supply Disposal 300 Surgeon's Fees EA 1,500 + 1 Receptionist & Nurses Salaries $ 10,500 Occupancy Costs $ 8,200 3 Monthly Communications Costs 1,200 14 Office & Operating Equipment $ 240,000 16 Useful Life in years 5 17 Useful Life in Months 60 18 Monthly Depreciation 19