SP ID - all -

Sum - No of Flats sold Education

Region Diploma Graduation PostGraduation Total Result
East 250 713 399 1362
North 183 450 699 1332
South 852 189 255 1296
West 198 618 412 1228
Total Result 1483 1970 1765 5218

2500

2000

1500

Education Diploma
1000 Graduation
PostGraduation

500

0
East North South West Total
Result
Education Diploma
Graduation
PostGraduation
Region
East North South West Total Result
6 6 6 6 24
Scenario Summary
Current Values: 2 % Bonus of Total Cost 3 % Bonus of Total Cost
Changing Cells:
$K$5 2 2 3
Result Cells:
$J$6 12,250,000 12,250,000 18,375,000
$J$7 10,000,000 10,000,000 15,000,000
$J$8 12,500,000 12,500,000 18,750,000
$J$9 9,950,000 9,950,000 14,925,000
$J$10 13,000,000 13,000,000 19,500,000
$J$11 10,400,000 10,400,000 15,600,000
$J$12 14,750,000 14,750,000 22,125,000
$J$13 9,150,000 9,150,000 13,725,000
$J$14 10,250,000 10,250,000 15,375,000
$J$15 9,950,000 9,950,000 14,925,000
$J$16 10,250,000 10,250,000 15,375,000
$J$17 12,250,000 12,250,000 18,375,000
$J$18 11,750,000 11,750,000 17,625,000
$J$19 11,250,000 11,250,000 16,875,000
$J$20 12,750,000 12,750,000 19,125,000
$J$21 10,450,000 10,450,000 15,675,000
$J$22 9,450,000 9,450,000 14,175,000
$J$23 9,150,000 9,150,000 13,725,000
$J$24 10,500,000 10,500,000 15,750,000
$J$25 9,900,000 9,900,000 14,850,000
$J$26 10,600,000 10,600,000 15,900,000
$J$27 10,000,000 10,000,000 15,000,000
$J$28 10,400,000 10,400,000 15,600,000
$J$29 10,000,000 10,000,000 15,000,000
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
4% Bonus of Total Cost 5 % Bonus of Total Cost

4 5

24,500,000 30,625,000
20,000,000 25,000,000
25,000,000 31,250,000
19,900,000 24,875,000
26,000,000 32,500,000
20,800,000 26,000,000
29,500,000 36,875,000
18,300,000 22,875,000
20,500,000 25,625,000
19,900,000 24,875,000
20,500,000 25,625,000
24,500,000 30,625,000
23,500,000 29,375,000
22,500,000 28,125,000
25,500,000 31,875,000
20,900,000 26,125,000
18,900,000 23,625,000
18,300,000 22,875,000
21,000,000 26,250,000
19,800,000 24,750,000
21,200,000 26,500,000
20,000,000 25,000,000
20,800,000 26,000,000
20,000,000 25,000,000
 Data
Region Sum - Exp in Months Sum - No of Flats sold Sum - Total Sale in Rs.
East 536 1362 3,405,000,000
North 535 1332 3,330,000,000
South 529 1296 3,240,000,000
West 498 1228 3,070,000,000
Total Result 2098 5218 13,045,000,000
 Total Sale in Rs. No of Flats sold

Mean 543541666.67 Mean 217.41666667

Standard Error 14267212.992 Standard Error 5.7068851966
Median 520000000 Median 208
Mode 500000000 Mode 200
Standard Deviation 69894783.762 Standard Deviation 27.957913505
Sample Variance 4.885281E+15 Sample Variance 781.64492754
Kurtosis 1.0081048523 Kurtosis 1.0081048523
Skewness 1.1566803033 Skewness 1.1566803033
Range 280000000 Range 112
Minimum 457500000 Minimum 183
Maximum 737500000 Maximum 295
Sum 13045000000 Sum 5218
Count 24 Count 24
 Age Exp in Months No of Flats sold Total Sale in Rs.
Age 1
Exp in Months -0.0853576361 1
No of Flats sold -0.2737457384 0.916810687495 1
Total Sale in Rs. -0.2737457384 0.916810687495 1 1
 Bins Frequency Cumulative %
30 2 8.33%
32 4 25.00% Histogram
34 3 37.50%
7
36 5 58.33%
38 6 83.33% 6
40 3 95.83%
42 1 100.00% 5
More 0 100.00%
4

Frequency
3

0
30 32 34 36 38 40 4
Bins
 Histogram
120.00%

100.00%

80.00%

Frequency
60.00%
Cumulative %

