Re: Excel template for ratios

[Gurpreet Singh]

Hi Deepak

This is cool. 

I have 2 points:

1. 

I suggest we stick to the format given by Hema Ma'am for BS, P/L and cash flows, like Robin did, and take ratio formulas, and definitions from your sheet.

It's difficult to show same info in different template to accounting guys for BS,P/L and CF, as they are born and brought up in that :)

2.

Looks like you have taken all consolidated figures, where as Robin had issues with that, as TI's major chunk is in Cholamandalam Investment and Finance Company Limited

So, we need to take a decision, on what we are taking. I'm not sure, but looks like financial analysis for a particular company should be done on unconsolidated reports(if we are looking in a particular sector, and a particular company).

http://www.ehow.com/info_8463041_disadvantages-consolidated-financial-statements.html

Others pls pitch in and show the way out.

Worst is if we have time and enthu, we finish unconsolidated first, and then consolidated ones....

For now, I'm reading the reports, and using unconsolidated figures...

PS: Out of the context, do u guys feel bored and sleepy reading the reports. What keeps u going? :)

Also, as I have to baby sit this weekend at home, may not be able to come to IIMB today/tomorrow. Can catch up on phone though.

But if some of us are meeting, have an clear cut agenda.

Regards

Gurpreet

On Sat, Jan 19, 2013 at 8:34 AM, Deepak Shivamurthy <deepak.sh...@gmail.com> wrote:

Hi guys,

I had a bit of challenge in recollecting lot of things(being half present in the classes last week as my dad underwent bypass in Apollo during the IIMB session week). 

Hence, i had put together all the ratios that Hema maám thought us in an excel, with definitions as explained in ppt/class, for singe point of reference.

All that i need to do now is, enter the input parameters(like total assets, current liabilities etc ) at the bottom of the sheet for every company for every FY and the sheet displays the ratios. This could help us draw trend graphs of one or more ratios, if we want to show in ppt.

Please find attached the same, if it is of any help to you. If there is something that i have misrepresented, please let me know.

Regards,

Deepak

[Deepak Shivamurthy]

Hi Gurpreet,

I agree. We need to have inputs in standardized format, though when i started off it was more of a scribble sheet! 

Will tweak sometime and share if it benefits team.

About the consolidated vs independent reports, now that we have response from Ma'am to Robin, we should just use the unconsolidated one.

Let me take your points in PS to a new thread, if it helps!

Regards,

Deepak

[Gurpreet Singh]

Thanks Robin...

Good to get inputs now.... from the right source....

So, we go only for two years(11-12), right?

Regards

Gurpreet

[Gurpreet Singh]

Well... not sure, what comparable means... but if it is to do with revenues, profits, etc, in the same sector company....

Check this.....

I feel CG, TI is comparable.... even for than matter MRF...

if we do common size....

what say? stick to CG and TI?

	CG	TI	MRF	Apollo
Assets/Liabilities	4570.49	2642.45	7212.27	57,694.84
Revenue	6559.77	3520.74	11902.19	81,760.69
Profit after tax	504.86	180.09	572.36	1,813.33
Net Increase in Cash and Cash Equivalents	170.21	72.28	5.08	-247.62

 

On Sat, Jan 19, 2013 at 11:38 AM, Scaria, Robin <robin....@capgemini.com> wrote:

Yes Gurpreet for Ti 2 yrs will do..

llooking at Ma'ams  response, the only point I would suggest review is if Cg is a comparable company. 

This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.

[Gurpreet Singh]

Don't want to confuse.... but here are use ful links:

What is a comparable company:

http://macabacus.com/valuation/comparable-companies

http://www.streetofwalls.com/articles/investment-banking/recruiting-interviewing/comparable-company-analysis/

http://www.investopedia.com/terms/c/comparable-company-analysis-cca.asp#axzz2INz9PnqU

http://en.wikipedia.org/wiki/Valuation_using_multiples

Basically it's about same sector, similar business, comparable EBITDA 

For example:

http://www.moneycontrol.com/competition/cromptongreaves/comparison/CG

Like Havells and CG -> Very comparable in terms of market cap and net profit.

http://www.moneycontrol.com/competition/mrf/comparison/MRF

Mrf and Apollo -> Our original choice, but very different in terms of net profit.

http://www.moneycontrol.com/competition/tubeinvestmentsindia/comparison/TII

So many competitors... and many from finance....

Robin, can you pls put forward a word to Ma'am, on our selection of TII and CG, and if we need to change, better change now.

[U have her email id?]

Regards

Gurpreet

[Gurpreet Singh]

Just talked with Robin and two members from retail group...

Robin is going to put a word to ma'am on our choice, and hope for the best.

The guys in retail group chose Pantaloons and shoppers stop, and got it confirmed from ma'am last week itself.

I guess it's better to ask to be on safer side.

But as many of us are using TII and CG unconsolidated reports for sometime, lets keep it going till we hear anything.

Regards

Gurpreet

[manohar rao]

Also one more thing i was looking at the Money Control and here we can get the required rations we are looking for any year we want based on the Presentation for Ma'am. We also can compare this with the values we have.
 
http://www.moneycontrol.com/financials/cromptongreave/consolidated-ratios/CG  
http://www.moneycontrol.com/financials/tubeinvestment/results/consolidated-nine-months/TII
 

Regards,
Manohar.
 

---

From: sing...@gmail.com
Date: Sat, 19 Jan 2013 13:55:52 +0530
Subject: [gmite7-group7] Re: Excel template for ratios
To: robin....@capgemini.com
CC: deepak.sh...@gmail.com; krishnamma...@wipro.com; manu...@hotmail.com; sahu_...@yahoo.com; padmesh....@intel.com; rahul.r...@gmail.com; sagi...@yahoo.com; am3...@gmail.com; gmite7...@googlegroups.com

