case study (finance)

Excell-HW2.xlsx

Balance Sheet

Exhibit1. Anheuser-Busch Balance Sheet (in Million $, except per share)
Dec.31 2006	Dec.31 2007
Assets
Current Assets	219.20	283.20
Accounts receivable	720.20	805.20
Inventories	694.90	723.50
Other Current assets	195.20	212.60
Investments in affiliated companies	1,829.50	2,024.50
Total Current Assets	3,680.30	4,019.50
Plant & equipment, net	8,916.10	8,833.50
Intangible assets, including goodwill	1,367.20	1,547.90
Other assets	584.10	729.60
Total Assets	16,377.20	17,155.00
Liabilities and shareholders' equity
Current liability	1,426.30	1,464.50
Accounts payable	342.80	374.30
Accrued salaries, wages and benefits	133.90	106.20
Arrued taxes	124.20	136.40
Accrued interest	218.90	222.40
Total Current liability	2,246.10	2,303.80
Retirement benefits	1,191.50	1,002.50
Debt	7,653.50	9,140.30
Deferred income taxes	1,194.50	1,314.60
Other long term liabilities	152.90	242.20
Shareholders' Equity
Common stock, $1 par value, authorized 1.6 billion shares	1,473.70	1,482.50
Capital in excess of par value	2,962.50	3,382.10
Retained earnings	16,741.00	17,923.90
Treasury stock, at cost	-16,007.70	-18714.70
Accumulated non-owner changes in shareholder equity	-1230.80	-922.20
Total shareholders' equity	3938.70	3151.6
Commitments and contingencies	-
Total liabilities and shareholders' equity	16377.2	17155
Source: Andrew Inkpen, InBev and Anheuser-Busch, Thunderbird School of Global Management, 2010.

Exhibit2: Income Statement and Assumptions
(Millions of dollars)	10-yr
Years Ended December 31,	2003	2004	2005	2006	2007	5 yrs	1 yr	Revenue

		Net sales	14,147	100.0%	14,934	100.0%	15,036	100.0%	15,717	100.0%	16,686	100.0%	3.4%	6.2%	100.0%
OPERATING EXPENSES:
	Cost of goods Sold	7,572	53.5%	8,050	53.9%	8,601	57.2%	9,176	58.4%	9,840	59.0%	5.4%	7.2%	56.4%
	S, G & A expenses	2,498	17.7%	2,591	17.3%	2,730	18.2%	2,833	18.0%	2,982	17.9%	3.6%	5.3%	17.8%
Depreciation, Depletion, and Amortization	877	6.2%	933	6.2%	979	6.5%	989	6.3%	996	6.0%	2.6%	0.8%	6.2%

OPERATING INCOME (EBIT)	3,199	22.6%	3,361	22.5%	2,726	18.1%	2,720	17.3%	2,894	17.3%	-2.0%	6.4%	19.6%
OTHER EXPENSES (INCOME):
	Interest expense	402	2.8%	454.5	3.0%	455	3.0%	451	2.9%	484	2.9%	3.8%	7.3%	2.9%
	Non-operating income/expense	365	2.6%	437	2.9%	508	3.4%	597	3.8%	685	4.1%	13.4%	14.7%	3.4%

PRETAX INCOME	3,169	22.4%	3,404	22.8%	2,690	17.9%	2,866	18.2%	3,095	18.5%	-0.5%	8.0%	20.0%
INCOME TAXES	1,093	7.7%	1,163	7.8%	850	5.7%	901	5.7%	970	5.8%	-2.4%	7.7%	6.5%
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%
NET INCOME PER SHARE,BASIC	4.95	4.56	5.72	6.05	6.40	0.04%	5.3%	5.8%
	0.0%
NET INCOME PER SHARE,DILUTED	4.89	4.52	5.63	5.96	6.32	0.04%	5.3%	6.0%	5 year
Effective tax rate	0.34	0.34	0.32	0.31	0.31	0.33
Capital expenditures (millions of $)	812.50	5.2%	870.00	5.2%	7.1%	5.2%
Working Capital	5,640	35.9%	6,136	36.8%	8.8%	51.2%
Source:	Anheuser-Busch Annual Reports except the last three columns.

