Valuation- Marriott International

betcab

cabello_valuation_s.s._marriott_international.xls

Home >Business & Finance homework help >Accounting homework help >Valuation- Marriott International

Sheet: FCFF Valuation

Sheet: DAE Valuation

Sheet: DAROE

Sheet: Earnings Growth Models

INPUTS FOR VALUATION

Current Inputs

Enter the current revenues of the firm =

12406.0

Enter current capital invested in the firm =

10000.0

{ As a naïve estimate, you can use BV of debt + BV of Equity)

Enter the current depreciation =

233.0

Enter the current capital expenditures for the firm =

298.0

Enter the change in Working Capital in last year =

115.0

Enter beginning shareholder's equity

20000.0

Enter the value of current debt outstanding =

10000.0

Enter the number of shares outstanding =

1500.0

High Growth Period

Your Inputs

Enter the growth rate in revenues for the next 5 years =

0.25

What will all operating expenses be as a % of revenues in the fifth year?

0.7

(Operating expenses include depreciation: This is equal to (1-Pre-tax Operating Margin))

How much debt do you plan to use in financing investments?

0.5

Enter the growth rate in capital expenditures & depreciation

0.25

Enter working capital as a percent of revenues

0.075

Enter the tax rate that you have on corporate income

0.36

What beta do you want to use to calculate cost of equity =

1.25

Enter the current long term bond rate =

0.065

Enter the market risk premium you want to use =

0.055

Enter your cost of borrowing money =

0.085

Stable Period

Enter the growth rate in revenues =

0.06

Enter operating expenses as a % of revenues in stable period =

0.75

Enter capital expenditures as a percent of depreciation in this period

2.0

How much debt do you plan to use in financing investments?

0.05

Enter interest rate of debt in stable period =

0.075

What beta do you want to use in the stable period =

1.1

ESTIMATED CASHFLOWS

Base

Growth in Revenue

0.25

0.212

0.174

0.136

0.098

0.06

Growth in Deprec'n

0.25

0.212

0.174

0.136

0.098

0.06

Revenues

12406.0

15507.5

19384.375

24230.46875

30288.0859375

37860.107421875

45886.4501953125

53870.69252929687

61197.10671328125

67194.42317118282

71226.08856145378

Operating Expenses

% of Revenues

0.7

0.71

0.72

0.73

0.74

0.75

- $ Operating Expenses

8684.199999999999

10855.25

13569.0625

16961.328125

21201.66015625

26502.0751953125

32579.379638671875

38786.89862109375

44673.887900695314

49723.87314667529

53419.566421090334

EBIT

3721.800000000001

4652.25

5815.3125

7269.140625

9086.42578125

11358.0322265625

13307.070556640625

15083.793908203123

16523.21881258594

17470.55002450753

17806.52214036345

Tax Rate

0.36

Ending equity

22381.952

25359.392

29081.192

33733.441999999995

39548.754499999995

46817.895124999995

55334.42028124999

64988.04838249999

75562.90842255499

86744.0604382398

98140.23460807241

EBIT (1-t)

2381.9520000000007

2977.44

3721.8

4652.25

5815.3125

7269.140625

8516.52515625

9653.628101249999

10574.860040055002

11181.15201568482

11396.174169832608

+ Depreciation

233.0

291.25

364.0625

455.078125

568.84765625

711.0595703125

861.80419921875

1011.7581298828125

1149.357235546875

1261.9942446304688

1337.713899308297

- Capital Expenditures

298.0

372.5

465.625

582.03125

727.5390625

909.423828125

1262.6246222233187

1615.8254163216375

1969.0262104199564

2322.227004518275

2675.427798616594

- Change in WC

115.0

232.61249999999998

290.765625

363.45703125

454.3212890625

567.901611328125

601.9757080078125

598.818175048828

549.4810637988285

449.79873434261737

302.3749042703224

= FCFF

2201.9520000000007

2663.5775

3329.471875

4161.83984375

5202.2998046875

6502.874755859375

7513.729025237619

8450.742639762346

9205.710001383091

9671.120521454395

9756.085366253988

