Answer & Explanation:cash flow estimation, breakeven analysis, and profitability analysis. Please see attached notes, problems, and mini-case. Please have results by Wednesday October 5, 2016 at 12 Noon.

chapter_11_review_notes.xlsx

chapter_11_problems_1.xlsx

chapter_11_mini_case.xlsx

Unformatted Attachment Preview

4/1/2015 UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Table of Contents

Chapter 11 — Capital Budgeting

THE IMPACT OF CASH FLOW TIMING (NO MATCHING TEXT SECTION)

CASH FLOW ESTIMATION EXAMPLE (PAGE 482)

EVALUATING PROJECTS WITH UNEQUAL LIVES (PAGE 499)

Note: Each link takes you directly to that section in the next tab, CH11MODEL.

Page numbers in parentheses correspond to the textbook.

A

B

C

D

E

F

G

H

1 4/1/2015 UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

I

J

K

L

M

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

41

42

43

44

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

Chapter 11 — Capital Budgeting

This spreadsheet model performs some of the calculations contained in Chapter 11. We recommend that you use the model in the

following manner:

1. First, recognize that you do not have to use this model at all to understand the basics of capital budgeting. However, if you do

use the model and experiment with it, this will increase your understanding of the concepts, and it will surely help you when you

use spreadsheet models for other purposes, especially any problem sets or cases assigned for this course.

2. Start by reading the chapter in its entirety.

3. Now place the text alongside your computer with this model on the screen. When you come to an explanation of a calculation

in the text, see if the model has a matching calculation. Use the Table of Contents tab for improved navigation of the model.

4. We assume that you know the basics of Excel, but that you have not encountered some of its features or that you may need a

refresher or two. So, we have built in explanations of how to do some of the functions in the model. As a result, you will learn

more about Excel at the same time you learn about the basics of capital budgeting.

5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a

yellow background, and output data are in green on a beige background. You are encouraged to change the input data to learn

more about the calculations in the model.

THE IMPACT OF CASH FLOW TIMING (NO MATCHING TEXT SECTION)

In this section, we analyze two projects (S and L) to examine the effects of cash flow timing. Along the way,

we also discuss the breakeven and profitability measures that are discussed in the text. Here are the input data:

Year (t)

0

1

2

3

4

Expected net

cash flows (CFt)

Project S Project L

($1,000)

($1,000)

500

100

400

300

300

400

100

600

Project S

0

(1,000)

1

500

2

400

3

300

4

100

3

400

4

600

Project L

0

(1,000)

1

100

2

300

Payback Period

The payback period is defined as the expected number of years required to recover the investment, and it was the

first formal method used to evaluate capital budgeting projects. First, we identify the year in which the cumulative

cash inflows exceed the initial cash outflows. That is the payback year. Then we take the previous year and add to it

unrecovered balance at the end of that year divided by the following year’s cash flow. Generally speaking, the

shorter the payback period, the better the investment.

Click fx, then Logical, then AND, then OK to get

dialog box. Then specify you want TRUE if

cumulative CF > 0 but the previous CF < 0.
Project S
There will be one TRUE.
0
1
2
3
4
Click fx, then Logical, then IF, then OK, and then
Net cash flow (1,000)

500

400

300

100

if true, payback is the previous year plus a fraction,

Cumulative cash flow (1,000)

(500)

(100)

200

300

if false, then 0.

FALSE

FALSE

FALSE

TRUE

FALSE

0.00

0.00

0.00

2.33

0.00

Click fx, then Statistical, then MAX, then OK, and

Payback =

2.33

then the range (C57 to G57) to find the payback.

Payback =

2.33

Alternatively, use nested IF statements to

find the payback.

Project L

Net cash flow

Cumulative cash flow

Payback =

0

(1,000)

(1,000)

3.33

1

100

(900)

2

300

(600)

Uses IF statement.

3

400

(200)

4

600

400

A

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

B

C

D

E

F

G

H

I

J

Discounted Payback Period

The discounted payback period is identical to the regular payback period, except that the calculation is

based on discounted cash flows. Note that both projects are assumed to have a cost of capital of 10 percent.

Project cost of capital =

10%

Project S

Net cash flow

Discounted cash flow

Disc. cum. cash flow

0

(1,000)

(1,000)

(1,000)

Discounted payback =

2.95

1

500

455

(545)

2

400

331

(215)

3

300

225

11

4

100

68

79

1

100

91

(909)

2

300

248

(661)

3

400

301

(361)

4

600

410

49

Project L

Net cash flow

Discounted cash flow

Disc. cum. cash flow

0

(1,000)

(1,000)

(1,000)

Discounted payback =

3.88

