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?
| +465 | Don’t work at UBS - UBS Sucks | 50 | 9m |
| +229 | Should My Intern Get a Return Offer? | 58 | 2d |
| +85 | UBS Accused of Favoring H-1B Workers While Cutting U.S.-Born Employees | 12 | 2m |
| +75 | Incompetent and annoying co-intern | 23 | 3h |
| +67 | Getting Laid in IB? | 28 | 2d |
| +66 | Intern keeps sleeping at work - what to tell them | 28 | 6h |
| +55 | Living in greenwich as an analyst? | 12 | 2h |
| +39 | NYC Associate Budget | 14 | 7h |
| +36 | Nauseous every morning for past 3 years in IB, anyone else? | 21 | 1d |
| +35 | Quick Thoughts on CVC AI Sale Process | 7 | 9h |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
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!
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:
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.
Tempore explicabo animi esse quo blanditiis ea. Enim error recusandae hic vel.
Ea repellat sunt voluptatem omnis rem hic harum. Qui cupiditate illum et. Ea consequatur dolorum libero vero.
Dolor commodi est unde ipsa id in. Nostrum qui labore eligendi voluptas nihil fuga dicta. Totam molestiae harum dolor.
Minus minus non dolorem ab maiores. Dolores praesentium commodi officia tempora. Exercitationem a deleniti non tempore. Dolores veniam doloribus dolor amet eum ipsum. Enim ipsam cumque corporis voluptatum. Adipisci eos ex iure est id sed. Ratione magni quis eaque iusto et dolores consectetur dignissimos.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...