Question

Exercise 5

Mr. Ahmed, MD of XYZ company wants to select one of the following candidates on the basis of their performance (marks) in the last 5 quarters of their MBA program.                                                           

            Quarter:           I           II         III        IV        V

            Mr. Abdulla    95        90        85        80        75

            Mr. Khalid      75        80        85        90        95

            (i). Which candidate is more consistent, show the quantitative working?

            (ii). Which candidate you will recommend, show the quantitative working?

Exercise 6

In a sample study about coffee-drinking habits in two towns, the following information was received.

Town-A: Females were 40 percent. Total coffee drinkers are 45 percent and male non-coffee drinkers were 20 percent.

Town-B: Males were 55 percent. Male non-coffee drinkers were 30 percent and Female coffee drinkers were 15 percent.

Present the above data in a tabular form.

Exercise 7

The data on fund flow (in million AED) of an international airport authority during the financial year 2015, 2016 and 2017 are given below.

Item	FY 2015	FY 2016	FY 2017
Non-traffic Revenue	40.00	50.75	70.25
Traffic Revenue	70.25	80.75	110.00
Profit Before Tax	40.15	50.50	80.25

Present the data by a suitable chart.

Exercise 8

The following data relate to area in millions of kilometers oceans of the world.

Ocean	Pacific	Atlantic	Indian	Antarctic	Arctic
Area in millions of kilometers	70.8	41.2	28.5	7.6	4.8

Represent the above data by a suitable method.

Exercise 9

The following data represent the income and dividends for the last six years.

Year	2012	2013	2014	2015	2016	2017
Income per share(AED)	5.86	6.67	6.98	7.42	8.23	9.37
Dividend per share(AED)	2.23	3.21	3.39	3.24	3.88	4.21

(a). Present the data graphically.

(b). Analyze the data and interpret.

Answer 1

Ans (5):

The given data is as follows:

Quarter	I	II	III	IV	V	Total
Mr. Abdulla	95	90	85	80	75	425
Mr. Khalid	75	80	85	90	95	425

For checking consistency, we have to calculate coefficient of variation. If coefficient of variation is low, the set of data is said to be uniform (consistent) or homogeneous.

The coefficient of variation is given by,

From the given data set:

For Mr. Abdulla,

For Mr. Khalid,

i) From above calculations, it seems that the coefficient of variations for both candidates are equal. Therefore we conclude that, they both are equally consistent.

ii) Since, Mr. Abdulla and Mr. Khalid both are equally consistent. So, we recommend both candidates to company.

Ans (6):

We can represent the given data in tabular form by using 2x2 contingency table.

For Town A:

	Coffee Drinkers	Non-coffee drinkers	Total
Male	40%	20%	60%
Female	5%	35%	40%
Total	45%	55%	100%

For Town B:

	Coffee Drinkers	Non-coffee drinkers	Total
Male	25%	30%	55%
Female	15%	30%	45%
Total	40%	60%	100%

Ans (7):

The given data is as follows:

Item	FY 2015	FY 2016	FY 2017
Non-traffic Revenue	40.00	50.75	70.25
Traffic Revenue	70.25	80.75	110.00
Profit Before Tax	40.15	50.50	80.25

For representing this data, we can use Stacked Column chart.

Procedure for creating the Stacked Column Chart using MS-Excel:

1. Enter the given data into Excel worksheet as given above.
2. In your spreadsheet, select the data to use for your stacked Column Chart.
3. Click Insert > Insert Column Chart, and then pick the stacked column chart.
4. Click the graph and then click the icons next to the chart to add finishing touches.

Then the Stacked Column Chart is as follows:

Ans (8):

The given data set is as follows:

Ocean	Pacific	Atlantic	Indian	Antarctic	Arctic
Area in millions of kilometers	70.8	41.2	28.5	7.6	4.8

For representing this data, we can use Pie Chart.

Procedure for creating the Pie Graph using MS-Excel:

1. Enter the given data into Excel worksheet as given above.
2. In your spreadsheet, select the data to use for your pie chart.
3. Click Insert > Insert Pie or Doughnut Chart, and then pick the chart you want.
4. Click the chart and then click the icons next to the chart to add finishing touches.

Then the pie chart is as follows:

Ans (9):

The given data is as follows:

Year	2012	2013	2014	2015	2016	2017
Income per share(AED)	5.86	6.67	6.98	7.42	8.23	9.37
Dividend per share(AED)	2.23	3.21	3.39	3.24	3.88	4.21

a) We can use Bar chart to represent the given data.

Procedure for creating the Stacked Column Chart using MS-Excel:

1. Enter the given data into Excel worksheet as given above.
2. In your spreadsheet, select the data to use for your Bar Chart.
3. Click Insert > Insert Bar Chart, and then pick the Clustered chart.
4. Click the graph and then click the icons next to the chart to add finishing touches.

b)  Correlation Analysis using MS-Excel:

Procedure:

1.Enter the data into Excel sheet.

2.If this is the first time you have used an Excel add-in, click the File tab, otherwise skip to step 7.

3.Click Options from the list on the left.

4.Select Add-ins in the Excel Options box.

5.In the Add-in list box, select Analysis Toolbox-VBA from the Inactive Application Add-ins list.

6.Click OK.

7.Then select Data/ Data Analysis tab from the menu bar.

8.The Data Analysis dialog box will appear on the screen.

9.From the Data Analysis dialog box, select Correlation and click OK.

10.The Correlation dialog box will appear on the screen.

11.Give the range of data (Select the columns corresponding to Income and Dividend per share) in Input Range.

12.Then select the Columns options in the Grouped By tab. Then click on Labels in first row.

13.Give the Output Range. Click OK.

The MS Excel output will appear on the screen.

The output is,

	Income per share(AED)	Dividend per share(AED)
Income per share(AED)	1
Dividend per share(AED)	0.938042	1

From the above Excel output, correlation coefficient (r) = 0.9380

Interpretation: Here r = 0.9380, which indicates that there is strong positive (perfect) relationship between variables Income per share and Dividend per share. As income goes increasing the dividend also increases and vice-versa.