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

A B C D E F G H Equity Valuations using Multiples and Transactions IN EPS BV Sales EBITDA Premium Synergy 3 Historical Multiples: - Average 14.00 3.00 2.00 10.00 40.0% 20.0% Jaguar shares ofs (M) - Median 13.50 2.50 2.20 9.00 30.0% 15.0% 75.00 6 EPS Last 4Q Est. 2021 Est. 2022 8 Jaguar EPS 4.84 4.91 5.69 Maximum 10 Minimum Book Value 12 Jaguar Book Value 32.46 13 Maximum 14 Minimum 15 Sales 16 Jaguar Sales (Last 4Q) 3,722 17 Average Sales Multiple 18 Firm Value 19 Debt Value 2,094 20 Equity Value 21 Maximum 22 Minimum 23 EBITDA 24 Jaguar EBITDA (Last 4Q) 1,017 25 Average EBITDA Multiple 26 Firm Value 27 Debt Value 2,094 28 Equity Value 8,076 29 Maximum 30 Minimum 31 Premium 32 Pre-Announcement Stock Price 71.00 33 Maximum 34 Minimum 35 Synergy 36 Pre-Announcement Stock Price 71.00 37 Maximum 38 MinimumE26 X V A B C D E F G H I J Equity Valuations using Multiples and Transactions EPS BV Sales EBITDA Premium Synergy Historical Multiples: - Average 17.19 3.63 2.41 10.57 53.3% 24.8% Jaguar shares o/s (M) - Median 18.13 3.69 2.37 9.91 53.0% 24.5% 88.33 EPS Last 4Q Est. 2021 Est. 2022 Jaguar EPS 4.84 4.91 5.69 Maximum 87.75 89.02 103.16 Minimum 83.20 84.40 97.81 Book Value Jaguar Book Value 32.46 Maximum 119.78 Minimum 117.83 Sales Jaguar Sales (Last 4Q) 3,722 Average Sales Multiple Firm Value Debt Value 2,094 Equity Value Maximum Minimum EBITDA Jaguar EBITDA (Last 4Q) 1,017 Average EBITDA Multiple Firm Value Debt Value 2,094 Equity Value 8,656 Maximum 30 Minimum 31 Premium Pre-Announcement Stock Price 71.00 33 Maximum 37.84 4 Minimum 37.63 35 Synergy 6 Pre-Announcement Stock Price 71.00 37 Maximum 17.61 38 Minimum 17.40 39 10