the writer only need to choose one or work on one that he/she feel comfortable working. I uploaded two Excel files. The Excel file titled "C12 staff scheduling
LP" only applies to question 1 or option 1. The other Excel file titled " Group Assignment 4-1" provides detailed illustrations on how to work on each
one of the options. Again, the writer should only work on one or choose one that he/she is comfortable working.

1. Use linear programming to optimize nursing staffing levels and schedules. This example is included in Excel file C12 Staff Scheduling LP example.xls
Now, two nurses (Mary and Jane) at the Riverview UCC have decided to work part time. They would like to work only two (consecutive) days per week. Because they would
be part-time employees, their salary and benefits per nurse day would be reduced to $160 on weekdays and $220 on weekend days. Riverview could hire an additional
full-time nurse if needed. Using linear programming to find answers to the following questions:

Should Riverview agree to this request? If allowing the two nurses to transfer to part time can lower the minimum salary and benefits expenses, then Riverview should
agree to this request. Otherwise, it should not agree.
If it does, will it need to hire additional nurses? The answer to this question depends on how many nurses Riverview needs to meet the target staffing level.
It turns out that to minimize the total salary and benefits expense, Riverview should hire a nurse, two part time nurses have no choice but to sign up a certain
schedule, and one full time nurse should be assigned to schedule A, two to schedule C, one to schedule E, and one to schedule G. Given that we know their preferences
and seniority, what new schedule would you recommend for each nurse?

The data used in this task are shown in the worksheets "scheduling 1" and "scheduling 2". The part that you need to complete are highlighted in
green. First, you need to complete the rows 11 to 16 in "scheduling 1", which set up the part-time schedules I to N. You can use row 10 as an example as it
shows the part-time schedule H. If a part-time nurse is assigned to schedule H, she would take Sunday to Thursday off and works only on Friday and Saturday.

Then, you need to specify the formulas (i.e., =sum()) that calculate the number of part-time nurses schedules on each day and the total number of nurses scheduled on
each day. After that, complete the formulas for part-time nurse salary/benefits expenses and total salary/benefits expenses for for each day.

Following that, click the Data ribbon, click Solver, and click "Solve" in "scheduling 1". The solver has been set up to make the task simpler. Keep
the solver solution in the worksheet.

The worksheet "scheduling 2" gives the nurse preference and seniority. Complete the formulas that calculate the preference score for the new hire. Note the
new hire has a seniority score of one year because she has been working for the organization in another department. Solve the linear programming model that maximizes
nurse satisfaction to find out which schedule each full-time nurse should be assigned to. Again, the solver has been set up already. The two part-time nurses should be
assigned to the schedule that minimizes the total salary/benefits expenses, which is shown in the linear programming solution in "scheduling 1".

2. Compare various sequencing rules. The radiology department of VVH uses FCFS to determine how to sequence patient X-rays. On a typical day, they collect data related
to patient X-rays. Use the data to compare various sequencing rules. Assuming these data are representative, what rule should the radiology department be using to
achieve shortest average completion time?

The data for this task is given in the worksheet "sequencing rules". First calculate the slack time and critical ratio for each patient. Then sort A1:E20 by
different columns to get the sequence of jobs. The information for first three patients under different sequencing rules has been given in the lower half of the
worksheet. Complete the other rows for each sequencing rules to find the average completion time corresponding to each sequencing rule. Note that formulas are
contained in some columns like Completion Time and Tardiness.

3. Forecast patient days for a hospital. Ronald Reagan UCLA Medical Center (Links to an external site.) in Los Angeles, CA is a well-known hospital in the nation. It
is a 466-bed general medical and surgical facility with 23,096 admissions in 2012. The worksheet “patient days” contains actual patient census days for the hospital
from 2002 to 2012. Use the forecasting templates to forecast the hospital’s patients day for 2013 using Simple Moving Average, Weighted Moving Average, Single
Exponential Smoothing, and Linear Trend. Which model do you believe gives the best forecast?

To complete this task, you just need to copy the values of patient days to the cells in blue in the templates for Simple Moving Average, Weighted Moving Average,
Single Exponential Smoothing, and linear trend. For Simple Moving Average, specify 3 as the number of time periods. For Weighted Moving Average, use 0.2, 0..3, and 0.5
for the least recent period to the most recent period. For Single Exponential Smoothing, specify the smoothing factor as 0.5. For Linear Trend, you just need to copy
the data to the column in blue. The best forecasting model should have the lowest MAD and MSE.

4. Determine the appropriate order quantity and reorder point without and with safety stock.

Hospital purchasing agent Abby Smith needs to order examination gloves for the hospital. She collects the following information.
Cost of the gloves: $4.00/box
Carrying costs: 33% or $1.33/box
Cost of ordering: $150/order
Lead time: 10 days
Annual demand: 10,000 boxes/year

Currently, she orders 1,000 boxes of gloves whenever she thinks there is a need. Abby has heard that there is a better way to manage the inventory and wants to use
economic order quantity (EOQ) to determine how much to order and when to order.

The worksheet "Basic EOQ" shows the template for the economic order quantity (EOQ) model. Specify the annual demand, ordering cost, carrying cost, and
working days per year (365 days), and you will get the EOQ. Specify the actual order quantity and you can check the total cost associated with each actual order
quantity in cell E19.

What are the total annual costs when you set the actual order quantity as the calculated EOQ, 1000, and 2000 boxes respectively? Which order quantity results in the
lowest total cost?

Based on the choice above, how often will Abby need to order? About how many days will there be between orders? These numbers are shown as numbers of orders per year
and length of order cycle.

Assuming that Abby is not worried about safety stock, when should she place an order? The template in worksheet "ROP" should be used to answer this question.
Specify the average daily demand as =10000/365, average lead time as 10 days, standard deviation of demand during lead time as 10.4 boxes, and service level (the
probability of not having stockout) as 0.5. The reorder point shows the inventory level at which Abby should place an order.

Assuming Abby decides she will be satisfied if the probability of a stockout is 5 percent. How much safety stock should Abby carry? When should she place an order this
time? To answer this question, the service level should be 1-0.05 which is 0.95 or 95%. Safety stock indicates the excess inventory that Abby should hold to ensure 95%
of the time she will not have a stockout. The reorder point this time is the reoder point calculated above+safety stock