Research needed; must know this 100%; Excel formulas needed to be created! Must be an expert, please!!!

Module4-EnterpriseValuation.xlsx

Home >Business & Finance homework help >Financial markets homework help >Research needed; must know this 100%; Excel formulas needed to be created! Must be an expert, please!!!

Multiples Valuation Example

	(in millions)	(in millions)	(in millions)	(in millions)
						Solution Legend
						Downloaded data
						Formula/Calculation/Analysis required
Peer Companies:	JetBlue Airways	Southwest Airlines Co.	Skywest Inc.	Virgin America Inc.
Ticker	JBLU	LUV	SKYW	VA
PERIOD ENDING	12/31/2015	12/31/2015	12/31/2015	12/31/2015
From Income Statement and Balance Sheet
Balance Sheet
Current Assets
Cash and cash equivalents	318	1,583	203	496
Other current assets	1,055	2,441	815	31
Total current assets	1,373	4,024	1,018	527
Property and Equipment	6,652	15,601	3,471	439
Other assets	635	1,687	315	603
Total Assets	8,660	21,312	4,803	1,569
Current Liabilities
Current maturies of long-term debt	448	637	272	49
Other current liabilities	1,827	6,769	479	368
Total current liabilities	2,275	7,406	751	417
Long-term Debt	1,395	2,541	1,677	216
Other liabilities	1,780	4,007	868	126
Total Liabilities	5,450	13,954	3,297	759
Stockholders' Equity
Preferred stock	-	-	-	-
Common stock	4	808	642	0.442
Treasury Stcok	(366)	(3,182)	(410)	(5)
Capital surplus	1,896	1,374	-	1,252
Retained earnings	1,679	9,409	1,275	(412)
Other stockholder's equity	(3)	(1,051)	(0)	(26)
Total stock holders' equity	3,210	7,358	1,506	809
Total Liabilities and Stockholders' Equity	8,660	21,312	4,803	1,568
Income Statement
Revenues	6,416	19,820	3,096	1,530
Cost of services	3,230	11,165	1,376	781
Gross Profit	3,186	8,655	1,720	749
Depreciation and amortization	345	1,015	265	19
Other operating expenses	1,625	3,524	1,221	553
Operating Income	1,216	4,116	235	177
Interest expense	128	121	76	7
Other Income/(Expense)	9	(516)	36	(2)
Income before income taxes	1,097	3,479	194	168
Income tax expense/(benefit)	420	1,298	77	(172)
Net Income	677	2,181	118	341
Basic EPS	$ 2.15	3.30	2.31	7.82
Shares Outstanding (millions)	322	661	51	44
42461.0	$ 21.33	$ 44.56	$ 19.78
Market Capitalization (millions)	$ 6,868.26	$ 29,454.16	$ 1,010.76
Interest-bearing debt (ST&LT)	$1,843	$3,178	$1,949	$265
Common equity (price x shares outstanding)	$6,868	$29,454	$1,011	$0
Less: Cash and equivalents	$318	$1,583	$203	$496
Equals: Enterprise value	$8,393	$31,049	$2,757
EBITDA	$1,570	$4,615	$535	$194
Calculated Multiples
	JetBlue Airways	Southwest Airlines Co.	Skywest Inc.	Peer Average
EV/EBITDA Multiple	5.35	6.73	5.16	5.74
P/E Multiple	9.92	13.50	8.56	10.66
Virgin America Inc. Valuation
					Virgin America Inc.
Enterprise Value based on EV/EBITDA multiple	$1,038	$1,306	$1,001	$1,115
Plus: VA Cash					$496
Less: VA Interest-bearing debt					$265
Total Equity value based on EBITDA for VA	$1,269	$1,537	$1,232	$1,346
VA Shares Outstanding (millions)					44
Equity value per share	$29.16	$35.33	$28.31	$30.94
Equity value per share based on P/E multiple	$77.58	$105.59	$66.96	$83.38
Total Equity value for VA based on P/E multiple	$3,375	$4,593	$2,913	$3,627
Interest-bearing debt					$265
Less: Cash and equivalents					$496
Enterprise value for VA based on P/E multiple	$3,144	$4,362	$2,682	$3,396

This exam requires you, among other things, to estimate the stock price for Virgin America (Ticker: VA), and provide the analysis as requested. You will need to use “Sources of Financial Data” listed in Course Content to obtain the necessary financial info/statements for Virgin America Inc., to identify its peer companies and to obtain pricing and financial information for them. A. Choose several peer companies for Virgin America Inc. and justify your choice. Choose several valuation multiples and using comparable ratios of peer companies (as we did in Project 2 and discussed in Conferences) and Virgin America Inc. financial information from prospectus, estimate the company’s equity value on April 3, 2016. It is required for this question to list your major assumptions and properly reference sources of information that you used in your calculations.

Using comparable ratios of peer companies and Virgin America Inc. financial information from prospectus, estimate the company’s equity value on April 3, 2016.

Multiples Valuation Template

	(in millions)	(in millions)	(in millions)	(in millions)
						Solution Legend
						Downloaded data
						Formula/Calculation/Analysis required
Peer Companies:	Peer 1	Peer 2	Peer 3	Amazon.com
Ticker
PERIOD ENDING
From Income Statement and Balance Sheet
Balance Sheet
Insert Relevant Items here
Income Statement
Insert Relevant Items here
Calculated Multiples
EV/EBITDA Multiple
P/E Multiple
Market to Book Multiple
Price/Revenue Multiple
Your Company Valuation
					Amazon.com
Enterprise Value based on EV/EBITDA multiple
Plus: Your Company Cash					$0
Less: Your Company Interest-bearing debt					$0
Total Equity value based on EBITDA
VA Shares Outstanding (millions)					-
Equity value per share
Equity value per share based on P/E multiple
Total Equity value based on P/E multiple
Plus: Your Company Interest-bearing debt					$0
Less: Your Company Cash and equivalents					$0
Enterprise value based on P/E multiple
Equity value per share based on MTB Ratio
Total Equity value based on MTB ratio
Plus: Your Company Interest-bearing debt					$0
Less: Your Company Cash and equivalents					$0
Enterprise value based on MTB ratio
Equity value per share based on P/Revenue ratio
Total Equity value based on P/Revenue ratio
Plus: Your Company Interest-bearing debt					$0
Less: Your Company Cash and equivalents					$0
Enterprise value based on P/Revenue ratio

Using the same company as in Module 1 assignment and its peers, find multiple ratios of peer companies and then estimate your company’s equity value and enterprise value.