I need help creating a simulation model using an Excel spreadsheet. With your guidance, I'm confident I can build and solve it independently. It would be great if you could share the necessary formulas. Thank you. This Initiative. As one element in a broad initiative to become more efficient and improve service to patients, we at MEDCARE want to examine our most widely used appointment scheduling system. Although the system has been used for some number of years, MEDCARE has become aware that this important system should be studied. Our Director of Operations (the undersigned) has received a number of comments and complaints from patients, doctors, and administrative staff. It is requested that your AIG team conduct an analysis of this appointment system.

Current Appointment Scheduling System.This particular appointment system is designed to serve many of our offices or clinics based upon similar appointment durations, similar variations in actual visit durations, and similar general assumptions about patients arriving for appointments. There are approximately 25 doctors whom MEDCARE supports that now use this appointment system.The system (for any one doctor) has these characteristics:

1. During the total appointment period on any given day, there are 16 appointments each scheduled for 15 or 20 minutes in length (includes all doctor pre- and post-appointment tasks). The sequence of the schedule is eight 15 minute appointments followed by eight 20 minute appointments. Thus the total planned appointment period spans four hours and 40 minutes. Typically, an appointment period begins at 7:30 AM and ends after the 16^th appointment (scheduled to end at 12:10 PM).
2. We place a reminder email or text message to each patient 1-2 days prior to their appointment and get confirmation.
3. We see patients only in the order scheduled, i.e., if any come very early, they do not get seen earlier than any other patient scheduled before them.
4. We allow a 10 minute "grace" period. If a patient arrives more than 10 minutes late, then this patient must reschedule for another day.
5. Although the first appointment never begins before 7:30 AM, for all other appointments we begin as soon as both the doctor and patient are available.
6. Doctors schedule their other activities and duties after 12:10 PM (the scheduled end of last appointment). Other activities include surgery or other operations, rounds with hospital patients, business meetings, lunch, and yes, occasionally golf. Thus, delays in completing the appointment period on time can have undesirable consequences for doctors and staff as well as for patients.

Appointment System Data: For this study, a large data gathering exercise was undertaken and the results are available for your team in an Excel file. We believe that these data are representative of patient behavior and doctor performance in our current appointment system. Note that in this data gathering, we only observed one patient arrival slightly later than 10 minutes after the appointment scheduled start - we recorded this as 10 min late. Thus, for your analysis, assume zero percent of patients arrive later than 10 minutes.

A Possible Improvement. MEDCARE's Operations Manager, in conjunction with staff and representatives of the doctors we support, have agreed upon the following as a potential improvement to the appointment system, if it can be shown to have significant performance enhancement. This possible improvement has three elements:

