Question: Please solve this using please Excel Solver feature and explain your work as you go along The weekly profit at Pickens Memorial Hospital depends on

Please solve this using please Excel Solver feature and explain your work as you go along

Please solve this using please Excel Solver feature and explain your work

The weekly profit at Pickens Memorial Hospital depends on the number of patients admitted in three separate categories (medical, surgical, and pediatric). The hospital can admit a total of 200 patients (regardless of category) each week. However, because Pickens Memorial serves a large community, patient demand in each category by itself, far exceeds the total patient capacity. Due to a fixed overhead, the profit per patient in each category actually. increases as the number of patients increase. Further, some patients who are initially classified as medical patients then get reclassified as surgical patients. As a result, the profit per surgical patient also depends on the number of medical patients admitted. The following information has been provided by the accounting department: Profit contribution per medical patient =$45+$2M Profit contribution per surgical patient =$70+$3S+$2M Profit contribution per pediatric patient =$60+$3P Where M,S, and P are the number of medical, surgical, and pediatric patients. Pickens Memorial has identified 3 main constraints for this model: x-ray capacity, marketing budget, and lab capacity. The table below shows the relevant weekly data for these thee constraints, for each category of patient. The table also shows the weekly availabilities of each of these three resources. In addition, based on historical data, the lab supervisor believes the time required per lab test increases as the number of medical patients increase, as follows: Time required per lab test (in hours) =0.2+.001M Pickens seeks to maximize its weekly profit by selecting the optimal amount of each type of patient to admit. Set up this problem as a non-linear programming problem. Solve this problem with Solver

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!