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

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%

About this Question

Answered

Approved

Sep 13, 2020

Tutor

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.