ESSENTIALS OF IT

1. From the following link Download the Company-data of Ladakh and highlight as per
the below-given points using Conditional formatting.

Link for Download Required Data: https://data.gov.in/catalog/company-

master- data?filters%5Bfield_catalog_reference
%5D=354261&format=json&offset=0&limit=6&s ort%5Bcreated%5D=desc

Task 1: Top 10 Active Registered companies as per the paid-up Capital. Hight public
companies form this top 10 list.

Task 1.2: Sort the above top 10 companies and make their placement on the top of the
data matrix. (take a snapshot of this arrangement once it is done, here you are advised
to keep five columns only; CORPORATE_IDENTIFICATION_NUMBER,
Company_Name, Company_status, Company_class, PAIDUP_CAPITAL )

Task 2: Using Pivot Table, prepare a frequency table of Active Registered Companies
as per the “PRINCIPAL_BUSINESS_ACTIVITY_AS_PER_CIN”. Please identify the
type of active companies higher in numbers. (10 Marks)

Ans 1.

Introduction

Established Paid up capital is the quantity of cash that an organization has obtained from
economic supporters in change for fairness. Settled capital is created when a company gives
its thoughts on the primary marketplace directly to financial backers, regularly through the
first offer of stock (IPO). While suggestions are sold and bought among monetary supporters
on the discretionary market, no more outstanding, settled capital is created because the
proceeds from such transactions visit the selling financial backers, now not the reliable
association. Ultimately settled capital, also called paid-in capital or contributed capital, is
derived from two resources of funding: standard stock and surplus capital. Each inventory is
assigned a base price, referred to as its norm. Any sum paid by way of a financial sponsor
that exceeds the usual value is referred to as more paid-in capital or paid-in capital above the
same old. The typical well worth of given shares is documented as anticipated stock or
supported inventory on the valuable resource document underneath the financial backer well
worth place.
 Discussion

Task 1

While a company seeks to enhance its worth, it cannot simply promote sections of the
employer to the highest bidder. Associations must gain permission to make public tips via
submitting a utility to the administrative center responsible for selecting associations in the

nation of the union. The high-quality percentage of capital that a corporation can elevate via
inventory idea is referred to as its supported capital. In most instances, the proportion of
recommended capital that an organization seeks is appreciably extra than its present want.
That is finished so that the organization may also, without difficulty, sell extra give shortly if
they want for additional cost development. Because settled-up capital is merely the
proposition of offers, the percentage of settled-up capital can never outperform the
proportion of supported sources.

Task 1.2

Paid-up capital refers to cash that has no longer been obtained. A settled company has
offered everything and cannot grow its worth until it receives money by accepting duty. In
any situation, an employer might be granted permission to promote extra offers. The amount
of paid-up capital of a corporation reflects the volume to which it is based on fee finance to
fund its operations. Given its activities, plan of action, and triumphing industry requirements,
this discern can measure as much as the organization's degree of responsibility to assess
whether it has an excellent association of funding.

CORPORATE PAIDUP
IDENTIFICATION Company REGISTERE CAPITA
NUMBER Company Name status D STATE L

KHATTIEE
CONSTRUCTIONS Leh and
U45201LH2018PTC010735 PRIVATE LIMITED Active ladakh 1000000

U45200LH2017PTC009868 HGHK CONSTRUCTIONS Active Leh and 1000000

 PRIVATE LIMITED ladakh

GRNACO INDUSTRIES
AND
U45203LH2007PLC002732 CONSTRUCTIONSLIMITE Leh and
D Active ladakh 10000000

HAIDARY
U45400LH2014PTC004200 CONSTRUCTIONS Leh and
PRIVATE LIMITED Active ladakh 2500000

U55101LH2008PTC002959 SABOO RESORTS Leh and

PRIVATE LIMITED Active ladakh 11600000

U55101LH2012PTC003571
ZEN ROYAL RESORTS Leh and
PRIVATE LIMITED Active ladakh 1000000

GANGZUR ADVENTURE
U55209LH2020PTC011264
LODGE PRIVATE Leh and
LIMITED Active ladakh 2000000

LAXMINARAYANTARA
FOREX PRIVATE Leh and
U93000LH2013PTC003890 LIMITED Active ladakh 5000000

DEEKINS TRAVEL
SOLUTIONS PRIVATE Leh and
U63030LH2018PTC010310 LIMITED Active ladakh 500000

CHANGTHANGI LENA Leh and

U74999LH2018PTC010434 PRIVATE LIMITED Active ladakh 500000

Table 1: Top 10 Active Registered companies as per the paid-up Capital.

Task 2

