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...

1 answer below »

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
Answered Same DayApr 03, 2022

Solution

Sandeep answered on Apr 04 2022
16 Votes
Comps Data
        Comparables Valuation Model
        Data & Assumptions
        Comparables Data
    Task 01        Ticker    Company Name     Market Cap    Ratio
    Task 02                    Trl. P/E    P/B    P/S        EV/EBITDA
    Task 03    Target:    T.TO    TELUS    $44,160    26.4    2.9    2.6        10.22
        Comparables    BCE.TO    BCE    $62,040    22.8    3.3    2.6        9.90
            RCI-B.TO    Rogers Communications    $34,910    22.5    3.3    2.4        10.19
            SJR-B.TO    Shaw...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here