MS Excel's Solver - Really Solves Real World Financial Problems

Greetings !!

Today I'll be throwing light over some important applications of Excel's Solver Tool with respect to real life financial problems. Here, we will see that how this magical tool executes following "Monsters" before they can even make note of that.

*Pls Note: This Intel is devoted to Solver tool only, detailed explanation of various calculations(like NPV etc.) is beyond the scope of this Intel.

Monster 1 - Complex Mathematical Problem.

Suppose, mathematical formula given by

Y = R*(N+R)+N*(1+R/X)*(X+N/X)+(N/X)*(1+R/N)/(N+4)

Subject to constraints :-

X greater than or equal to 5
R = 15
N = (1+R/X) and N smaller than or equal to 1.5

We have to find the value of X for which Y is minimum.

Killing Monster 1 - Refer to Figure 1

Variables Y, X, R, N are entered in cells A1 to A4
Enter formula as shown in the figure in B1 to calculate Y
Enter any arbitrary values in cells B2 & B3
Enter formula =(1+B3/B2) in cell B4

Go To Tools - Solver, It will open solver window as shown, Enter Solver parameters as shown in the figure and click Solve.

We will find that for value X = 30, Y is minimum i.e. 315
------

------
Monster 2 - Cost of Capital (Cost of Debt Borrowing)

Simplest Formula for calculating Pre-Tax Cost of Debt is as Shown in Figure 2, Kd represents the pre-tax cost of Debt Borrowing.

Now I want to know minimum rate of return that Firm must earn from the raised capital through debt/bond whose current value is 1200 Euro, Maturity Value 2000 Euro, Annual Coupon rate is 10% and maturity period is 10 years.

Killing Monster 2 - Refer again to Figure 2

We can see the formula entered in cell B1, Enter solver parameter as shown and click solve.

We will find that, Firm must earn atleast 14% (i.e after-tax cost of debt) to maintain its value.
------

------
Monster 3 - Capital Budgeting (Buy Vs Leasing)

Lessee has 2 Options, either to purchase an equipment or lease it. (Terms of above options are as given in the figure 3). Now it is required for lessor to find the maximum lease rate that will be accepted by Lessee.

Killing Monster 3- Refer to Figure 3

Firstly it is required to calculate the Net present Value (NPV) of cashflows from the two options. Lessee will accept any lease rate which is less than or equal to the rate at which NPV from both the options is equal.

So from figure, by using help of Solver Tool, we can find that Lessee will accept lease rate smaller than or equal to 10%, because at this lease rate, NPV from both options will be equal, so he/she will be interested in opting Lease option, in which he/she has to pay equal or less lease interest payments than 10%.
------

------
List is very long, this can go on and on....

I think these three examples have given a good idea about Excel Solver tool.

See ya.... Don't forget to comment... Cheers !!

Original Article: http://www.qondio.com/ms-excels-solver-really-solves-real-world-financial-problems

Analysis of World Stock Markets

Greetings !!

Hope everybody is fine, and enjoying life at its fullest. As, I am still searching a good job, last week I had full time to spend in analyzing World Stock Markets and their performance over last 10 years. It's an ongoing process but I would like to share some of the findings till now, one can consider it as a detailed excerpt.

Data used : From Jan 01, 2001 to Dec 31, 2011
*For S&P Global 100 and S&P Global 1200, only last 5 years data was available.

Market Indices taken in consider : S&P 500, NASDAQ, DOW JONES, NIKKEI 225, IBOVESPA, RTSI, NIFTY, BSE SENSEX, SHANGHAI, HANG SENG, KOSPI, STRAIT TIMES, DAX, FTSE 100, CAC 40, S&P GLOBAL 100, S&P GLOBAL 1200

Economic Factors considered : GDP Growth Rate, Investment(as %GDP), Gross National Savings (as %GDP), Average Inflation(CPI YOY Basis), Change in Import volume of goods and services, Change in Export volume of goods and services, Unemployment rate, General Government Expenditure(as %GDP), General Government Revenue(as %GDP), General Government Net Lending/Borrowing(as %GDP), Current account balance(as %GDP), Real Interest Rate

Tools Used : Microsoft Excel 2003, SPSS Statistics 17.0

This excerpt be will limited to below attached 10 images/figures.

