Someone please show me how to set this up in table. I know what I did wrong. Please use =Formualtext() when you provide your answer. I don't need you to explain the LHS and RHS and binary variables. Please see below solutions and then the table I have set up.

The table below is my attempt to set up.

Final Exam 1. A local hospital has been experiencing labor shortages and needs to find ways to improve efficiency. One source of inefficiency that has been identified is operating room scheduling. There are four surgeons scheduled to work tomorrow. In the table below you can find tomorrow's scheduled patients and the estimated time their surgery case will require. Note that each patient should be operated on by only one surgeon, that all of the surgeons are capable of performing all types of surgery for the day, and that all patients only need one surgery. The surgery teams start their cases at 7am and are all contractually obligated to receive overtime pay (time and a half) if they are working past 5pm. Find the optimal surgery schedule which allows all patients to be treated while minimizing the amount of time that each surgeon deviates from the standard workday. a. Formulate a mathematical programming model using binary variables for this problem in Excel. b. Report the schedule by explaining which patients each surgeon will operate on. c. Will the optimal schedule require paying any over time? d. Will the optimal schedule allow any surgeons to leave early for the day? Operating Room Allocation Patient 1 2 3 4 5 6 7 8 9 10 11 12 Estimated Hours Required 3.0 6.0 4.0 3.0 1.0 4.5 5.0 2.0 2.0 4.0 3.0 1.5Step 1/2 A a. Mathematical programming model: Let binary variable xjj be equal to 1 if patient i is operated on by surgeon j, and 0 otherwise. Let t; be the total time that surgeon j spends in the operating room. Then, we want to minimize the maximum deviation from the standard workday, which we can represent as: minimize max; (t; - 8) subject to: i) Each patient is operated on by exactly one surgeon: EjlXi = 1 for all i = 1, ..., 12 ii) Each surgeon operates on at most one patient at any given time: EXi = 0 SP$8:SP$12 = SN$5 Change A B C D E F G H J K L M N O P Delete Customer Allocation Reset All Customer 1 2 3 5 6 7 8 9 10 Target Sales Potential $113 $106 $84 $52 $155 $103 $87 $91 $128 $131 $210 Load/Save Make Unconstrained Variables Non-Negative Sales rep Customer Assignments Potential Under Over Target 0 0 0 $204 $6 SO $210 Select a Solving Simplex LP v 1 Options Co Method: 0 0 0 0 $209 $1 SO $210 10 0 0 0 0 0 0 $207 $210 UI AWN Solving Method 11 0 $218 SO $8 $210 12 0 0 0 0 1 Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the L 1 0 0 0 0 $212 SO $210 Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver 13 1 1 1 1 1 1 1 1 1 1 problems that are non-smooth. 14 Total Under/Over $20 Minimize: P14 16 By changing: C8 : L12, N8:012 Help Solve Close Subject to: P8 : P12=N5 17 C13 :013- 18 C8:L12 Binary 19File Home Insert Draw Page Layout Formulas Data Review View Automate Developer Help Acrobat Power Pivot Comments Set Objective: SCS20 D'a From Text/CSV From Picture Queries & Connections +3 Data Analysis To: Get Le From Web To Recent Sources IIII O Max Min O Value Of: ?, Solver Refresh Properties Sort Filt Group Ungroup Subtotal Stocks Currencies Data Edit Links Geography From Table/Range Existing Connections All By Changing Variable Cells: Get & Transform Data Queries & Connections Data Types Sort & SC$16:$G$16 Outline Analyze Subject to the Constraints: SH$9:SH$15 > = 1 Add C20 v ! X fx Health Care Systems problem SC$16:SG$16 = binary Change Delete Reset All Load/Save C D F G H K M W X Y Z AA Health Cafe Systems problem Make Unconstrained Variables Non-Negative Select a Solving Simplex LP v Options Method: Health Care Systems of Florida Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Region Sanford Altamonte Apopka Casselberry Maitland Covered by 0 Help Solve Close IN HHOOP OOH OPP OHOFOO 7 O Select 0 0 1 Cost (1000's) $450 $650 $550 $500 $525 Total cost (1000's) $1,475 Optimal solution is to select Sanford, Casselberry, and Maitland for the new facilities. Total cost for this recommendation is $1,475,000