everything under the pro forma should be a formula FREE CASH FLOW SECTION. could you use formulas to do the calculation and put it in excel.

M&A in Wine Country

Starshine Base Case Valuation: Brief

<=History

Pro Forma =>

Operating Forecasts

2006

2007

2008

2009

2010

2011

2012

2013

Pro forma assumptions

US Sales

250

255

265

276

287

298

310

322

4.0%

annual growth

International Sales

225

240

260

281

303

328

354

382

8.0%

annual growth

Net Sales

475

495

525

556

590

626

664

705

Cost of Goods Sold

200

205

230

244

258

274

291

309

43.8%

of sales

Depreciation

40

55

46

39

33

29

25

22

20.0%

of beginning net PP&E

Marketing Expense

52

53

53

56

59

63

66

70

10.0%

of sales

Other SG&A

148

152

152

161

171

181

193

204

29.0%

of sales

EBIT

35

30

44

57

68

79

89

99

Supplementary Schedules

Net Working Capital

working cash

40

30

21

22

24

25

27

28

4.0%

of sales

A/R

175

179

181

192

204

216

229

243

126

days sales outstanding

Inventory

250

262

271

287

304

323

343

364

430

days of COGS

Other CA

33

34

34

36

38

41

43

46

6.5%

of sales

A/P

83

85

86

91

96

102

108

115

136

days of COGS

Net working capital

415

419

422

447

474

502

533

566

D NWC

25

27

29

31

33

Other assets

24

24

24

26

28

29

31

33

4.7%

of sales

D Other assets

2

2

2

2

2

Beginning net PP&E

307

277

232

195

166

143

124

110

Capital Expenditures

10

10

10

10

10

10

10

10

given

Depreciation

40

55

46

39

33

29

25

22

20%

of beginning net PP&E

Ending Net PP&E

277

232

195

166

143

124

110

98

Free Cash Flow Calculation

Pro Forma =>

2009

2010

2011

2012

2013

WACC Calculation

EBIT

57

68

79

89

99

Asset beta

0.82

EBIT(1-t)

tax rate =

40%

34

41

47

54

60

Risk-free rate

4.86%

Depreciation

39

33

29

25

22

Market Risk Premium

5.00%

Capital expenditures

10

10

10

10

10

Cost of debt

6.00%

D NWC

25

27

29

31

33

Target D/V

27%

D Other assets

2

2

2

2

2

Free cash flow

37

36

36

36

37

Implied debt beta

Terminal value

Perp. g =

3%

712

growing perpetuity

Re-levered equity beta

1.00

Discount factor

Cost of equity

9.87%

PV(FCF + TV)

WACC

8.18%

PV Enterprise

638

Less EOY 2008 Debt

235

Estimated Equity Value

403

number of shares (000,000s)

8.0

Value per share

$ 50.38