Figure 1

It depicts absolute 10 year return of Major Stock Markets in the world. Russian RTSI has clearly outperformed other indices while Indian, Brazil & South Korean Indices have managed to give decent return as compared to others over last 10 years. It is interesting to know that US & European Markets have failed to give any significant return during the same period. Clearly Emerging Markets have outperformed others in terms of 10 year return.

Figure 2

It depicts yearly return at a point of time. Russian RTSI, China SHANGHAI, Brazil IBOVESPA & Indian BSE Sensex have been most active indices, both on upside and downside of the return scale. Russian RTSI seen to be most volatile index among all.

Figure 3

This is simple and quite interesting depiction of Return against its Variability/Volatility of major stock indices. SHANGHAI has large difference between its mean & median return over last 10 years while US & European Markets have comparatively less standard deviation(i.e they are comparatively more stable) but they failed to give any significant magnitude of return to investors.

KOSPI, BSE Sensex, Nifty 50, IBOVESPA & RTSI have clearly emerged out as favorite as their mean & median return are almost similar and not very far from the standard deviation line with comparatively decent returns.

Here nature and behavior of S&P Global 100 & S&P Global 1200 indices are not so Global. They still resembles majorly developed markets only, emerging markets seems to have very little space in their global indices composition. That's why I choose not to analysis these so called Global Indices further.

Figure 4

It shows correlation among World Stock Markets. It can easily be seen that all these indices' return are highly & positively correlated with each other. HANGSENG & Strait Times are two mostly correlated indices with rest of the world indices(specially SHANGHAI).

Figure 5a & 5b

These 2D figures shows behavior of US Market Indices and various economic factors/variables over last 10 years. Among all, Unemployment Rate and Government Revenue seems to be two most correlated economic factors with US Market Indices' return.

Figure 6a & 6b

These 2D figures shows behavior of Indian Market Indices and various economic factors/variables over last 10 years. These factors individually are not highly correlated with the Indian Market Indices' return. GDP Growth Rate is most positively correlated factor among all.

Figure 7

It shows the regression equation of US S&P Index Return with respect to various economic factors affecting it. It is interesting to note that out of considered economic factors, Index return is increasing with increase in Unemployment Rate and Average Inflation(CPI). This needs further research.

Figure 8

It shows the regression equation of Indian Nifty 50 Index Return with respect to various economic factors affecting it. It is interesting to note that constant value is negative and Index return decreases with increase in exports. This needs further research.

Not sure, how accurately & rationally I made my effort, but it surely gives a brief insight about World Stock Indices and their respective behavior over last 10 years.

Hoping it was worth to read... Always Love to have comments/corrections/opinions... Have A Great Day... Cheers !!

Original Article : http://www.qondio.com/world-stock-markets-from-my-eyes

How Tax & Inflation eat my Investments' Return

Hi All,

Hmm.. tough time, specially with these monsters called "Tax" and ever rising "Inflation" around. They resemble "Piranhas" in eating up return on my investments. Here, how they are...

Impact of Tax alone

Considering only "Income Tax" for now, suppose I invest $1000 in 1 year Bank Fixed Deposit fetching around 10% nominal interest rate compounded annually and my little income falls into 10% tax slab.

After 1 year, invested $1000 fetching 10% return becomes (1+0.10)*$1000 = $1100,
making my nominal gain $1100-$1000 = $100

But due to tax, Gain after Tax = $100(1-0.10)= $90
i.e my effective return(after tax) will be $90/$1000 = 9%

So, tax eats up my 1% return for the given scenario.


Impact of Inflation alone

Considering same investment of $1000 in 1 year Bank Fixed Deposit fetching around 10% nominal interest rate compounded annually and assuming annual inflation rate of 10%.

After 1 year, invested $1000 fetching 10% return becomes (1+0.10)*$1000 = $1100

But due to inflation, the purchasing power of $1100 after one year is same as purchasing power of $1100/(1+0.10) = $1000 right now. That means, with 10% inflation and 10% return on investment, my effective return is 0%, yes a big "ZERO" or In other words, my purchasing power remains the same.

So, inflation eats up my whole return for the given scenario.


Combined Impact of Tax and Inflation

