Case #6 Columbia Memorial Hospotal

case_6_columbia_memorial_hospotal case06.xls

Case #6 Columbia Memorial Hospotal

 

Min Pages: 2 
Level of Detail: Show all work 
Other Requirements: All excel work must be shown. The four questions on page 52 must be answered as well as the questions posed in the last paragraph of the case on page 52. I would like 2 pages minmum of detail.

2

>CASE

6 CASE 6 Student Version Copyright 2

0 1

0 8

/2

4

/0

9 by FACHE COLUMBIA MEMORIAL HOSPITAL Breakeven Analysis This case illustrates the use of breakeven analysis to help make operating decisions. Note that the model extends out to Column AR. The model consists of a complete base case analysis–no changes need to be made to the existing MODEL-GENERATED DATA section. However, all relevant values in the INPUT DATA section of the student version have been replaced with zeros. Thus, students must determine the appropriate input values and enter them into the model. These cells are colored red. When this is done, any error cells will be corrected and the base case solution will appear. Note that the model does not contain any risk analyses, so students will have to create their own if required by the case. Furthermore, students must create their own graphics (charts) if needed to supplement those included with this model.

The instructor version of the model contains three graph sheets.

Figure 1

plots total monthly revenue, total

monthly costs, and total monthly profit versus number of incremental visits without the new marketing program.

Figure 2

plots the same information with the new marketing program, and

Figure

3

contains the incremental

marketing program plot. INPUT DATA: Historical Financial Data: Monthly Averages CY

20

09 Jan 20

10 Feb 2010

2009

Jan/Feb10 Combined Number of visits 14

,

5 22 1,

36

5 1,3

35 1,

21

0 1,3

50 1,2

30 Net revenue $5

48

,

7 47 $

55

,0

28 $

54

,

74

8 $

45

,7

29 $54,888 $47,0

37 Salaries and wages $

15

4,

25

0 $

13

,5

40 $13,544 $

12

,

85

4 $13,542 $12,9

52 Physicians fees 19

2,000 18

,000

18,000

16

,000

18,000

16,286 Malpractice insurance 31

,440 3,215

3,215

2,

62

0

3,215

2,

70

5 Travel and education 5,3

65 5

38 66

5 447 60

2 46

9 General insurance 8,

11

2 84

3

843

67

6

843

700 Subscriptions 189

0 0 16 0 14
Electricity 11,

82

0 1,1

24 1,029 985 1,0

77 998 Water 1,

26

0 135 142 105 1

39 110 Equipment rental

1,260 105 105 105 105 105
Building lease 155,745 12,500

12,500

12,9

79

12,500

12,910 Other operating expenses 103,779 8,152 7,9

23 8,

64

8 8,038 8,

56

1 Total operating expenses $665,220 $

58

,152 $

57

,966 $55,435 $58,0

59 $55,

81

0 Net profit (loss) ($116,4

73

) ($3,124) ($3,218) ($9,706) ($3,

17

1) ($8,773) Gross margin (%) -21.2% -5.7% -5.9%

-21.2%

-5.8% -18.7% Pro Forma Average Month: Number of visits 0
Net revenue

$0 Salaries and wages $0
Physicians fees 0
Malpractice insurance 0
Travel and education 0
General insurance 0
Subscriptions 0
Electricity 0
Water 0
Equipment rental 0
Building lease 0
Other operating expenses 0
Total operating expenses $0
Net profit (loss) $0
Gross margin (%)

0.0% Incremental Monthly Costs: Number of Additional Visits per Day 0

1-10 11-20 21-30 31-40 Variable costs: Medical supplies $

0.00 per visit Administrative supplies

0.00 per visit
Total variable costs per visit $0.00

per visit
Semifixed costs: Salaries and wages $0 $0 $0 $0
Physician fees 0 0 0 0
Total monthly semifixed costs

$0 $0 $0 $0 $0
Fixed costs: Marketing assistant’s salary

$0 $0 $0 $0 $0
Advertising expenses

0 0 0 0 0
Total monthly fixed costs

$0 $0 $0 $0 $0
MODEL-GENERATED DATA: Breakeven Analysis: Without New Marketing Program: Maximum additional monthly visits

0

150 300 450 600 75

0 900 1050 1200 Maximum additional daily visits 0 5 10 15 20 25 30 35 40
Incremental variable costs

$0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental semifixed costs

$0 $0 $0 $0 $0 $0 $0 $0 $0
Total incremental monthly costs

$0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental cost/visit NA

$0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Summary Financial Statements:

Number of Additional Visits per Day
0 5 10 15 20 25 30 35 40

Total monthly visits

0 150 300 450 600

750

900

1,050 1,200 Total daily visits

0 5 10 15 20 25 30 35 40
Net revenue per visit

$0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Total net monthly revenue

$0 $0 $0 $0 $0 $0 $0 $0 $0
Total current costs

$0 $0 $0 $0 $0 $0 $0 $0 $0
Total incremental costs

0 0 0 0 0 0 0 0 0
Current + incremental costs

$0 $0 $0 $0 $0 $0 $0 $0 $0
Monthly profit (loss)

$0 $0 $0 $0 $0 $0 $0 $0 $0
Gross margin (%) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Graphics Input: Number of Incremental Visits per Day 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

27

28 29 30 31

32 33 34

35 36 37 38 39 40
Total daily visits 45 46 47 48

49

50

51

52

53

54 55 56 57 58 59 60

61

62

