CASE6
CASE 6 Student Version Copyright 2010
8/24/09 by FACHE
COLUMBIA MEMORIAL HOSPITAL
Breakeven Analysis
This case illustrates the use of
eakeven 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 e
or cells will be co
ected
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 2009 Jan 2010 Feb 2010 2009 Jan/Feb10 Combined
Number of visits 14,522 1,365 1,335 1,210 1,350 1,230
Net revenue $548,747 $55,028 $54,748 $45,729 $54,888 $47,037
Salaries and wages $154,250 $13,540 $13,544 $12,854 $13,542 $12,952
Physicians fees 192,000 18,000 18,000 16,000 18,000 16,286
Malpractice insurance 31,440 3,215 3,215 2,620 3,215 2,705
Travel and education 5,365 538 665 447 602 469
General insurance 8,112 843 843 676 843 700
Subscriptions 189 0 0 16 0 14
Electricity 11,820 1,124 1,029 985 1,077 998
Water 1,260 135 142 105 139 110
Equipment rental 1,260 105 105 105 105 105
Building lease 155,745 12,500 12,500 12,979 12,500 12,910
Other operating expenses 103,779 8,152 7,923 8,648 8,038 8,561
Total operating expenses $665,220 $58,152 $57,966 $55,435 $58,059 $55,810
Net profit (loss) ($116,473) ($3,124) ($3,218) ($9,706) ($3,171) ($8,773)
Gross margin (%) -21.2% -5.7% -5.9% -21.2% -5.8% -18.7%
Pro Forma Average Month:
Number of visits 1,350
Net revenue $54,888
Salaries and wages $13,542
Physicians fees 18,000
Malpractice insurance 3,215
Travel and education 602
General insurance 843
Subscriptions 0
Electricity 1,077
Water 139
Equipment rental 105
Building lease 12,500
Other operating expenses 8,038
Total operating expenses 58,059
Net profit (loss) (3,171)
Gross margin (%) -5.8%
Incremental Monthly Costs:
Number of Additional Visits per Day
0 1-10 11-20 21-30 31-40
Variable costs:
Medical supplies $3.00 per visit
Administrative supplies 0.50 per visit
Total variable costs per visit $3.50 per visit
Semifixed costs:
Salaries and wages $4,000 $5,000 $6,000 $7,000
Physician fees 10,000 10,000 10,000 10,000
Total monthly semifixed costs $0 $14,000 $15,000 $16,000 $17,000
Fixed costs:
Marketing assistant's salary $3,000 $3,000 $3,000 $3,000 $3,000
Advertising expenses 4,000 4,000 4,000 4,000 4,000
Total monthly fixed costs $7,000 $7,000 $7,000 $7,000 $7,000
MODEL-GENERATED DATA:
Breakeven Analysis:
Without 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 $525 $1,050 $1,575 $2,100 $2,625 $3,150 $3,675 $4,200
Incremental semifixed costs $0 $14,000 $14,000 $15,000 $15,000 $16,000 $16,000 $17,000 $17,000
Total incremental monthly costs $0 $14,525 $15,050 $16,575 $17,100 $18,625 $19,150 $20,675 $21,200
Incremental cost/visit NA $96.83 $50.17 $36.83 $28.50 $24.83 $21.28 $19.69 $17.67
Summary Financial Statements:
Number of Additional Visits per Day
0 5 10 15 20 25 30 35 40
Total monthly visits 1,350 1,500 1,650 1,800 1,950 2,100 2,250 2,400 2,550
Total daily visits 45 50 55 60 65 70 75 80 85
Net revenue per visit $40.66 $40.66 $40.66 $40.66 $40.66 $40.66 $40.66 $40.66 $40.66
Total net monthly revenue $54,888 $60,987 $67,085 $73,184 $79,283 $85,381 $91,480 $97,579 $103,677
Total cu
ent costs $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059
Total incremental costs 0 14,525 15,050 16,575 17,100 18,625 19,150 20,675 21,200
Cu
ent + incremental costs $58,059 $72,584 $73,109 $74,634 $75,159 $76,684 $77,209 $78,734 $79,259
Monthly profit (loss) ($3,171) ($11,597) ($6,024) ($1,450) $4,124 $8,697 $14,271 $18,845 $24,418
Gross margin (%) -5.8% -19.0% -9.0% -2.0% 5.2% 10.2% 15.6% 19.3% 23.6%
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 $54,888 $56,108 $57,327 $58,547 $59,767 $60,987 $62,206 $63,426 $64,646 $65,866 $67,085 $68,305 $69,525 $70,745 $71,964 $73,184 $74,404 $75,623 $76,843 $78,063 $79,283 $80,502 $81,722 $82,942 $84,162 $85,381 $86,601 $87,821 $89,041 $90,260 $91,480 $92,700 $93,919 $95,139 $96,359 $97,579 $98,798 $100,018 $101,238 $102,458 $103,677
Total cu
ent costs $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059
Incremental variable costs $0 $105 $210 $315 $420 $525 $630 $735 $840 $945 $1,050 $1,155 $1,260 $1,365 $1,470 $1,575 $1,680 $1,785 $1,890 $1,995 $2,100 $2,205 $2,310 $2,415 $2,520 $2,625 $2,730 $2,835 $2,940 $3,045 $3,150 $3,255 $3,360 $3,465 $3,570 $3,675 $3,780 $3,885 $3,990 $4,095 $4,200
Incremental semifixed costs 0 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000
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 $14,105 $14,210 $14,315 $14,420 $14,525 $14,630 $14,735 $14,840 $14,945 $15,050 $16,155 $16,260 $16,365 $16,470 $16,575 $16,680 $16,785 $16,890 $16,995 $17,100 $18,205 $18,310 $18,415 $18,520 $18,625 $18,730 $18,835 $18,940 $19,045 $19,150 $20,255 $20,360 $20,465 $20,570 $20,675 $20,780 $20,885 $20,990 $21,095 $21,200
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 $54,888 $56,108 $57,327 $58,547 $59,767 $60,987 $62,206 $63,426 $64,646 $65,866 $67,085 $68,305 $69,525 $70,745 $71,964 $73,184 $74,404 $75,623 $76,843 $78,063 $79,283 $80,502 $81,722 $82,942 $84,162 $85,381 $86,601 $87,821 $89,041 $90,260 $91,480 $92,700 $93,919 $95,139 $96,359 $97,579 $98,798 $100,018 $101,238 $102,458 $103,677
Total monthly costs $58,059 $72,164 $72,269 $72,374 $72,479 $72,584 $72,689 $72,794 $72,899 $73,004 $73,109 $74,214 $74,319 $74,424 $74,529 $74,634 $74,739 $74,844 $74,949 $75,054 $75,159 $76,264 $76,369 $76,474 $76,579 $76,684 $76,789 $76,894 $76,999 $77,104 $77,209 $78,314 $78,419 $78,524 $78,629 $78,734 $78,839 $78,944 $79,049 $79,154 $79,259
Monthly profit (loss) ($3,171) ($16,056) ($14,942) ($13,827) ($12,712) ($11,597) ($10,483) ($9,368) ($8,253) ($7,138) ($6,024) ($5,909) ($4,794) ($3,679) ($2,565) ($1,450) ($335) $779 $1,894 $3,009 $4,124 $4,238 $5,353 $6,468 $7,583 $8,697 $9,812 $10,927 $12,042 $13,156 $14,271 $14,386 $15,500 $16,615 $17,730 $18,845 $19,959 $21,074 $22,189 $23,304 $24,418
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 $525 $1,050 $1,575 $2,100 $2,625 $3,150 $3,675 $4,200
Incremental semifixed costs $0 $14,000 $14,000 $15,000 $15,000 $16,000 $16,000 $17,000 $17,000
Incremental fixed costs $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000
Total incremental monthly costs $7,000 $21,525 $22,050 $23,575 $24,100 $25,625 $26,150 $27,675 $28,200
Incremental cost/visit NA $144 $74 $52 $40 $34 $29 $26 $24
Summary Financial Statements:
Number of Additional Visits per Day
0 5 10 15 20 25 30 35 40
Total monthly visits 1,350 1,500 1,650 1,800 1,950 2,100 2,250 2,400 2,550
Total daily visits 45 50 55 60 65 70 75 80 85
Net revenue per visit $40.66 $40.66 $40.66 $40.66 $40.66 $40.66 $40.66 $40.66 $40.66
Total net monthly revenue $54,888 $60,987 $67,085 $73,184 $79,283 $85,381 $91,480 $97,579 $103,677
Total cu
ent costs $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059
Total incremental costs 7,000 21,525 22,050 23,575 24,100 25,625 26,150 27,675 28,200
Cu
ent + incremental costs $65,059 $79,584 $80,109 $81,634 $82,159 $83,684 $84,209 $85,734 $86,259
Monthly profit (loss) ($10,171) ($18,597) ($13,024) ($8,450) ($2,876) $1,697 $7,271 $11,845 $17,418
Gross margin (%) -18.5% -30.5% -19.4% -11.5% -3.6% 2.0% 7.9% 12.1% 16.8%
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 $54,888 $56,108 $57,327 $58,547 $59,767 $60,987 $62,206 $63,426 $64,646 $65,866 $67,085 $68,305 $69,525 $70,745 $71,964 $73,184 $74,404 $75,623 $76,843 $78,063 $79,283 $80,502 $81,722 $82,942 $84,162 $85,381 $86,601 $87,821 $89,041 $90,260 $91,480 $92,700 $93,919 $95,139 $96,359 $97,579 $98,798 $100,018 $101,238 $102,458 $103,677
Total cu
ent costs $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059 $58,059
Incremental variable costs $0 $105 $210 $315 $420 $525 $630 $735 $840 $945 $1,050 $1,155 $1,260 $1,365 $1,470 $1,575 $1,680 $1,785 $1,890 $1,995 $2,100 $2,205 $2,310 $2,415 $2,520 $2,625 $2,730 $2,835 $2,940 $3,045 $3,150 $3,255 $3,360 $3,465 $3,570 $3,675 $3,780 $3,885 $3,990 $4,095 $4,200
0 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 16,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000 17,000
Incremental fixed costs 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000
Total incremental costs $7,000 $21,105 $21,210 $21,315 $21,420 $21,525 $21,630 $21,735 $21,840 $21,945 $22,050 $23,155 $23,260 $23,365 $23,470 $23,575 $23,680 $23,785 $23,890 $23,995 $24,100 $25,205 $25,310 $25,415 $25,520 $25,625 $25,730 $25,835 $25,940 $26,045 $26,150 $27,255 $27,360 $27,465 $27,570 $27,675 $27,780 $27,885 $27,990 $28,095 $28,200
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 $54,888 $56,108 $57,327 $58,547 $59,767 $60,987 $62,206 $63,426 $64,646 $65,866 $67,085 $68,305 $69,525 $70,745 $71,964 $73,184 $74,404 $75,623 $76,843 $78,063 $79,283 $80,502 $81,722 $82,942 $84,162 $85,381 $86,601 $87,821 $89,041 $90,260 $91,480 $92,700 $93,919 $95,139 $96,359 $97,579 $98,798 $100,018 $101,238 $102,458 $103,677
Total monthly costs $65,059 $79,164 $79,269 $79,374 $79,479 $79,584 $79,689 $79,794 $79,899 $80,004 $80,109 $81,214 $81,319 $81,424 $81,529 $81,634 $81,739 $81,844 $81,949 $82,054 $82,159 $83,264 $83,369 $83,474 $83,579 $83,684 $83,789 $83,894 $83,999 $84,104 $84,209 $85,314 $85,419 $85,524 $85,629 $85,734 $85,839 $85,944 $86,049 $86,154 $86,259
Monthly profit (loss) ($10,171) ($23,056) ($21,942) ($20,827) ($19,712) ($18,597) ($17,483) ($16,368) ($15,253) ($14,138) ($13,024) ($12,909) ($11,794) ($10,679) ($9,565) ($8,450) ($7,335) ($6,221) ($5,106) ($3,991) ($2,876) ($2,762) ($1,647) ($532) $583 $1,697 $2,812 $3,927 $5,042 $6,156 $7,271 $7,386 $8,500 $9,615 $10,730 $11,845 $12,959 $14,074 $15,189 $16,304 $17,418
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 $1,220 $2,439 $3,659 $4,879 $6,099 $7,318 $8,538 $9,758 $10,978 $12,197 $13,417 $14,637 $15,857 $17,076 $18,296 $19,516 $20,735 $21,955 $23,175 $24,395 $25,614 $26,834 $28,054 $29,274 $30,493 $31,713 $32,933 $34,153 $35,372 $36,592 $37,812 $39,031 $40,251 $41,471 $42,691 $43,910 $45,130 $46,350 $47,570 $48,789
Incremental monthly costs 7,000 21,105 21,210 21,315 21,420 21,525 21,630 21,735 21,840 21,945 22,050 23,155 23,260 23,365 23,470 23,575 23,680 23,785 23,890 23,995 24,100 25,205 25,310 25,415 25,520 25,625 25,730 25,835 25,940 26,045 26,150 27,255 27,360 27,465 27,570 27,675 27,780 27,885 27,990 28,095 28,200
Incr monthly profit (loss) ($7,000) ($19,885) ($18,771) ($17,656) ($16,541) ($15,426) ($14,312) ($13,197) ($12,082) ($10,967) ($9,853) ($9,738) ($8,623) ($7,508) ($6,394) ($5,279) ($4,164) ($3,050) ($1,935) ($820) $295 $409 $1,524 $2,639 $3,754 $4,868 $5,983 $7,098 $8,213 $9,327 $10,442 $10,557 $11,671 $12,786 $13,901 $15,016 $16,130 $17,245 $18,360 $19,475 $20,589
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
54888
58059
-3171
XXXXXXXXXX
72164
XXXXXXXXXX
XXXXXXXXXX
72269
XXXXXXXXXX
58547.2
72374
XXXXXXXXXX
XXXXXXXXXX
72479
XXXXXXXXXX
XXXXXXXXXX
72584
XXXXXXXXXX
62206.4
72689
XXXXXXXXXX
XXXXXXXXXX
72794
XXXXXXXXXX
XXXXXXXXXX
72899
XXXXXXXXXX
65865.6
73004
-7138.4
XXXXXXXXXX
73109
XXXXXXXXXX
XXXXXXXXXX
74214
XXXXXXXXXX
69524.8
74319
-4794.2
XXXXXXXXXX
74424
XXXXXXXXXX
XXXXXXXXXX
74529
XXXXXXXXXX
73184
74634
-1450
XXXXXXXXXX
74739
XXXXXXXXXX
XXXXXXXXXX
74844
XXXXXXXXXX
76843.2
74949
1894.2
XXXXXXXXXX
75054
XXXXXXXXXX
XXXXXXXXXX
75159
XXXXXXXXXX
80502.4
76264
4238.4
XXXXXXXXXX
76369
XXXXXXXXXX
XXXXXXXXXX
76474
XXXXXXXXXX