1Assignment Number 2

Total Marks- 30
Deadline- Wednesday, 16th of November

Note: Do not copy from others. Plagiarism will lead to disqualification of answers sheets. I will use
Plagiarism Software Urkund for Plagiarism Check.

Question Number 1: Read the given Case and answer the provided question using Excel wherever
necessary (10 Marks)

The Cement Industry- Cement- The Wonder Material

Cement is a mixture of compounds, consisting mainly of silicates and aluminates of calcium, formed
out of calcium oxide, silica, aluminium oxide and iron oxide. Cement is manufactured by burning a
mixture of limestone and clay at high temperatures in a kiln, and then by finely grinding the resulting
clinker along with gypsum. The end product obtained through this process is called Ordinary
Portland Cement (OPC). In India, OPC is manufactured in three grades, namely 33 grade, 43 grade
and 53 grade. The grade indicates the compressive strength of the cement after 28 days. Another
popular general purpose cement is the Portlant Pozzolana Cement (PPC). Apart from these two types
of cement, there are special purpose cements such as sulphate resistant cement, coloured cement
and oil well cement.

Cement Industry in India

Cement is one of the important ingredients for the infrastructure development. Thus, the Indian
cement industry is directly related to our country's infrastructure sector, and thus, its growth is very
important for the development of our country. The growth of the cement industry is aided by the
potential growth in the infrastructure of our country. The Indian cement industry is the second
largest producer in the world, accounting for 8% of the total global production. The Indian marker is
also the second largest consumer of cement. Our country has a cement production capacity of
around 366 million tonnes (MT). The below table provides data about the production of cement in
our country. The Indian cement industry is dominated by a few companies. The top 20 cement
companies account for almost 70% of the total cement production of the country. Ambuja Cement,
ACC Limited, Binani Cement, India Cement, Madras Cement, Shree Cement, Prism Cement, Ultratech
Cement and Lafarge Cement are some of the leading manufacturers of cement in India. High capital
costs, long gestation periods and access to limestone reserves act as entry barriers for this industry
for new players.

Production of Cement over the years

Year Production of cement in million tonnes
2006-2007 156
2007-2008 168
2008-2009 182
2009-2010 209
2010-2011 229
2011-2012 247
2012-2013 291
According to the working group for the 12th five-year plan, the projected cement consumption of
cement indicates an upward trend in the demand for cement. In India, the housing sector is the
biggest demand driver of cement, accounting for about 67% of the total consumption. The other
major consumers of cement include infrastructure at 13% commercial construction at 11% and
industrial construction at 9%. Cement is a cyclical commodity with a high correlation with gross
domestic product.

The per capita consumption of cement in India still remains substantially low at about 192 kg when
compared with the world average. Hence, it is clear that there is a tremendous scope for growth in
the Indian cement industry in the long term.

The Various Cement Manufacturers

Cement factories are located in places with easy access to limestone reserves. Limestone deposits
are available in few states, and hence the cement factories are also located in large numbers in a few
states. A total of 188 large cement plants together account for 97% of the total installed capacity in
the country, whereas 365 small plants account for the rest. Of these large cement plants, 37 are
located in the erstwhile state of Andhra Pradesh, 21 in Rajasthan, 19 in Tamil Nādu, 12 in
Gujarat, 11 each in Madhya Pradesh, Karnataka and Uttar Pradesh, 10 each in Jharkhand and
Maharashtra and the balance are located in the other states of our country. The installed capacities
of cement production in the five regions of South, North, East, West and Central are 126.9, 66.4,
43.5, 44.1 and 37.3 million tonnes, respectively.

The installed capacity of the various cement manufacturers is given in the below table.

Installed capacity of various cement manufacturers

Company Installed capacity
Grasim Industries 25.65
JP Associates 17.5
Madras Cements 13.72
Ultratech Cements 24.3
India cements 14.05
Dalmia Cements 9
Key Financials of the Cement Industry

The total incomes of the cement industry for the financial years 2008, 2009, 2010, 2011, 2012 and
2013 are 54264.7, 59843.3, 64726.8, 84086.7, 96928.4 and 107512.4 crore, respectively. The income
growth for these periods are 29.7%, 10.9%, 11.7%, 6.6 %, 28.6% and 14.7%, respectively. For the
financial year 2013, the indirect taxes constituted 14.9% of the operating expenses, the excise duty
constituted 14% of the operating expenses, the cost of raw materials and spares constituted 18.5%
of the operating expenses and the cost of power and fuel constituted 25.2% of the operating
expenses. The operating expense for the financial year 2013 was 100298.8 crore. Table below
provides a comparative statement of key financial indicators of leading cement manufacturers for
the financial year ending 2013.
Key financial indicators of leading cement manufacturers for the financial year ending 2013
Cement Total income in Net sales in Rs Total expenses in Profit after Tax in
manufacturer Rs crore crore Rs crore Rs crore
Ultratech
20704.4 20279.8 18452.9 2144.4
 Cements
