Tulsa Memorial Hospital Case Study Analysis TULSA MEMORIAL HOSPITAL Breakeven Analysis Using the historical data as a guide, construct a pro forma (forec

Tulsa Memorial Hospital Case Study Analysis TULSA MEMORIAL HOSPITAL

Breakeven Analysis

Using the historical data as a guide, construct a pro forma (forecasted) profit and loss statement for the clinic’s average month for all of 2018 assuming the status quo. With no change in volume (utilization), is the clinic projected to make a profit?

Although you are basically satisfied with the analysis thus far, you are concerned about the uncertainties inherent in the revenue and expense data supplied by the clinic’s director. Assess each element in your Question 1 pro forma profit and loss statement. Are there any items that are more uncertain than the others? How could uncertainty be worked into the analysis? Is there any additional information that you might want to get from the clinic’s director?

Suppose you just found out that the $3,215 monthly malpractice insurance charge is based on an accounting allocation scheme that divides the hospital’s total annual malpractice insurance costs by the total annual number of inpatient days and outpatient visits to obtain a per episode charge. Then, the per episode value is multiplied by each department’s projected number of patient days or outpatient visits to obtain each department’s malpractice cost allocation. Does this allocation scheme bias your breakeven analysis? (No calculations are necessary.)

After all the work thus far in the analysis, you suddenly realize that the hospital, as a for-profit corporation, must pay taxes. What effect does tax status have on your breakeven analysis?

What is your final recommendation concerning the future of the walk-in clinic?

In your opinion, what are three key learning points from this case? Model
CASE 4 TULSA MEMORIAL HOSPITAL: Breakeven Analysis
12/1/2017
Copyright 2018 Foundation of the American College of Healthcare Executives. Not for sale.
Model without Questions, Student Version
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, values in the INPUT DATA section of the student
spreadsheet have been replaced by zeros. Students must select appropriate input values and enter
them into the cells with values colored red. After this is done, any error cells will be corrected and
the base case solution will appear.
INPUT DATA:
Historical Financial Data:
Monthly Average
CY 2017
Number of visits
Jan 2018
Feb 2018
2017
14,522
1,365
1,335
1,210
Net revenue
$548,747
$55,028
$54,748
$45,729
Salaries and wages
Physicians fees
Malpractice insurance
Travel and education
General insurance
Subscriptions
Electricity
Water
Equipment rental
Building lease
Other operating expenses
$154,250
192,000
31,440
5,365
8,112
189
11,820
1,260
1,260
155,745
103,779
$13,540
18,000
3,215
538
843
0
1,124
135
105
12,500
8,152
$13,544
18,000
3,215
665
843
0
1,029
142
105
12,500
7,923
$12,854
16,000
2,620
447
676
16
985
105
105
12,979
8,648
Total operating expenses
$665,220
$58,152
$57,966
$55,435
Page 1
Net profit (loss)
Gross margin (%)
Model
($116,473)
($3,124)
($3,218)
($9,706)
-21.2%
-5.7%
-5.9%
-21.2%
Pro Forma Average Month:
Number of visits
0
Net revenue
$0
Salaries and wages
Physicians fees
Malpractice insurance
Travel and education
General insurance
Subscriptions
Electricity
Water
Equipment rental
Building lease
Other operating expenses
Total operating expenses
$0
0
0
0
0
0
0
0
0
0
0
$0
Net profit (loss)
$0
Gross margin (%)
To complete this input, choose the historical valu
from above that represent the best estimates for
average month for the entire coming year.
#DIV/0!
Incremental Monthly Costs:
Number of Additional Visits per Day
1-10
11-20
0
Variable costs:
Medical supplies
Administrative supplies
Total variable costs per visit
Semifixed costs:
Salaries and wages
Physicians fees
Total monthly semifixed costs
Fixed costs:
Marketing assistant’s salary
Advertising expenses
$0.00 per visit
0.00 per visit
$0.00 per visit
Page 2
$0
$0
0
$0
$0
0
$0
$0
0
$0
0
$0
0
Model
Total monthly fixed costs
$0
$0
$0
Maximum additional monthly visits
Maximum additional daily visits
Incremental variable costs
Incremental semifixed costs
0
0
$0
$0
150
5
$0
$0
300
10
$0
$0
Total incremental monthly costs
Incremental cost/visit
$0
NA
$0
$0.00
$0
$0.00
MODEL-GENERATED DATA:
Breakeven Analysis:
Without New Marketing Program:
Summary Financial Statements:
Total monthly visits
Total daily visits
Net revenue per visit
Number of Additional Visits per Day
0
5
10
0
150
300
0
5
10
#DIV/0!
#DIV/0!
#DIV/0!
$0
Total current costs
Total incremental costs
$0
0
$0
0
$0
0
$0
0
Current + incremental costs
$0
$0
$0
$0
Monthly profit (loss)
$0
#DIV/0!
#DIV/0!
450
15
#DIV/0!
Total net monthly revenue
Gross margin (%)
#DIV/0!
15
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Graphics Input:
Total daily visits
Total monthly visits
Total monthly revenue
Total current costs
Number of Incremental Visits per Day
0
1
2
0
1
2
0
30
60
#DIV/0!
#DIV/0!
#DIV/0!
$0
Page 3
$0
$0
3
3
90
#DIV/0!
$0
Model
Incremental variable costs
Incremental semifixed costs
Incremental fixed costs
$0
0
0
$0
0
0
$0
0
0
$0
0
0
Total incremental costs
$0
$0
$0
$0
Graphical recap:
Incremental visits
Total monthly revenue
Total monthly costs
0
#DIV/0!
$0
1
#DIV/0!
$0
2
#DIV/0!
$0
3
#DIV/0!
$0
Monthly profit (loss)
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
With New Marketing Program:
Maximum additional monthly visits
Maximum additional daily visits
Incremental variable costs
Incremental semifixed costs
Incremental fixed costs
0
0
$0
$0
$0
150
5
$0
$0
$0
300
10
$0
$0
$0
Total incremental monthly costs
Incremental cost/visit
$0
NA
$0
$0
$0
$0
Summary Financial Statements:
Total monthly visits
Total daily visits
Net revenue per visit
Number of Additional Visits per Day
0
5
10
0
150
300
0
5
10
#DIV/0!
#DIV/0!
#DIV/0!
$0
Total current costs
Total incremental costs
$0
0
$0
0
$0
0
$0
0
Current + incremental costs
$0
$0
$0
$0
Monthly profit (loss)
$0
#DIV/0!
Page 4
#DIV/0!
450
15
#DIV/0!
Total net monthly revenue
Gross margin (%)
#DIV/0!
15
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Model
Graphics Input:
Total daily visits
Total monthly visits
Total monthly revenue
Number of Incremental Visits per Day
0
1
2
0
1
2
0
30
60
#DIV/0!
#DIV/0!
#DIV/0!
3
3
90
#DIV/0!
Total current costs
$0
$0
$0
$0
Incremental variable costs
Incremental fixed costs
$0
0
0
$0
0
0
$0
0
0
$0
0
0
Total incremental costs
$0
$0
$0
$0
Graphical recap:
Incremental visits
Total monthly revenue
Total monthly costs
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
Monthly profit (loss)
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
1
#DIV/0!
2
#DIV/0!
3
#DIV/0!
0
1
2
3
Expanded Marketing Program Incremental Analysis:
Added daily visits
Incremental monthly revenue
Incremental monthly costs
Incremental monthly profit (loss)
0
$0
0
$0
Page 5
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
ot for sale.
Note that the
o the existing
the student
ues and enter
rected and
Monthly Averages
Jan/Feb18
Combined
1,350
1,230
$54,888
$47,037
$13,542
18,000
3,215
602
843
0
1,077
139
105
12,500
8,038
$12,952
16,286
2,705
469
700
14
998
110
105
12,910
8,561
$58,059
$55,810
Page 6
Model
($3,171)
($8,773)
-5.8%
-18.7%
oose the historical values
the best estimates for the
re coming year.
ditional Visits per Day
21-30
31-40
$0
0
$0
$0
0
$0
$0
0
$0
0
Page 7
Model
$0
$0
450
15
$0
$0
600
20
$0
$0
750
25
$0
$0
900
30
$0
$0
1050
35
$0
$0
1200
40
$0
$0
$0
$0.00
$0
$0.00
$0
$0.00
$0
$0.00
$0
$0.00
$0
$0.00
40
1,200
40
#DIV/0!
#DIV/0!
20
25
30
600
20
#DIV/0!
750
25
#DIV/0!
900
30
#DIV/0!
35
1,050
35
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0
0
$0
0
$0
0
$0
0
$0
0
$0
$0
$0
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
4
5
6
7
8
9
10
4
120
#DIV/0!
5
150
#DIV/0!
6
180
#DIV/0!
7
210
#DIV/0!
8
240
#DIV/0!
9
270
#DIV/0!
10
300
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
Page 8
Model
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
4
#DIV/0!
$0
5
#DIV/0!
$0
6
#DIV/0!
$0
7
#DIV/0!
$0
8
#DIV/0!
$0
9
#DIV/0!
$0
10
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
450
15
$0
$0
$0
600
20
$0
$0
$0
750
25
$0
$0
$0
900
30
$0
$0
$0
1050
35
$0
$0
$0
1200
40
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
40
1,200
40
#DIV/0!
#DIV/0!
20
25
30
600
20
#DIV/0!
750
25
#DIV/0!
900
30
#DIV/0!
35
1,050
35
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0
0
$0
0
$0
0
$0
0
$0
0
$0
$0
$0
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Page 9
Model
4
5
6
7
8
9
10
4
120
#DIV/0!
5
150
#DIV/0!
6
180
#DIV/0!
7
210
#DIV/0!
8
240
#DIV/0!
9
270
#DIV/0!
10
300
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
4
5
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
10
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
4
#DIV/0!
5
#DIV/0!
6
#DIV/0!
7
#DIV/0!
8
#DIV/0!
9
#DIV/0!
10
#DIV/0!
0
#DIV/0!
6
0
#DIV/0!
7
0
#DIV/0!
8
0
#DIV/0!
Page 10
9
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
Page 11
Model
Page 12
Model
11
12
13
14
15
16
17
11
330
#DIV/0!
12
360
#DIV/0!
13
390
#DIV/0!
14
420
#DIV/0!
15
450
#DIV/0!
16
480
#DIV/0!
17
510
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
Page 13
Model
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
11
#DIV/0!
$0
12
#DIV/0!
$0
13
#DIV/0!
$0
14
#DIV/0!
$0
15
#DIV/0!
$0
16
#DIV/0!
$0
17
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Page 14
Model
11
12
13
14
15
16
17
11
330
#DIV/0!
12
360
#DIV/0!
13
390
#DIV/0!
14
420
#DIV/0!
15
450
#DIV/0!
16
480
#DIV/0!
17
510
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
11
#DIV/0!
$0
12
#DIV/0!
$0
13
#DIV/0!
$0
14
#DIV/0!
$0
15
#DIV/0!
$0
16
#DIV/0!
$0
17
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
11
#DIV/0!
12
#DIV/0!
13
#DIV/0!
14
#DIV/0!
15
#DIV/0!
16
#DIV/0!
17
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Page 15
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
Page 16
Model
Page 17
Model
18
19
20
21
22
23
24
18
540
#DIV/0!
19
570
#DIV/0!
20
600
#DIV/0!
21
630
#DIV/0!
22
660
#DIV/0!
23
690
#DIV/0!
24
720
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
Page 18
Model
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
18
#DIV/0!
$0
19
#DIV/0!
$0
20
#DIV/0!
$0
21
#DIV/0!
$0
22
#DIV/0!
$0
23
#DIV/0!
$0
24
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Page 19
Model
18
19
20
21
22
23
24
18
540
#DIV/0!
19
570
#DIV/0!
20
600
#DIV/0!
21
630
#DIV/0!
22
660
#DIV/0!
23
690
#DIV/0!
24
720
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
18
#DIV/0!
$0
19
#DIV/0!
$0
20
#DIV/0!
$0
21
#DIV/0!
$0
22
#DIV/0!
$0
23
#DIV/0!
$0
24
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
18
#DIV/0!
19
#DIV/0!
20
#DIV/0!
21
#DIV/0!
22
#DIV/0!
23
#DIV/0!
24
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Page 20
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
Page 21
Model
Page 22
Model
25
26
27
28
29
30
31
25
750
#DIV/0!
26
780
#DIV/0!
27
810
#DIV/0!
28
840
#DIV/0!
29
870
#DIV/0!
30
900
#DIV/0!
31
930
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
Page 23
Model
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
25
#DIV/0!
$0
26
#DIV/0!
$0
27
#DIV/0!
$0
28
#DIV/0!
$0
29
#DIV/0!
$0
30
#DIV/0!
$0
31
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Page 24
Model
25
26
27
28
29
30
31
25
750
#DIV/0!
26
780
#DIV/0!
27
810
#DIV/0!
28
840
#DIV/0!
29
870
#DIV/0!
30
900
#DIV/0!
31
930
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
25
#DIV/0!
$0
26
#DIV/0!
$0
27
#DIV/0!
$0
28
#DIV/0!
$0
29
#DIV/0!
$0
30
#DIV/0!
$0
31
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
25
#DIV/0!
26
#DIV/0!
27
#DIV/0!
28
#DIV/0!
29
#DIV/0!
30
#DIV/0!
31
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Page 25
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
Page 26
Model
Page 27
Model
32
33
34
35
36
37
38
32
960
#DIV/0!
33
990
#DIV/0!
34
1,020
#DIV/0!
35
1,050
#DIV/0!
36
1,080
#DIV/0!
37
1,110
#DIV/0!
38
1,140
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
Page 28
Model
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
32
#DIV/0!
$0
33
#DIV/0!
$0
34
#DIV/0!
$0
35
#DIV/0!
$0
36
#DIV/0!
$0
37
#DIV/0!
$0
38
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Page 29
Model
32
33
34
35
36
37
38
32
960
#DIV/0!
33
990
#DIV/0!
34
1,020
#DIV/0!
35
1,050
#DIV/0!
36
1,080
#DIV/0!
37
1,110
#DIV/0!
38
1,140
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
32
#DIV/0!
$0
33
#DIV/0!
$0
34
#DIV/0!
$0
35
#DIV/0!
$0
36
#DIV/0!
$0
37
#DIV/0!
$0
38
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
32
#DIV/0!
33
#DIV/0!
34
#DIV/0!
35
#DIV/0!
36
#DIV/0!
37
#DIV/0!
38
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Page 30
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
Page 31
Model
Page 32
Model
39
40
39
1,170
#DIV/0!
40
1,200
#DIV/0!
$0
$0
Page 33
Model
$0
0
0
$0
0
0
$0
$0
39
#DIV/0!
$0
40
#DIV/0!
$0
#DIV/0!
#DIV/0!
Page 34
Model
39
40
39
1,170
#DIV/0!
40
1,200
#DIV/0!
$0
$0
$0
0
0
$0
0
0
$0
$0
39
#DIV/0!
$0
40
#DIV/0!
$0
#DIV/0!
#DIV/0!
39
#DIV/0!
40
#DIV/0!
0
#DIV/0!
0
#DIV/0!
END
Page 35
Model
CASE 4 TULSA MEMORIAL HOSPITAL: Breakeven Analysis
12/1/2017
Copyright 2018 Foundation of the American College of Healthcare Executives. Not for sale.
Model without Questions, Student Version
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, values in the INPUT DATA section of the student
spreadsheet have been replaced by zeros. Students must select appropriate input values and enter
them into the cells with values colored red. After this is done, any error cells will be corrected and
the base case solution will appear.
INPUT DATA:
Historical Financial Data:
Monthly Average
CY 2017
Number of visits
Jan 2018
Feb 2018
2017
14,522
1,365
1,335
1,210
Net revenue
$548,747
$55,028
$54,748
$45,729
Salaries and wages
Physicians fees
Malpractice insurance
Travel and education
General insurance
Subscriptions
Electricity
Water
Equipment rental
Building lease
Other operating expenses
$154,250
192,000
31,440
5,365
8,112
189
11,820
1,260
1,260
155,745
103,779
$13,540
18,000
3,215
538
843
0
1,124
135
105
12,500
8,152
$13,544
18,000
3,215
665
843
0
1,029
142
105
12,500
7,923
$12,854
16,000
2,620
447
676
16
985
105
105
12,979
8,648
Total operating expenses
$665,220
$58,152
$57,966
$55,435
Page 1
Net profit (loss)
Gross margin (%)
Model
($116,473)
($3,124)
($3,218)
($9,706)
-21.2%
-5.7%
-5.9%
-21.2%
Pro Forma Average Month:
Number of visits
0
Net revenue
$0
Salaries and wages
Physicians fees
Malpractice insurance
Travel and education
General insurance
Subscriptions
Electricity
Water
Equipment rental
Building lease
Other operating expenses
Total operating expenses
$0
0
0
0
0
0
0
0
0
0
0
$0
Net profit (loss)
$0
Gross margin (%)
To complete this input, choose the historical valu
from above that represent the best estimates for
average month for the entire coming year.
#DIV/0!
Incremental Monthly Costs:
Number of Additional Visits per Day
1-10
11-20
0
Variable costs:
Medical supplies
Administrative supplies
Total variable costs per visit
Semifixed costs:
Salaries and wages
Physicians fees
Total monthly semifixed costs
Fixed costs:
Marketing assistant’s salary
Advertising expenses
$0.00 per visit
0.00 per visit
$0.00 per visit
Page 2
$0
$0
0
$0
$0
0
$0
$0
0
$0
0
$0
0
Model
Total monthly fixed costs
$0
$0
$0
Maximum additional monthly visits
Maximum additional daily visits
Incremental variable costs
Incremental semifixed costs
0
0
$0
$0
150
5
$0
$0
300
10
$0
$0
Total incremental monthly costs
Incremental cost/visit
$0
NA
$0
$0.00
$0
$0.00
MODEL-GENERATED DATA:
Breakeven Analysis:
Without New Marketing Program:
Summary Financial Statements:
Total monthly visits
Total daily visits
Net revenue per visit
Number of Additional Visits per Day
0
5
10
0
150
300
0
5
10
#DIV/0!
#DIV/0!
#DIV/0!
$0
Total current costs
Total incremental costs
$0
0
$0
0
$0
0
$0
0
Current + incremental costs
$0
$0
$0
$0
Monthly profit (loss)
$0
#DIV/0!
#DIV/0!
450
15
#DIV/0!
Total net monthly revenue
Gross margin (%)
#DIV/0!
15
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Graphics Input:
Total daily visits
Total monthly visits
Total monthly revenue
Total current costs
Number of Incremental Visits per Day
0
1
2
0
1
2
0
30
60
#DIV/0!
#DIV/0!
#DIV/0!
$0
Page 3
$0
$0
3
3
90
#DIV/0!
$0
Model
Incremental variable costs
Incremental semifixed costs
Incremental fixed costs
$0
0
0
$0
0
0
$0
0
0
$0
0
0
Total incremental costs
$0
$0
$0
$0
Graphical recap:
Incremental visits
Total monthly revenue
Total monthly costs
0
#DIV/0!
$0
1
#DIV/0!
$0
2
#DIV/0!
$0
3
#DIV/0!
$0
Monthly profit (loss)
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
With New Marketing Program:
Maximum additional monthly visits
Maximum additional daily visits
Incremental variable costs
Incremental semifixed costs
Incremental fixed costs
0
0
$0
$0
$0
150
5
$0
$0
$0
300
10
$0
$0
$0
Total incremental monthly costs
Incremental cost/visit
$0
NA
$0
$0
$0
$0
Summary Financial Statements:
Total monthly visits
Total daily visits
Net revenue per visit
Number of Additional Visits per Day
0
5
10
0
150
300
0
5
10
#DIV/0!
#DIV/0!
#DIV/0!
$0
Total current costs
Total incremental costs
$0
0
$0
0
$0
0
$0
0
Current + incremental costs
$0
$0
$0
$0
Monthly profit (loss)
$0
#DIV/0!
Page 4
#DIV/0!
450
15
#DIV/0!
Total net monthly revenue
Gross margin (%)
#DIV/0!
15
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Model
Graphics Input:
Total daily visits
Total monthly visits
Total monthly revenue
Number of Incremental Visits per Day
0
1
2
0
1
2
0
30
60
#DIV/0!
#DIV/0!
#DIV/0!
3
3
90
#DIV/0!
Total current costs
$0
$0
$0
$0
Incremental variable costs
Incremental fixed costs
$0
0
0
$0
0
0
$0
0
0
$0
0
0
Total incremental costs
$0
$0
$0
$0
Graphical recap:
Incremental visits
Total monthly revenue
Total monthly costs
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
#DIV/0!
$0
Monthly profit (loss)
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
1
#DIV/0!
2
#DIV/0!
3
#DIV/0!
0
1
2
3
Expanded Marketing Program Incremental Analysis:
Added daily visits
Incremental monthly revenue
Incremental monthly costs
Incremental monthly profit (loss)
0
$0
0
$0
Page 5
0
#DIV/0!
0
#DIV/0!
0
#DIV/0!
Model
ot for sale.
Note that the
o the existing
the student
ues and enter
rected and
Monthly Averages
Jan/Feb18
Combined
1,350
1,230
$54,888
$47,037
$13,542
18,000
3,215
602
843
0
1,077
139
105
12,500
8,038
$12,952
16,286
2,705
469
700
14
998
110
105
12,910
8,561
$58,059
$55,810
Page 6
Model
($3,171)
($8,773)
-5.8%
-18.7%
oose the historical values
the best estimates for the
re coming year.
ditional Visits per Day
21-30
31-40
$0
0
$0
$0
0
$0
$0
0
$0
0
Page 7
Model
$0
$0
450
15
$0
$0
600
20
$0
$0
750
25
$0
$0
900
30
$0
$0
1050
35
$0
$0
1200
40
$0
$0
$0
$0.00
$0
$0.00
$0
$0.00
$0
$0.00
$0
$0.00
$0
$0.00
40
1,200
40
#DIV/0!
#DIV/0!
20
25
30
600
20
#DIV/0!
750
25
#DIV/0!
900
30
#DIV/0!
35
1,050
35
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0
0
$0
0
$0
0
$0
0
$0
0
$0
$0
$0
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
4
5
6
7
8
9
10
4
120
#DIV/0!
5
150
#DIV/0!
6
180
#DIV/0!
7
210
#DIV/0!
8
240
#DIV/0!
9
270
#DIV/0!
10
300
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
Page 8
Model
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$0
$0
$0
$0
$0
$0
$0
4
#DIV/0!
$0
5
#DIV/0!
$0
6
#DIV/0!
$0
7
#DIV/0!
$0
8
#DIV/0!
$0
9
#DIV/0!
$0
10
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
450
15
$0
$0
$0
600
20
$0
$0
$0
750
25
$0
$0
$0
900
30
$0
$0
$0
1050
35
$0
$0
$0
1200
40
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
40
1,200
40
#DIV/0!
#DIV/0!
20
25
30
600
20
#DIV/0!
750
25
#DIV/0!
900
30
#DIV/0!
35
1,050
35
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0
0
$0
0
$0
0
$0
0
$0
0
$0
$0
$0
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Page 9
Model
4
5
6
7
8
9
10
4
120
#DIV/0!
5
150
#DIV/0!
6
180
#DIV/0!
7
210
#DIV/0!
8
240
#DIV/0!
9
270
#DIV/0!
10
300
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
0
0
$0
0
0
$0
0
0
$0
0
0
$…
Purchase answer to see full
attachment