Terminal Value (in '05)

167812.58398749254

COSTS OF EQUITY AND CAPITAL

1.0

2.0

3.0

4.0

5.0

6.0

7.0

8.0

9.0

10.0

Cost of Equity

0.13375

0.1321

0.13045

0.1288

0.12715

0.1255

Proportion of Equity

0.5

0.59

0.6799999999999999

0.77

0.86

0.95

After-tax Cost of Debt

0.054400000000000004

0.05312

0.051840000000000004

0.05056

0.04928

0.048

Proportion of Debt

0.5

0.41000000000000003

0.32000000000000006

0.22999999999999998

0.14

0.050000000000000044

Cost of Capital

0.094075

0.09971819999999999

0.1052948

0.1108048

0.11624820000000001

0.121625

Cumulative WACC

1.094075

1.1970001056249997

1.3096078905616715

1.4328092528662606

1.567600783329654

1.723919111761877

1.9054388298510216

2.116570598304898

2.3626181205307653

2.6499715494403198

Cumulative Cost of Equity

1.13375

1.2853890625000002

1.4573098496093753

1.6522250419946292

1.8732101413614108

2.120661201035253

2.3973014547103015

2.7060738820769883

3.0501511761830775

3.4329451487940537

Present Value

2434.547448758083

2781.5134345886754

3177.928198008221

3630.8390626879113

4148.297720320718

4358.515996471812

4435.063727772922

4349.351733769564

4093.39132609453

67007.7644385463

FIRM VALUATION

Value of Firm

100417.21308701874

- Value of Debt

10000.0

Value of Equity

90417.21308701874

Value of Equity per Share

60.27814205801249

Value of Firm by year

100417.21308701874

107200.38490818003

113955.78924341704

120514.3402727415

126649.42702921214

132061.09712112587

137716.26299083213

143766.32671943685

150490.6157969356

158313.7584787665

$ Value of Debt

50208.60654350937

53600.192454090015

56977.89462170852

60257.17013637075

63324.71351460607

54145.04981966161

44069.20415706629

33066.25514547047

21068.686211570985

7915.6879239383325

Base

1.0

2.0

3.0

4.0

5.0

6.0

7.0

8.0

9.0

10.0

Terminal Year

EBIT (1-t)

2381.9520000000007

2977.44

3721.8

4652.25

5815.3125

7269.140625

8516.52515625

9653.628101249999

10574.860040055002

11181.15201568482

11396.174169832608

12079.944620022565

- WACC (CI)

1337.5

1379.479109375

1431.95299609375

1497.5453544921875

1579.5358024902343

1661.2736615673828

1771.3382159375378

1903.8650488405667

2053.5629007096654

2216.4231801237393

3636.350225667729

Abnormal Earnings

1639.94

2342.320890625

3220.29700390625

4317.7671455078125

5689.604822509766

6855.251494682617

7882.289885312461

8670.994991214435

9127.589114975155

9179.750989708868

8443.594394354835

Terminal AE

137015.73053719813

1498.92831844252

1956.8259681998813

2458.9780094598486

3013.4975307217924

3629.4985834497934

3976.550551537435

4136.732054488857

4096.719002975281

3863.336624593665

55168.6985310404

PV of AE

83799.76517490947

+ Capital Invested

10000.0

+ PV of Chg Capital in Yr 10

3650.6319941112897

This reconciles the assumptions on stable growth, ROC and Capital Invested

= Firm Value

97450.39716902075

Indicated Price per share

64.96693144601383

Cost of Equity

0.13375

0.1321

0.13045

0.1288

0.12715

0.1255

0.0

ROC

0.297744

0.3608541416952185

0.43453831180032815

0.5193819636526125

0.6155273954923608

0.6772111056520188

0.7109403356611538

0.7154088857235722

0.692300916764236

0.645282846317347

0.41691062624047676

Capital Invested

10000.0

10313.8625

10706.190625

11196.60078125

11809.6134765625

12575.879345703124

13578.675476715505

14781.560938203158

16150.710976875069

17660.742471105492

28974.90219655561

(Adjusted to reflect terminal ROC)

Calculation of Capital Invested

Initial

10000.0

10313.8625

10706.190625

11196.60078125

11809.6134765625

12575.879345703124

13578.675476715505

14781.560938203158

16150.710976875069

17660.742471105492

+ Net Cap Ex

81.25

101.5625

126.953125

158.69140625

198.3642578125

400.82042300456874

604.067286438825

819.6689748730814

1060.2327598878062

1337.713899308297

+ Chg in WC

232.61249999999998

290.765625

363.45703125

454.3212890625

567.901611328125

601.9757080078125

598.818175048828

549.4810637988285

449.79873434261737

302.3749042703224

Ending

10000.0

10313.8625

10706.190625

11196.60078125

11809.6134765625

12575.879345703124

13578.675476715505

14781.560938203158

16150.710976875069

17660.742471105492

19300.83127468411

Cumulated Cost of Equity

1.094075

1.1970001056249997

1.3096078905616715

1.4328092528662606

1.567600783329654

1.723919111761877

1.9054388298510216

2.116570598304898

2.3626181205307653

2.6499715494403198

DAROE

Terminal Year

Forecast ROE

0.13302861162422294

0.14676219366773463

0.1599745292421301

0.17239013143099957

0.1838020113882474

0.18190747647905925

0.1744597314326815

0.16272007397136432

0.1479714353126636

0.1313769970215595

Cost of Equity

0.13375

0.1321

0.13045

0.1288

0.12715

0.1255

Abnormal ROE

-7.213883757770656E-4

0.013012193667734623

0.02622452924213009

0.038640131430999564

0.050052011388247386

0.049807476479059254

0.0440097314326815

0.03392007397136432

0.02082143531266359

0.005876997021559499

Present Value of AROE

-6.362852267052398E-4

0.010123155741209382

0.0179951636566235

0.0233867242348244

0.02671991266920571

0.023658423201133868

0.01865474045356233

0.012868288373211464

0.007090473295512936

0.0018017746703882641

0.014356770281978199

Total DAROE

1.1560191413509449

Beginning Equity

20000.0

Firm Value

23120.382827018897

Indicated value per share

15.413588551345931

Buffett Valuation Worksheet (January/February 1998, Computerized Investing, www.aaii.com)

Enter values into shaded cells

Date of Analysis:

34333.0

Current Stock Data

Seven Year Averages

Company:

Nike, Inc.

Return on Equity:

0.22842732048902933

Ticker:

NKE

Payout Ratio:

0.15914841308019573

Price:

48.25

P/E Ratio-High:

18.395384308765205

EPS:

2.77

P/E Ratio-Low:

9.517676591639258

DPS:

0.48

P/E Ratio:

13.956530450202232

BVPS:

11.38

Sustainable Growth

0.19207347492903906

P/E:

17.418772563176894

(ROE * (1 - Payout Ratio))

Earnings Yield:

0.05740932642487047

Dividend Yield:

0.009948186528497409

P/BV:

4.239894551845342

Gv't Bond Yield:

0.06

Historical Company Data

Price

P/E Ratio

Payout

Year

EPS

DPS

BVPS

High

Low

High

Low

ROE

Ratio

Year 8

8.7

3.2

10.874999999999998

4.0

0.3053435114503817

0.11249999999999999

Year 7

11.984

6.0

12.748936170212767

6.382978723404256

0.2772861356932153

0.13829787234042554

Year 6

4.35

18.938

8.781

17.699065420560746

8.206542056074767

0.2459770114942529

0.14018691588785046

Year 5

22.563

13.75

19.121186440677967

11.652542372881356

0.22138836772983114

0.1610169491525424

Year 4

22.313

10.781

22.538383838383837

10.88989898989899

0.17157712305026

0.20202020202020204

Year 3

11.563

14.062499999999998

8.502205882352941

0.2035928143712575

0.1764705882352941

Year 2

1.88

0.29

8.28

35.188

17.188

18.717021276595748

9.14255319148936

0.22705314009661837

0.15425531914893617

Year 1

23.88059701492537

11.847014925373134

0.2521166509877705

0.1417910447761194

EPS

DPS

BVPS

High Price

Low Price

Annually Compounded Rates of Growth (7 year)

[(Year 1 / Year 8) ^ (1/7)] - 1

0.18851974473321875

0.2284656360952806

0.2214910294366217

0.3298684243008263

0.387939500567938

Annually Compounded Rates of Growth (3 year)

[(Year 1 / Year 4) ^ (1/3)] - 1

0.3936911207406313

0.23856232963017066

0.2258927702668394

0.4208252598415636

0.4333807285676057

Projected Company Data Using Historical Earnings Growth Rate

Year

EPS

DPS

Current

2.77

0.4408411042321422

15.579150727670855

Earnings after 10 years

Year 1

3.2921996929110158

0.5239483566698958

13.29288518853185

Sum of dividends paid over 10 years

Year 2

3.9128443386293816

0.622722967122694

Year 3

4.650492754528613

0.7401185419241769

217.43089151902856

Projected price (Average P/E * EPS)

Year 4

5.52720246149603

0.8796455005200448

230.7237767075604

Total gain (Projected Price + Dividends)

Year 5

6.569189258626079

1.045476045733808

Year 6

7.80761114076647

1.2425689230002404

0.16939034404775044

Projected return using historical EPS growth rate

Year 7

9.2795

1.4768176991776762

[(Total Gain / Current Price) ^ (1/10)] - 1

Year 8

11.028868971251905

1.7552269948441515

Year 9

13.108028534408431

2.086121939861026

Year 10

15.579150727670855

2.4793971154459933

Projected Company Data Using Sustainable Growth Rate

Year

BVPS

EPS

DPS

Current

11.38

2.599502907165154

0.4137067624726896

15.063314809778905

Earnings after 10 years (BVPS * ROE)

Year 1

13.565796144692467

3.0987984636325048

0.49316885794246174

12.724579154591865

Sum of dividends paid over 10 years

Year 2

16.17142575038251

3.6939954526471674

0.587893514214256

Year 3

19.277527688815425

4.4035139956091784

0.7008122643776328

210.23161182366152

Projected price (Average P/E * EPS)

Year 4

22.98022942004697

5.24931223064449

0.835419711269533

222.9561909782534

Total gain (Projected Price + Dividends)

Year 5

27.394121939421925

6.257565871771882

0.9958816782372866

Year 6

32.65580613295652

7.459478293360469

1.1871641327944853

0.1653925081447145

Projected return using sustainable growth rate

Year 7

38.928120293522504

8.892246210323952

1.4151868730914414

[(Total Gain / Current Price) ^ (1/10)] - 1

Year 8

46.405179630755015

10.600210839865452

1.6870067334800756

Year 9

55.318383737140394

12.636230170858877

2.011035979008281

Year 10

65.94357792899099

15.063314809778905

2.3973026477037234