Oladimeji_hw10

Template for Waiting Line Analysis © 1999-2002 by Armann Ingolfsson M/M/s M/M/s/s+C (M/M/s finite capacity) M/M/s with finite arrival population (finite population) M/G/1 NOTE TO 413 students: I include this worksheet so that the Queueing functions will wor This workbook calculates performance measures for the following queueing models: Click on the worksheet tab of your choice and enter the required parameters. The calculations in this workbook use some special functions that are defined in this workbook. If you wish to use these functions in another workbook, then save this workbook under another name. The functions will be available in that new workbook (even if you delete some of the worksheets).

rk.

"A" classification % 10% "C" classification % 1% $ 135.00 18.18% Hourly Salary of Buyer $ 22.00 0.10 Lead time (in days) 1 Days in the year (assume 365) 365 Annual Holding cost for each item category Annual Holding cost for each item category Time needed to place each order (IN HOURS)

Item Name/Description Acetaminophen supp. 3133 $0.43 $1,347.19 Fentanyl inj. 6460 $0.35 $2,261.00 Oxymetazoline nose spr 1248 $1.87 $2,333.76 Sensorcaine® inj. 1799 $1.45 $2,608.55 Albuterol inh. nebule 28800 $0.11 $3,168.00 Acetaminophen oral liq. 11063 $0.29 $3,208.27 Ketorolac inj. 3710 $0.98 $3,635.80 Morphine inj. 10750 $0.47 $5,052.50 Ampicillin inj. 4810 $1.26 $6,060.60 Heparin flush syringe 4140 $1.66 $6,872.40 Ciprodex otic drops 1632 $5 $7,344.00 Ibuprofen oral susp. 12400 $0.65 $8,060.00 Morphine carpuject inj. 12,500 $0.78 $9,750.00 Methotrexate liq. Inj. 358 $34 $12,172.00 Ifex® inj. 126 $160 $20,160.00 Lorazepam inj. 22225 $0.94 $20,891.50 Ambisome® inj. 497 $51.62 $25,655.14 Winrho® inj. 46 $635 $29,210.00 Rituxan® inj. 74 $447 $33,078.00 Thymoglobulin inj. 100 $369 $36,900.00 Cefotaxime inj. syringe 12,996 $3.02 $39,247.92 Suprane® inhalant 408 $106 $43,248.00 Infuvite inj. 846 $52 $43,585.92 Vfend® inj 479 $91 $43,589.00 Mylotarg® inj. 21 $2,215 $46,515.00 Prevnar® inj 805 $63 $50,715.00 Neupogen® inj. 290 $184 $53,360.00 Synagis® inj 102 $600 $61,149.00 Oncaspar® inj. 55 $1,466 $80,630.00 Aldurazyme® inj. 138 $649 $89,562.00 Lupron® Depot inj. 82 $1,162 $95,284.00 Rituxan® inj. 46 $2,234 $102,764.00 Thrombin® top. Inj 504 $252 $127,008.00 Novoseven inj. 89 $1,697 $151,033.00 Curosurf® ETT soln 345 $465 $160,425.00 Polygam® inj. 400 $406 $162,400.00 Gammunex® inj 176 $1,020 $179,520.00 Cytogam® inj 243 $743 $180,549.00 Ultane® inhalant 1134 $213 $241,542.00 Zofran® inj. 24060 $17 $409,020.00 Remicade® inj. 828 $543 $449,604.00 Botox® inj. 1164 $457 $531,948.00 TOTAL $3,582,467.55 Demand (D) Contract price/unit Total product cost % of Total Product Cost ABC Classification

Investment Cost (Q times contract price per unit)

M/M/s Queueing Calculations Basic Parameters COST Parameters (Cos Arrival Rate per hour Service Rate per hour Number of Servers Time Unit hour Basic Performance Measures Utilization #VALUE! P(0), probability that the system is empty #VALUE! Lq, expected number in queue #VALUE! L, expected number in system #VALUE! Wq, expected time in queue #VALUE! hours W, expected total time in system #VALUE! hours Probability that customer waits #VALUE! Advanced Parameters Threshold time 0.016667 hours Desired service level 95% Advanced Performance Measures Current service level #VALUE! #VALUE! Busy Server c b Idle Server c i Customer Waiting c w Customer being Served c s Number of servers required to achieve desired service level Note: this worksheet uses some that are defined in this workboo

4 le for that NOTE for the columns of 1,2, and 3 servers: After completing the calculations or copying the relevant data into each cell, do a COPY->PASTE SPECIAL->VALUES on that column of data. Otherwise, the data will change as you change the number of servers in cell B6. Leave all your formulas in the cells for the column of 4 servers so that I can check your programming logic.

month month_num visits Jan-17 1 464 Feb-17 2 439 Mar-17 3 463 Apr-17 4 430 May-17 5 462 Jun-17 6 495 Jul-17 7 695 Aug-17 8 618 Sep-17 9 517 Oct-17 10 540 Nov-17 11 474 Dec-17 12 495 Jan-18 13 589 Feb-18 14 588 Mar-18 15 667 Apr-18 16 656 May-18 17 644 Jun-18 18 588 Jul-18 19 1178 Aug-18 20 1029 Sep-18 21 840 Oct-18 22 732 Nov-18 23 780 Dec-18 24 875 Jan-19 25 834 Feb-19 26 952 Mar-19 27 785 Apr-19 28 701 May-19 29 731 Jun-19 30 786 Jul-19 31 1112 Aug-19 32 1105 Sep-19 33 814 Oct-19 34 848 Nov-19 35 774 Dec-19 36 842 Jan-20 ??? Conduct a regression analysis using the 3 years of data, a regression on a new worksheet, and name the worksheet assignment assumes that it is currently the end of Decem Question 1: Is there trend in your data? Type YES or N Question 2: Using a 12 month moving average, what woul Put your calculation in cell R14. Question 3: Based on your regressions results for estimati for the month of Jan 2020? This calculation does not dep Put your calculation in cell R17, clicking on the appropriat Question 4: Suppose a pandemic hit your organization's c regarding forecast error in February if you use a 12 month

X1 Y1 X2 Y2 X3 cost per nurse per shift # nurses DAILY COST shift 1 constraint shift 2 constraint shift 3 constraint 10% overtime constraint Results X1 Y1 X2 Y2 X3 Including 10% constraint Excluding 10% constraint # Shift 1 who don't work overtime # Shift 1 who do work overtime # Shift 2 who don't work overtime # Shift 2 who do work overtime # Shift 3 who don't work overtime

Y3 RHS # used Y3 Total Daily Cost Difference 365 days/yr # Shift 3 who do work overtime Annual Cost Difference

Make sure that one of the cells has a formula in it! L l W Make sure that one of the cells has a formula in it! L l W lower quality outcomes. r transplant for a variety of reasons. -per-year-study-says-heres-why/2139644001/) kidney transplant? rd one. lant with this additional supply?