Hi, I need help on the practice guide question below. Please provide excel formulas and information to the question is attached below.
All the data you need is in the template provided to you. There is not any additional information provided for this question.
JTM Airlines is looking to buy Jaguar Airlines. Your boss, the CFO, wants a quick and dirty valuation of Jaguar. You choose to look at past transactions in the airline industry to get some numbers and put them in an Excel spreadsheet. For Jaguar, you find out the firm’s key financial values and put them in the spreadsheet. To remind yourself that they are inputs, you should color them red. Using EPS, Book Value (BV), Sales, EBITDA, Premium and Synergy over stock price, what should be Jaguar’s prices per share.
(Jaguar EPS * Median EPS Multiple)
(Jaguar EPS* Average EPS Multiple)
(Jaguar Book Value* Median BV Multiple)
(Jaguar Book Value* Average BV Multiple)
Sales (using average) (using median)
Equity Value (Firm Value-Debt Value)
Max (Equity Value/Jaguar shares o/s)
EBITDA (using Average) (using median)
Average EBITDA Multiple
Firm Value (Jaguar EBITDA*EBITDA Multiple)
Equity Value (Firm Value-Debt Value)
Max (Equity Value/Jaguar shares o/s)
Min
Premium
Pre-Announcement Stock Price
Max Pre-announcement price*(1+median premium multiple)
Min Pre-announcement price*(1+median premium multiple)
Synergy
Pre-announcement price
Max Pre-announcement price*(1+median synergy multiple)
Min
Image transcription text
using Multiples and Transactions IN
EPS BV Sales EBITDA Pr… Show more
Image transcription text
Valuations using Multiples and Transactions
EPS BV Sales EBITDA Premiu… Show more
Equity Valuations using Multiples and Transactions
EPS BV Sales EBITDA Premium Synergy
Historical Multiples
Average 17.19 3.63 2.41 10.57 53.30% 24.80% Jaguar shares o/s (M)
Median 18.13 3.69 2.37 9.91 53% 24.50% 88.33
EPS Last 4Q Est 2021 Est 2022
Jaguar EPS 4.84 4.91 5.69
Maximum 87.7492 89.0183 103.1597 (Jaguar EPS* Median EPS Multiple)
Minimum 83.1996 84.4029 97.8111 (Jaguar EPS* Average EPS Multiple)
Book Value
Jaguar Book Value 32.46
Maximum 119.7774 (Jaguar Book Value*Median BV Multiple)
Minimum 117.8298 (Jaguar Book Value*Average BV Multiple)
Sales (using average) (using median)
Jaguar Sales (Last 4Q) 3722 3722
Average Sales Multiple 2.41 2.37
Firm Value 8970.02 8821.14 (Jaguar Sales* Sales Multiple)
Debt Value 2094 2094
Equity Value 6876.02 6727.14 (Firm Value- Debt Value)
Maximum 77.84467338 (Equity Value/Jaguar shares o/s)
Minimum 76.15917582
EBITDA (using average) (using median)
Jaguar EBITDA (Last 4Q) 1017 1017
Average EBITDA Multiple 10.57 9.91
Firm Value 10749.69 10078.47 (Jaguar EBITDA*EBITDA Multiple)
Debt Value 2094 2094
Equity Value 8655.69 7984.47 (Firm Value- Debt Value)
Maximum 97.99264123 (Equity Value/Jaguar shares o/s)
Minimum 90.3936375
Premium
Pre-Announcement Stock Price 71
Maximum 108.843 Pre-announcement price*(1+median premium multiple)
Minimum 108.63 Pre-announcement price*(1+average premium multiple)
Synergy
Pre-Announcement Stock Price 71
Maximum 88.608 Pre-announcement price*(1+median synergy multiple)