ACC 11590.1 11149.6 10487.8 1095.8
Ambuja Cement 9817.3 91743.3 8404.4 1294.6
Shree Cement 6168.1 5887.3 5397.3 787.2
Prism Cement 5160.8 4964.8 5273.3 -81.7
India Cements 4529.8 4479.4 4596.7 -35.9
Net Sales of JK Cements
Quarter Net sales , in Crores
March,2011 679.7
June,2011 609.1
September,2011 517.8
December,2011 618.2
March,2012 809.4
June,2012 737.5
September,2012 714.9
December,2012 688.1
Answer the following question. Use Excel wherever necessary.

1. Construct a pie chart to present the major consumers of cement in our country. Comment
on the Pie Chart
2. Construct a bar chart to present the production of cement in our country. Explain the Bar
Chart.
3. Summarise the data about the projected consumption of cement in our country. What
inference you can draw from the summary.
4. Construct a bar chart indicating the projected consumption of cement in our country. Explain
the Bar Chart.
5. Represent the installed capacity of cement production in the five regions using a suitable
chart.
6. Represent the details of the operating expenses for the financial year 2013 using a suitable
chart.
7. Using a suitable chart, present the total income, operating expenses for the financial year
2013 of the five cement manufacturers given. Explain why you consider the chart as a
suitable chart.
8. Represent the net sales of JK Cements Ltd using a suitable chart. Explain why you consider
the chart as a suitable chart.
9. With the help of these charts and data, prepare a brief analysis of the cement industry in
India. Write answer in minimum 300 words.
Question 2. Read the given Case and answer the provided question using R (Excel for Q8)
(10 Marks)

MRF LIMITED-THE MUSCLEMAN AND HIS MAGIC

The MRF Story started during the 1940s with a rubber balloon manufacturing factory in a shed in
Thiruvottiyur, Chennai, with a funding of 14,000 by KM Mammen Mappillai. Today, MRF is a
multibillion business that produces quality tyres used all around India and internationally along with
a presence in paints and coats, toys, motorsports and cricket training. In 1952, MRF started
manufacturing tread rubber and by 1956, MRF became the leader in tread rubber with a market
share of almost 50%. In 1961, MRF became a public company and manufactured its first tyre from its
factory in Thiruvottiyur, Chennai.

MRF supplies its tyres to the original equipment manufacturers and the replacement market is
serviced through its authorised dealers. MRF's strength lies in brand loyalty and an extensive
distribution network with more than 9,000 dealers, apart from its strong presence in the
replacement market. Almost 76% of the company's sales turnover is contributed by the sales in the
replacement market. Also, the replacement market is expected to have a steady rate of growth
because of sustained rise in the absolute number of vehicles on the road. MRF manufactures tyres
for passenger cars, two wheelers, off the road vehicles, trucks, buses and tractors.

Table 1. provides information about the sales turnover, total expenses, net profit and book value of
MRF limited.

Table 2. provides details of the share price of MRF Limited as quoted in the National Stock Exchange
(NSE).

Table 1. Key Financials of MRF Limited

Sales turnover, Total expenses, in Rs Net profit, in Rs Book Value, in Rs

Year in Rs crores crores crores crores
2004 3034.18 2404.13 28.8 1705.7
2005 3427.29 2840.98 40.31 1777.94
2006 4255.84 3477.81 79.91 1943.56
2007 5054.2 4035.76 171.78 2325.2
2008 5731.63 4711.96 141.97 2642.66
2009 6146.06 4759.7 256.32 3210.03
2010 8104.31 6784.83 353.98 3986.38
2011 10644.86 9234.54 619.42 5417.81
2012 11870.18 10627.01 572.36 6738.28
2013 12131.16 10391.25 802.21 8594.71
Table 2: Share price of MRF

Closing Total quantity of Number

Day Opening Price Highest Price Lowest Price Price shares traded of trades
02.06.1
4 22506 23130 22506 23009.15 5101 2061
03.06.1
4 22985 23300 22950 23198.45 3638 1132
04.06.1
4 23189.4 23000.05 23055.1 23142.4 2060 745
05.06.1
4 23012 23921.65 23012 23848.4 7363 2402
06.06.1
4 23900.05 24548 23900.05 24340.2 9635 2730
09.06.1 24150 24635 23122 24585.9 4142 1369
 4