Considering same example, investing $1000 in 1 year Bank Fixed Deposit fetching around 10% nominal interest rate compounded annually, my little income falls into 10% tax slab and assuming annual inflation rate of 10%.

After 1 year, invested $1000 fetching 10% return becomes (1+0.10)*$1000 = $1100,
making my nominal gain $1100-$1000 = $100

But due to tax, Gain after Tax = $100(1-0.10)= $90, and after-tax inflow will be $1090.

Now, its reduced purchasing power due to inflation is given by $1090/(1+0.10) = $990.91, i.e net return after tax and inflation will ($990.91-$1000)/$1000 = -0.91%

It means, in actual I am losing my money(by 0.91% a year), if I invest in current scenario.

So, Does it mean that I stop investing ???

No, because if I don't invest, then considering 10% inflation rate, my uninvested amount $1000 becomes $1000/(1+0.10) = $909.09 after 1 year and that means, I'll be losing my money/purchasing power by 9% which is 10 times higher than 0.9%.

I should keep on investing and try to invest in an alternative that can fetch higher return to nullify the combined effect of tax and inflation.

It should fetch return higher than {[(1-tax rate)*nominal interest rate]- inflation rate}/(1+inflation rate), in order to have effective gain over an investment.


Be a smart investor rather than being just an investor... Hope it was worth to read...  !!

Original Article : http://www.qondio.com/how-tax-inflation-eat-my-investments-return

Tricky Average: Mean, Median & Mode

Hi,
Hoping you all are in great health and enjoying prosperous time.

Its been long since I last post my Intel. Hey !! Here I come, this time throwing some light on a tricky statistical/mathematical tool called "Average". Going by definition "An Average is a measure of a central tendency or in other words, it's a value that defines the whole set of values to which it belongs."

Now many of us may believe that "Average" and "Mean" is exactly same... hmm... but that is only 1/3 of the truth. Yes, you heard me right, any one of the terrific trio in stats "Mean", "Median" or "Mode" can be regarded as an "Average" individually.
 
Mean, we know, arithmetic mean i.e sum of values divided by total no. of values. Median is the central value that divides a set of values into 2 halves i.e upper 50% and lower 50% while Mode is the value that appears most in a set of values.
Example : for values 1,2,2,3,4,5,6
Mean = 3.28, Median = 3, Mode = 2
Now, there is a trick/catch, which is being used to fool/dupe customers, don't believe me.... See by yourself...
Let suppose, I have to buy a laptop and I visit some electronic store. Salesman gives me following 3 options with respective ratings* :-
Brand 1 - Average Rating 4 out of 5
Brand 2 - Average Rating 3 out of 5
Brand 3 - Average Rating 5 out of 5
*ratings based on last 10 respective customers reviews

Now, on first look I may go for Brand 3, as it has the best rating among all three, But Hey !! I am not a guy who can be easily fooled or duped. The term "Average" doesn't give me ample information which I would like to have. Rather, I would be interested in knowing whether "Average" here refers to Mean or Median or Mode.

Now, have a look at detailed rating data given by last 10 respective customers below :-
Brand 1 - 4,3,3,4,4,4,4,5,4,5
Brand 2 - 1,2,2,2,3,3,4,4,4,4
Brand 3 - 1,2,2,1,3,4,5,5,5,3

It is clear from above data that,
Brand 1 used "Mean" as an Average
Brand 2 used "Median" as an Average
Brand 3 used "Mode" as an Average

In Brand 2 & Brand 3 there is large variations among their respective customer ratings while Brand 1 seems to have comparatively stable, consistent and high ratings. So, my final decision will definitely be to buy Brand 1.
 
Hope it was interesting and worth enough to read.... always love to have your precious comments/opinions.... Cheers !!

http://www.qondio.com/tricky-average-mean-median-mode

CAT Papers 2001 - 2008 with solutions

Prestigious B-School Entrance CAT Papers 2001 - 2008 with solutions*

Download CAT Papers 2001-2008 with Solutions


*Please note actual IIM answers may differ

MBA Tutorials: Entity, Attribute and Relationships

Entity


An entity is a business object/real world thing like person, place, event etc. that represents a group, class or category of data.

Entities are represented by database tables in a relational database, with each row of the table representing an instance of that entity.

Like student, customer, school etc.



Attribute