Exhibit 1	(in Million $, except where noted)
31-Dec-06	31-Dec-07	% change
Net Sales	15,717.10	16,685.70	6.20%
Gross Profit	5,552.10	5,849.60	5.40%
As a % of sales	35.33%	35.06%	(0.2) pts
Operating Income	2,719.60	2,894.00	6.40%
As a % of sales	17.30%	17.30%	(0.0) pts
Net Income	1,965.20	2,115.30	7.60%
Diluted EPS	2.53	2.79	10.30%
# of shares outstanding	715.00
Operating cash flow before the change in working capital	2,502.60	2,963.10	18.40%
Common dividend paid per share (in $)	1.13	1.25	10.60%
Return on shareholders' equity	51.60%	59.70%	(8.1) pts
Total assets	6,377.00	17,155.00	169.00%
Capital expenditures	812.50	870.00	7.10%
Closing stock price (in $)	49.20	52.34	6.40%
Source: Andrew Inkpen, InBev and Anheuser-Busch, Thunderbird School of Global Management, 2010.

Exhibit
$ millions	Weight	Notes
Equity market cap as of 12/31/2007	37,423	0.804	(a)
Book value of debt as of 12/31/2007	9,140.30	0.196
Credit rating	A	(b)
Beta	0.6	(c)
10-year U.S. treasury YTM as of 12/31/2007	4.10%	(d)
A 10-yr corporate bond yield as of 12/31/2007	5.50%	(e)
expected annual market risk premium	4.79%	(f)
tax rate	32.61%	(g)
dividend growth rate	4.75%	(g)
last dividend	$ 1.250	(a)
closing share price 12/31/2007	$ 52.34	(a)
Cost of Equity Ke:
CAPM	6.974
Constant dividend growth (or "Dividend Discount Model")	7.25%
WACC:	0.0370645
with Ke based on CAPM	6.33%
with Ke based on Constant dividend growth model	6.56%
Sources:
(a) Find relevant information from the "Other Info" tab.
(b) Source: http://www.netadvantage.standardandpoors.com
(c)Souce: Center for Research on Security Price
(d) http://www.federalreserve.gov/releases/h15/data.htm
(e) http://ycharts.com
(f)http://people.stern.nyu.edu/adamodar/pdfiles/papers/ERP2012.pdf
(g) Estimated based on historical dividend payments.

Assumption	Base Year
2007	2008	2009	2010	2011	2012	Notes for assumptions made
Period	0	1	2	3	4	5
Sales	6.00%	annual growth rate	$ 16,685.70	$ 17,686.84	$ 18,748.05	$ 19,872.94	$ 21,065.31	$ 22,329.23	1. Historical pattern (including one-year growth rate of 6.1%); (2) Expectation of recession and (3) strategy to expand globally
OPERATING EXPENSES:
Cost of sales	59.0%	of total revenue	9,840	10,430	11,056	11,719	12,423	13,168	Expectation of rising commodity prices
S G & A	17.8%	of total revenue	2,982	3,150	3,339	3,540	3,752	3,977
Dep. and am.	6.2%	of total revenue	996	1,104	1,171	1,241	1,315	1,394
Total operating expenses		13,818	14,685	15,566	16,500	17,490	18,539
OPERATING INCOME (EBIT)	2,894	3,002	3,182	3,373	3,575	3,790
PROVISION FOR INCOME TAXES	32.61%	of operating income	970	979	1038	1100	1166	1236
Operating Cash Flows
EBIT	3,002	3,182	3,373	3,575	3,790
Taxes	979	1038	1100	1166	1236
Depreciation and amortization	1,104	1,171	1,241	1,315	1,394
Operating Cash Flows	3,127	3,315	3,514	3,725	3,948
Net Capital Spending	5.19%	of revenue or depr (whichever is greater)	$ 870	$ 1,104	$ 1,171	$ 1,241	$ 1,315	$ 1,394
Change in NWC	51.21%	of incremental sales	$ 496	$ 513	$ 543	$ 576	$ 611	$ 647
FREE CASH FLOW (=OCF-NCS-Change in NWC)	1,510	1,601	1,697	1,799	1,907

Discounted Cash Flow Valuation Based on Constant Growth Assumption
Your Growth Rate Assumption for TV	4%
WACC	6.33%
No. of common shares O/S (millions)	715.00
Debt	9140.3
Year	2007	2008	2009	2010	2011	2012
Period	0	1	2	3	4	5
Cash flows (millions)	$ 1,510.28	$ 1,600.90	$ 1,696.95	$ 1,798.77	$ 1,906.69
Terminal value (millions)	$ 85,105.55
Free Cash Flows (with Terminal Value)	$ 1,510.28	$ 1,600.90	$ 1,696.95	$ 1,798.77	$ 87,012.24
Value of Firm Vfirm	$69,672.96
Less: Value of Long-term Debt Vd	$9,140.30
Value of Equity Ve	$60,532.66
Share Value	$ 84.66