10.06.1
4 24560 24634.65 24192.95 24242.75 6130 2578
11.06.1
4 24265.05 24599 23655 23785.3 7476 2103
12.06.1
4 23784 24085 23705.6 23866.95 2877 1074
13.06.1
4 23800 24079.15 23200 23279.05 5082 1929
16.06.1
4 23250 23410 22448.95 22804.6 6487 2720
17.06.1
4 22801.7 23550 22801.7 23469.3 5179 1960
18.06.1
4 23400 23888 23400 23483.9 5118 1649
19.06.1
4 23332 23749.95 23200 23312.15 2692 1106
20.06.1
4 23263 23500 23110.05 23180.5 1916 765
23.06.1
4 23300 23697.95 23250 23610.9 4893 1732
24.06.1
4 23601 24048 23601 23798.05 5502 1857
25.06.1
4 23521 23950 23406 23490.6 5333 1945
26.06.1
4 23580 23687 22986.95 23290.2 10634 2091
27.06.1
4 23340 23550 23110.5 23176.3 5171 1466
30.06.1
4 23199.6 23670 23199.6 23611.6 3791 1489
Answer the following question using R. For Question number 8 only use Excel.
1. Find the average sales turnover of MRF Limited for the period 2004-2013. Comment on your
answer.
2. Find the average net profit of MRF Limited for the period 2006-2013. Comment on your
answer.
3. Find the average book value of MRF Limited for the period 2005-2012. Comment on your
answer.
4. Find the average highest price of the share of MRF during the month of June, 2014.
Comment on your answer.
5. Find the average closing price of the share of MRF during the month of June, 2014. Comment
on your answer.
6. Find the average number of trades of the share of MRF during the month of June, 2014.
Comment on your answer.
7. Find the average quantity of shares of MRF that was traded during the month of June, 2014.
Comment on your answer.
 8. Conduct a trend analysis in Excel using regression line to predict net profit of MRF on the
basis of Sales Turnover. Comment can we significantly predict net profit of MRF on the basis
of Sales Turnover. If Yes or No, why? Also, comment on the accuracy of the prediction
considering if the prediction is significant.

Question 3. Read the given Case and answer the provided question using R and Excel (10 Marks)

NATIONAL STOCK EXCHANGE- AN INVESTMENT CALLED INDIA

The National Stock Exchange is India's leading stock exchange. In the 1980s, the Indian stock market
saw decent growth in business. The trading volume of the stock market increased substantially.
However, during this period, the service levels of the stock brokers were poor and many small
investors were finding it difficult to buy and sell shares. The operations of the stock brokers were not
and there was a general opinion that the stock market transparent was not a safe place for small
investors. Most of the small investors were at the mercy of the brokers. Although the stock market
was witnessing impressive growth, the market was plagued with lack of transparency and poor
service levels. In 1991, the Government of India constituted the Pherwani Committee to review the
functioning of the stock market and provide recommendations for improving the stock market so as
to make it possible for all to participate in it in a more transparent manner. The Pherwani Committee
recommended the establishment of an integrated national stock market system. For this system to
be successful, the Committee also recommended the establishment of a national clearing and
settlement system, a depository system for enabling paperless trading of shares. Based on the
recommendations of the Pherwani Committee, the National Stock Exchange (NSE) was started in
November 1992 with the objective of providing a nation-wide trading facility for equities and debt
instruments. NSE was promoted by the leading financial institutions of India with the ownership
removed from the right to trade in it. NSE commenced its operations in April 1993. Now, NSE has
three business segments, namely the wholesale debt market, capital market and derivatives market.
NSE operates a nation-wide electronics-based market for trading in equities, equities-based
derivatives, currency futures and options, equity-based exchange traded funds, gold exchange
traded funds and retail Government securities. With the functioning of NSE, transparency was
introduced into the stock market and various steps to ensure the safety of the small investors were
taken up. This resulted in the huge increase in the stock market. The number of companies listed,
volume traded and the number of investors increased. Today, any Indian with a knowledge of the
stock market can invest in it with complete transparency and safety. Technology has been a great
enabler of this. NSE has used cutting edge technology to provide the necessary support to all the
stake holders of the stock market.