63

64 65 66 67

68 69

70

71 72

73 74 75

76

77

78

79

80

81 82

83

84 85
Total monthly visits

1,350 1,380 1,410 1,440 1,470 1,500 1,530 1,560 1,590 1,620 1,650 1,680 1,710 1,740 1,770 1,800 1,830 1,860 1,890 1,920 1,950 1,980 2,010 2,040 2,070 2,100 2,130 2,160 2,190 2,220 2,250 2,280 2,310 2,340 2,370 2,400 2,430 2,460 2,490 2,520 2,550 Total monthly revenue

$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total current costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental variable costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental semifixed costs 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Incremental fixed costs

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total incremental costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Graphical recap: Incremental visits

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

Total monthly revenue $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Total monthly costs

$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Monthly profit (loss) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
With New Marketing Program:

Maximum additional monthly visits 0 150 300 450 600 750 900 1050 1200

Maximum additional daily visits 0 5 10 15 20 25 30 35 40

Incremental variable costs $0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental semifixed costs $0 $0 $0 $0 $0 $0 $0 $0 $0

Incremental fixed costs $0 $0 $0 $0 $0 $0 $0 $0 $0

Total incremental monthly costs $0 $0 $0 $0 $0 $0 $0 $0 $0

Incremental cost/visit NA $0 $0 $0 $0 $0 $0 $0 $0

Summary Financial Statements:
Number of Additional Visits per Day
0 5 10 15 20 25 30 35 40
Total monthly visits 0 150 300 450 600 750 900 1,050 1,200
Total daily visits 0 5 10 15 20 25 30 35 40
Net revenue per visit $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Total net monthly revenue $0 $0 $0 $0 $0 $0 $0 $0 $0
Total current costs $0 $0 $0 $0 $0 $0 $0 $0 $0
Total incremental costs 0 0 0 0 0 0 0 0 0
Current + incremental costs $0 $0 $0 $0 $0 $0 $0 $0 $0
Monthly profit (loss) $0 $0 $0 $0 $0 $0 $0 $0 $0
Gross margin (%) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Graphics Input:
Number of Incremental Visits per Day
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
Total daily visits 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
Total monthly visits 1,350 1,380 1,410 1,440 1,470 1,500 1,530 1,560 1,590 1,620 1,650 1,680 1,710 1,740 1,770 1,800 1,830 1,860 1,890 1,920 1,950 1,980 2,010 2,040 2,070 2,100 2,130 2,160 2,190 2,220 2,250 2,280 2,310 2,340 2,370 2,400 2,430 2,460 2,490 2,520 2,550
Total monthly revenue $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total current costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental variable costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Incremental fixed costs 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total incremental costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Graphical recap:
Incremental visits 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
Total monthly revenue $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total monthly costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Monthly profit (loss) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Expanded Marketing Program Incremental Analysis: Added daily visits

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
Incremental monthly rev

$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Incremental monthly costs

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Incr monthly profit (loss)

$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
END

&A
Page &P
To complete this input, choose the historical values from above that represent the best estimates for the average month for the entire coming year.

Figure 1

0 0 0 0

1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10 10 10
11 11 11 11
12 12 12 12
13 13 13 13
14 14 14 14
15 15 15 15
16 16 16 16
17 17 17 17
18 18 18 18
19 19 19 19
20 20 20 20
21 21 21 21
22 22 22 22
23 23 23 23
24 24 24 24
25 25 25 25
26 26 26 26
27 27 27 27
28 28 28 28
29 29 29 29
30 30 30 30
31 31 31 31
32 32 32 32
33 33 33 33
34 34 34 34
35 35 35 35
36 36 36 36
37 37 37 37
38 38 38 38
39 39 39 39
40 40 40 40

Total Monthly Revenue
Total Monthly Costs
Total Monthly Profit
Figure 1
Breakeven without New Marketing Program
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Figure 2

0 0 0 0
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10 10 10
11 11 11 11
12 12 12 12
13 13 13 13
14 14 14 14
15 15 15 15
16 16 16 16
17 17 17 17
18 18 18 18
19 19 19 19
20 20 20 20
21 21 21 21
22 22 22 22
23 23 23 23
24 24 24 24
25 25 25 25
26 26 26 26
27 27 27 27
28 28 28 28
29 29 29 29
30 30 30 30
31 31 31 31
32 32 32 32
33 33 33 33
34 34 34 34
35 35 35 35
36 36 36 36
37 37 37 37
38 38 38 38
39 39 39 39
40 40 40 40

Total Monthly Revenue
Total Monthly Costs
Total Monthly Profit
Figure 2
Breakeven with New Marketing Program
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Figure 3

0 0 0 0
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10 10 10
11 11 11 11
12 12 12 12
13 13 13 13
14 14 14 14
15 15 15 15
16 16 16 16
17 17 17 17
18 18 18 18
19 19 19 19
20 20 20 20
21 21 21 21
22 22 22 22
23 23 23 23
24 24 24 24
25 25 25 25
26 26 26 26
27 27 27 27
28 28 28 28
29 29 29 29
30 30 30 30
31 31 31 31
32 32 32 32
33 33 33 33
34 34 34 34
35 35 35 35
36 36 36 36
37 37 37 37
38 38 38 38
39 39 39 39
40 40 40 40

Total Monthly Revenue
Total Monthly Costs
Total Monthly Profit
Figure 3
Incremental Breakeven of Marketing Program
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0