Lab 3 - Equity (Filled

xlsx

School

Oregon State University, Corvallis *

*We aren’t endorsed by this school

Course

123

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

29

Uploaded by AdmiralJay493

Report
342 Term Project -- Lab 3: Equity Name: Company Stock Ticker: Prompt: Instructions: Do Not Delete Any Columns, Rows, or Cells in this Workbook. It will me Part A: Practice On the tab "Comparables - Practice" -Use the 3M sample data to fill in the tables at the top - The definition of all multiples calculated is on the right hand sid -Question 1 requires you to calculate the multiple for 3M and its -Question 2 requires that you calculate the average multiple acro -Question 3 requires you to determine if 3M is trading above (pre -Question 4 requires you to apply the peer average to 3M to com Part B: You Try You will need to gather data from Yahoo finance to fill in the Company On the tab "Comparables - You Try" -Fill in the company data using information from Yahoo finance, som -Fill in the tables with the closest competitors you identified in Que -Complete the analysis at the top in the same way as the practice ta On the tab "Comparables Chart - You Try" -Use the data you have generated to create a bar chart for each mu Tips / FAQ: The CFO wants to know about the firms valuation relative to competitors the CFO whether it is appropriate to issue or buy back shares. You will us company using the multiples method . You have been given some sample in multiples for a firm and its competitors. Use these exercises to practice. They w gather data for your company yourself. This information will go on slide 11-12 o If a competitors of your company has a negative values for any of their m Why? A negative P/E ratio for a competitor would imply a negative price method and negative prices are not possible because of limited liability. S the peer average. You can include the competitor in any graph you make on the graph "NM" showing that the value is not meaningful.
You must work on this lab by yourself, though you can ask questions to not submit anyone else's workbook as your own work.
Applied Materials Inc. AMAT ess up the grading! de of the sheet competitors individually oss all of 3M's peers. Be sure to exclude 3M from the average. We want to compare 3M to its peers, no emium), below (discount), or in-line (within 1X) of its peers me up with a price for 3M implied by each of the multiples y data at the bottom of the page. This can be found Financials > Income Statement / Balance Sheet > Q me formulas are already there for you estion 14 of the overview tab in Lab 1. ab ultiple that shows how your firm compares to its peers. See page 12 of the 3M sample term project for s. A high or low relative valuation might inform se information from Yahoo Finance to value the nformation to familiarize yourself with calculating will be graded for accuracy. Then, you will need to of your presentation. multiples, these numbers are not meaningful. for your own company if we use the multiples So, if this is the case, leave this number out of by setting the value to zero, then creating a label
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
classmates, the TA, or the professor. You may
ot itself + its peers. Quarterly > TTM / MRQ and the Statistics tabs on Yahoo Finance reference.
59632 86612
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Questions Question 1: Calculate the following multiples by referencing cells in the sample company data b 3M MSA Safety Danaher P/E (ttm) 10.47X 81.08X 29.28X P/B (mrq) 4.86X 6.39X 4.28X EV/EBITDA (ttm) 8.57X 39.14X 20.26X P/S (ttm) 1.98X 3.61X 6.19X Question 2: Calculate the Peer Average for each multple in Q1 using the =AVERAGE formula. Exc Peer Average P/E 39.93X P/B 6.43X EV/EBITDA 23.79X P/S 4.49X Question 3: Is 3M currently trading a a discount, premium, or in-line (within +/- 1X) relative to t Discount Question 4: If the company traded at the average ratio of these competitors, what is the implie Multiple Method Implied Price P/E $474.94 <--- Example: Take the peer average P/E P/B $164.62 EV/EBITDA $230.78 <--- Note that this number represents the P/S $282.65 Sample Company Data 3M MSA Safety Danaher Current Value 124.53 137.85 265.35 83.12 6.01 215.39 Competitors Pro-Forma Income Statement (in billions $, trailing twelve months -- TTM) Sales 34.76 1.49 31.25 Sales Per Share 62.89 38.16 42.90 Gross Profit 16.58 0.65 18.95 Gross Profit Margin 47.7% 43.8% 60.6% EBIT 7.82 0.10 8.39 EBIT Margin 22.5% 6.5% 26.8% Price Per Share ($/share) Enterprise Value ($B)
Net Income 6.58 0.07 6.60 Profit Margin 18.9% 4.5% 21.1% Shares Outstanding 0.55 0.04 0.73 Earnings Per Share 11.90 1.70 9.06 Non-GAAP Income Statement Measures Depreciation & Amortization 1.88 0.06 2.24 EBITDA 9.70 0.15 10.63 Competitors Pro Forma Balance Sheet (in billions $, most recent quarter -- MRQ) Total Assets 46.18 2.39 83.18 Total Debt 16.63 0.64 23.27 Total Liabilities 32.02 1.54 38.01 Total Equity 14.16 0.84 45.18 Book Value Per Share 25.61 21.57 62.01 Numbers for example purposes only and do not reflect current values
below: Definitions: Amphenol Procter and Gamble Peer Average P/E (ttm): Price Per Sha 25.05X 24.29X 34.04X P/B: Price Per Share / B 7.01X 8.03X 6.11X EV/EBITDA: Enterprise 17.60X 18.14X 20.74X P/S: Price Per Share / S 3.75X 4.43X 3.99X clude 3M from the average. the peer average P/E ratio. ed stock price or enterprise value? Multiply the peer average ratio X 3M ratio-specific den (cell B11) and multiply that by 3M's earnings per share (cell B48) to get 3M's price implie e implied Enterprise Value in $B, not price per share Amphenol Procter and Gamble 78.27 149.28 51.48 384.03 12.41 80.46 20.85 33.73 3.93 37.82 31.7% 47.0% 2.52 18.41 20.3% 22.9%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
1.86 14.66 15.0% 18.2% 0.60 2.39 3.12 6.14 0.41 2.76 2.92 21.17 15.10 116.28 4.75 33.00 8.45 71.95 6.65 44.33 11.17 18.58
are / Earnings Per Share over last 12 months Book Value Per Share … (Book Value = Total Assets - Total Liabilities in most recent quarter) Value / Earnings Before Interest Taxes Depreciation and Amortization over last 12 months Sales Per Share over last 12 months nominator ed by the multiple method
1 1 1 1 2 2 2 3 3 3 4 4 4
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5 5 6 7 7 8 8 9 9 10 22
86612
Questions Question 1: Fill in the table below then calculate the following multiples for your company and its com Applied Materials Inc. KLA Corporation P/E (ttm) 17.32X 20.49X P/B (mrq) 7.28X 20.88X EV/EBITDA (ttm) 13.28X 15.17X P/S (ttm) 4.14X 6.14X Question 2: Calculate the Peer Average for each multple in Q1 using the =AVERAGE formula. Exclude yo Peer Average P/E 17.22X P/B 13.25X EV/EBITDA 12.26X P/S 5.28X Question 3: Is your company currently trading a a discount, premium, or in-line (within +/- 1X) relative In-Line Question 4: If the company traded at the average ratio of these competitors, what is the implied stock Multiple Method Implied Price P/E $132.65 P/B $239.07 EV/EBITDA $100.97 <--- Note that this number represe P/S $167.69 Company Data Fill in the blank cells with data from the Financials > Income Statement / Balance Sheet > Quarterly > T Applied Materials Inc. (AMAT) KLA Corporation (KLAC) Current Value 131.3 457.93 109.42 64.97 Competitors Pro-Forma Income Statement (in billions $, trailing twelve months -- TTM) Sales 26.54 10.17 Sales Per Share 31.73 74.59 Gross Profit 12.32 60.45 Gross Profit Margin 46.4% 594.4% EBIT 7.73 3.87 EBIT Margin 29.1% 38.0% Price Per Share ($/share) Enterprise Value ($B)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Net Income 6.44 3.10 Profit Margin 24.3% 30.5% Shares Outstanding 0.84 0.14 EPS 7.70 22.76 Non-GAAP Income Statement Measures Depreciation & Amortization 0.51 0.42 EBITDA 8.24 4.28 Competitors Pro Forma Balance Sheet (in billions $, most recent quarter -- MRQ) Total Assets 30.41 14.14 Total Debt 5.66 5.89 Total Liabilities 15.32 11.15 Total Equity 15.09 2.99 Book Value Per Share 18.04 21.94
mpetitors Arrow Electronics, Inc. Broadcom Inc. NXP Semiconductors 5.68X 25.77X 16.95X 11.01X 15.67X 5.45X 4.96X 18.22X 10.68X 1.74X 9.76X 3.50X our company and any negative or missing numbers from the average. e to the peer average P/E ratio. k price or enterprise value? ents the implied Enterprise Value in $B, not price per share TTM / MRQ and the Statistics tabs on Yahoo Finance Arrow Electronics, Inc. (ARW) Broadcom Inc NXP Semiconductors (NXPI) 111.8 838.36 179.13 10.14 373.31 53.51 35.84 35.45 13.18 64.39 85.90 51.11 4.57 24.21 7.51 12.8% 68.3% 57.0% 1.86 16.37 3.82 5.2% 46.2% 29.0%
1.20 13.87 2.77 3.4% 39.1% 21.0% 0.56 0.41 0.26 2.16 33.60 10.76 0.18 4.12 1.19 2.04 20.49 5.01 20.54 71.60 23.80 4.16 39.34 11.17 14.88 49.52 15.32 5.65 22.08 8.48 10.16 53.49 32.88
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Definitions Peer Average P/E (ttm): Price Per Share / Earnings Per Share over last 12 mont 17.24X P/B: Price Per Share / Book Value Per Share … (Book Value = Tota 12.06X EV/EBITDA: Enterprise Value / Earnings Before Interest Taxes Dep 12.46X P/S: Price Per Share / Sales Per Share over last 12 months 5.06X This is just the current stock price This is on the Statistics tab All info below is on Financials > Quarterly > Income Statement or Balance Sheet tabs TTM TTM TTM
TTM Most recent quarter TTM Most recent quarter Most recent quarter Most recent quarter
ths al Assets - Total Liabilities in most recent quarter) preciation and Amortization over alst 12 months
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
1 1 1 1 2 2 2 3 3 3 4 4 4
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5 5 6 7 7 8 8 9 9 10 16
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
86612
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Questions: Question 1: Create a bar chart to visualize how your company's P/E ratio compares to its peers Question 2: Create a bar chart to visualize how your company's P/B ratio compares to its peers Question 3: Create a bar chart to visualize how your company's EV/EBITDA ratio compares to its peers Question 4: Create a bar chart to visualize how your company's P/S ratio compares to its peers Applied Materials Inc. KLA Corporation Arrow Electronics, Inc. Broadcom Inc. NXP Semiconductors Peer Average 0.00X 5.00X 10.00X 15.00X 20.00X 25.00X 30.00X P/E ratio Applied Materials Inc. KLA Corporation Arrow Electronics, Inc. Broadcom Inc. NXP Semiconductors Peer Averag 0.00X 5.00X 10.00X 15.00X 20.00X 25.00X P/B ratio Applied Materials Inc. KLA Corporation Arrow Electronics, Inc. Broadcom Inc. NXP Semiconductors Peer Avera 0.00X 4.00X 8.00X 12.00X 16.00X 20.00X EV/EBITDA ratio 4.00X 6.00X 8.00X 10.00X 12.00X P/S ratio
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Comparables data You can copy data from the "Comparables" tab in to a table here to create the charts above if you'd lik Applied Materials Inc. KLA Corporation Arrow Electronics, Inc. Broadcom Inc. NXP Semiconductors Peer Ave 0.00X 2.00X
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
s e e age
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
ke. Or just reference the data in that tab directly. erage
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
86612
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help