6 Free Financial Modeling Lessons...and More.

Enter your e-mail below to get our new Free Modeling Tutorials ($200+ Value).

< previous | next >

stupid stock chart question
 

StupidIBMonkey's picture
(Monkey, 32
 
Points)
 on 6/21/12 at 12:38am

how do you do a relative value stock price chart? if you have company A and 4 comps, can you use company A's stock price on the Y axis and still the 4 as its peers on the same Y axis? If Company's A and the 4 peers prices differ drastically, what's a solution that still shows the company's A stock prices throughout the timeline?

Not not a boutique's picture

You want to index the prices.

IB
 
Points)
 on 6/21/12 at 1:35am

You want to index the prices.

So your Y axis will be in %'s, and you'll have two lines (your Company and the 4 peers indexed) both starting at 100%

To calculate the company, use a column next to your stock prices and take each price divided by your starting price. For example, say you start at 1/1/12, and the price is $10. At 1/7/12 the price went to $9, so your % will be 90%. Then the stock went to $10.50 at 1/14/12, so your $ will then be 105%. This % is the line you'll want to chart out.

To calculate the comps, there are two ways. You can do the same as above for each of the four (each being divided by its own starting share price), then average the four at each point in time.

Or, you can do one formula to calculate all at once. The formula is (assuming your 4 peers are in column C, D, E and F and the prices begin in row 5):

=( C5 / $C$5 + D5 / $D$5 + E5 / $E$5 + F5 / $F$5 ) / 4

Then drag that formula all the way down for all stock prices.

If you want to show the 4 peers relative to your company's stock price, just take the calculated average for the peers (using either method) and multiply by your company's beginning stock price.

Hope all that makes some sense!

Asatar's picture

As the previous poster said,

O
(Neanderthal, 2,115
 
Points)
 on 6/21/12 at 6:31am

As the previous poster said, you index them all either to the share price of Company A or to 100. The best way I found of doing it is:

  • Put the share price of each company in row 1 at the start date of the period, say cells A1, B1, C1, D1 and E1 for this description
  • Leave row 2 blank
  • Starting in row 3, put the input formula for the full range of share prices from date 0 to date x
  • Modify the input formula so that cell is divided by row 1, then multiplied by 100
  • I.e. in cell A3 you write =(Input Formula, FactSet / Bloomberg etc)/A$1*100
  • You can then copy this formula into B3, C3, D3 and E3 and then copy it all the way down your cell range

Note that this exact method will only work if you derive your share prices from an input formula, if they are hardcoded numbers then you will need to make a new cell range referencing those numbers and apply the same method.

See my other WSO blog posts>

bonusbomb's picture

Good responses... what about

 
Points)
 on 6/22/12 at 2:34pm

Good responses... what about if you have Company A, Company B, then Company C to H's prices for the last year, and you want to make 3 separate lines comprised of A, B as two separate lines and C to H as one line (weighted average). How do you calc C to H weighted average? Based on their market cap? If so, how?

6 Free Financial Modeling Lessons...Straight to Your Inbox.

Confirm your name and e-mail below to get our best tips

Monkey Stats
Account information
We respect your Name and E-mail privacy. By joining you accept our terms of service.