To sign up for any corporation, a specific amount of money needs have been invested for the
firm to characterize well. The facts Matrix are just a two-dimensional code that incorporates
vastly numerous "cells" orbits that are coordinated in both a square or rectangle form, that's
known as a passing phase. The statistics to be encoded can be both text and numeric. The
typical statistics size tiers from two or 3 bytes to 1546 bytes. The duration of the encoded
statistics is decided through the wide variety of cells inside the community. Mistake cure
codes are often hired to assemble steadfast quality: whether one or extra cells are damaged,
and the message is jumbled, the news can be inspected.

PRINCIPAL_BUSINESS_ACTIVITY_AS_PER_CIN Total

Agriculture, hunting and related service activities 1

Computer and related activities 2

Construction 22

Education 1

Electricity, gas, steam and hot water supply 2

Health and social work 3

Hotels and restaurants 5

Manufacture of electrical machinery and apparatus n.e.c. 1

Manufacture of food products and beverages 1

Manufacture of furniture; manufacturing n.e.c. 1

Manufacture of rubber and plastics products 1

Manufacture of textiles 1

Other business activities 5

Other community, social and personal service activities 7

Other mining and quarrying 1

Real estate activities 1

Retail trade, except of motor vehicles and motorcycles; repair of personal and household
goods 1

Transport, storage and communications 2

Wholesale and retail trade ; repair of motor vehicles, motorcycles and personal and
household goods 1

Wholesale trade and commission trade, except of motor vehicles and motorcycles 4

Grand Total 63
  

Total
25
20
15
10
5
0

Total

Conclusion

This mixture furnished by the association's individuals is called the proposition capital, and it
is typically the quality amount for which an association may additionally make it gives.
Encouraged offer capital refers to the shows that a company could make, whereas settled
proportion capital refers to the total value of the company's gives. Regardless of how long an
organization's supported deal capital and settled-up percentage capital can be extended, the
settled-up share capital can never surpass the permitted provide capital of the company. A
data system code is a -dimensional code composed of excessive-difference cells that might
be continuously coordinated in a square model; however, rectangular models exist correctly.
The fraction of statistics set aside in the code, which is restrained to 2,333 alphanumeric
characters; increase the number of strains and sections. The L-form that follows its lines is its
finder plan, and scanners put it to use to view and examine the code.
2. Apply filter as per the following points on D3 Table, census 2011 and show the data
pattern using Treemap chart separately for male and female both with consideration of
their reason for migration.

1) focus on Total-to-Total migration [select Total from ‘last residence type (Total/

rural/ urban)” and “place of enumeration type(Total/ rural/ urban)”]

2) focus on “States in India beyond the state of enumeration” from ‘Last Residence’.

3) also, focus on Total from “state of the last residence”

4) also, focus on “All duration” from ‘duration of residence’

5) consider the Data for “India” (select India from ‘Area Name’)

Important instruction: provide the resultant table with appropriate titles (or snapshot)
then show the graphs. The resultant table should cover all reasons for migration and
respective figures for male and female migrants. Use Data Labels and Assign a suitable
title to your chart. (10 Marks)

Donload the data from here =>: https://censusindia.gov.in/2011census/d-series/d-3.html

Ans 2.

Introduction

Treatment is a process of showing hierarchical facts with nesting figures, generally

rectangles, for visualizing and utilizing computer records. Tree maps are a series of nested
rectangles that represent hierarchical (tree-dependent) data. The rectangle is supplied with
every branch of the tree sub-branched in tiled form with smaller rectangles. A rectangle of
the leaf node has a place proportionate to that of the facts. The leaf nodes are frequently
colored to suggest a distinct data measurement. When the scale of color and length are in
some way linked with tree structure, one often sees styles that can be tough to detect in
different respects, including the relevance of a given color. The second benefit of treemaps is
that they efficaciously make use of the area through construction. For this reason, heaps of
gadgets may be shown on the screen at the same time.

Discussion

Analyzing migration from various social, cultural, economic, and political causes is a crucial
component of population studies. The intricacy of people's migration insignificant country
areas permits a massive country like India to comprehend society's dynamics. Studies on
migration are incredibly huge at this critical moment in our country's monetary growth. For
instance, a person's birthplace is a migrant if the area he's listed inside the census differs
from his home. Considering that an individual might have moved numerous times in their
existence, migration by way of the house would no longer provide a terrific photograph of
the existing movement. Alternatively, suppose the vicinity in which someone is listed on the
census differs from his primary closing house region. In that case, he has to be deemed to be
immigration by using the previous residency. This technique could better photograph the
current migration state of affairs via recording the newest movement while human beings
travelled more than as soon as possible.

