# 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

## Leave a Reply

Want to join the discussion?Feel free to contribute!