Estimating Overtime Expenses
A health care facility is planning its personnel budget for the coming year. One nurse with a salary of $1000 per week is required for every 5 patients. The average patient census is $300, as shown below. Which contains 52 weeks of census data. Therefore, it is estimated that 60 nurses will be needed at an average cost of $60000 per week. This translates to an estimated annual budget of $3,120,000 for nurse salaries. On those weeks when the census is below the average of 300, the nurses must be paid full salary anyway. On those weeks when the census is exceeded, $500 in overtime expense is incurred per patient in excess of 300. For example, if the census were 310, an additional $5000 would be incurred that week.
Create a histogram of the census data using the Simulate Data Range command.
What is the average weekly nursing cost?
What is the chance that on any given week actual expenses will be less than or equal to that budgeted? HINT: create an output cell that equals1 if the expense is less than or equal to the budget. Such a formula is known as an indicator. The average of the indicator is just the likelihood that the budget was met for the week.
Given your answer to part c, what is the chance that the overall nursing budget will be met for the year?
|
Week
|
Census
|
|
Average
|
|
1
|
280
|
|
300
|
|
2
|
279
|
|
|
|
3
|
260
|
|
|
|
4
|
308
|
|
|
|
5
|
303
|
|
|
|
6
|
295
|
|
|
|
7
|
287
|
|
|
|
8
|
337
|
|
|
|
9
|
284
|
|
|
|
10
|
308
|
|
|
|
11
|
270
|
|
|
|
12
|
313
|
|
|
|
13
|
267
|
|
|
|
14
|
307
|
|
|
|
15
|
277
|
|
|
|
16
|
377
|
|
|
|
17
|
297
|
|
|
|
18
|
287
|
|
|
|
19
|
272
|
|
|
|
20
|
293
|
|
|
|
21
|
278
|
|
|
|
22
|
265
|
|
|
|
23
|
281
|
|
|
|
24
|
301
|
|
|
|
25
|
294
|
|
|
|
26
|
283
|
|
|
|
27
|
278
|
|
|
|
28
|
342
|
|
|
|
29
|
282
|
|
|
|
30
|
274
|
|
|
|
31
|
363
|
|
|
|
32
|
340
|
|
|
|
33
|
322
|
|
|
|
34
|
301
|
|
|
|
35
|
286
|
|
|
|
36
|
381
|
|
|
|
37
|
309
|
|
|
|
38
|
364
|
|
|
|
39
|
285
|
|
|
|
40
|
269
|
|
|
|
41
|
387
|
|
|
|
42
|
276
|
|
|
|
43
|
288
|
|
|
|
44
|
294
|
|
|
|
45
|
323
|
|
|
|
46
|
292
|
|
|
|
47
|
278
|
|
|
|
48
|
269
|
|
|
|
49
|
307
|
|
|
|
50
|
303
|
|
|
|
51
|
290
|
|
|
|
52
|
294
|
|
|