Census offers information on the migration of some of these streams using both categories to
understand the dynamics and vast factors underlying populace mobility. Migration facts have
been submitted totally for the beginning of the 1961 census given that from obtained 1872
information on shipping area. As accelerated the variety of information gathered on
migration in 1961, together with the urban or rural status of birthplace and the residency
period. Like questions about the beginning region, records have been accumulated based
totally at the last house since the 1971 census. Since 1981, a question has been placed on
'motivation for migration.' The pattern of the census in 1991 and 2001 remained the same as
in 1981, except that the rural city of the beginning was now not collected in the 2001 census.
Because were eliminating the 'herbal Calamities' category as one of the motives at the back
of migration in 1991, the new motive 'Moved at the start' was protected in 2001.

Table D1, D-2, and D-3 are the current set of migration statistics posted. These are the
essential migration tables and are comparable to the ones launched in 1991. These tables
incorporate migrant information with the aid of sex, age, rural-town fame, birthplace, closing
house and area of the census, and motives for motion. Desk D1 and TableD2, handy at the
nation degree alone and for towns, in my opinion, are available at the district stage.
Round 307 million (or 29.9 according to cent) migrants born from doors village/metropolis
of their enumeration were recorded inside the standard population of 1, 028, 6 million in
India as in step with 2001 census. The migrants from inside the district or state to the listing
site had been around 181, 7 and 76, 8 million. Over 48, 4 million migrants, including more or
less 6, 1 million foreigners, have been from other States or Union territories. As shown in
assertion one below, a few 216.7 million migrants have been ladies, extra than their male
counterparts (90.4 million), mainly because they modified their houses because of marriage.

Conclusion

Migrants by using birthplace are individuals indexed at the time of the census in a village
and a city aside from their home. The area in which someone is recorded in the census isn't
like the location of someone's ultimate house. However, the residency of a person is the
same. This approach might provide a better picture of the current migration scenario by
recording the most modern motion while human beings travelled extra than once.2001 As
before, the census accumulated statistics about migration via the beginning and final home
location for every person. Last residency statistics and information, including the length of
life and reason for movement supply, precious enter the studies of population migration
dynamics. While a census is enumerated, a person can also have migrated from another
agreement or town or another nation in India or another state in that same district or from
every other phase of the country or other form.
Q 3a. Assign grades to the following students by considering the following grading
pattern using Vlookup. (take a snapshot of your worksheet and share as an
answer and please make sure that your formula is visible in the same)
Table: Suggested grading pattern.

Marks Grade
Less than 35 marks D
35-44 C
45-54 C+
55-64 B
65-74 B+
75-84 A
85 and above marks A+

Data Table: Roll number-wise marks secured by the students in statistics.

Marks (out
Student_Roll_Number
of
1 100)
7
2 75
3 58
4 92
5 66
6 41
7 94
8 11
9 3
10 14
11 30
12 6
13 59
14 14
15 31
16 70
17 83
18 64
19 78
20 56
 21 90
22 34
23 81
24 84
25 53
26 58
27 8
28 96
(5 Marks)

3.b. Calculate the mean and the standard deviation from the following data
with the help of
EXCEL functions. Here, mean and SD need to be calculated separately for
four columns
(Total MSMEs, Micro, Medium, and Small Enterprises). Write your
interpretation also.
Total No
Total Total No Total No
State Name District Name Of_Medium
MSMEs Of_Micro_Enterpris Of_Small_Enterpris
_
es es
UTTARAKHAND ALMORA 1235 1069 149 Enterprises
17
UTTARAKHAND BAGESHWAR 836 805 29 2
UTTARAKHAND CHAMOLI 739 688 49 2
UTTARAKHAND CHAMPAWAT 802 745 55 2
UTTARAKHAND DEHRADUN 12604 10599 1909 96
UTTARAKHAND HARIDWAR 8882 6416 2262 204
UTTARAKHAND NAINITAL 3752 3002 676 74
PAURI
UTTARAKHAND 4955 4678 264 13
GARHWAL
UTTARAKHAND PITHORAGAR 1061 962 95 4
H RUDRA
UTTARAKHAND PRAYA 740 698 37 5
GTEHRI
UTTARAKHAND 1399 1227 163 9
GARHWAL
UDHAM
UTTARAKHAND SINGH 9746 7763 1844 139
UTTARAKHAND NAGAR
UTTARKASHI 1114 1002 111 1

(5 Marks)

Part A
Introduction
VLOOKUP formulae are generally used to make simulations bendier and consist of various
scenarios in economic modeling and other financial analyses. For example, a VLOOKUP may
look for a low, slight to high layout and enter the applicable economic modeling interest rate.

Discussion

The following is the grading system:

