how do i find the PV enterpriseand the Estimated equity value in the M&A in Wine Country Starshine Base Case Valuation: Expanded Excel 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 Operating Forecasts 2006 2007 2008 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 10 100 272 7 90 299 45 45 45 144 20 24 140 140 20 28 132 132 20 26 126 Supplementary Schedules Net Working Capital working cash A/R Inventory Other CA A/P Net working capital D NWC Other assets 2009 2010 2011 2012 2013 Pro forma assumptions 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 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 given 20% of beginning net PP&E Pro Forma => 2009 tax rate = 40% Perp. g = 3% 2010 2011 2012 2013 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 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 40 175 30 179 21 181 250 262 271 33 83 34 85 34 86 415 419 422 24 24 24 307 10 40 277 277 10 55 232 232 10 46 195 Supplementary Schedules Net Working Capital working cash A/R Inventory Other CA A/P Net working capital D NWC Other assets 2011 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 Pro Forma => 2009 tax rate = 40% Perp. g = 3% 2010 2011 Discount factor PV(FCF + TV) 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 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 2012 2013 WACC Calculation Asset beta Risk-free rate Market Risk Premium Cost of debt Target D/V Implied debt beta growing perpetuity 0.82 4.86% 5.00% 6.00% 27% Re-levered equity beta Cost of equity WACC