The inherent problem with both paybacks is that they ignore cash flows that occur after the payback period mark.

While the discounted payback accounts for timing issues, it still falls short of being a complete measure.

However, these two methods do provide information about a project’s liquidity and risk.

Net Present Value (NPV)

To calculate the NPV, we sum the present values of the individual net cash flows. NPV represents the contribution

(on a present value basis) of the project to owner’s wealth.

Project cost of capital =

10%

Project S

Net cash flow

Discounted net CF

NPVS =

$78.82

0

(1,000)

(1,000)

1

500

455

2

400

331

Sums the discounted CFs, or

3

300

225

$78.82

4

100

68

uses the NPV function.

Project L

Net cash flow

Discounted net CF

NPVL =

$49.18

0

(1,000)

(1,000)

1

100

91

2

300

248

Sums the discounted CFs, or

3

400

301

$49.18

4

600

410

uses the NPV function.

The NPV method of capital budgeting dictates that all independent projects that have positive NPV should accepted.

The rationale is that all such projects add wealth, which should be the goal of the financial management function.

If two mutually exclusive projects were being evaluated, the one with the higher NPV should be chosen.

K

L

M

A

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

B

C

D

E

F

G

H

I

J

K

Internal Rate of Return (IRR)

The internal rate of return is defined as the discount rate that equates the present value of a project’s cash inflows

to the present value of its outflows. In other words, the internal rate of return is the discount rate that forces NPV to zero.

The calculation of IRR used to be tedious, but Excel provides an IRR function that merely requires you to access the

function and specify the range of cash flows. The IRRs for Project S and L are shown below:

Year (t)

0

1

2

3

4

IRR =

Expected net

cash flows (CFt)

Project S Project L

($1,000)

($1,000)

500

100

400

300

300

400

100

600

14.5%

The IRR function assumes that the cash flows in the range specified

occur at end of each period, so no adjustment is necessary.

11.8%

Note that if a guess is not entered,

the function assumes a value of

10%. A guess is needed to start

the trial-and-error process that

Excel used to calculate IRR.

Projects should be accepted if their IRR is greater than the cost of capital. When mutually exclusive projects are

being evaluated, the one with the higher IRR should be chosen.

When dealing with independent projects, the NPV and IRR methods will always yield the same accept/reject result.

However, in the case of mutually exclusive projects, NPV and IRR can give conflicting results. One shortcoming of

the internal rate of return is that it assumes that cash flows received are reinvested at the project’s internal rate of

return, which is not usually true. This point is examined in a later section of this model.

Multiple IRRs

Because of the mathematics involved, it is possible for some (but not all) projects that have more than one change of

signs in the net cash flows to have more than one IRR. If you attempted to find the IRR with such a project using a

financial calculator, you would get an error message. The HP-10B says “Error – Soln” and the HP-17B says

“Many/No Solutions; Key in Guess” when such a project is evaluated. The procedure for correcting the problem is

to store in a guess for the IRR, and then the calculator will report the IRR that is closest to your guess. You can

then use a different “guess” value, and you should be able to find the other IRR. However, the nature of the

mathematics creates a scenario in which one IRR is quite extraordinary (often, a few hundred percent). Consider

the case of Project M.

0

(1.6)

1

10

2

(10)

We will solve this IRR twice, the first time using the default guess of 10 percent, and the second time we will enter a

guess of 300 percent. Notice, that the first IRR calculation is exactly as it was above.

IRR =

25.0%

when the default guess of 10% is used to start the calculation.

IRR =

400%

when a guess of 300% is used to start the calculation.

L

M

A

B

C

D

E

F

G

H

I

J

The two solutions to this problem tell us that this project will have a positive NPV for all costs of capital between

25 percent and 400 percent. We illustrate this point by creating a data table and a graph of the project NPVs.

Project M:

k

0%

25%

50%

75%

100%

125%

150%

175%

200%

225%

250%

275%

300%

325%

350%

375%

400%

425%

450%

475%

500%

525%

550%

25.0%

0.00

1

10

2

(10)

= Discount rate

NPV

0.00

(1.60)

0.00

0.62

0.85

0.90

Max.

0.87

0.80

0.71

0.62

0.53

0.44

0.36

0.28

0.20

0.13

0.06

0.00

(0.06)

(0.11)

(0.16)

(0.21)

(0.26)

(0.30)

Multiple Rates of Return

$1.50

$1.00

$0.50

$0.00

NPV

R(R)

NPV =

0

(1.6)

0%

100%

200%

300%

400%

500%

-$0.50

-$1.00

-$1.50

-$2.00

Discount Rate

NPV Profiles