40.00%

20.00%

0.00%
36 38 40 42 More
Bins
Point Total Sale in Rs. Rank Percent
7 737,500,000 1 100.00%
5 650,000,000 2 95.60%
15 637,500,000 3 91.30%
3 625,000,000 4 86.90%
1 612,500,000 5 78.20%
12 612,500,000 5 78.20%
13 587,500,000 7 73.90%
14 562,500,000 8 69.50%
21 530,000,000 9 65.20%
19 525,000,000 10 60.80%
16 522,500,000 11 56.50%
6 520,000,000 12 47.80%
23 520,000,000 12 47.80%
9 512,500,000 14 39.10%
11 512,500,000 14 39.10%
2 500,000,000 16 26.00%
22 500,000,000 16 26.00%
24 500,000,000 16 26.00%
4 497,500,000 19 17.30%
10 497,500,000 19 17.30%
20 495,000,000 21 13.00%
17 472,500,000 22 8.60%
8 457,500,000 23 0.00%
18 457,500,000 23 0.00%
SUMMARY OUTPUT

Regression Statistics
Multiple R 0.273745738392157
R Square 0.074936729287868
Adjusted R Square 0.032888398800952
Standard Error 27.4943244586711
Observations 24

ANOVA
df SS MS F Significance F
Regression 1 1347.2 1347.2 1.782157 0.19553582096
Residual 22 16630.63 755.9379
Total 23 17977.83

Coefficients Standard Error t Stat P-value Lower 95% Upper 95%

Intercept 302.704007123776 64.1329 4.719949 0.000104 169.700510078 435.7075
Age -2.39964381121995 1.79752 -1.334975 0.195536 -6.127472667 1.328185

RESIDUAL OUTPUT

Observation Predicted No of Flats sold Residuals

1 230.714692787177 14.28531
2 225.915405164737 -25.91541
3 225.915405164737 24.08459
4 223.515761353517 -24.51576
5 213.917186108638 46.08281
6 209.117898486198 -1.117898
7 221.116117542297 73.88388
8 216.316829919857 -33.31683
9 216.316829919857 -11.31683
10 211.517542297418 -12.51754
11 201.918967052538 3.081033
12 230.714692787177 14.28531
13 218.716473731077 16.28353
14 213.917186108638 11.08281
15 211.517542297418 43.48246
16 211.517542297418 -2.517542
17 206.718254674978 -17.71825
18 216.316829919857 -33.31683
19 225.915405164737 -15.91541
20 221.116117542297 -23.11612
21 216.316829919857 -4.31683
22 213.917186108638 -13.91719
23 209.117898486198 -1.117898
24 225.915405164737 -25.91541
 age no of flat
30 219.05

Lower 95.0%
Upper 95.0%
169.7005 435.7075
-6.127473 1.328185
 Real Estates ( Sales Person details) Year 2012 - 2017
Region SP ID Education Age

East SP001 Graduation 30

East SP002 PostGraduation 32

East SP003 Diploma 32

East SP004 PostGraduation 33

