Ourdev – Fnce XXXXXXXXXXAss.05
1
Ass.05 – Comparables
Summary
The purpose of this assignment is to apply the method of comparables valuation model to the target
stock, in this case it is TELUS, stock ticker T.TO.
Note: The assignment is to a large extend illustrative:
Because the price multiples change with price, the valuation would depend on the day you take the
values. So do not change the values in the Excel model! For your project you would read
Review of relevant theory
♦ ???? – measure of value of company x (earnigs, sales, etc.)
♦ ?????? – price multiple, using that measure of value
?????? ≜
??0
????
For two companies, A & B (benchmark), that have the same measure of value
??????� = ??????
→
??0
????
= ??????
→ ??0 = ?????? × ????
Tasks
Task. 01: Determine the peer group of companies that you would use in the valuation
You can skip this step for the assignment, because the Excel model already has a partial list
of peers to be used. Don’t change it!
For the project, two potential sources of this information are:
• Seeking Alpha (https:
seekingalpha.com/), “Peers”
• StockRover
Ourdev – Fnce XXXXXXXXXXAss.05
2
Stock Rover steps:
1. In a
owser type https:
www.stockrover.com/
2. Sign in with any one of the following three NAIT licences
o User ID: naitsif01, naitsif02, naitsif03
o Pass: NaitSIF01, NaitSIF02, NaitSIF03 (case-sensitive!, number - matching the "User")
o Note: Only one user is allowed at any time! If a second user loads, the fitst user is kicked
out.
3. In the left panel select “Insight”
4. Type T.TO in “Enter the Ticker…”
5. Click on the tab called “Summary” and slide to its bottom to get the list of “Competitors”.
(The example only uses 3 to avoid repetition of the same tasks).
Task. 02: Obtain the representative EV/EBITDA multiple for the peer group
For both the target company and its peers listed in the excel model get the value multiple
Steps:
1. Select each company Yahoo Finance
2. From Statistics → Valuation Measures copy the number for Enterprise Value/EBITDA
And paste in the co
esponding tab of the “Fn3381 - Ass05.xlsx” called “Comps Data”.
3. Calculate the median and the harmonic mean for each multiple for the peer group, excluding
the target.
Task. 03: Compute the values of earnings, book value, sales, and enterprise value on a per share basis
Steps:
1. From the “Balance Sheet” tab copy the 2021 numbers for Common Stock Equity
2. From the “Income statement” tab copy the following 2021 numbers and paste them in the
co
esponding tab of the “Fn3381 - Ass05.xlsx” , “Comps Val”
a. Basic Average Shares. (Note: since the data for 2021 is not available, use the one for
2020.)
. Basic EPS; in the cell for “E/Share”. (Note: since the data for 2021 is not available, use
the one for 2020.)
c. Total revenue.
Ourdev – Fnce XXXXXXXXXXAss.05
3
d. EBITDA. (Note: since the data for 2021 is not available, use the one for TTM.)
3. Divide the total quantities for sales and book value by the number of shares to obtain the sales
value per share (SPS) and the book value per share (BPS). Same for EBITDA.
Task. 04: Apply the Method of multiples to value the stock
Steps:
1. Copy the values of the peer group multiples from “Comps Data” into “Comps Val”. (Note: since
all price multiples change with price, for the purposes of the assignment, do not update these
multiples and use the numbers given in the model spreadsheet.)
2. Multiply them by the co
esponding per share quantities for the target
3. Take median or harmonic mean to get the price estimate
Ass.05 – Comparables
Summary
Review of relevant theory
Tasks
Comps Data
Comparables Valuation Model
Data & Assumptions
Comparables Data
Ticker Company Name Market Cap Ratio
Trl. P/E P/B P/S EV/EBITDA
Target: T.TO TELUS $37,831 30.7 2.6 2.3 10.00
Comparables BCE.TO BCE $56,411 25.1 3.1 2.5 9.00
RCI-B.TO Rogers Communications $34,032 21.3 3.4 2.4 8.00
SJR-B.TO Shaw Communications $18,149 20.4 3.1 3.4 9.00
Median 21.3 3.1 2.5 9.0
Harmonic 22.1 3.2 2.7 8.6
Target Data Target: T.TO
Ticker Company Market Cap Ratio
P/E P/B P/S P/FCF EV/EBITDA
Comps Val
Comparables Valuation Model
Comparables Valuation
Type of averaging: median
Target T.TO Peers Avg. Price estimate
per ratio
Quantities Value Ratio
E/Share = $0.95 P/E 21.30 $20.24
B/Share = $12.00 P/B 3.10 $37.20
S/Share = $15.00 P/S 2.50 $37.50
EBITDA/Share = $5.00 EV/EBITDA 9.00 $45.00
Final estimated price: $37.35
Actual market price: $25.00
EBITDA 5,000,000
Total revenue 15,000,000
Common Stock Equity 12,000,000
Basic Average Shares 1,000,000