NPV profiles, as illustrated in the graph above, show the relationship between NPV and discount rate (cost of capital = k).

Here we create NPV profiles for Projects S and L.

Project S

$78.82

300.00

249.74

202.77

158.79

117.55

78.82

42.39

8.08

(24.27)

k

0%

2%

4%

6%

8%

10%

12%

14%

16%

Project L

$49.18

400.00

317.63

242.00

172.44

108.35

49.18

(5.53)

(56.20)

(103.21)

k

0%

2%

4%

6%

8%

10%

12%

14%

16%

NPV Profiles of Projects S and L

500

400

Project L

300

NPV

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

200

Project S

100

0

(100)

0%

2%

4%

6%

8%

10%

(200)

Cost of Capital

12%

14%

16%

K

L

M

A

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

B

C

D

E

F

G

H

I

J

K

L

4

5

Modified Internal Rate of Return (MIRR)

The modified internal rate of return is the discount rate that causes a project’s cost (or cash outflows) to equal the

present value of the project’s terminal value. The terminal value is defined as the sum of the future values of the

project’s cash inflows, compounded at the project’s cost of capital. To find MIRR, calculate the PV of the outflows

and the FV of the inflows, and then find the rate that equates the two. Or, you can solve using the MIRR function.

Cost of capital

10%

Project S

0

(1,000)

MIRRS =

1

500

2

400

3

300

4

100

3

400

4

600

440.0

363.0

133.1

1,536.1

12.1%

Project L

0

(1,000)

PV =

1

100

(1,000)

2

300

Terminal value =

MIRRL =

11.3%

MIRRL =

11.3%

The advantage of using the MIRR, relative to the IRR, is that the MIRR assumes that cash flows received are

reinvested at the cost of capital, not the IRR rate. Because reinvestment at the cost of capital is more likely, the MIRR is a

better indicator of a project’s profitability. Moreover, it solves the multiple IRR problem, as a set of cash flows can

have but one MIRR.

CASH FLOW ESTIMATION EXAMPLE (PAGE 482)

The project being analyzed here is the same as in the text, although the analysis is in thousands of dollars. This presents you

with a good opportunity to see how changes to input values affect profitability. For example, change the tax rate input to

40 percent to see what the profitability of the project would be for a taxable hospital.

System cost

Related expenses

Weekly volume

Average charge

Uncollectable percent

Labor costs

Maintenance costs

Supplies cost/patient

Incremental overhead

Depreciation

Net salvage value

Inflation rate

Tax rate

Cost of capital

NPV =

IRR =

MIRR =

$1,500

$1,000

40

$500

25.0%

$50

$150

$15

$10

$350

$750

5.0%

0.0%

10.0%

$82

11.1%

10.7%

System cost

Related expenses

Gross revenues

Deductions

Net revenues

Labor costs

Maintenance costs

Supplies

Incremental overhead

Depreciation

Operating income

Taxes

Net op. income

Depreciation

Net salvage value

Net cash flow

0

($1,500)

(1,000)

($2,500)

1

2

3

$1,000

250

$750

50

150

30

10

350

$160

0

$160

350

$1,050

263

$788

53

158

32

11

350

$186

0

$186

350

$1,103

276

$827

55

165

33

11

350

$212

0

$212

350

$1,158

289

$868

58

174

35

12

350

$240

0

$240

350

$1,216

304

$912

61

182

36

12

350

$270

0

$270

350

750

$510

$536

$562

$590

$1,370

M

A

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

B

C

D

E

F

G

H

I

J

K

L

M

EVALUATING PROJECTS WITH UNEQUAL LIVES (PAGE 499)

Replacement Chain (Common Life) Analysis

If two mutually exclusive projects have different lives, and if the projects can be repeated, then it is necessary to deal explicitly

with those unequal lives. In this section, we use the replacement chain (common life) approach to deal with the problem.

Corporate cost of capital

Year

0

1

2

3

4

5

6

NPV =

IRR =

11.5%

Project C

($40,000)

$8,000

$14,000

$13,000

$12,000

$11,000

$10,000

Project F

($20,000)

$7,000

$13,000

$12,000

$7,165

17.5%

$5,391

25.2%

Initially, based on NPV, it would appear that Project C is the better investment. However, if the firm chooses Project F,

it would have the opportunity to make the same investment three years from now. Therefore, we must reevaluate Project F

using an extended common life of six years.

Year

0

1

2

3

4

5

6

NPV =

IRR =

Project C

($40,000)

$8,000

$14,000

$13,000

$12,000

$11,000

$10,000

Project F

($20,000)

$7,000

$13,000

($8,000)

7,000

13,000

12,000

$7,165

17.5%

$9,281

25.2%