East SP005 Graduation 37
East SP006 Graduation 39
North SP007 PostGraduation 34
North SP008 Diploma 36
North SP009 PostGraduation 36
North SP010 PostGraduation 38
North SP011 Graduation 42
North SP012 Graduation 30
South SP013 Diploma 35
South SP014 Diploma 37
South SP015 PostGraduation 38
South SP016 Diploma 38
South SP017 Graduation 40
South SP018 Diploma 36
West SP019 Graduation 32
West SP020 Diploma 34
West SP021 PostGraduation 36
West SP022 Graduation 37
West SP023 Graduation 39
West SP024 PostGraduation 32
East SP025 Graduation 26
East SP026 Graduation 24
East SP027 Diploma 31
East SP028 Diploma 29
East SP029 PostGraduation 21
East SP030 Diploma 34
North SP031 Graduation 38
North SP032 Diploma 39
North SP033 Graduation 40
North SP034 Diploma 38
North SP035 Graduation 24
North SP036 Diploma 26
South SP037 PostGraduation 36
South SP038 Graduation 31
South SP039 Graduation 37
South SP040 PostGraduation 24
South SP041 Graduation 34
South SP042 Graduation 34
West SP043 Diploma 37
West SP044 Graduation 37
West SP045 PostGraduation 28
West SP046 Diploma 33
West SP047 PostGraduation 26
West SP048 Graduation 37
East SP049 Graduation 37
East SP050 PostGraduation 30
East SP051 Diploma 37
East SP052 PostGraduation 24
East SP053 PostGraduation 21
East SP054 Graduation 34
North SP055 Graduation 33
North SP056 Diploma 37
North SP057 Diploma 39
North SP058 PostGraduation 36
North SP059 Diploma 24
North SP060 Graduation 40
South SP061 Diploma 33
South SP062 Graduation 23
South SP063 Diploma 30
South SP064 PostGraduation 40
South SP065 Graduation 33
South SP066 Graduation 22
West SP067 PostGraduation 32
West SP068 Graduation 36
West SP069 Graduation 28
West SP070 Diploma 27
West SP071 Diploma 23
West SP072 PostGraduation 26
East SP073 Diploma 33
East SP074 Graduation 22
East SP075 Graduation 29
East SP076 Graduation 33
East SP077 Diploma 30
East SP078 Diploma 23
North SP079 PostGraduation 34
North SP080 Diploma 27
North SP081 Graduation 26
North SP082 Diploma 23
North SP083 Graduation 35
North SP084 Diploma 38
South SP085 PostGraduation 27
South SP086 Graduation 23
South SP087 Graduation 38
South SP088 PostGraduation 28
South SP089 Graduation 20
South SP090 Graduation 28
West SP091 Diploma 28
West SP092 Diploma 26
West SP093 PostGraduation 31
West SP094 Diploma 36
West SP095 Graduation 26
West SP096 Graduation 37
tes ( Sales Person details) Year 2012 - 2017
Exp in Months No of Flats sold Total Sale in Rs.

90 245 612,500,000

85 200 500,000,000

95 250 625,000,000

82 199 497,500,000
98 260 650,000,000
86 208 520,000,000
105 295 737,500,000
78 183 457,500,000
86 205 512,500,000
89 199 497,500,000
87 205 512,500,000
90 245 612,500,000
94 235 587,500,000
94 225 562,500,000
97 255 637,500,000
87 209 522,500,000
79 189 472,500,000
78 183 457,500,000
80 210 525,000,000
84 198 495,000,000
82 212 530,000,000
81 200 500,000,000
86 208 520,000,000
85 200 500,000,000
97 245 544,382,240
43 214 464,114,173
60 223 713,419,732
65 281 623,620,541
60 159 525,616,179
36 153 458,811,466
96 259 545,762,205
119 168 487,712,648
70 286 654,121,077
99 216 524,652,996
 96 277 549,617,883
66 244 473,092,691
80 241 624,485,774
88 207 483,459,441
81 202 733,415,900
36 158 513,862,813
107 198 707,403,273
109 166 653,102,926
68 282 611,370,359
91 253 467,514,647
58 209 708,894,387
120 185 600,607,040
67 184 555,332,776
71 178 530,359,056
54 203 663,620,196
65 280 552,767,967
110 171 466,732,441
96 255 607,420,470
113 177 731,590,072
72 229 469,334,972
89 274 589,746,857
45 225 686,985,996
83 288 519,321,271
51 222 729,454,058
27 292 472,392,990
41 257 542,152,789
40 156 525,541,487
31 274 717,098,109
116 222 487,480,093
79 295 687,754,790
97 176 724,200,016
45 289 719,519,291
55 204 573,737,480
31 259 736,080,037
97 161 594,384,018
52 212 523,144,228
118 150 562,828,949
47 161 537,235,125
53 169 550,015,543
38 236 518,667,024
29 217 475,614,857
113 275 504,517,304
56 182 643,453,511
94 189 736,627,538
79 291 514,195,843
107 167 545,509,778
 56 177 467,063,149
56 225 731,191,995
112 261 605,538,241
44 231 653,334,000
111 265 537,914,234
75 247 515,929,302
105 237 606,769,567
59 290 535,603,049
37 178 661,168,119
78 169 490,832,810
86 188 515,910,516
88 289 498,393,978
45 249 653,711,846
31 190 613,016,994
37 291 511,987,113
24 275 489,645,890
 Number of sales persons in East & West
regions using Countif function in Excel

48
•Number of sales persons who are
Graduate & Diploma holders using
Countif function in Excel. 72
•Number of sales persons whose
experience is more than 82 Months using
Countif function in Excel.
44
•Tabulate region-wise number of Sales
Person, No. of flats sold, and Sales
Revenue.
Region Sales Persons No.of Flats Sold
East 24 5220
West 24 5148
South 24 5266
North 24 5692
Sales Revenue
13655326226
13354154439
14165540994
13621846467