S&P CNX NIFTY is the major index of the NSE. It consists of 50 well-diversified list of companies
whose shares are actively traded in the exchange. NSE Infotech Services Limited is a fully owned
subsidiary of NSE started to cater to the technology needs of NSE. The trading system of NSE,
referred to as the National Exchange for Automated Trading (NEAT), is an advanced system based on
the client server architecture. The NEAT system has uptime record of 99%. The year 2014 witnessed
record-breaking rally in the Indian stock market. The stock market has seen a positive investor
sentiment, impressive flow of foreign funds into the market mainly due to positive sentiments
attached to the formation of new majority Government in the Centre during May. The months of
June, July and August saw robust trading activities in NSE with the index going northwards
substantially. Table 1 provides details of the number of securities traded, number of trades, traded
quantity and traded value in the Equity Cash Market segment of NSE during the month of July, 2014.
 Table 1. Equity Cash Market segment of NSE during the
month of July, 2014
Number Traded
of Number quantity
Date
securities of trades , in
traded Lakhs
01.07.1 10261.2 16601.0
1612 6861777
4 3 8
02.07.1 11494.1 19477.2
1614 7886278
4 4 5
03.07.1 19466.8
1644 7563989 10976.2
4 9
04.07.1 16394.2
1621 7027220 9965.26
4 1
07.07.1 18875.9
1626 7270352 9853.68
4 1
08.07.1 12242.8 22078.8
1617 9035237
4 8 4
09.07.1 11894.5 19558.7
1607 8725669
4 5 7
10.07.1 1041283 15811.2 25924.5
1582
4 3 7 2
11.07.1 11383.5 19323.6
1587 8162321
4 7 3
14.07.1 13752.5
1589 6812362 7743.22
4 8
15.07.1 14489.2
1598 6589878 8332.8
4 3
16.07.1 17702.1
1604 7487685 9687.23
4 6
17.07.1 4 1601 6985415 8986.87 16753.2 5

18.07.1 4 1598 6807546 8605.84 16084.0 9

21.07.1 4 1616 6263790 7228.96 14273.8

22.07.1 4 1629 6710343 8142.87 15822.1
23.07.1 4 1613 6983291 8638.44 16186.8 8

24.07.1 4 1596 7115623 8412.39 15780.1 4

25.07.1 4 1597 7549672 9530.54 18437.3

28.07.1 4 1601 6118913 7078.72 13342.4 5
 30.07.1 4 1610 7612928 8417.01 18465.0 2

31.07.1 4 1584 7902415 10892.72 22637.6 7

Answer the following question using R and Excel both.

1. Find the average and the standard deviation of the number of securities traded on each day
during the month of July 2014 in the National Stock Exchange. Comment on your answer.
2. Find the average and the standard deviation of the number of trades on each day during the
month of July 2014 in the National Stock Exchange. Comment on your answer.
3. Find the average and the standard deviation of the traded quantities on each day during the
month of July 2014 in the National Stock Exchange. Comment on your answer.
4. Find the average and the standard deviation of the traded value on each day during the
month of July 2014 in the National Stock Exchange. Comment on your answer.
5. Identify on the basis of appropriate measures if the ‘Number of Trades’ can be parametric
data or not (Normal Curve). Justify your answers
6. Provide the 5 Number Summary of Traded quantity and Traded Value using Excel and R.
Comment on your answer.

India's cement industry has a significant impact on the nation's infrastructural development.
It serves as the framework for the large structures that house the planning offices for our
economic activities, actively contributing to the development of the nation. The production
of cement climbed from 156 million tonnes to 291 million tonnes in just seven years,
according to the data provided. Housing makes up the majority of cement users (67%) along
with other sectors like infrastructure ,Constructions and the Industrial usage. Cement
Industry constituted a major part of Gross Domestic Product.
The makers are the next to ask, "From where are we able to generate thus much cement?"
Indian soils include limestone reserves, with which six large businesses are operating at full
installed capacity. The first manufacturer on the list is Grasim Industries (25.65%), followed
by JP Associates (17.5%), Madras Cements (13.72%), Ultratech Cements (24.3%), India
Cements (14.05%), and Dalmia Cements (9%) with the remaining tiny cement producers
handling the rest.
Talking about finances, Cement Industry has been constantly outperforming their standards.
The highest Net Sales aroused in the month of March 2021 which stood around 809.4 crores
leaving behind an operating profit enough to cater the costs of running the organization. The
Southern part of India capitalizes for major stake in the production capacity followed by
North, East, West and Central.
A graphical representation showed all major manufacturers of Cement covering up more
income than their expenses which seems as a good sign. Cement Industry also acts a major
Tax Money generator as it is seen that a huge proportion of their profits is deducted in the
form of taxes, thus reducing their Profit after tax.

After a detailed analysis of these graphs and representations, it can be concluded that
Cement Industry plays a major role in the growth and development of country considering
all major factors influencing and acting upon to it.