1. Insert Buffer Times. Some of the strongest complaints about the current system come from doctors and staff indicating they are "not able to predict the completion time of the appointment period." As one person said, "There is too much variability in end time! I can't plan anything after work because sometimes we're as much as 45 minutes or more after the scheduled end time." One concept to reduce variability is buffers. We believe that it would be possible to add one or more buffers or "catch-up" periods to the appointment schedule. Although this would mean extending the schedule somewhat past the current 12:10 PM scheduled end time, our doctors and staff have indicated agreement with this idea if it would result in a more consistent actual ending time (we've asked a number of them). For this study, we propose adding 15 minutes to the schedule (in "chunks" of 5 min). We're uncertain exactly where in the total appointment period the buffers would work best - but we suggest as a first try to put the three 5-minute buffers before the 10^th, 12^th and 14^th appointments. Note that the new (agreed upon) scheduled completion time will be 15 minutes past 12:10 PM or 12:25 PM.
2. Change the "grace" period. Since we now have patients arriving as much as 10 minutes past the scheduled appointment start time, we believe we could change this to an 8 minute "grace" period. If this change is made, we think that over time all patients will become more sensitive to their arrival times. For modeling this new grace period, we believe that the change to arrival times (from that shown in the data) would stabilize to the following: the arrivals after 8 min late would be added to the 9 arrival times of 0, 1, ...,8 in equal measures. For example, if 9% of the current total arrive 9 or 10 minutes late, add 1% to the arrival times 0, 1, ..., 8.
3. Tighten up the appointment durations. In discussing appointment length with a number of doctors, most believe that (if given signals to help keep them on time) they could insure that most all appointment durations are not more than 3 minutes over the scheduled length. Thus, we would implement a system of signals such that the appointment durations would change to the following. For 15 minute appointments, the percentages now over 18 minutes would be equally divided over the 12 to 18 minute durations. For 20 minute appointments, the percentages now over 23 minutes would be equally divided over the 17 to 23 minute durations.

Charge to the Analysis Team.

In summary, make sure your study answers these business questions:

1. Do the new arrival and appointment duration data (we gathered for your study) by themselves give insights about causes of poor appointment system performance?
   1. Are there any conclusions about system performance to be drawn from a careful examination of the newly-acquired arrival and appointment duration data? Use tables and/or graphs.
   2. Are there any other insights you would offer?
2. What is the performance of the current appointment system? This is the most important question for your study.
   1. Use the measures identified in the attached table (Note that we use all CAPS for these key measures): (1) COMPLETE:the time of completion measured in minutes before or after the full appointment period end time, now 12:10 PM, (2) WAIT: average patient waiting time (after the scheduled appointment start) for the 16 appointments, and (3) IDLE: total idle time of doctors during the full 16 appointment period. If you propose any other performance measures you believe to be insightful, consult with your point of contact. This is the key need from your study -- for you to help us develop a good understanding of the overall characteristics of the current appointment system per these three measures.
   2. Note that we want to understand our system performance measures "on average" but also how these key measures vary, e.g., how often the appointment system has a "very low WAIT, low IDLE, very low COMPLETE or very high values of these three measures" Another measure we propose is how often the appointment period ends "on time."
   3. Produce a table of results that describes the current system versus the target values we have set (see attached table) and perhaps graphical figures.

1. Can the possible improvement to the system identified earlier provide significant enhancement to performance?
   1. Examine the possible improvement to this appointment system identified earlier. Evaluate it (with the same method you develop to answer (2) above) using the same performance measures. Use the same patient arrival and appointment duration data - modified as indicated for this improvement.
   2. Also, help identify any other non-quantifiable pros and cons that you see of the existing system versus this possible improved system.
   3. Provide one or more comparison tables.

From your analysis, MEDCARE may be willing to examine in detail (perhaps with pilot projects) this possible improvement if your work indicates it may have significant payoff.

1. Are there any additional insights or recommendations from your analysis?

Develop a report (business memo) of your study to present to the director of operations (signature below) and to the liaison with all the MEDCARE doctors (your point of contact). Use the requested format for business memos - this is the format that AIG uses for analysis projects.

Bottom Line: MEDCARE is depending on you to help us to first understand (and second perhaps improve) this important customer support system.

JOAN SMITH, R.N., MBA

DIRECTOR OF OPERATIONS, MEDCARE, INC

TABLE OF MEDCARE APPOINTMENT SYSTEM PERFORMANCE MEASURES

(Use these measures in your study.)

NAME*	DEFINITION	TARGET VALUES
COMPLETE	Time of completion of the full 16 appointment period measured in minutes before (-) or after (+) the scheduled end time of the last appointment	Average <= 8 min St Dev <= 5 min 90%tile <= 15 min
WAIT	Average waiting time** (after the scheduled start) of the 16 patients of a full appointment period.	Good: Average <= 8 min 90%tile <= 12 min Excellent: Average <= 5 min 90%tile <= 8 min
IDLE	Total idle time of a doctor for the full 16 appointment period	Average <= 10 min 90%tile <= 16 min

*Listed in Priority Order. Please use all caps for these measures - this is the practice we use.

** NOTE that if a patient arrives early and the appointment begins at or before the scheduled start time, the value of waiting time is zero. If a patient arrives after the scheduled start time but must wait, the value of waiting time is (actual start time - actual arrival time). Thus, we don't consider as 'wait' any time before the scheduled start time. Such time is due to patients arriving early for their appointment. Here is the data that was provided to me

Note 1: Arrivals and durations rounded to nearest minute.

Note 2: Negative arrival values indicate before scheduled appointment start.

Note 3: We found no correlation between arrival time and appointment duration.

Patient Arrivals(minutes early or late)		15 Min Appt Durations(minutes)		20 Min Appt Durations(minutes)
9		15		24
-4		16		26
7		20		24
7		16		24
6		17		26
0		17		23
-2		16		25
-2		17		17
8		21		21
-10		16		23
1		17		26
6		17		24
10		17		25
1		16		23
-10		15		23
8		17		20
-1		15		21
7		19		23
9		21		21
10		12		20
5		17		26
7		21		26
3		16		26
9		19		21
-10		14		24
9		13		19
5		16		26
-4		19		21
2		13		21
-6		12		23
-12		17		20
9		17		26
0		21		21
1		16		23
8		20		17
0		12		20
-6		17		24
-2		20		20
2		17		26
2		21		26
-1		12		26
-18		17		26
-14		17		26
4		21		21
0		17		20
-9		16		19
3		16		26
10		20		24
-10		14		24
4		19		17
-8		20		20
-18		19		18
-1		16		26
-10		17		24
10		17		21
-7		17		19
0		13		26
-13		21		21
-15		21		26
10		19		26
2		17		24
-9		17		23
9		17		21
4		16		26
7		14		21
10		16		24
-6		13		23
10		17		24
1		16		18
-2		21		23
10		13		26
10		19		23
-12		16		24
9		17		24
-10		15		24
-4		14		24
0		12		23
5		19		21
8		20		24
-8		17		26
-5		15		26
7		14		21
9		21		26
-17		12		26
2		21		23
-15		19		24
9		20		24
-1		21		17
7		17		26
8		21		19
-1		14		20
7		17		26
9		16		19
10		17		17
-8		14		21
0		21		21
0		21		26
8		16		24
-7		21		24
3		17		24
9		21		20
-13		16		26
10		15		20
-9		17		20
-1		21		20
7		21		26
10		20		26
8		14		26
3		21		21
7		13		20
7		21		23
7		20		23
0		17		23
8		16		26
-1		21		18
6		13		25
6		16		23
5		21		23
5		17		26
9		21		17
6		21		20
-13		12		20
6		14		19
6		17		23
4		16		26
-7		17		24
0		16		18
8		19		25
0		19		23
6		13		21
0		21		20
8		16		23
-10		20		21
-16		17		23
10		16		17
-7		19		18
-1		19		23
4		20		26
6		17		20
-13		21		23
6		19		21
7		21		24
-1		21		26
-5		21		24
6		17		21
9		17		26
-5		20		26
2		12		17
-11		14		23
0		16		18
6		21		26
-4		15		24
5		16		23
4		12		25
8		16		18
-12		16		26
9		19		23
7		13		21
-3		17		21
7		17		24
4		20		25
-1		20		23
-9		21		20
6		21		26
-7		16		21
-15		19		19
3		14		21
-6		16		26
6		21		20
-20		13		17
4		17		24
7		21		26
8		21		18
-2		20		26
0		21		21
-2		20		19
6		16		24
-10		17		21
6		21		20
10		17		17
3		21		23
7		17		19
6		19		23
3		19		18
3		21		23
-16		20		20
-19		17		23
1		16		19
-12		19		26
-11		17		17
1		20		23
-5		16		20
-6		15		23
-20		20		21
10		16		21
6		21		20
-6		16		23
9		16		26
-7		16		25
-4		21		25
7		16		21
1		19		21
-3		16		26
-8		21		21
-8		14		26
-9		16		24
8		17		24
7		17		23
7		21		26
2		20		20
8		20		23
0		17		23
-1		13		23
3		21		18
-13		20		23
0		21		24
9		19		26
-1		17		23
5		16		17
1		16		20
3		19		23
9		21		21
-19		16		20
9		19		19
-18		16		18
9		21		24
-1		17		17
-12		17		18
6		16		25
-14		21		24
-2		17		26
10		20		26
8		19		20
0		15		24
7		15		26
-1		20		20
-17		20		19
-1		15		25
7		17		23
2		16		20
2		21		19
4		12		26
1		17		21
8		17		24
10		16		20
5		14		21
-3		17		26
4		20		23
9		13		26
8		19		23
-9		16		24
6		14		26
0		18		24
0		19		26
-17		16		24
8		15		22
7		15		21
0		20		21
-15		19		19
-7		13		22
8		16		22
-4		15		17
8		20		17
-7		12		21
2		13		17
-2		14		22
-4		17		25
7		18		18
-19		18		24
-7		13		23
-1		17		23
5		17		23
-3		17		23
-9		20		20
1		13		19
10		16		21
8		19		24
10		16		23
-2		17		23
-5		18		24
-19		18		23
0		20		23
-5		16		17
10		19		22
8		18		21
-3		18		22
1		19		22
-5		17		19
3		16		25
-2		18		17
10		17		22
-5		19		19
-13		19		24
2		16		25
-4		16		23
-18		19		21
9		18		21
8		19		24
-2		14		24
-17		12		21
6		19		21
9		17		23
10		15		21
3		20		20
9		16		25
-2		18		19
8		18		22
10		19		22
1		19		22
7		12		22
5		19		18
7		15		20
-9		16		22
-3		16		24
-20		17		23
-11		19		25
1		17		21
9		18		21
0		17		24
8		18		22
-17		17		20
-11		17		23
5		20		23
7		18		22
-9		17		22
-14		20		24
6		16		22
8		16		21
-16		15		19
9		16		19
1		16		20
10		18		24
6		20		21
-1		20		17
-6		15		22
-9		18		20
1		13		22
1		20		22
-10		16		17
9		18		23
1		15		23
0		16		23
9		15		22
-8		14		18
-7		17		22
-12		19		24
-1		18		25
-2		17		20
-16		19		21
10		16		22
7		20		20
-2		19		22
8		20		24
-14		16		24
-18		16		25
2		18		25
7		18		21
-9		15		24
3		15		21
-2		20		20
5		13		19
6		17		23
10		15		17
3		17		24
9		15		21
9		12		21
9		16		20
6		19		22
-2		15		22
4		16		23
-2		18		21
-1		17		24
9		15		23
2		16		23
3		13		24
0		15		25
-5		18		23
10		17		23
7		18		22
-1		19		23
7		18		18
10		20		23
-16		19		23
2		18		21
2		17		22
1		16		20
1		20		22
0		15		17
-2		16		22
7		17		24
-7		18		23
7		20		23
0		17		20
-2		17		23
7		18		21
-10		12		23
-2		17		20
7		15		21
6		18		23
8		16		25
10		20		24
8		15		22
6		17		22
9		15		20
-5		15		22
1		17		24
0		20		18
7		18		24
7		16		23
10		12		21
-2		14		23
6		17		21
6		17		21
9		20		24
-2		20		25
3		17		24
2		20		24
6		17		24
-1		18		20
9		17		23
6		13		21
-8		16		21
8		19		17
9		12		17
-19		18		25
-8		17		22
-7		16		24
-2		17		22
6		16		20
6		16		23
10		18		23
7		15		24
-7		17		24
-6		18		20
0		20		23
-1		18		20
-7		18		23
-16		18		18
10		19		21
9		17		22
9		16		18
5		18		20
6		15		23
-1		20		20
3		16		22
-6		19		22
-2		13		24
10		19		17
10		18		21
0		18		22
6		15		22
-1		16		25
-1		18		24
3		17		24
6		18		25
-1		18		17
-14		17		22
-16		20		22
3		17		22
7		17		23
8		16		22
-2		19		23
-14		16		22
-1		20		22
5		18		23
10		20		22
-16		12		22
-12		18		24
-18		17		23
1		17		23
8		15		19
7		18		22
-8		19		23
5		20		22
9		15		25
4		13		21
6		15		25
-16		15		24
10		16		17
-2		12		22
8		12		24
-5		18		22
0		19		24
-4		19		22
2		13		24
-2		19		20
7		13		18
8		19		22
-15		16		17
3		17		24
-18		15		23
-10		18		23
6		12		17
-6		18		22
-6		13		22
9		15		20
9		18		25
10		13		20
-10		20		25
-2		18		23
9
2
7
-10
0
9
10
-2
10
-8
-12
-3
-11
-9
8
-9
10
0
1
10
6
0
-12
-3
8
10
-6
8
0
-1
7
-9
2
6
-18
-5
-1
7
10
8
-1
6
-9
-1
-2
8
2
3
5
8
2
7
5
-2
8
8
6
-11
6
-2
-13
1
4
-2
8
-7
10
0
-10
6
7
10
-1
9
2
-17
-6
8
0
3
-16
6
6
10
9
-10
10
0
10
10
7
5
-5
0
8
-19
0
10
2
-1
6
9
-8
1
1
-20
10
-4
9
1
-2
-14
0
7
10
7
4
8
6
7
10
9
3
6
6
0
0
-14
2
2
-7
-10
-1
7
5
0
-3
7
8
-1
-9
5
-2
-1
0
-2
10
3
-15
9
-13
-4
7
-1
3
7
-6
7
10
9
6
-6
1
-2
-15
-2
2
5
-9
6
10
-8
9
9
5
-6
5
-2
5
-11
0
10
-1
7
-2
-3
-18
7
10
8
8
8
9
6
9
9
-1
10
1
6
7
7
-15
-15
7
-16
1
-1
5
8
-3
7
2
6
0
-2
-5
10
5
-2
-1
-2
10
10
2
-11
0
-9
7
7
-1
-8
7
5
-8
9
7
10
0
8
6
-18
8
-14
-1
5
-9
9
2
9
6
1
9
-5
4
3
-13
-7
-19
-2
-8
-16
2
6
7
1
-10
0
-3
7
-7
8
8
-20
9
-2
0
6
-6
6
10
3
-14
-1
-4
10
-1
-11
10
7
8
9
1
1
8
-6
-2
2
1
6
9
3
1
-19
6
10
10
6
0
-18
9
0
-10
5
7
7
-2				There are 3 Steps involved in it Step: 1 Get Instant Access to Expert-Tailored Solutions See step-by-step solutions with expert insights and AI powered tools for academic success Step: 2 Step: 3 Ace Your Homework with AI Get the answers you need in no time with our AI-driven, step-by-step assistance Get Started Recommended Textbook for Calculus Early Transcendentals Authors: William L. Briggs, Lyle Cochran, Bernard Gillett 2nd edition 321954428, 321954424, 978-0321947345 More Books Students also viewed these Mathematics questions Question I have to do an accounting assignment. I have attached the instructions, the questions, and pdf's containing the information requested for the questions. Company Estimates & Opinions Sanmina Corp... Answered: 1 week ago Question Please create an excel spreadsheet with formulas for requirement 5 (pages 24-27)and requirement 6 (pages 28-31)only. Please submit in excel format. Both questions and answers are provided for... Answered: 1 week ago Question ★★★★★ Read and answer the question below. EMBRACE DEMOGRAPHICS AND SOCIETAL CHANGES Using Differences to Drive Value Force 7: Demographics and Societal Changes. One of the most important forces that is... Answered: 1 week ago Question ★★★★★ In Figure P23.15, determine the point (other than infinity) at which the electric field is zero. 1.00 m -2.50 : 6.00 C Answered: 1 week ago Question ★★★★★ Suppose that the joint cost function for two products is C(x, y) = y ln (x +1) dollars Find the marginal cost with respect to (a) x. (b) y. Answered: 1 week ago Question ★★★★★ Repeat Exercise 20.14, adding a check in T 1 so that Y does not exceed 90. Exercise 20.14 Change transaction T 2 in Figure 20.2(b) to read read_item(X); X := X + M; if X > 90 then exit else... Answered: 1 week ago Question ★★★★★ =+f) What is the average size of the error standard deviation? Answered: 1 week ago Question ★★★★★ Davis Consulting performs systems consulting. The company has also begun selling accounting software and uses the perpetual inventory system to account for software inventory. During January, Davis... Answered: 1 week ago Question ★★★★★ Presented below are the components in determining cost of goods sold. Determine the missing amounts. Beginning Inventory Purchases Cost of Goods Available for Sale Ending Inventory Cost of goods sold... Answered: 1 week ago Question ★★★★★ Kraftstoff of Germany. Kraftstoff is a German-based company that manufactures electronic fuel-injection carburetor assemblies for several large automobile companies in Germany, including Mercedes,... Answered: 1 week ago Question ★★★★★ Sample number Data 1 74.030 74.002 74.019 73.992 74.008 2 73.995 73.992 74.001 74.011 74.004 3 73.988 74.024 74.021 74.005 74.002 4 74.002 73.996 73.993 74.015 74.009 5 73.992 74.007 74.015 73.989... Answered: 1 week ago Question ★★★★★ Corporations providing products and/or services are made up of many parts. There is accounting, marketing, as well as operations (and other groups too). How might these three groups integrate (or... Answered: 1 week ago Question ★★★★★ john calls because his mortgage company is requesting proof of insurance for a two - family home he is purchasing that he plans to rent to tenants. what product best matches his needs? Answered: 1 week ago Question ★★★★★ Case 2-22 (Algo) Plantwide versus Departmental Overhead Rates; Pricing (LO2-1, LO2-2, LO2-3, LO2-4] "Blast it" said David Wilson, president of Teledex Company. "We've just lost the bid on the Koopers... Answered: 1 week ago Question ★★★★★ 1. Conduct a research online and analyse the approaches use in system documentation, problems, and propose possible solutions. Address the following: 1a. Discuss the specific recommendations that you... Answered: 1 week ago Question ★★★★★ JJ Industries will pay a regular dividend of $1.40 per share for each of the next four years. At the end of the four years, the company will also pay out a $46 per share liquidating dividend, and the... Answered: 1 week ago Question ★★★★★ Avatar Financials, Inc., located on Madison Avenue, New York City, is a company that provides financial advice to individuals and small- to mid-sized businesses. Its primary operations are in wealth... Answered: 1 week ago Question ★★★★★ For the n55 ordered measurements from Example 2.2, the position of the median is .5(n1)5.5(6)53, and the median is the 3rd ordered observation, or m56. For the n56 ordered measurements from Example... Answered: 1 week ago Question ★★★★★ Find the median for the set of measurements 2, 9, 11, 5, 6, 27. Answered: 1 week ago Question ★★★★★ Find the median for the set of measurements 2, 9, 11, 5, 6. Answered: 1 week ago Previous Question Next Question View Answer in SolutionInn App Services Sitemap Fun Definitions Become Tutor Study Help Categories Recent Questions Expert Questions Company Info Security Copyrights Privacy Policy Terms & Conditions SolutionInn Fee Scholarship Get In Touch About Us Contact Us Career Jobs FAQ Campus Ambassador Secure Payment Download Our App © 2024 SolutionInn. All Rights Reserved