Healthcare Operations $$ Homework $1$

Question $1(70$ points$)$

Submit an Excel file for this question.

Following the example on page $153$ of the textbook under the "Linear Programming Example"

subheading, use the Solver add$-$in in Microsoft Excel to find the optimum quantity for combining

four services to maximize the profit margin.

Service

Resources

Needed $($Unit$)$ Respiratory Coronary

Surgery

Birth$/$

Delivery

Alcohol$/$

Drug Abuse

Available

Resources

Diagnostic

services $($hours$)81053350$

ICU bed $($days$)6710110$

Routine bed

$($days$)2644480$

Nursing care

$($hours$)386028564100$

Profit Margin $ $300\mathrm{.}00$ $ $2,500\mathrm{.}00$ $ $360\mathrm{.}00$ $ $80\mathrm{.}00$

Minimum

Cases $4386$

Total Available Resources: the maximum unit of available resources in the hospital. For

example, this hospital can only have $110$ ICU bed days in a week.

Profit Margin: After deducting all fixed and variable costs associated with a given service from

the payment of the insurance company or the patients, this is the remaining profit for each unit of

service.

Minimum Cases: Assume that this hospital is obligated by law or the payer to provide a

minimum unit of each service to the beneficiaries$/$ patients.

Steps to consider:

$1-$ Copy this table into Excel.

$2-$ Make sure that the "Solver" add$-$in is enabled in Excel. If not, follow the steps to enable

the add$-$in$.($File$>>$options$>>$ Add$-$ins$>>$ Manage: Excel Add$-$ins$)$

$3-$ Add a row for "Optimal cases" and set the default value to any number such as $"1".$

$4-$ Add a column for the "Actual" unit of resources. Calculate this column by multiplying

the corresponding required resources by the optimal cases.

$5-$ Ensure that the "Actual" unit of resources does not surpass "Total available resources".

$6-$ Ensure that the "Optimal cases" for each service are not less than the "Minimum cases".