A sub group of information within an entity.

Attribute is a unit fact that describes characteristics or properties of an entity.

Like roll no., age, name, height, weight are attributes that describe student.

Attributes are represented by table columns in database.



Relationships


Relationships are associations among entities.

Relationship lines indicate that each instance of an entity have a relationship with instances of the connected entity, and vice versa.

There are 4 types of relationships:-

One to One Relationship

A one to one relationship is an association between two entities where an instance of one entity can be associated with at most one instance of the other entity and vice versa. Like husband and wife etc.

The relationship is also mandatory in both directions.

One to Many Relationship

A one to many relationship is an association between two entities where an instance of one entity can be associated with more than one instances of the second entity while any instance of the second entity may be associated with at most one instance of the first. Like School and Student etc.

One to many relationships are quite common, in relational database, maximum relationships are in one to many format.

Many to Many Relationship

A many to many relationship is an association between two entities where any instance of the first entity may be associated with more than one instances of the second entity and vice versa. Like Teacher and Student etc.


Recursive Relationship

Instance of an entity may have relationships with other instances of the same entity. Like in an entity Employee, an instance (a particular employee) may be a father/mother/husband/wife/relative of another instance.

Depending upon the mutual relationships between the instances of the same entity, it can be classified into three categories :-

One to One
One to Many
Many to Many

MBA Tutorials: Foreign Direct Investment and Foreign Institutional Investor

Foreign Direct Investment(FDIs)

* FDI occurs when an investor from one country obtains or acquires or invests in an asset in another country.
* Long term Investments
* Focus Long term returns
* In India, growth rate of FDI are more than the GDP growth rate
* In the long run, FDIs are able to provide stable capital inflows
* In the long run, FDIs are proved to be more fruitful and balanced from investment & savings point of view.


Foreign Institutional Investor(FIIs)

* An institution or entity, which invests money in the financial stock markets or securities of another country.
* Invest in Equities & Debts
* Short term Investments
* Highly volatile
* Focus Short term returns
* FIIs have been the key drivers of the stock markets.
* When FIIs withdraw the capital from the domestic market or we can say when they sell their share it creates the demand for US dollar and that time demand for dollar will be more and INR will depreciate.

Scholarships In India

All the currently available and closed scholarship & fellowship notifications in India.

• Scholarships In India
• International Scholarships
• Awards & Competitions
• Fellowships In India

A must visit site for young budding scholars, engineers, doctors, MBA etc.

MBA Tutorials: Economic Growth and Economic Development

Economic Growth

-It is a Narrower Concept.
-It refers to quantitative changes in the economy.
-This term generally signifies developed countries
-It is an increase in a country's quantitative national output.
-It can be measured by an increase in a country's GDP(Gross National Product).


Economic Development

-It is a Broader Concept.
-It refers to both qualitative and quantitative changes in the economy.
-This term generally signifies developing or under-developed countries.
-It is an increase in living standards, improvements in self needs/demands, having more choices/options etc.
-It's measurement depends upon various factors like Life Expectancy Index, Education Index, Adult Literacy Index, Human Development Index etc.


Economic Growth is the necessary but not sufficient for Economic Development.

MBAUniverse.com : Top MBA coaching institutes in India

MBAUniverse.com : Top MBA coaching institutes in India

Management portal MBAUniverse.com have announced the city-wise ranking of leading MBA test preparation / coaching institutes.

The survey results include cities like Delhi, Mumbai, Chennai, Kolkata, Bangalore, Hyderabad, Chandigarh, Bhopal, Jaipur, Pune, Indore and Patna.

A very helpful link, if you are looking for a MBA coaching institute.Though, I am firm believer that self study is a best form of study, no coaching institue can proved to be better than yours self preparation. A good coaching institute helps you only in maintaining a regular schedule/timetable, it's your self study that will proved to be decisive.

Joint Management Entrance Test (JMET) previous year papers

Joint Management Entrance Test (JMET) is the first step in the process of admission to the TWO YEAR FULL TIME Postgraduate Degree Programmes in Management offered by IITs.

Click below to download JMET previous years ( 2005 - 2008 ) papers with answers.


JMET Papers 2005-2008 with Solutions


Institutes accepting JMET Score :-

• All the IITs and IISc
  
