help

profileProfessordebar
heath_excel.xls
Home>Business & Finance homework help>help

CASE6

CASE 6 Student Version Copyright 2010
8/24/09 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.
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,061
Net profit (loss) ($3,173)
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 current costs $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061
Total incremental costs 0 14,525 15,050 16,575 17,100 18,625 19,150 20,675 21,200
Current + incremental costs $58,061 $72,586 $73,111 $74,636 $75,161 $76,686 $77,211 $78,736 $79,261
Monthly profit (loss) ($3,173) ($11,599) ($6,026) ($1,452) $4,122 $8,695 $14,269 $18,843 $24,416
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 current costs $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061
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,061 $72,166 $72,271 $72,376 $72,481 $72,586 $72,691 $72,796 $72,901 $73,006 $73,111 $74,216 $74,321 $74,426 $74,531 $74,636 $74,741 $74,846 $74,951 $75,056 $75,161 $76,266 $76,371 $76,476 $76,581 $76,686 $76,791 $76,896 $77,001 $77,106 $77,211 $78,316 $78,421 $78,526 $78,631 $78,736 $78,841 $78,946 $79,051 $79,156 $79,261
Monthly profit (loss) ($3,173) ($16,058) ($14,944) ($13,829) ($12,714) ($11,599) ($10,485) ($9,370) ($8,255) ($7,140) ($6,026) ($5,911) ($4,796) ($3,681) ($2,567) ($1,452) ($337) $777 $1,892 $3,007 $4,122 $4,236 $5,351 $6,466 $7,581 $8,695 $9,810 $10,925 $12,040 $13,154 $14,269 $14,384 $15,498 $16,613 $17,728 $18,843 $19,957 $21,072 $22,187 $23,302 $24,416
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 current costs $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061
Total incremental costs 7,000 21,525 22,050 23,575 24,100 25,625 26,150 27,675 28,200
Current + incremental costs $65,061 $79,586 $80,111 $81,636 $82,161 $83,686 $84,211 $85,736 $86,261
Monthly profit (loss) ($10,173) ($18,599) ($13,026) ($8,452) ($2,878) $1,695 $7,269 $11,843 $17,416
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 current costs $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061 $58,061
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,061 $79,166 $79,271 $79,376 $79,481 $79,586 $79,691 $79,796 $79,901 $80,006 $80,111 $81,216 $81,321 $81,426 $81,531 $81,636 $81,741 $81,846 $81,951 $82,056 $82,161 $83,266 $83,371 $83,476 $83,581 $83,686 $83,791 $83,896 $84,001 $84,106 $84,211 $85,316 $85,421 $85,526 $85,631 $85,736 $85,841 $85,946 $86,051 $86,156 $86,261
Monthly profit (loss) ($10,173) ($23,058) ($21,944) ($20,829) ($19,714) ($18,599) ($17,485) ($16,370) ($15,255) ($14,140) ($13,026) ($12,911) ($11,796) ($10,681) ($9,567) ($8,452) ($7,337) ($6,223) ($5,108) ($3,993) ($2,878) ($2,764) ($1,649) ($534) $581 $1,695 $2,810 $3,925 $5,040 $6,154 $7,269 $7,384 $8,498 $9,613 $10,728 $11,843 $12,957 $14,072 $15,187 $16,302 $17,416
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.