Write report and excel solution for the finance case study.This is the instruction:

Prepare a report based on the case entitled InBev and Anheuser-Busch. Please post your Excel sheet and analysis. (A suggested outline for the report is as follows: (1) An executive summary that includes your recommendation; (2) A statement of the issue(s) the financial managers face; (3) A presentation and discussion of your Excel findings; (4) Your analysis; (5) Concluding remarks)

Your report should at least address the following questions

1a. What is the intrinsic value? Why is it so important? How is it estimated in business valuation?

1b. What is WACC? Why is it so important in business valuation? Please estimate WACC for Anheuser in 2008 using the Excel template attached. In your estimation of cost of equity, use either CAPM or Dividend Discount Model but make sure to justify your model selection.

1c. Assess InBevs bids for Anheuser in 2008 based on discounted cash flow valuation model. How do they compare with the firms intrinsic value? Complete the attached Excel sheet by projecting free cash flows, with the terminal date at the end. What should determine the terminal date, i.e., when you stop forecasting annual cash flows and estimate a terminal value? Do you think 2012 is the appropriate terminal date for Anheuser-Busch given its circumstances at the end of 2007? Why?

1d. Could you recommend an appropriate growth-rate assumption for Anheuser-Busch in the estimation of its terminal value? Justify your growth-rate assumption and estimate Anheusers terminal value.

1e. What is your best estimate (or the range of your estimates) of Anheuser-Buschs intrinsic value?

1f. Should InBevs shareholders endorse the acquisition of Anheuser at $70 per share?