--
 
 

[manohar rao]

Sorry I had given the link for the ratio computations for Consolidated report. Below is the link for the Standalone
 
http://www.moneycontrol.com/financials/tubeinvestment/results/nine-months/TII
http://www.moneycontrol.com/financials/cromptongreave/ratios/CG
 
Regards,
Manohar.

 

---

From: manu...@hotmail.com
CC: gmite7...@googlegroups.com
Subject: RE: [gmite7-group7] Re: Excel template for ratios
Date: Sat, 19 Jan 2013 14:17:15 +0530

--
 
 

[Archana Mohan]

Hi all ma'am had requested a mail with the companies we've chosen with a cc to the test of the class.

Just confirming we r going ahead with TI and cg.

On Jan 19, 2013 2:31 PM, "Scaria, Robin" <robin....@capgemini.com> wrote:

Have dropped ma'am a mail. Will update asa. 

Suggestion:

Today and tomorrow 

As we have my template ready to now auto generate the analysis by just plugging in the bs / pl data from the Ar. (This should not take us much time ) May I request we build the template further by 1.validating the existing formulas 2. Add more analysis (source: let's look at what is analysed in the light mfg industries from various websites) .  With a robust Auto compute template The only effort we need to put is to copy data from the AR. This will not take more than 2 - 3 hr. 

Monday to Friday.

With the base worksheet ready we would need to just port to the ppt and add

Our analysis to finish our ppt. 2 days max.

Thanks

Robin

[Gurpreet Singh]

Yes Pls go ahead.

Also pls put a word across that we have deliberately chosen companies from lil diff business but same sector, so understand different types of annual reports, nitty gritties...

But if it defeats the purpose of comparative analysis as Ma'am is expecting, then we can change...

Other options could be

Crompton Greaves Vs Havells

TII Vs Titan Ind

MRF Vs Apollo

@Robin: I assume u didn't get any reply from Ma'am?

Regards

Gurpreet

[Gurpreet Singh]

:) Sure,...

I some how started liking annual reports...

when u do plus and minus... and then values match... it's unique feeling :)

Am now stuck with ratios..... will send u my working sheet in next few hours...

I have taken best of Robin and Deepak's templates and verifying formulas.... so in case we need to change company, we will need to change only in B/S and P/L sheet, and everything else will work.

On Sun, Jan 20, 2013 at 11:47 AM, Scaria, Robin <robin....@capgemini.com> wrote:

Not yet Gurpreet

, I think the more companies we do the more we would appreciate the subject. So lets cheer up:)

[manohar rao]

Robin,
As discussed below are the links of the youtube and some readings.
 
Team,
These will give a very good understanding and the perceptive of these key ratio we have.
 
The below recordings is from a user "Surfwtw" and "Subjectmoney" look at their other recordings for other ratios
Current Ratio : http://www.youtube.com/watch?v=ECfb97KUYBk
 
Debt Ratio : The way it gets calculated (which includes payables and Tax component)
http://www.investopedia.com/university/ratios/debt/ratio2.asp#axzz2IRPumFDs
 
Another good site which gives a good insight about various ratios:
http://beginnersinvest.about.com/od/analyzingabalancesheet/a/inventory-turns.htm
 
Thanks and Regards,
Manohar.
 

---

From: robin....@capgemini.com
To: sing...@gmail.com
CC: am3...@gmail.com; rahul.r...@gmail.com; gmite7...@googlegroups.com; krishnamma...@wipro.com; padmesh....@intel.com; sagi...@yahoo.com; sahu_...@yahoo.com; deepak.sh...@gmail.com; manu...@hotmail.com
Subject: Re: Excel template for ratios
Date: Sun, 20 Jan 2013 06:17:55 +0000

Not yet Gurpreet

, I think the more companies we do the more we would appreciate the subject. So lets cheer up:)

On 20-Jan-2013, at 11:40, "Gurpreet Singh" <sing...@gmail.com> wrote:

[Gurpreet Singh]

Hey Manohar

Are you able to get the same ratios as shown by moneycontrol.com, for standalone reports?

Regards

Gurpreet

--
 
 

[manohar rao]

No Gurupreet. Tried to see how they calculate but no where it's mentioned how to get those value based on the formula we have. Looks like they have some assumption and knock out few variables which we need to figure out. Trying to reach few of my friends in this area and will keep you posted if I make any progress further.

Sent from my iPod

--
 
 

[Gurpreet Singh]

All

This is my WIP version for CG vs TII.

Things done:

1. B/S and P/l are accurate from standalone reports of CG and TII.

2. Ratios: Have completed liquidity, Turnover, Leverage and profitability ratios (there may be mistakes here and there).

Issues:

1. The ratios are not matching moneycontrol's, so that's a worry.

To dos(for me):

1. Complete the template with all ratios.... and double check them

2. Check out stuff sent out by Manohar. 

3. Read both the reports(11-12) thoroughly.

4. Include shared/dividend thing. I still need to understand this equity concept.

5. Google around, learn more, and put the analysis in some form or shape (in ppt)

I guess I'll be ready with final xls in 2 days(hopefully), and ppt by End of the week[Some night outs coming on the way].

PS: Also attaching the ppt template, I'll be using. Will try to avoid too many numbers, and keep it simple.

Can you guys share ur plans too.

Regards

Gurpreet

On Sun, Jan 20, 2013 at 2:28 PM, Satya Peesapati <sagi...@yahoo.com> wrote:

Hi all,

 

I haven't had a chance to work on this till date. I am starting now and I have seen many companies were discussed...I am going ahead with TI and CG, hope we are all OK with this.

 

Regards,

Satya