• NIT Calicut
• Rajiv Gandhi Institute of Petroleum Technology (RGIPT), Noida
  

Do comment.......
_

Top Institutes / Colleges / B-Schools under Management Aptitude Test (MAT)

There are many good b-schools which are under MAT, some of the good names are as under :-

Fees less than 5 Lakhs (2009 year)

• NIT Durgapur
  
• Christ University, Bangalore
  
• IIRM Jaipur
  
• Various state universities
• Maharashtra CET exam (Feb MAT)

Fees more than 5 Lakhs (2009 year)

• Amity Business School,
  
• Alliance Business Academy, Bangalore
• UPES, Gurgaon
• Asia Pacific, Delhi
• NDIM, Delhi
• XISS Ranchi
• MS Ramaiah,bangalore

Institutes in bold letters are in Top 100 B-schools of India. Just google for more information about an individual institute listed above. Best of Luck.........
_

10 Important Characteristics of an Effective Communication.

From my personal experiences, these are the 10 most important characteristics of an Effective Communication and Public Speaking. Also, given below are the quick tips for improving these characteristics.


1. Clarity :

• One of the most essential characteristics of an impressive communication is "Clarity".
• Use Simple and Sound words, so that listeners can grab it easily.
  
• Be clear in your thoughts, jumbled & confused mind cannot deliver a good and clear saying.
• Avoid using any technical terms, try to explain in laymen language.
• Use Examples to explain & support complex scenarios.
  
• Work a little bit on your accent and pronunciation.
  

2. Aim or Goal :

• At every stage of your talk/communication, don't forget your "Aim or Goal".
• Try to deduce an acceptable stuff by judging Pros & Cons impartially.
• Communicate with a broad and practical mind.
  

3. Precision :

• Be precise & exact in your approach. Neither be too deep nor be too short.
• Include some good facts acknowledging your topic.
  

4. Avoid Repeatability, unless required so.


5. Linkage :

• Try to maintain a logic link between your sayings.
• Don't put two opposite faces of coin at a same time.
• Deliver in a structured & planned way.
  

6. Globalization & Localization :

• Try to explain the broader aspects but not on the cost of local values.
• Aggregation of local values should result into global and broader aspects.
  

7. Style of Expressing :

• Control various speech parameters like pitch, tone, intensity etc. according to the environment.
• Don't be too fast or too slow.
  
• Light Humour at the right time is always accepted.
• Look straight & forward. Keep a light smile on your face.
• Avoid using words that show arrogance.
• Feel what you say.
• Avoid being too formal, be natural and practical.
  

8. Know & Analyze your audiences.


9. Do a good Homework.


10. Dress Properly :

• 25% confidence and 25% Respect from audiences comes automatically, if you have dressed up well.
• Be neat, clean, ironed and polished irrespective of the fact that you have dressed up formally or informally.
• Do a good hair styling, avoid any casual or unethical looks.
  

These habits are hard to develop but once developed, they become hard to give up.

Live your life lively because life is very short.

_

Notification : Management Aptitude Test ( MAT Dec-2008 )

Management Aptitude Test (MAT) on December 7, 2008



MAT Bulletin :
Rs. 750/- by cash across the AIMA, New Delhi counter / select Bank of Baroda branches / AIMA Study Centres all over India. Rs. 790/- against Demand Draft (DD) by post.The Demand Draft should be in favor of "All India Management Association", payable at New Delhi.

Important Dates :

• Last date of Availability of MAT Bulletin : 12 November 2008(Wednesday)
• Last date for Receipt of Filled form At AIMA, New Delhi : 17 November 2008(Monday)
• Test Date : 07 DECEMBER 2008 (Sunday)

Participating Management Institutes


For More Details Click Here

_

Build Your Vocabulary With Fun

Learn English Vocabulary in the most funny way. Around 100 words will be added each week.

These words are very common in various competitive exams like CAT, GMAT, GRE, XAT, FMS, TOEFL etc.

Guess the right word using clue in the image attached and the hint(if given).

Get and Login from your C2W.com id and you can save your current status for future.



All The Best

Top Colleges / Institutes Ranking of India

Successcds.net provides their own Top Colleges in India Rankings in the field of Engineering, Medical, Arts, Commerce, Fashion, Science , B.Schools - MBA, Hotel Management, Healthcare, Mass Communication, MCA etc.

