CASE STUDY AND EXCEL !

Tuleenhb
excelsample.xlsx

Sheet1

Group Names; Nour Abdulaziz
Q3) Weights of Equity and Debt; Maryam Barifah
Current Share Price; $42.09 Shrouq Al-Jaaidi
Current Shares Outstanding; 271.5 m Balquis Mekhlafi
Current portion of Long-term Debt; $54.4 m
Notes Payable; $855.3 m
Long-term Debt; $435.9 m
Market Value of Equity= Current share price * Current shares outstanding
$42.09*271m
$11,457.44 million
Market Value of Debt= Current portion of long-term debt + Notes Payable + Long-Term Debt
$5.4m + $855.3m + $435.9 m
$1,296.6 million
We = Market value of equity/ (MVE + MVD)
$11,457.44m / ($11,427.44m + $1,296.6m)
89.81%
Wd = MVD / (MVE + MVD)
$1,296.6m / ($11,427.44m + $1,296.6m)
10.19%
After Tax Cost of Debt;
Current Price= $95.60
Issued Date= 7/15/96
Maturity Date= 7/15/21
Coupon Rate (Semi Annual)= 6.75%
PMT 3.375
PAR Value= $100
N for 5 years= 40
Rate (Semi Annual)= 3.58%
Rate (Annual)= 7.16%
After Tax Cost Of Debt= 4.439%
Cost of Equity;
Risk Free Rate= 5.74%
Market Risk Premium= 5.90%
Beta= 0.69
Re= Rf+B *( Market Risk Premium) 5.74+0.69(5.90%)
9.811%
WACC
Wd= 10.19%
Rd= 7.16%
Tax Rate= 38%
We= 89.81%
Re= 9.81%
WACC= Wd x Rd x (1-T) + We x Re
10.19% x 7.16% x (1-38%) + 89.81% x9.811%
9.264%

Cash Flow

Assumption: 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011
Cost of Capital % 9.27
Terminal Value Growth rate % 3
Discounted Cash Flow
Free Cash Flow 764.1 663.1 777.6 866.2 1014 1117.6 1275.2 1351.7 1483.7 1572.7
Terminal Value 25835.42
Total Flows 764.1 663.1 777.6 866.2 1014 1117.6 1275.2 1351.7 1483.7 27408.12
Present Values of Flows 699.28 555.36 596.01 607.595 650.93 656.57 685.601 665.078 668.09 11294.57
Enterprise Value 17079
Less Current Outstanding Debt 1296.6
Equity Value 15782
Current Share Outstanding 271.5
Equity Value Per Share 58.13
Terminal Value= 1572.7*1.03/(0.0927-0.03)
Current Share price: $42.09