Question Details

(solution) How do i calculate the estimated equity value and the PV


How do i calculate the estimated equity value and the PV enterprise in this excel spreadsheet (M&A winery spreadsheet) 


Finance Simulation: M&A in Wine Country

 

Valuation Exercise

 

Note:

 

This exercise is designed to help you determine the value of the assigned enterprise. Use assumptions supplied in the Foreground

 

Reading and in the spreadsheet to estimate free cash flows, a WACC, and terminal values for Bel Vino Corporation and Starshine

 

Vineyards.

 

Complete the valuation exercise and submit to your instructor as directed. M&A in Wine Country

 

Bel Vino Base Case Valuation: Expanded

 

<=History Pro Forma =>

 

2008

 

2009 Operating Forecasts 2006 2007 US Sales

 

International Sales

 

Net Sales

 

Cost of Goods Sold

 

Depreciation

 

Marketing Expense

 

Other SG&A

 

EBIT 330

 

29

 

359

 

160

 

24

 

23

 

107

 

45 328

 

32

 

360

 

150

 

9

 

24

 

108

 

69 330

 

36

 

366

 

140

 

9

 

24

 

111

 

82 10

 

98

 

310

 

7

 

90

 

335 10

 

99

 

291

 

7

 

90

 

317 45 144

 

20

 

24

 

140 Supplementary Schedules

 

Net Working Capital

 

working cash

 

A/R

 

Inventory

 

Other CA

 

A/P

 

Net working capital

 

D NWC

 

Other assets 2011 2012 332

 

41

 

372

 

141

 

25

 

26

 

112

 

68 333

 

46

 

379

 

144

 

24

 

27

 

114

 

71 335

 

52

 

387

 

147

 

23

 

27

 

116

 

73 337

 

59

 

395

 

150

 

23

 

28

 

119

 

76 338

 

66

 

405

 

154

 

154

 

28

 

121

 

-53 10

 

100

 

272

 

7

 

90

 

299 10

 

102

 

274

 

7

 

35

 

359

 

60 11

 

104

 

280

 

8

 

36

 

366

 

7 11

 

106

 

285

 

8

 

36

 

374

 

7 11

 

108

 

291

 

8

 

37

 

382

 

8 11

 

111

 

298

 

8

 

38

 

391

 

9 45 45 47

 

2 47

 

1 48

 

1 49

 

1 51

 

1 140

 

20

 

28

 

132 132

 

20

 

26

 

126 126

 

20

 

25

 

121 121

 

20

 

24

 

117 117

 

20

 

23

 

113 113

 

20

 

23

 

110 110

 

20

 

22

 

108 2010

 

71

 

42

 

24

 

20

 

7

 

1 2011

 

73

 

44

 

23

 

20

 

7

 

1 2012

 

76

 

46

 

23

 

20

 

8

 

1 2013

 

-53

 

-32

 

22

 

20

 

9

 

1 D Other assets

 

Beginning net PP&E

 

Capital Expenditures

 

Depreciation

 

Ending Net PP&E Free Cash Flow Calculation

 

EBIT

 

EBIT(1-t)

 

Depreciation

 

Capital expenditures

 

D NWC

 

D Other assets

 

Free cash flow

 

Terminal value

 

Discount factor

 

PV(FCF + TV)

 

PV Enterprise

 

Less EOY 2008 Debt

 

Estimated Equity Value

 

number of shares (000,000s)

 

Value per share 2013 Pro forma assumptions 2010 tax rate = 40% Perp. g = 3% Pro Forma =>

 

2009

 

68

 

41

 

25

 

20

 

60

 

2 0.5% annual growth

 

13.0% annual growth

 

38.0% of sales

 

20.0% of beginning net PP&E

 

7.0% of sales

 

30.0% of sales 2.8% of sales

 

100 days sales outstanding

 

708 days of COGS

 

2.0% of sales

 

90 days of cash op expenses 12.50% of sales given

 

20% of beginning net PP&E WACC Calculation

 