Further in each field, they have further divided and given the rankings in sub-categories.

Few of the Rankings given are :-

• Top 50 B-Schools in India
• Top 100 Engineering Colleges India
• Top 10 Fashion Technology Institutes in India
• Top 10 Institutes for Mass Communication
• Top Indian Hotel Management Institutes (10)
• TOP MCA Institutes - Universities
• Top Health care Management Institutes
• Top 25 Medical Colleges in India
• Top 25 Law Colleges in India
• Top 10 Arts Colleges in India
• Top 10 Commerce Colleges in India etc.

These rankings are updated often and a must watch data for a student enrolling in any professional / non-professional course.

Top Colleges / Institutes Ranking of India

_

PaGaLGuY.com : Top B-school Rankings 2008

Top MBA forum Pagalguy.com has come up with it's B-school rankings for the year 2008. Whole team have polled 5,410 MBA students, aspirants, alumni, recruiters and academicians.

Classification of Rankings given :-

• National Rankings (Top 76)
  
• Regional Rankings (Top 76 each)
  
• Best Private B-Schools (Top 49)
  
• Best B-Schools by Entrance Exam ( CAT - Top 42, XAT - Top 16, SNAP - Top 7)
  

A very useful data for calculating your preferences, must watch for a MBA Aspirant.

Click here to visit the site or you can directly download the pdf version by clicking here.

_

CAT Tutorials : Permutations & Combinations

Factorial of n: n! = n(n-1)(n-2)…3.2.1

1! = 0! = 1

Permutations refer to the different arrangements of objects by taking some or all at a time. It is denoted by nPr , where n is the total number of objects and r is the number of objects taken at a time for arrangement.

nPr = n! / (n-r)! For r < n

= n! For r = n i.e. all the objects taken at a time

If n = p1 + p2 + p3 +…+ pr , where p1, p2, p3…pr are group of similar objects


Then number of permutation of these n objects

= n! / [(p1)!.(p2)!.(p3)!.....(pr)!]


Combinations refer to the selections or different groups, which are formed by taking some or all of the number of objects. It is denoted by nCr , where n is the total number of objects and r is the number of objects taken at a time for arrangement.

nCr = n! / r!(n-r)! For r < n

= 1 For r = n i.e. all the objects taken at a time

= n For r = 1


nCr = nC(n-r)
nCr + nC(r-1) = (n+1)Cr

Note: ab and ba are two different permutations but they are same combination.

CAT Tutorials : Tenses

Tense express the time of verb

1. Simple Present Tense :
   used for things that are true at the present,
   but not necessarily happening right now
   e.g. He plays for the Chennai team.


2. Simple Present Continous Tense :
   used for things, happening right now
   e.g. he is playing with a great spirit.


3. Simple Past Tense :
   used for things that were true in the past,
   but not necessarily happening that time
   e.g Ryan went to the zoo and enjoyed a lot.


4. Simple Past Continous Tense :
   used for things, that was happening in the past
   e.g. He was eating his lunch at that time.


5. Simple Future Tense :
   used for things that will be true at the future,
   but not necessarily will happen
   e.g. He will play for the Kolkatta team.


6. Simple Future Continous Tense :
   used for things, will be happening in future
   e.g. He will be playing for the Kolkatta team with a great hope to win.


7. Present Perfect Tense :
   indicates action occurred in the past and has continued into the present
   e.g. He has been a great employee.


8. Past Perfect Tense :
   indicates an action completed in the past before another action completed in the past
   e.g. His century had been the fastest before the one scored by Sachin.


9. Future Perfect Tense :
   indicates an action will be completed in future before another future action
   e.g. By Monday, RBI will have reduced the CRR by 2 percent .

_

CAT Tutorials : Verbs

Verb is a part of speech which express state of being or state of action.

1. Linking verbs or Being verbs :
   verb express state of being, not doing any action
   e.g. Ram is a boy with a big and generous heart.


2. Action verbs :
   verb express state of action
   e.g. He presented her a gold ring.


3. Helping Verbs :
   help main verb to express meaning
   e.g. He is playing with a great determination.


4. Infinitive Form : to + verb
   e.g. to move, to laugh etc.