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 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 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%