Stock Valuation

MGMT332_Excel_Template_5.xls

Prob. 1 WACC

	Component	Eastern	Western	Source
1	Net Income (M$)	24.00	13.00	Given
2	Earnings per share	1.30	1.00	Given
3	# of shares (M)	18.46	13.00	Net Income / Earnings per share
4	Price per share	12.00	15.00	Given
5	Market Value - Equity (M)	222	195	# of shares x Price per share
6	Market Value - Debt (M)	244	122	Given
7	Market Value - Total (M)	466	317	Market Value - Debt + Equity
8	- % Debt	52%	38%	Market Value - Debt / Market Value - Total
9	- % Equity	48%	62%	1 - % Debt
10	Beta (levered)	1.32	1.11	Given
11	Beta (unlevered)	0.63	0.68	Beta (levered) x % Equity
12	Average Beta (unlevered)	0.66		Average
	Values for combined Argo/SkyBlue airline:
	% Debt	50.0%		Given
	% Equity	50.0%		1 - % Debt
	Beta (relevered)	1.31		Average Beta (unlevered) / % Equity
	Risk free rate	2.0%		Assumption
	Market risk premium	5.5%		Historical figure
	Expected equity return	9.2%		CAPM calculation
	Expected cost of debt	6.0%		Given
	Tax rate	21.0%		Statutory rate
	WACC			Weighed average of costs of equity and debt

	Start	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Investment
Working Capital
Operating Cash Flow
Total Cash Flow
NPV
IRR
Note: Show all numbers in thousand dollars.

	Actual	Projected
Income Statement	2020	2021	2022	2023	Notes
Key Assumptions:
Average growth rate:		3.3%	3.4%	3.5%
Tax Rate	21%	21%	21%	21%	Use current statutory rate of 21%
Net Sales	4,233				Increase using the average growth rate
- Cost of Goods Sold (COGS)	2,700				Use same percent of Net Sales as in 2018
= Gross Profit	1,533				Calculation
- S,G & A Expenses	1,199				Use same percent of Net Sales as in 2018
= EBIT	334				Calculation (Use for Free Cash Flow valuation!)
- Interest	77				Hold level
= EBT	257				Calculation
- Taxes	54				Use implicit rate of 2018
= Net Income	203				Calculation
Balance Sheet
Cash	230				Increase at average growth rate
Notes and Acc. Rec.	664
Inventory	715
Prepaid	65
Current Assets	1,674
Other	102				Increase at average growth rate
Total Assets	1,776
Bank Loan	550				Plug to make balance sheet balance
Payables	33				Increase at average growth rate
CPLTD	42				Leave flat
Other	34				Increase at average growth rate
Current Liabilities	659
LTD	125				LTD of previous year minus CPLTD of current year
Equity	992				Equity of previous year plus net income of current year
Total Liabilities & Equity	1,776
Working Capital					Exclude Bank Loan

SkyBlue Acquisition
- Earnings Multiple
- 2020 actual net earnings
Price					Price Paid
Value based on free cash flows:
	2021	2022	2023	TV	Notes
EBIT					From Pro Formas - EBIT projection
EBIAT					EBIT minus tax
- Change in Working Capital					From Pro Formas (exclude Bank note plug!)
- Change in Other assets					From Pro Formas
Free Cash Flow					EBIAT plus changes in WC/Other assets
PV of Free Cash Flows					Calculation
- Existing Debt					Bank Loan + CPLT + LTD
= Present Value of SkyBlue equity					Calculated value
(Underpay)/Overpay for SkyBlue					If negative, the purchase prices is less than SkyBlue is worth

No content - Intentionally left blank