This cell contains the Year 3 cash flow from the first project

plus the investment required to replicate the project.

On the basis of this extended analysis, it is clear that Project F is the better of the two investments. Note that the IRR sends

this signal on both the “regular” and common life analyses.

End of Model

9/1/2014

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 1

Winston Clinic is evaluating a project that costs $52,125 and has expected net cash flows of $12,000 per

year for eight years. The first inflow occurs one year after the cost outflow, and the project has a cost of

capital of 12 percent.

a. What is the project’s payback?

b. What is the project’s NPV? Its IRR?

c. Is the project financially acceptable? Explain your answer.

ANSWER

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 2

Better Health, Inc., is evaluating two investment projects, each of which requires an up-front expenditure

of $1.5 million. The projects are expected to produce the following net cash inflows:

Year

Project A

Project B

0 -$1,500,000 -$1,500,000

1

$500,000 $2,000,000

2 $1,000,000 $1,000,000

3 $2,000,000

$600,000

a. What is each project’s IRR?

b. What is each project’s NPV if the cost of capital is 10 percent? 5 percent? 15 percent?

ANSWER

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 3

Capitol Health Plans, Inc., is evaluating two different methods for providing home health services to its

members. Both methods involve contracting out for services, and the health outcomes and revenues are

not affected by the method chosen. Therefore, the incremental cash flows for the decision are all outflows.

Here are the projected flows:

Year

Method A Method B

0 -$300,000 -$120,000

1 -$66,000 -$96,000

2 -$66,000 -$96,000

3 -$66,000 -$96,000

4 -$66,000 -$96,000

5 -$66,000 -$96,000

a. What is each alternative’s IRR?

b. If the cost of capital for both methods is 9 percent, which method should be chosen? Why?

ANSWER

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 4

Great Lakes Clinic has been asked to provide exclusive healthcare services for next year’s World

Exposition. Although flattered by the request, the clinic’s managers want to conduct a financial analysis

of the project. There will be an up-front cost of $160,000 to get the clinic in operation. Then, a net cash

inflow of $1 million is expected from operations in each of the two years of the exposition. However, the

clinic has to pay the organizers of the exposition a fee for the marketing value of the opportunity. This

fee, which must be paid at the end of the second year, is $2 million.

a. What are the cash flows associated with the project?

b. What is the project’s IRR?

c. Assuming a project cost of capital of 10 percent, what is the project’s NPV?

ANSWER

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 5

Assume that you are the CFO at Porter Memorial Hospital. The CEO has asked you to

analyze two proposed capital investments: Project X and Project Y. Each project requires a net

investment outlay of $10,000, and the cost of capital for each project is 12 percent. The project’s expected

net cash flows are as follows:

Year

0

1

2

3

4

Project X Project Y

-$10,000 -$10,000

$6,500

$3,000

$3,000

$3,000

$3,000

$3,000

$1,000

$3,000

a. Calculate each project’s payback period, net present value (NPV), and internal rate of return (IRR).

b. Which project (or projects) is financially acceptable? Explain your answer.

ANSWER

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 6

The director of capital budgeting for Big Sky Health Systems, Inc., has estimated the following cash flows

in thousands of dollars for a proposed new service:

Year

0

1

2

3

Expected Net

Cash Flow

-100

70

50

20

The project’s cost of capital is 10 percent.

a. What is the project’s payback period?

b. What is the project’s NPV?

c. What is the project’s IRR?

ANSWER

UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT

Chapter 11 — Capital Budgeting

PROBLEM 7

California Health Center, a for-profit hospital, is evaluating the purchase of new diagnostic equipment.

The equipment, which costs $600,000, has an expected life of five years and an estimated pretax salvage

value of $200,000 at that time. The equipment is expected to be used 15 times a day for 250 days a year

for each year of the project’s life. On average, each procedure is expected to generate $80 in collections,

which is net of bad debt losses and contractual allowances, in its first year of use. Thus, net revenues for

Year 1 are estimated at 15 X 250 X $80 = $300,000.

Labor and maintenance costs are expected to be $100,000 during the first year of operation, while utilities

will cost another $10,000 and cash overhead will increase by $5,000 in Year 1. The cost for expendable

supplies is expected to average $5 per procedure during the first year. All costs and revenues, except

depreciation, are expected to increase at a 5 percent inflation rate after the first year.

The equipment falls into the MACRS five-year class for tax depreciation and hence is subject to the

following depreciation allowances:

Year

1

2

3

4

5

6

Allowance

0.2

0.32

0.19

0.12

0.11

0.06

The hospital’s tax rate is 40 percent, and its corporate cost of capital is …

Purchase answer to see full

attachment