Dec.312006 Dec.31 2007 4 Assets 5 Current Assets 6 Accounts receivable 7 Inventories 8 Other Current assets 9 Investments in affiliated companies 10 Total Current Assets 11 Plant & equipment, net 12 Intangible assets, including goodwill 13 Other assets 14 Total Assets 15 16 Liabilities and shareholders' equity 17 Current liability 18 Accounts payable 19 Accrued salaries, wages and benefits 20 Arrued taxes 21 Accrued interest 22 Total Current liability 23 Retirement benefits 24 Debt 25 Deferred income taxes 26 Other long term liabilities 27 Shareholders' Equity 28 Common stock, $1 par value, authorized 1.6 billion shares 29 Capital in excess of par value 30 Retained earnings 31 Treasury stock, at cost 32 Accumulated non-owner changes in shareholder equity 33 Total shareholders' equity 34 Commitments and contingencies 35 Total liabilities and shareholders' equity 36 219.20 720.20 694.90 195.20 1,829.50 3,680.30 8,916.10 1,367.20 584.10 16,377.20 283.20 805.20 723.50 212.60 2,024.50 4,019.50 8,833.50 1,547.90 729.60 17,155.00 1,426.30 342.80 133.90 124.20 218.90 2,246.10 1,191.50 7,653.50 1,194.50 152.90 1,464.50 374.30 106.20 136.40 222.40 2,303.80 1,002.50 9,140.30 1,314.60 242.20 1,473.70 2,962.50 16,741.00 16,007.70 -1230.80 3938.70 1,482.50 3,382.10 17,923.90 18714.7 922.20 3151.6 16377.2 17155 Balance Sheet income statement & assumptions Other Info WACC cash flow projection Valuation 1Exhibit2: Income Statement and Assumptions 3 4 (Millions of dollars) Years Ended December 31 2003 2004 2005 2006 2007 5 yrs yrevenue Net sales 14.147 100.0% 14,934|100.0%) 15,036| 100.0% 15,717| 100.0%) 16,686|100.0 3.4%1 6.2%) 100.0% 7 OPERATING EXPENSES: 8 Cost of goods Sold 9 S, G& A ex 10 Depreciation, Depletion, and Amorti 7,572| 53.5%) 2.498 | 17.7%) 8771 6.2% 8,050|53.9% 2,591| 17.3% 9331 6.2% 8,601| 2,730 | 57.2% 18.2%) 6.5% 9,176| 2,833 | 989 58.4% 9,840| 2.982 | 996 | 59.0% 17.9% 6.0%) 5.4% 3.6%) 2.6% 7.2%) 5.3%) 0.8%| 56.4% 17.8% 6.2% 18.0% 979| 2720| 2 OPERATING INCOME (EBIT) 13 OTHER EXPENSES (INCOME): 14 Interest expense 15 Non-operating income/expense 3,199 | 22.6% 3,361|22.5% 2726| 18.1% 17.3% 2.894| 17.390 -2.0%) 6.4%) 19.6% 402 365| 454.5| 3.0% 437| 2.9% 455| 3.0% 508| 3.4% 484 685| 451 2.9% 2.9%) 3.8%) 7.3%) 2.9% 2.6% 597 4.1%) 13.4% 14.7%) 3.4% 17 PRETAX INCOME 18 INCOME TAXES 3,169 22.4 1,093| 7.7%| 3,404| 22.8% 1,163| 2,690| 17.9% 8501 5.7% 2,866| 901 18.2% 5.7% 3,095 970 | 18.5%) 5.8%, -0.5%) -2.4%) 8.0%) 7.7%) 20.0% 6.5% 7.8% 0 NET INCOME 2,076 | 14.7%) 2,240 | 15.0% 1,939| 12.9% 1,965| 12.5% 2,125 | 12.7%) 0.5% 8.1%) 13.6% 22 NET INCOME PER SHARE,BASIC 0.0% 4.52 0.34 6.32| 0.31 24 NET INCOME PER SHARE,DILUTED 25 Effective tax rate 26 Capital expenditures (millions ofS) 27 Working Capital 28 29 4.89 5.63 5.96 0.04% 5.3%| 6.0% 0.34 0.32 0.33 988.70 6.3% 996.201 6.0% 0.8%) 6.1% 5,640| 35.9% 6,136| 36.8% 8.8%| 51.2 Anhauser-Busch Annual Reports exoept the last three columns, 31-Dec-06 31-Dec-07 % change 4 Net Sales 15,717.10 16,685.70 6.20% 6 Gross Profit 5,552.10 5,849.60 5.40% As a % of sales 35.33% 35.06% (0.2) pts 9 Operating Income 10 As a % of sales 2,719.60 2,894.00 6.40% 17.30% 17.30% (0.0) pts Net Income 1,965.20 2,115.30 7.60% 12 Diluted EPS 2.53 2.79 10.30% 13 # of shares outstanding Operating cash flow before the Common dividend paid per share (in 715.00 14 2,502.60 2,963.10 18.40% 15 change in working capital 1.13 1.25 16 $) Return on shareholders' equity 51.60% 59.70% (8.1) pts 17 Total assets 6,377.00 988.70 49.20 17,155.00 996.20 52.34 4.80% 18 19 Capital expenditures 20 Closing stock price (in $) 0.80% 6.40% 21 1 Exhibit millions ote 3 Equity market cap as of 12/31/2007 4 Book value of debt as of 12/31/2007 7 Credit rating 8 Beta 9 10-year U.S. treasury YTM as of 12/31/2007 10 A 10-yr corporate bond yield as of 12/31/2007 11 expected annual market risk premium 12 tax rate 13 14 dividend growth rate 15 last dividend 16 closing share price 12/31/2007 0.6 410% 5.50% 4.79% 32.61% 4.75% L/ 18 Cost of Equity Ke 19 CAPM 20 Constant dividend growth (or "Dividend Discount Model") 21 22 WACC: 23 with Ke based on CAPM 24 with Ke based on Constant dividend growth model 25 26 27 Sources: 28 (a) Find relevant information from the "Other Info" tab 29 (b) Source: http://www.netadvantage.standardandpoors.com 30 (c)Souce: Center for Research on Security Price 31 (d) http://www.federalreserve.gov/releases/h15/data.htm 32 (e) http://ycharts.com 33 (f)http://people.stern.nyu.edu/adamodar/pdfiles/papers/ERP2012.pdf 34 (g) Estimated based on historical dividend payments. 35 Balance Sheet income statement & assumptions Other Info WACC cash flow projection Valuation Name Box Assumption Base Year 2007 2008 2009 2011 2012 4 Period 6 Sales 6.00% annual growth rate S 16,685.70 Historical patten (including one-year growth rate of 6.1%; b pecte OPERATING EXPENSES: 59.0% ot total revenue 17.8% of total revenue 62% of total revenue 9,840 2,982 996 Expectation of raingcammadity pric 11 De and am 13 Total operating expenses 15 OPERATING INCOME (EBIT) 13,818 2,894 17 PROVISION FOR INCOME TAXES 32.61% of operating income 970 9 Operating Cash Flows EBIT Taxes Depreciation and amortization 23 Operating Cash Flows 25 Net Capital Spend 26 Change in NWC 6.13% of revenue or depr (whichever is gr S 51.21% of incremental sales 996 S 496 8.544) S 28 FREE CASH FLOW -OCP-NCS-Chang@ in NWC) 31 15 Discounted Cash Flow Valuation Based on Constant Growth Assumption Your Growth Rate Assumption for TV WACC No. of common shares O/S (millions) Debt 2010 2011 2012 Cash flows (millions) Terminal value (millions) Free Cash Flows (with Terminal Value) 15 Value of Firm Vfim Less: Value of Long-term Debt Vd 17 Value of Equity Ve Share Value 19 21 25 36 Dec.312006 Dec.31 2007 4 Assets 5 Current Assets 6 Accounts receivable 7 Inventories 8 Other Current assets 9 Investments in affiliated companies 10 Total Current Assets 11 Plant & equipment, net 12 Intangible assets, including goodwill 13 Other assets 14 Total Assets 15 16 Liabilities and shareholders' equity 17 Current liability 18 Accounts payable 19 Accrued salaries, wages and benefits 20 Arrued taxes 21 Accrued interest 22 Total Current liability 23 Retirement benefits 24 Debt 25 Deferred income taxes 26 Other long term liabilities 27 Shareholders' Equity 28 Common stock, $1 par value, authorized 1.6 billion shares 29 Capital in excess of par value 30 Retained earnings 31 Treasury stock, at cost 32 Accumulated non-owner changes in shareholder equity 33 Total shareholders' equity 34 Commitments and contingencies 35 Total liabilities and shareholders' equity 36 219.20 720.20 694.90 195.20 1,829.50 3,680.30 8,916.10 1,367.20 584.10 16,377.20 283.20 805.20 723.50 212.60 2,024.50 4,019.50 8,833.50 1,547.90 729.60 17,155.00 1,426.30 342.80 133.90 124.20 218.90 2,246.10 1,191.50 7,653.50 1,194.50 152.90 1,464.50 374.30 106.20 136.40 222.40 2,303.80 1,002.50 9,140.30 1,314.60 242.20 1,473.70 2,962.50 16,741.00 16,007.70 -1230.80 3938.70 1,482.50 3,382.10 17,923.90 18714.7 922.20 3151.6 16377.2 17155 Balance Sheet income statement & assumptions Other Info WACC cash flow projection Valuation 1Exhibit2: Income Statement and Assumptions 3 4 (Millions of dollars) Years Ended December 31 2003 2004 2005 2006 2007 5 yrs yrevenue Net sales 14.147 100.0% 14,934|100.0%) 15,036| 100.0% 15,717| 100.0%) 16,686|100.0 3.4%1 6.2%) 100.0% 7 OPERATING EXPENSES: 8 Cost of goods Sold 9 S, G& A ex 10 Depreciation, Depletion, and Amorti 7,572| 53.5%) 2.498 | 17.7%) 8771 6.2% 8,050|53.9% 2,591| 17.3% 9331 6.2% 8,601| 2,730 | 57.2% 18.2%) 6.5% 9,176| 2,833 | 989 58.4% 9,840| 2.982 | 996 | 59.0% 17.9% 6.0%) 5.4% 3.6%) 2.6% 7.2%) 5.3%) 0.8%| 56.4% 17.8% 6.2% 18.0% 979| 2720| 2 OPERATING INCOME (EBIT) 13 OTHER EXPENSES (INCOME): 14 Interest expense 15 Non-operating income/expense 3,199 | 22.6% 3,361|22.5% 2726| 18.1% 17.3% 2.894| 17.390 -2.0%) 6.4%) 19.6% 402 365| 454.5| 3.0% 437| 2.9% 455| 3.0% 508| 3.4% 484 685| 451 2.9% 2.9%) 3.8%) 7.3%) 2.9% 2.6% 597 4.1%) 13.4% 14.7%) 3.4% 17 PRETAX INCOME 18 INCOME TAXES 3,169 22.4 1,093| 7.7%| 3,404| 22.8% 1,163| 2,690| 17.9% 8501 5.7% 2,866| 901 18.2% 5.7% 3,095 970 | 18.5%) 5.8%, -0.5%) -2.4%) 8.0%) 7.7%) 20.0% 6.5% 7.8% 0 NET INCOME 2,076 | 14.7%) 2,240 | 15.0% 1,939| 12.9% 1,965| 12.5% 2,125 | 12.7%) 0.5% 8.1%) 13.6% 22 NET INCOME PER SHARE,BASIC 0.0% 4.52 0.34 6.32| 0.31 24 NET INCOME PER SHARE,DILUTED 25 Effective tax rate 26 Capital expenditures (millions ofS) 27 Working Capital 28 29 4.89 5.63 5.96 0.04% 5.3%| 6.0% 0.34 0.32 0.33 988.70 6.3% 996.201 6.0% 0.8%) 6.1% 5,640| 35.9% 6,136| 36.8% 8.8%| 51.2 Anhauser-Busch Annual Reports exoept the last three columns, 31-Dec-06 31-Dec-07 % change 4 Net Sales 15,717.10 16,685.70 6.20% 6 Gross Profit 5,552.10 5,849.60 5.40% As a % of sales 35.33% 35.06% (0.2) pts 9 Operating Income 10 As a % of sales 2,719.60 2,894.00 6.40% 17.30% 17.30% (0.0) pts Net Income 1,965.20 2,115.30 7.60% 12 Diluted EPS 2.53 2.79 10.30% 13 # of shares outstanding Operating cash flow before the Common dividend paid per share (in 715.00 14 2,502.60 2,963.10 18.40% 15 change in working capital 1.13 1.25 16 $) Return on shareholders' equity 51.60% 59.70% (8.1) pts 17 Total assets 6,377.00 988.70 49.20 17,155.00 996.20 52.34 4.80% 18 19 Capital expenditures 20 Closing stock price (in $) 0.80% 6.40% 21 1 Exhibit millions ote 3 Equity market cap as of 12/31/2007 4 Book value of debt as of 12/31/2007 7 Credit rating 8 Beta 9 10-year U.S. treasury YTM as of 12/31/2007 10 A 10-yr corporate bond yield as of 12/31/2007 11 expected annual market risk premium 12 tax rate 13 14 dividend growth rate 15 last dividend 16 closing share price 12/31/2007 0.6 410% 5.50% 4.79% 32.61% 4.75% L/ 18 Cost of Equity Ke 19 CAPM 20 Constant dividend growth (or "Dividend Discount Model") 21 22 WACC: 23 with Ke based on CAPM 24 with Ke based on Constant dividend growth model 25 26 27 Sources: 28 (a) Find relevant information from the "Other Info" tab 29 (b) Source: http://www.netadvantage.standardandpoors.com 30 (c)Souce: Center for Research on Security Price 31 (d) http://www.federalreserve.gov/releases/h15/data.htm 32 (e) http://ycharts.com 33 (f)http://people.stern.nyu.edu/adamodar/pdfiles/papers/ERP2012.pdf 34 (g) Estimated based on historical dividend payments. 35 Balance Sheet income statement & assumptions Other Info WACC cash flow projection Valuation Name Box Assumption Base Year 2007 2008 2009 2011 2012 4 Period 6 Sales 6.00% annual growth rate S 16,685.70 Historical patten (including one-year growth rate of 6.1%; b pecte OPERATING EXPENSES: 59.0% ot total revenue 17.8% of total revenue 62% of total revenue 9,840 2,982 996 Expectation of raingcammadity pric 11 De and am 13 Total operating expenses 15 OPERATING INCOME (EBIT) 13,818 2,894 17 PROVISION FOR INCOME TAXES 32.61% of operating income 970 9 Operating Cash Flows EBIT Taxes Depreciation and amortization 23 Operating Cash Flows 25 Net Capital Spend 26 Change in NWC 6.13% of revenue or depr (whichever is gr S 51.21% of incremental sales 996 S 496 8.544) S 28 FREE CASH FLOW -OCP-NCS-Chang@ in NWC) 31 15 Discounted Cash Flow Valuation Based on Constant Growth Assumption Your Growth Rate Assumption for TV WACC No. of common shares O/S (millions) Debt 2010 2011 2012 Cash flows (millions) Terminal value (millions) Free Cash Flows (with Terminal Value) 15 Value of Firm Vfim Less: Value of Long-term Debt Vd 17 Value of Equity Ve Share Value 19 21 25 36