MGMT 332

MGMT332_Excel_Template_51.xls

Read 1st!

This case has the following road map:
Tab
WACC	Shows the financials for both airlines and calculated the weighed average cost of capital (WACC) for the combined airline
Machine NPV	Please calculate the NPV of the new gates
SkyBlue financials	Calculate SkyBlue's income statement (IS) and balance sheet (BS) in order to run free cash flows in the valuation tab
SkyBlue valuation	Calculate the present value of SkyBlue using free cash flows
Apache BS (balance sheet)	Shows Apache's balance sheet for 2018 and forecast 2019-2021
Consolidated BS	Consolidates Apache's and SkyBlue's balance sheets and recalculates the bank ratios

Please note: All figures are in thousand $ unless noted otherwise

	Component	Caledonian	Laker	Source
1	Net Income (M$)	33.40	24.10	Given
2	Earnings per share	1.04	1.53	Given
3	# of shares (M)	32.12	15.75	Net Income / Earnings per share
4	Price per share	14.00	12.63	Given
5	Market Value - Equity (M)	450	199	# of shares x Price per share
6	Market Value - Debt (M)	341	75	Given
7	Market Value - Total (M)	791	274	Market Value - Debt + Equity
8	- % Debt	43%	27%	Market Value - Debt / Market Value - Total
9	- % Equity	57%	73%	1 - % Debt
10	Beta (levered)	1.07	0.93	Given
11	Beta (unlevered)	0.61	0.68	Beta (levered) x % Equity
12	Average Beta (unlevered)	0.64		Average

	Values for combined Apache/SkyBlue airline:
	% Debt	50.0%		Given
	% Equity	50.0%		1 - % Debt
	Beta (relevered)	1.28		Average Beta (unlevered) / % Equity
	Risk free rate	6.0%		Assumption
	Market risk premium	5.0%		Historical figure
	Expected equity return	12.4%		CAPM calculation
	Expected cost of debt	7.0%		Given
	Tax rate	21.0%		Statutory rate
	WACC			Weighed average of costs of equity and debt

trombino:

trombino: Exhibit 5

	Start	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8
Investment
Working Capital
Operating Cash Flow
Total Cash Flow

NPV
IRR

Note: Show all numbers in thousand dollars.

	Actual	Projected
Income Statement	2018	2019	2020	2021	Notes
Key Assumptions:
Average growth rate:		0%	6%	6%
Tax Rate	21%	21%	21%	21%	Use current statutory rate of 21%
Net Sales	5,500				Increase at average growth rate
- Cost of Goods Sold (COGS)	3,200				Use same percent of Net Sales as in 2018
= Gross Profit	2,300				Calculation
- S,G & A Expenses	1,600				Use same percent of Net Sales as in 2018
= EBIT	700				Calculation (Use for Free Cash Flow valuation!)
- Interest	110				Hold at 2018 level
= EBT	590				Calculation
- Taxes	124				Use implicit rate of 2018
= Net Income	466				Calculation
Balance Sheet
Cash	300				Increase at average growth rate
Notes and Acc. Rec.	1,300
Inventory	1,100
Prepaid	80
Current Assets	2,780
Other	1,300				Hold at 2018 level
Total Assets	4,080

Bank Loan	830				See below
Payables	100				Increase at average growth rate
CPLTD	100				Hold at 2018 level
Other	125				Hold at 2018 level
Current Liabilities	1,155
LTD	675				LTD of previous year minus CPLTD of current year
Equity	2,250				Equity of previous year plus net income of current year
Total Liabilities & Equity	4,080

Working Capital	2,455				Exclude Bank Loan

Note: the Bank Loan is a plug figure. Its formula starts with total assets and subtracts the liability and equity accounts.
If the Bank Loan number is positive, it is debt. If negative, it is basically cash.

SkyBlue Acquisition
- Earnings Multiple
- 2018 actual net earnings
Price				Price Paid
Value based on free cash flows:
	2019	2020	2021	Notes
EBIT				From Pro Formas - EBIT projection
EBIAT				EBIT minus tax
- Change in Working Capital				From Pro Formas (exclude Bank note plug!)
- Change in PP&E				From Pro Formas
Free Cash Flow				Calculation
Terminal Value				Assumes growth rate given
Present Value				Calculation
PV of Free Cash Flows				Calculation
- Existing Debt				Bank Loan + CPLT + LTD
= Present Value of SkyBlue				Calculated value

(Underpay)/Overpay for SkyBlue				If negative, Apache Airlines paid less than SkyBlue is worth