Application for Healthcare Quantitative Research Resources
Sort Data in a Range or Table
How to Use Excel to Find the Mean, Median & Mode Ranges
Insert a Line Break in a Cell
BUSI 640 Application For Healthcare Quantitative Research Assignment
INSTRUCTIONS
OVERVIEW
The assignment will provide practical knowledge in determining cost containment is the predominant challenge for healthcare executives in the United States. As technology improves cost pressures will continue to increase in the widespread use of the quantitative tools can help to stabilize for reduce healthcare inflation.
INSTRUCTIONS
The country has recently experienced a major health emergency with the rapid spread of the X24 virus. Fortunately, a vaccine has recently been released that is very effective. As a result, VVH
has quickly set up mass immunization centers at most of its primary care clinics. The vaccine will be available on Saturday at 9:00 am.
VVH staff have had experience with mass immunizations one year ago and collected data to help them optimize this process. Because the vaccine was very expensive, they collected insurance
information for each patient in order to recover their costs. They discovered that individuals could be classified into various insurance categories and that the processing time for each group
varied. The categories were:
•
•
•
•
•
•
•
Commercial insurance – individual (Category 1)
Commercial insurance – family (2)
Medicare (Category 10)
Medicare advantage (Category 15)
Medicaid (Category 3)
Medicaid eligible (Category 11)
No insurance (Category 16)
Their system for collection insurance information was stations staffed by financial workers that
accepted each patient on a first come, first served basis. Their experience also showed the
collection of payer information was a bottleneck in the system and that this process needed to be
improved.
What master production scheduling system would you create to maximize the flow of patients
through the immunization center?
Directions for use of the Application for Healthcare Quantitative Research Template.
Using the Excel spreadsheet provided, follow the steps below:
On the tab labeled Analysis:
•
•
•
•
Step 1: Sort by category
Step 2: Calculate the mean of each category
o Insert a line space between the categories to input your categorial mean in red
Step 3: Sort by Mean
o On the Tab labeled student worksheet
Step 4: Copy the mean to the student worksheet
Page 1 of 2
BUSI 640
•
•
Step 5: Group into categories and organize the serving stations
Length of assignment
o 2 excel sheets
You will find helpful links for the above steps on the Application for Healthcare Quantitative
Research Assignment page under Application for Healthcare Quantitative Research
Resources.
Equations for models will be given when available.
Page 2 of 2
Start Time
1:56:16 PM
1:57:52 PM
1:59:24 PM
2:00:14 PM
2:00:22 PM
2:02:18 PM
2:04:22 PM
2:04:42 PM
2:05:22 PM
2:08:54 PM
2:09:28 PM
2:09:30 PM
2:09:58 PM
2:10:22 PM
2:11:26 PM
2:14:54 PM
2:15:04 PM
2:16:46 PM
2:17:28 PM
2:18:38 PM
2:19:04 PM
2:20:20 PM
2:21:54 PM
2:22:28 PM
2:24:08 PM
2:25:44 PM
2:26:32 PM
2:27:56 PM
2:28:30 PM
2:28:44 PM
2:31:16 PM
2:32:08 PM
Wait
Process
Time
Category Time
0:19:24
1
0:03:06
0:14:24
1
0:02:30
0:15:46
1
0:02:50
0:00:00
16
0:44:30
0:15:12
2
0:04:00
0:10:40
1
0:03:02
0:18:08
2
0:05:08
0:00:00
16
0:03:50
0:10:38
1
0:03:30
0:08:48
1
0:02:52
0:04:58
15
0:01:56
0:15:48
2
0:03:04
0:00:00
16
1:10:28
0:07:42
1
0:06:52
0:33:46
3
0:07:36
0:00:00
16
0:02:34
0:35:44
3
0:09:42
0:03:36
10
0:02:18
0:20:06
2
0:04:26
0:37:44
3
0:07:04
0:33:42
3
0:06:40
0:29:46
3
0:08:18
0:12:14
2
0:06:02
0:00:00
1
0:03:12
0:24:32
3
0:10:46
0:01:38
1
0:02:44
0:21:32
3
0:17:32
0:13:22
2
0:03:18
0:04:14
1
0:04:26
0:15:48
3
0:05:54
0:13:30
2
0:07:32
0:17:10
3
0:07:54
Step 1: Sort by category
Step 2: Calculate the mean of each category
Step 3: Sort by Mean
Go to student worksheet for step 4 and 5
2:34:42 PM
2:37:06 PM
2:43:14 PM
2:44:32 PM
2:45:50 PM
2:47:00 PM
2:47:54 PM
2:48:50 PM
2:51:26 PM
2:55:34 PM
2:57:06 PM
3:04:06 PM
3:04:48 PM
3:05:04 PM
3:06:46 PM
3:07:32 PM
3:10:00 PM
3:12:50 PM
3:15:24 PM
3:15:30 PM
3:16:06 PM
3:17:50 PM
3:20:42 PM
3:22:06 PM
3:22:32 PM
3:22:36 PM
3:28:14 PM
3:28:48 PM
3:32:42 PM
3:35:08 PM
3:35:52 PM
3:36:32 PM
3:37:22 PM
3:38:14 PM
0:10:46
0:15:58
0:13:02
0:13:24
0:14:26
0:00:00
0:15:40
0:15:58
0:18:22
0:00:00
0:23:40
0:00:00
0:21:02
0:20:06
0:16:36
0:16:48
0:18:22
0:16:56
0:19:04
0:09:52
0:42:16
0:42:58
0:14:28
0:39:56
0:27:54
0:14:10
0:05:00
0:29:44
0:33:28
0:33:00
0:00:00
0:32:10
0:30:56
0:46:58
3
2
3
3
1
16
3
3
3
16
3
16
1
3
3
1
3
15
10
10
2
2
11
2
3
11
15
1
3
1
16
1
1
2
0:08:14
0:19:20
0:04:34
0:06:54
0:02:56
1:15:52
0:14:32
0:15:26
0:05:38
0:26:20
0:07:42
0:11:20
0:02:38
0:01:04
0:21:58
0:02:26
0:04:42
0:02:34
0:00:38
0:02:06
0:12:08
0:08:08
0:03:22
0:04:00
0:09:30
0:11:30
0:08:48
0:07:44
0:07:12
0:02:14
0:00:38
0:04:36
0:03:50
0:04:04
3:39:30 PM
3:40:08 PM
3:41:12 PM
3:42:30 PM
3:43:12 PM
3:43:26 PM
3:47:52 PM
3:53:08 PM
3:53:18 PM
3:54:40 PM
3:58:04 PM
3:58:30 PM
4:00:56 PM
4:02:44 PM
4:02:58 PM
4:06:56 PM
4:09:42 PM
4:11:06 PM
4:14:22 PM
4:15:18 PM
4:15:50 PM
4:16:58 PM
4:17:06 PM
4:18:06 PM
4:19:24 PM
4:19:44 PM
4:21:28 PM
4:22:00 PM
4:22:32 PM
4:22:52 PM
4:23:00 PM
4:24:08 PM
4:24:16 PM
4:31:50 PM
0:33:40
0:24:30
0:13:48
0:44:38
0:07:12
0:33:08
0:00:00
0:42:34
0:41:36
0:07:22
0:08:32
0:56:10
0:55:40
0:47:18
0:00:00
0:58:58
0:59:36
0:42:24
0:43:04
0:56:08
0:54:44
0:44:42
0:00:00
1:03:24
0:40:32
0:40:02
0:00:00
0:34:16
0:32:18
0:32:06
0:28:26
0:55:58
1:06:00
0:58:08
3
1
1
2
11
3
16
3
3
15
10
2
2
3
16
2
2
1
1
3
3
1
16
2
1
1
16
1
1
1
1
3
2
3
0:13:36
0:03:18
0:01:58
0:12:10
0:14:50
0:09:50
0:14:06
0:09:54
0:09:22
0:09:26
0:00:24
0:02:26
0:05:58
0:07:56
0:14:06
0:02:46
0:10:00
0:03:10
0:02:36
0:07:28
0:06:48
0:02:22
0:07:28
0:13:50
0:02:34
0:02:48
0:18:00
0:00:42
0:01:44
0:03:10
0:02:28
0:07:24
0:22:38
0:06:00
4:31:58 PM
4:32:00 PM
4:38:02 PM
4:38:30 PM
4:39:28 PM
4:40:54 PM
4:42:22 PM
4:44:06 PM
4:45:22 PM
4:46:08 PM
4:46:42 PM
4:46:46 PM
4:49:26 PM
4:50:00 PM
4:51:38 PM
4:54:16 PM
4:55:00 PM
4:57:06 PM
4:57:16 PM
4:57:34 PM
4:58:22 PM
4:58:36 PM
5:00:12 PM
5:00:40 PM
5:00:52 PM
5:04:38 PM
5:05:48 PM
5:07:26 PM
5:08:02 PM
5:09:08 PM
5:10:12 PM
5:13:26 PM
5:13:48 PM
5:13:52 PM
0:53:54
0:50:54
0:54:10
0:42:10
0:00:00
0:41:00
0:52:34
0:35:02
0:50:32
0:46:44
0:40:48
0:00:52
0:00:58
1:01:06
0:57:42
0:42:28
0:42:20
0:43:14
0:48:48
0:41:00
0:41:04
0:47:24
0:41:08
0:36:34
0:35:12
0:37:16
0:35:08
0:34:48
0:50:18
0:47:38
0:36:30
0:38:26
0:54:30
0:00:00
3
3
3
1
16
1
3
1
3
3
3
10
15
2
2
1
1
1
2
1
1
2
1
1
1
1
1
1
3
2
1
2
3
16
0:08:56
0:06:18
0:06:36
0:03:48
0:01:26
0:01:34
0:06:38
0:02:38
0:00:42
0:08:50
0:07:08
0:01:52
0:05:16
0:09:26
0:05:36
0:02:42
0:02:32
0:03:24
0:07:20
0:00:46
0:02:18
0:09:24
0:09:42
0:03:36
0:13:52
0:02:48
0:04:24
0:01:40
0:07:02
0:06:00
0:05:00
0:14:24
0:04:38
0:07:46
5:15:04 PM
5:15:08 PM
5:15:48 PM
5:16:06 PM
5:16:34 PM
5:17:40 PM
5:18:28 PM
5:18:46 PM
5:19:26 PM
5:20:58 PM
5:21:28 PM
5:21:48 PM
5:22:56 PM
5:24:18 PM
5:26:38 PM
5:27:46 PM
5:27:54 PM
5:29:06 PM
5:31:24 PM
5:33:04 PM
5:33:38 PM
5:35:42 PM
5:35:46 PM
5:36:50 PM
5:37:26 PM
5:39:04 PM
5:39:20 PM
5:39:36 PM
5:40:48 PM
5:42:38 PM
5:43:34 PM
5:44:22 PM
5:44:46 PM
5:46:22 PM
0:42:54
0:38:26
0:42:48
0:41:50
0:39:32
0:39:16
0:38:12
0:35:32
0:31:44
0:32:08
0:38:00
0:38:04
0:31:06
0:27:48
0:36:30
0:40:28
0:30:22
0:25:26
0:36:02
0:27:46
0:41:10
0:34:14
0:31:40
0:29:10
0:43:38
0:43:16
0:30:00
0:42:50
0:34:48
0:35:54
0:36:22
0:33:44
0:04:44
0:00:00
3
1
3
3
1
1
3
1
1
1
3
3
1
1
2
3
1
1
2
1
3
2
2
1
3
3
1
3
3
3
3
3
10
16
0:06:44
0:02:32
0:00:44
0:05:04
0:02:10
0:01:44
0:18:48
0:02:42
0:01:30
0:01:56
0:07:34
0:05:54
0:01:20
0:02:20
0:04:44
0:05:52
0:04:40
0:03:56
0:04:18
0:02:36
0:09:36
0:05:14
0:01:04
0:02:28
0:22:18
0:05:16
0:02:42
0:00:56
0:01:10
0:02:10
0:07:54
0:09:24
0:15:40
0:14:34
5:51:32 PM
5:53:28 PM
5:54:12 PM
5:56:08 PM
5:56:26 PM
5:57:32 PM
5:58:58 PM
5:59:52 PM
6:00:28 PM
6:04:58 PM
6:13:54 PM
6:14:12 PM
6:25:54 PM
1:00:32 PM
1:01:20 PM
1:01:40 PM
1:04:52 PM
1:05:56 PM
1:06:16 PM
1:09:18 PM
1:09:52 PM
1:12:08 PM
1:14:30 PM
1:15:30 PM
1:16:26 PM
1:17:38 PM
1:19:02 PM
1:20:24 PM
1:21:30 PM
1:22:04 PM
1:24:14 PM
1:27:58 PM
1:31:04 PM
1:32:40 PM
0:33:06
0:33:10
0:23:16
0:11:20
0:11:30
0:12:04
0:02:46
0:10:16
0:02:32
0:00:00
0:00:00
0:00:00
0:00:00
0:00:00
0:00:02
0:00:02
0:02:58
0:03:22
0:02:04
0:01:34
0:19:48
0:03:14
0:04:32
0:00:00
0:05:44
0:02:14
0:08:14
0:29:52
0:08:20
0:19:42
0:09:28
0:04:08
0:20:04
0:09:18
2
1
1
2
3
2
2
3
2
16
16
16
16
16
1
1
1
1
2
1
3
1
2
16
1
10
1
3
1
3
1
15
2
1
0:05:34
0:02:38
0:03:12
0:07:12
0:07:30
0:03:20
0:09:56
0:01:44
0:13:22
0:17:18
0:11:58
0:14:52
0:03:26
0:14:52
0:04:34
0:03:10
0:01:24
0:03:20
0:05:52
0:07:04
0:12:10
0:02:22
0:16:34
0:10:40
0:02:34
0:05:32
0:02:26
0:06:54
0:02:42
0:11:20
0:07:58
0:08:40
0:03:26
0:03:04
1:33:26 PM
1:34:30 PM
1:36:40 PM
1:37:52 PM
1:39:16 PM
1:49:36 PM
1:50:46 PM
1:53:28 PM
1:55:06 PM
1:55:12 PM
1:55:18 PM
1:57:20 PM
10:00:58 AM
10:03:48 AM
10:04:36 AM
10:04:40 AM
10:05:44 AM
10:07:26 AM
10:09:48 AM
10:10:12 AM
10:10:38 AM
10:10:56 AM
10:12:26 AM
10:14:06 AM
10:15:08 AM
10:15:24 AM
10:16:32 AM
10:17:38 AM
10:17:52 AM
10:20:12 AM
10:20:16 AM
10:20:40 AM
10:22:12 AM
10:22:58 AM
0:25:52
0:15:16
0:26:22
0:09:06
0:16:32
0:38:22
0:15:28
0:18:00
0:27:58
0:42:22
0:40:04
0:37:34
0:22:36
0:23:46
0:00:00
0:00:04
0:00:00
0:25:48
0:00:00
0:30:58
0:23:44
0:23:56
0:22:20
0:23:30
0:20:38
0:00:00
0:16:20
0:43:20
0:36:22
0:23:52
0:15:28
0:17:56
0:11:02
0:19:46
3
2
3
1
2
3
1
1
2
3
3
3
2
1
16
10
16
1
16
2
1
1
2
1
1
16
2
3
3
3
1
3
2
3
0:16:08
0:04:46
0:04:02
0:12:50
0:05:18
0:12:18
0:02:40
0:02:46
0:02:44
0:07:20
0:05:06
0:06:42
0:11:26
0:03:36
0:05:34
0:02:02
0:05:06
0:03:12
0:53:36
0:06:20
0:03:24
0:04:10
0:07:50
0:06:04
0:02:38
0:02:08
0:14:46
0:05:18
0:06:26
0:10:52
0:01:56
0:04:26
0:07:14
0:06:44
10:24:22 AM
10:25:48 AM
10:26:38 AM
10:29:26 AM
10:29:46 AM
10:31:06 AM
10:31:20 AM
10:31:28 AM
10:33:36 AM
10:38:10 AM
10:38:26 AM
10:38:44 AM
10:39:14 AM
10:39:20 AM
10:41:52 AM
10:42:42 AM
10:43:14 AM
10:44:48 AM
10:47:28 AM
10:48:32 AM
10:48:54 AM
10:51:46 AM
10:52:28 AM
10:55:52 AM
10:56:44 AM
10:57:40 AM
11:01:06 AM
11:02:58 AM
11:03:38 AM
11:03:48 AM
11:05:26 AM
11:06:52 AM
11:08:38 AM
11:09:50 AM
0:00:00
0:19:02
0:05:24
0:06:08
0:17:32
0:16:30
0:09:12
0:00:00
0:06:06
0:00:00
0:09:48
0:06:48
0:07:36
0:06:16
0:08:36
0:05:20
0:04:20
0:08:58
0:04:06
0:06:28
0:00:40
0:09:20
0:08:54
0:13:06
0:00:00
0:11:18
0:14:24
0:00:00
0:10:12
0:12:16
0:00:42
0:02:16
0:06:14
0:05:02
16
3
1
11
3
3
1
16
3
1
3
3
2
2
3
3
1
2
15
1
15
1
1
3
16
3
3
1
3
2
1
15
3
1
0:01:26
0:05:38
0:08:12
0:08:34
0:08:40
0:07:38
0:03:48
0:02:06
0:08:14
0:08:50
0:04:12
0:13:00
0:11:00
0:05:28
0:05:36
0:05:46
0:07:32
0:19:00
0:09:14
0:03:52
0:04:44
0:05:52
0:02:50
0:07:00
0:01:38
0:13:32
0:07:12
0:02:24
0:09:00
0:00:00
0:04:18
0:08:36
0:08:38
0:03:22
11:11:16 AM
11:12:54 AM
11:13:16 AM
11:15:14 AM
11:15:28 AM
11:16:02 AM
11:17:36 AM
11:24:44 AM
11:25:06 AM
11:25:24 AM
11:27:12 AM
11:27:26 AM
11:30:48 AM
11:30:58 AM
11:31:00 AM
11:32:12 AM
11:35:50 AM
11:36:56 AM
11:37:14 AM
11:37:36 AM
11:37:54 AM
11:39:26 AM
11:41:28 AM
11:44:28 AM
11:46:00 AM
11:46:02 AM
11:48:02 AM
11:48:30 AM
11:50:26 AM
11:52:32 AM
11:52:52 AM
11:53:10 AM
11:55:22 AM
11:55:40 AM
0:06:22
0:07:44
0:07:56
0:10:02
0:06:56
0:06:20
0:05:42
0:18:32
0:19:50
0:12:42
0:03:46
0:00:00
0:09:40
0:25:14
0:02:32
0:00:00
0:28:48
0:15:58
0:30:04
0:26:40
0:09:16
0:13:44
0:16:46
0:15:06
0:21:00
0:18:58
0:23:54
0:18:14
0:00:00
0:21:44
0:01:24
0:03:06
0:17:24
0:26:26
3
3
1
11
1
1
3
10
2
10
1
16
3
2
1
16
2
3
2
2
1
3
3
1
3
3
2
1
16
1
10
15
1
3
0:20:08
0:08:02
0:02:42
0:08:34
0:01:22
0:11:06
0:07:14
0:00:40
0:09:54
0:05:32
0:03:46
0:10:06
0:07:04
0:06:14
0:06:48
0:15:28
0:09:58
0:04:30
0:02:12
0:07:58
0:06:32
0:05:38
0:04:30
0:08:02
0:05:46
0:10:22
0:15:44
0:04:22
0:03:50
0:02:48
0:07:34
0:07:00
0:07:42
0:02:46
11:56:26 AM
11:58:48 AM
12:00:28 PM
12:03:14 PM
12:03:48 PM
12:05:24 PM
12:06:06 PM
12:06:06 PM
12:08:38 PM
12:09:00 PM
12:11:18 PM
12:11:48 PM
12:12:32 PM
12:15:10 PM
12:16:30 PM
12:18:48 PM
12:21:26 PM
12:22:40 PM
12:24:32 PM
12:25:12 PM
12:25:22 PM
12:25:46 PM
12:28:56 PM
12:29:26 PM
12:29:38 PM
12:29:52 PM
12:30:26 PM
12:30:36 PM
12:31:44 PM
12:33:28 PM
12:34:20 PM
12:34:42 PM
12:34:54 PM
12:37:24 PM
0:26:04
0:27:12
0:13:42
0:03:30
0:00:00
0:00:44
0:32:44
0:32:20
0:18:46
0:34:58
0:34:30
0:34:04
0:34:20
0:34:30
0:10:26
0:35:46
0:11:42
0:34:52
0:30:00
0:11:50
0:25:32
0:01:42
0:28:52
0:29:06
0:29:10
0:00:00
0:22:24
0:21:36
0:00:00
0:19:44
0:20:20
0:11:56
0:00:32
0:02:08
3
3
1
1
16
10
3
3
2
3
3
3
3
3
1
3
1
3
3
2
3
1
3
3
3
16
3
3
16
3
3
3
1
1
0:09:36
0:06:42
0:04:56
0:03:24
0:02:18
0:03:10
0:05:22
0:05:10
0:07:50
0:06:08
0:05:56
0:08:10
0:06:14
0:12:42
0:04:54
0:10:32
0:02:14
0:02:38
0:04:40
0:04:32
0:05:10
0:09:04
0:05:08
0:06:22
0:02:06
0:39:42
0:03:32
0:02:52
0:05:52
0:03:46
0:13:58
0:05:44
0:02:28
0:04:12
12:37:36 PM
12:39:14 PM
12:39:48 PM
12:40:30 PM
12:41:40 PM
12:43:20 PM
12:44:36 PM
12:49:34 PM
12:51:54 PM
12:52:30 PM
12:52:46 PM
12:53:38 PM
12:53:42 PM
12:55:10 PM
2:01:04 PM
2:01:34 PM
2:02:50 PM
2:04:04 PM
2:07:52 PM
2:08:40 PM
8:50:58 AM
9:02:44 AM
9:03:04 AM
9:03:54 AM
9:04:06 AM
9:05:52 AM
9:06:50 AM
9:09:36 AM
9:11:48 AM
9:12:26 AM
9:15:14 AM
9:17:14 AM
9:22:46 AM
9:22:54 AM
0:08:46
0:15:58
0:10:44
0:11:12
0:06:00
0:09:30
0:04:58
0:11:32
0:18:44
0:13:58
0:11:56
0:00:00
0:11:34
0:08:30
0:39:48
0:31:58
0:31:12
0:30:38
0:33:18
0:33:04
0:00:00
0:01:22
0:00:56
0:01:16
0:00:00
0:02:04
0:04:00
0:06:34
0:07:52
0:11:34
0:11:06
0:16:08
0:17:52
0:00:54
3
2
3
3
1
3
1
3
2
3
2
16
2
3
3
3
3
3
3
3
16
3
3
1
16
10
3
3
3
2
3
2
1
15
0:07:02
0:08:38
0:12:40
0:10:44
0:02:54
0:06:06
0:04:40
0:05:28
0:11:16
0:17:20
0:00:54
0:11:32
0:06:14
0:05:40
0:06:56
0:08:38
0:12:12
0:03:02
0:05:06
0:09:44
0:48:10
0:01:06
0:12:44
0:02:54
0:05:28
0:06:26
0:04:56
0:13:10
0:03:22
0:04:10
0:07:38
0:08:28
0:02:52
0:06:22
9:25:42 AM
9:25:44 AM
9:28:30 AM
9:28:42 AM
9:29:26 AM
9:31:04 AM
9:35:18 AM
9:40:56 AM
9:41:16 AM
9:42:30 AM
9:43:00 AM
9:44:46 AM
9:45:06 AM
9:46:28 AM
9:46:58 AM
9:47:04 AM
9:49:24 AM
9:53:02 AM
9:54:00 AM
9:55:36 AM
Start Time
0:24:00
2
0:09:18
0:19:06
1
0:02:46
0:19:34
1
0:04:28
0:18:20
1
0:02:04
0:26:06
2
0:15:42
0:19:46
1
0:01:30
0:30:30
3
0:02:58
0:25:04
1
0:04:08
0:24:14
1
0:01:32
0:37:22
3
0:02:34
0:07:18
1
0:01:36
0:08:04
1
0:01:42
0:33:30
3
0:13:56
0:03:06
10
0:14:28
0:09:44
15
0:06:02
0:29:46
3
0:03:42
0:01:34
15
0:06:10
0:43:20
2
0:08:38
0:29:10
3
0:21:22
0:33:24
2
0:26:58
Wait
Process
Time
Category Time
Step 4: Copy the mean to the student worksheet
Step 5: Group into categories and organize the serving st
s and organize the serving stations into 3 groups

Purchase answer to see full
attachment