General Mills in a group project and I need help with the following 4 questions. All questions are focused around General Mills

dcfandeva3-1.xls

Home >Business & Finance homework help >Accounting homework help >General Mills in a group project and I need help with the following 4 questions. All questions are focused around General Mills

Sheet: FCFF Valuation

Sheet: EVA Valuation

INPUTS FOR VALUATION

Current Inputs

Enter the current revenues of the firm =

12406.0

Enter current capital invested in the firm =

20000.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 the value of current debt outstanding =

0.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.0

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

See capital expenditure worksheet (capex.xls) for details.

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

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

1.0

0.99

0.98

0.97

0.96

0.95

After-tax Cost of Debt

0.054400000000000004

0.05312

0.051840000000000004

0.05056

0.04928

0.048

Proportion of Debt

0.0

0.010000000000000009

0.020000000000000018

0.030000000000000027

0.040000000000000036

0.050000000000000044

Cost of Capital

0.13375

0.13131020000000002

0.12887780000000001

0.1264528

0.12403520000000001

0.121625

Cumulative WACC

1.13375

1.2853890625000002

1.4573098496093753

1.6522250419946292

1.8732101413614108

2.119181739665606

2.392297220073882

2.6948099019844407

3.029061187139061

3.39747075402485

Present Value

2349.35170893054

2590.2444420402867

2855.8373120620577

3148.6629680948818

3471.5137465213693

3545.5802985652567

3532.480232327219

3416.0888286049662

3192.778198907477

52264.958909031935

FIRM VALUATION

Value of Firm

80367.496645086

- Value of Debt

0.0

Value of Equity

80367.496645086

Value of Equity per Share

53.57833109672399

Value of Firm by year

80367.496645086

88453.07182136623

96954.19830247399

105759.98248167988

114703.08033391708

123541.7425727191

132250.30447305375

140843.69012310874

149448.0591001251

158313.7584787665

$ Value of Debt

0.0

1235.417425727192

2645.0060894610774

4225.310703693266

5977.9223640050095

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)

2675.0

2716.979109375

2769.4529960937502

2835.0453544921875

2917.0358024902343

2964.443232060147

3038.767822353046

3133.697769006417

3243.6086661588956

3364.2378030482064

3524.0724796560758

EVA

302.44000000000005

1004.8208906250002

1882.7970039062498

2980.2671455078125

4352.104822509766

5552.081924189853

6614.860278896953

7441.162271048585

7937.543349525924

8031.936366784401

8555.87214036649

Terminal EVA

138837.68179093694

266.76074972436606

781.725097824224

1291.9675279837875

1803.7900829235225

2323.3404124893027

2619.9177825427746

2765.066239843168

2761.293947142231

2620.463192763336

43229.10446947356

PV of EVA

60463.42950271027

+ Capital Invested

20000.0

+ PV of Chg Capital in Yr 10

-95.93285762427642

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

= Firm Value

80367.496645086

WACC

0.13375

0.13131020000000002

0.12887780000000001

0.1264528

0.12403520000000001

0.121625

ROC

0.148872

0.18321478743887334

0.22467918335413278

0.27435118300402606

0.3332998373772976

0.37724001913001687

0.4094219843172778

0.42672291977188725

0.427565890104183

0.4119981299033129

0.41691062624047676

Capital Invested

20000.0

20313.8625

20706.190625

21196.60078125

21809.6134765625

22575.879345703124

23578.675476715507

24781.56093820316

26150.710976875074

27660.7424711055

28974.90219655561

(Adjusted to reflect terminal ROC)

Calculation of Capital Invested

Initial

20000.0

20313.8625

20706.190625

21196.60078125

21809.6134765625

22575.879345703124

23578.675476715507

24781.56093820316

26150.710976875074

27660.7424711055

+ 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

20000.0

20313.8625

20706.190625

21196.60078125

21809.6134765625

22575.879345703124

23578.675476715507

24781.56093820316

26150.710976875074

27660.7424711055

29300.83127468412

Cumulated WACC

1.13375

1.2853890625000002

1.4573098496093753

1.6522250419946292

1.8732101413614108

2.119181739665606

2.392297220073882

2.6948099019844407

3.029061187139061

3.39747075402485