I have attached the files below. They include the questions for the project, the rubric and everything that will be needed.
Ourdev – Fnce3381 Ass.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 – Fnce3381 Ass.05 2 Stock Rover steps: 1. In a browser 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 corresponding 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 corresponding 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.) b. 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 – Fnce3381 Ass.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 corresponding 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 TickerCompany Name Market CapRatio Trl. P/EP/BP/SEV/EBITDA Target:T.TOTELUS$37,83130.72.62.310.00 ComparablesBCE.TOBCE$56,41125.13.12.59.00 RCI-B.TORogers Communications$34,03221.33.42.48.00 SJR-B.TOShaw Communications$18,14920.43.13.49.00 Median21.33.12.59.0 Harmonic22.13.22.78.6 Target DataTarget:T.TO TickerCompanyMarket CapRatio P/EP/BP/SP/FCFEV/EBITDA Comps Val Comparables Valuation Model Comparables Valuation Type of averaging:median TargetT.TOPeers Avg.Price estimate per ratio QuantitiesValueRatio E/Share = $0.95P/E 21.30$20.24 B/Share =$12.00P/B3.10$37.20 S/Share =$15.00P/S2.50$37.50 EBITDA/Share =$5.00EV/EBITDA9.00$45.00 Final estimated price:$37.35 Actual market price:$25.00 EBITDA5,000,000 Total revenue 15,000,000 Common Stock Equity12,000,000 Basic Average Shares 1,000,000