Asset beta

 

Risk-free rate

 

Market Risk Premium

 

Cost of debt

 

Target D/V

 

Implied debt beta

 

growing perpetuity

 

Re-levered equity beta

 

Cost of equity

 

WACC 301

 

10

 

$ - 0.82

 

4.86%

 

5.00%

 

6.00%

 

35% M&A in Wine Country

 

Starshine Base Case Valuation: Expanded

 

<=History Pro Forma =>

 

2007

 

2008

 

2009

 

2010 Operating Forecasts 2006 US Sales

 

International Sales

 

Net Sales

 

Cost of Goods Sold

 

Depreciation

 

Marketing Expense

 

Other SG&A

 

EBIT 250

 

225

 

475

 

200

 

40

 

52

 

148

 

35 255

 

240

 

495

 

205

 

55

 

53

 

152

 

30 265

 

260

 

525

 

230

 

46

 

53

 

152

 

44 276

 

281

 

556

 

244

 

39

 

56

 

161

 

57 287

 

303

 

590

 

258

 

33

 

59

 

171

 

68 298

 

328

 

626

 

274

 

29

 

63

 

181

 

79 40

 

175 30

 

179 21

 

181 250 262 271 33

 

83 34

 

85 34

 

86 415 419 422 22

 

192

 

287

 

36

 

91

 

447

 

25 24

 

204

 

304

 

38

 

96

 

474

 

27 25

 

216

 

323

 

41

 

102

 

502

 

29 24 24 24 26

 

2 28

 

2 29

 

2 307

 

10

 

40

 

277 277

 

10

 

55

 

232 232

 

10

 

46

 

195 195

 

10

 

39

 

166 166

 

10

 

33

 

143 143

 

10

 

29

 

124 2010

 

68

 

41

 

33

 

10

 

27

 

2 2011

 

79

 

47

 

29

 

10

 

29

 

2 Supplementary Schedules

 

Net Working Capital

 

working cash

 

A/R

 

Inventory

 

Other CA

 

A/P

 

Net working capital

 

D NWC

 

Other assets D Other assets Beginning net PP&E

 

Capital Expenditures

 

Depreciation

 

Ending Net PP&E Free Cash Flow Calculation

 

EBIT

 

EBIT(1-t)

 

Depreciation

 

Capital expenditures

 

D NWC

 

D Other assets

 

Free cash flow

 

Terminal value

 

Discount factor

 

PV(FCF + TV) tax rate = 40% Perp. g = 3% Pro Forma =>

 

2009

 

57

 

34

 

39

 

10

 

25

 

2 2011 PV Enterprise

 

Less EOY 2008 Debt

 

Estimated Equity Value

 

number of shares (000,000s)

 

Value per share 235

 

8.0

 

$ - 2012 2013 Pro forma assumptions 310

 

354

 

664

 

291

 

25

 

66

 

193

 

89 322

 

382

 

705

 

309

 

22

 

70

 

204

 

99 27

 

229

 

343

 

43

 

108

 

533

 

31 28

 

243

 

364

 

46

 

115

 

566

 

33 31

 

2 33

 

2 124

 

10

 

25

 

109 109

 

10

 

22

 

97 2012

 

89

 

54

 

25

 

10

 

31

 

2 2013

 

99

 

60

 

22

 

10

 

33

 

2 4.0% annual growth

 

8.0% annual growth

 

43.8% of sales

 

20.0% of beginning net PP&E

 

10.0% of sales

 

29.0% of sales 4.0% of sales

 

126 days sales outstanding

 

430 days of COGS

 

6.5% of sales

 

136 days of COGS 4.7% of sales given

 

20% of beginning net PP&E WACC Calculation

 

Asset beta

 

Risk-free rate

 

Market Risk Premium

 

Cost of debt

 

Target D/V

 

Implied debt beta

 

growing perpetuity

 

Re-levered equity beta

 

Cost of equity

 

WACC 0.82

 

4.86%

 

5.00%

 

6.00%

 

27%

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now