Marks Grade
Less than 35 marks D
35-44 C
45-54 C+
55-64 B
65-74 B+
75-84 A
85 and above marks A+

Following are the grades appointed as per markings:

Student Roll Number Marks (out of 100) Grade

1 7 D
2 75 A
3 58 B
4 92 A+
5 66 B+
6 41 C
7 94 A+
8 11 D
9 3 D
10 14 D
11 30 D
12 6 D
13 59 B
 14 14 D
15 31 D
16 70 B+
17 83 A
18 64 B
19 78 A
20 56 B
21 90 A+
22 34 D
23 81 A
24 84 A
25 53 C+
26 58 B
27 8 D
28 96 A+

Following are the grades appointed as per markings:

Ten students got D, one scholar who was given C, one student who got C+, five college students
who were given B, two students who were given B+, five students who got A, and four college
students who got A+.

So, 10+1+1+5+2+5+4= 28

The VLOOKUP worksheet feature is a device to discover data in a desk or database and extract
relevant data/info. Essentially, the VLOOKUP function tells Excel to: "refer to this item of facts
in this organization of statistics (a table) (e.g., bananas), and inform me about it (e.g., the banana
prices)."

The value this is proven inside the first column of a desk defines the value we would need to
peer.
The array for the desk is the data array to look for. In the left-hand column of this array, the
VLOOKUP function examines.

Col index num (argument wanted) – that is an integer, which specifies the column integer of the
table array supplied, from which you wish a cost to be back.

Conclusion

Range lookup (optional parameter) – This determines what the approach should return if a match
to the research value is not found. Proper or fake can be the argument, i.e.:

Actual – the nearest suit below lookup value, i.e., if a specific match is not discovered.

The false–solid case, i.e., an error, will return if a precise fit is not found.

Part B
Introduction

A standard deviation is a dimension that quantifies a statistics set's dispersion from its average.
While the data points are longer than average, there is an extra massive difference inside the
facts set; subsequently, the more the records are sent, the more critical the same old difference is.
Using determining each difference between the records factor and the implications, the standard
deviation is described because of the rectangular root of the variance.

Discussion

The mean and preferred deviation as follows:

Mean and standard deviation is follows:

State Name District Name Total Total No of Total No of Total No of

MSMEs Micro Small Medium
Enterprises Enterprises Enterprises
UTTARAKHAN ALMORA 1235 1069 149 17
D
UTTARAKHAN BAGESHWA 836 805 29 2
D R
UTTARAKHAN CHAMOLI 739 688 49 2
D
UTTARAKHAN CHAMPAWA 802 745 55 2
D T
UTTARAKHAN DEHRADUN 12604 10599 1909 96
D
UTTARAKHAN HARIDWAR 8882 6416 2262 204
D
UTTARAKHAN NAINITAL 3752 3002 676 74
D
UTTARAKHAN PAURI 4955 4678 264 13
D GARHWAL
UTTARAKHAN PITHORAGA 1061 962 95 4
D RH
UTTARAKHAN RUDRA 740 698 37 5
D PRAYAG
UTTARAKHAN TEHRI 1399 1227 163 9
D GARHWAL
UTTARAKHAN UDHAM 9746 7763 1844 139
D SINGH
NAGAR
UTTARAKHAN UTTARKASH 1114 1002 111 1
D I
Mean 3681.9231 3050.3077 587.9231 43.6923

Standard 4117.1854 3297.0713 830.2705 65.4502

Deviation
Using all the specific types of establishments, we can conclude that the MSMEs are in a more
good-sized quantity than Micro, greater than small, more than medium, respectively. The
standard deviation also follows the exact order because it implies proportional to the standard
deviation without delay. The widespread divergence is a statistical economic indicator that
suggests the historical volatility while implementing the yearly return rate.

The larger the standard deviation of shares, the greater the rate-mean variance, reflecting a
broader range of expenses. For example, there is a vital everyday distinction in unstable
inventories, while the difference from a consistent blue-chip stock is usually tremendously slight.
The default deviation is computed for this reason:

• By combining all facts points and divided by way of the wide variety of information
factors, the standard price is determined.

• For every information factor, the variance is determined by subtracting the imply from
the value of the fact. Every range is doubled, and the findings are then said. The result will then
be divided with the aid of less than one facts factor.

• The rectangular root of the variance—which ends up from range 2—is applied to get the
default.

Conclusion

The well-known deviation is a helpful way to evaluate marketplace and securities volatility and
forecast performance tendencies in investment and exchange strategies. As an instance, an index
fund will, in all likelihood, have a low variance compared to its benchmark portfolio on the
subject of investments, given that the fund goals to mimic the index.