In: Finance
K Co. is a publicly listed company involved in the production of highly technical and sophisticated electronic components for complex machinery. It has a number of diverse and popular products, an active research and development department, significant cash reserves and a highly talented management who are very good in getting products to market quickly.
A new industry that K Co. is looking to venture into is biotechnology, which has been expanding rapidly and there are strong indications that this recent growth is set to continue. However, K Co. has limited experience in this industry. Therefore, it believes that the best and quickest way to expand would be through acquiring a company already operating in this industry sector.
Discussions taken place about the possibility of acquiring Tee Co. being acquired by K Co. Price of Tee company in stock market during last one year are as follows.
Price at the end of month
Month |
Month end Price |
KSE 100 INDEX |
Jan |
175 |
32600 |
Feb |
185 |
33900 |
March |
152 |
33500 |
April |
190 |
34000 |
May |
195 |
33500 |
June |
188 |
33800 |
July |
190 |
33700 |
Aug |
195 |
33200 |
Sep |
190 |
32900 |
Oct |
185 |
33100 |
Nov |
190 |
33900 |
Dec |
88 |
34100 |
Step 1: For both Tee Co. and KSE 100, we calculate the percentage change in prices using the formula -
(Current price - Last month price)/Last month Price
Month | Tee Co. | KSE 100 | %age change Tee Co. | %age change KSE 100 |
Dec | 88 | 34100 | -53.68% | 0.59% |
Nov | 190 | 33900 | 2.70% | 2.42% |
Oct | 185 | 33100 | -2.63% | 0.61% |
Sept | 190 | 32900 | -2.56% | -0.90% |
Aug | 195 | 33200 | 2.63% | -1.48% |
July | 190 | 33700 | 1.06% | -0.30% |
June | 188 | 33800 | -3.59% | 0.90% |
May | 195 | 33500 | 2.63% | -1.47% |
April | 190 | 34000 | 25.00% | 1.49% |
March | 152 | 33500 | -17.84% | -1.18% |
Feb | 185 | 33900 | 5.71% | 3.99% |
Jan | 175 | 32600 | #DIV/0! | #DIV/0! |
Step 2: Calculating the average price for both Tee Co. and Market price
Using excel AVERAGE function , Tee Co. average Return = AVERAGE(%age change in stock price values from Jan to Dec)
KSE 100 average Return = AVERAGE(%age change in values from Jan to Dec)
Month | %age change Tee Co. | %age change KSE 100 |
Dec | -53.68% | 0.59% |
Nov | 2.70% | 2.42% |
Oct | -2.63% | 0.61% |
Sept | -2.56% | -0.90% |
Aug | 2.63% | -1.48% |
July | 1.06% | -0.30% |
June | -3.59% | 0.90% |
May | 2.63% | -1.47% |
April | 25.00% | 1.49% |
March | -17.84% | -1.18% |
Feb | 5.71% | 3.99% |
Jan | ||
AVERAGE | -3.69% | 0.42% |
Step 3: Standard Deviation is calculated by using excel STDEV.P function
For Tee Co. = STDEV.P(%age returns from Jan to Dec)
For KSE 100 = STDEV.P(%age returns from Jan to Dec)
Month | %age change Tee Co. | %age change KSE 100 |
Dec | -53.68% | 0.59% |
Nov | 2.70% | 2.42% |
Oct | -2.63% | 0.61% |
Sept | -2.56% | -0.90% |
Aug | 2.63% | -1.48% |
July | 1.06% | -0.30% |
June | -3.59% | 0.90% |
May | 2.63% | -1.47% |
April | 25.00% | 1.49% |
March | -17.84% | -1.18% |
Feb | 5.71% | 3.99% |
Jan | ||
STDEV | 0.1848 | 0.0166 |
Step 4: Covariance is calculated by using excel Covariance function
COVARIANCE.P(%age returns of Tee Co. , %age returns of KSE 100)
So, Covariance = 0.0006
Step 5: Calculating the Beta. Using excel slope function
SLOPE(%age returns of Tee Co. , % returns of KSE 100)
Beta = 2.07
A beta of 2.07 shows that Tee Co. stocks are twice as volatile than the market returns. If market return is 10%, then Tee Co. stock return is approx 20%. Similarly , is market return falls 10%, Tee Co. stock will fall 20%