please explain briefly with the excel formulas.

4.(15+5 pts) VVH wants to determine the optimal case mix for diagnosis-related groups/DRGs) that will maximize profits. Limited resources (eg, space, qualified employees) are available to serve patients classified in the various DRGs, and minimum levels of service (number of cases) must be achieved for each DRG DRGS Resources Respiratory Coronary Alcohol Drug Birth Delivery Surgery Availability Abuse Diagnostic services 7 10 2 1 325 (hours) ICU bed days 1 25 05 SS Routine bed 3 7 days 2 7 420 Nursing care 50 88 27 SO (hours) 3.800 Margin $400 $2,500 $300 SSO Minimum 15 10 20 10 0 (D) (Bonus 5 pts) Formulate linear optimization problem. Is non-negativity constraint required in this problem? (2) Use Excel's solver to find optimal cases for each DRG with Question 4.xlsx file. Use name manager function to define blocks contain constraints, decision variables and coefficients. (3) Answer below questions. Hint: use Excel Solver's sensitivity report. (a) How the total profit would be if VVH adhd one ICU bed day? (b) How the total profit would be if VVH decides to take one more Birth Delivery case? (c) Currently, VVH carns $2,500 margin from Coronary Surgery. What is the maximum discount amount VVH can offer in Coronary Surgery? (4) Suppose VHH can make flexibilities in availability of Routine bed and Nursing care. VHH can set up range of [360, 480) in the availability of Routine bed and range of [3,300, 4,300 in the availability of Nursing care. Which DRG(s) could be affected by these two flexibilities? Hint use Solver Table. Respiratory Alcohol Drug Abuse Resources Diagnostic services (hours) ICU bed days Routine bed days Nursing care (hours) Margin Coronay Surgery 10 25 7 88 $2.500 1 5 50 $400 DRGS Birth/Delivery 2 0.5 2 27 $300 DRGS Birth Delivery Range Definitions Number_of_cases Resources used Resources available Minimum cases Total profit 0 7 SO 550 Respiratory Coronay Surgery Alcohol Drug Abuse 10 10 15 Resources used Number of cases Minimum cases Constraints Diagnostic services (hours) ICU bed days Routine bed days Nursing care (hours) 20 Resources available 325 35 420 3.800 Respiratory Coronay Surgery Birth Delivery Alcohol Drug Abuse Total profit Net profit