excel assignment in statistics

profileWright
Week5-ApplicationExercise.xlsx
Home>Mathematics homework help>Statistics homework help>excel assignment in statistics

Background

In discussion this week, we will be analyzing various cell phone plans for Tellucell, a regional cellular service provider. In order to be prepared to do the analysis in class, some pre-work is required. In particular, you need to summarize the historical data that Tellucell has gathered on the usage by its customers. The Historical Data sheet has historical data on the number of calls per month made by 2,500 Tellucell customers, rounded to the nearest 5 calls per month. Tellucell wants to use this data to derive the probability distribution of the number of calls per month their customers make. We will use the empirical approach of analyzing the historical data to come up with this probability distribution. (1) Use the FREQUENCY function to find the number of users who made 35, 40, ...., 75 calls per month, respectively. That is, use the FREQUENCY function to fill in cells E3:E12 on the Historical Data sheet. Your result should look like what is in cells G2:H12. If you forgot how to use the FREQUENCY function, consult ExcelNow! under the Quick Tips. (2) Use the result you have in cells D2:E12 to create a histogram of the number of calls per month. Your result should look like the figure in columns K through T of the Historical Data sheet. (3) Copy and Paste the values in E3:E11 into the appropriate table on the Problems sheet. In column D of the table, calculate the probability the number of calls per month equals the values in column B. Use the SUM function to check that the total of Number of Users is 2,500 and the total probability is 1. (4) You now have the information you need to answer Problems 2 and 3. Answer those problems now. In class, we will address the rest of the problems.

Historical Data

User # calls/month Your result should look like this: Your histogram should look like this:
1 40 Bins Frequency Bins Frequency
2 50 35 510 35 510
3 35 40 457 40 457
4 55 45 366 45 366
5 35 50 332 50 332
6 55 55 274 55 274
7 70 60 211 60 211
8 50 65 152 65 152
9 60 70 104 70 104
10 45 75 94 75 94
11 55 0 0
12 60
13 40
14 35 Average 48.456
15 75 Std. Dev. 11.4240215033
16 65
17 50
18 60
19 35
20 70
21 45
22 45
23 60
24 55
25 45
26 40
27 35
28 50
29 40
30 50
31 70
32 40
33 70
34 35
35 65
36 40
37 35
38 50
39 65
40 50
41 70
42 35
43 55
44 75
45 40
46 50
47 60
48 40
49 45
50 65
51 75
52 60
53 50
54 35
55 60
56 40
57 35
58 50
59 35
60 50
61 45
62 35
63 50
64 35
65 45
66 35
67 55
68 70
69 60
70 50
71 50
72 55
73 45
74 35
75 55
76 45
77 35
78 40
79 40
80 45
81 40
82 70
83 40
84 40
85 50
86 75
87 45
88 55
89 60
90 40
91 40
92 70
93 40
94 35
95 40
96 65
97 55
98 55
99 50
100 50
101 40
102 40
103 35
104 50
105 60
106 35
107 40
108 40
109 65
110 65
111 60
112 55
113 35
114 50
115 60
116 35
117 45
118 60
119 40
120 45
121 35
122 35
123 40
124 40
125 35
126 50
127 35
128 35
129 40
130 65
131 40
132 45
133 50
134 65
135 60
136 65
137 45
138 35
139 40
140 35
141 35
142 40
143 45
144 50
145 45
146 35
147 40
148 35
149 50
150 65
151 35
152 55
153 55
154 75
155 45
156 70
157 55
158 65
159 45
160 65
161 55
162 35
163 60
164 55
165 45
166 35
167 40
168 75
169 70
170 35
171 40
172 60
173 45
174 50
175 70
176 40
177 75
178 40
179 50
180 45
181 40
182 55
183 35
184 35
185 55
186 40
187 35
188 50
189 50
190 35
191 50
192 35
193 75
194 35
195 45
196 50
197 45
198 40
199 65
200 60
201 40
202 50
203 45
204 45
205 35
206 65
207 35
208 55
209 35
210 35
211 35
212 50
213 45
214 50
215 70
216 50
217 55
218 40
219 40
220 65
221 45
222 40
223 35
224 70
225 35
226 45
227 55
228 35
229 45
230 35
231 35
232 40
233 35
234 40
235 45
236 40
237 40
238 40
239 40
240 40
241 45
242 35
243 75
244 60
245 35
246 65
247 50
248 45
249 50
250 55
251 60
252 40
253 40
254 45
255 50
256 40
257 35
258 55
259 55
260 45
261 55
262 40
263 35
264 60
265 35
266 40
267 55
268 40
269 70
270 45
271 75
272 60
273 35
274 55
275 35
276 55
277 75
278 35
279 40
280 55
281 65
282 35
283 65
284 55
285 50
286 35
287 65
288 40
289 50
290 55
291 40
292 45
293 40
294 55
295 50
296 40
297 45
298 35
299 45
300 40
301 35
302 40
303 50
304 50
305 70
306 50
307 55
308 40
309 45
310 40
311 50
312 45
313 75
314 55
315 55
316 35
317 45
318 45
319 50
320 40
321 40
322 35
323 65
324 40
325 50
326 35
327 35
328 55
329 70
330 35
331 55
332 35
333 55
334 60
335 35
336 35
337 50
338 50
339 60
340 50
341 60
342 65
343 40
344 65
345 50
346 50
347 40
348 45
349 45
350 45
351 40
352 50
353 35
354 45
355 40
356 55
357 45
358 35
359 35
360 60
361 45
362 75
363 55
364 40
365 35
366 55
367 70
368 40
369 50
370 35
371 60
372 60
373 50
374 45
375 50
376 45
377 55
378 35
379 55
380 55
381 50
382 35
383 40
384 45
385 45
386 60
387 35
388 35
389 75
390 35
391 50
392 35
393 65
394 40
395 50
396 50
397 45
398 60
399 40
400 65
401 60
402 40
403 35
404 50
405 35
406 35
407 35
408 50
409 60
410 60
411 45
412 40
413 35
414 35
415 70
416 40
417 50
418 55
419 50
420 45
421 35
422 35
423 40
424 35
425 40
426 50
427 35
428 60
429 35
430 35
431 55
432 45
433 55
434 40
435 40
436 45
437 50
438 40
439 55
440 40
441 35
442 75
443 70
444 70
445 45
446 70
447 35
448 65
449 55
450 60
451 60
452 45
453 70
454 60
455 40
456 35
457 35
458 50
459 55
460 35
461 40
462 35
463 45
464 60
465 35
466 65
467 55
468 75
469 45
470 55
471 45
472 35
473 45
474 60
475 35
476 60
477 35
478 55
479 40
480 40
481 40
482 35
483 50
484 40
485 40
486 55
487 65
488 50
489 35
490 35
491 40
492 55
493 65
494 35
495 45
496 60
497 40
498 50
499 35
500 45
501 50
502 35
503 35
504 75
505 40
506 45
507 35
508 65
509 40
510 55
511 35
512 55
513 55
514 55
515 45
516 35
517 45
518 35
519 45
520 45
521 45
522 60
523 40
524 55
525 50
526 50
527 55
528 50
529 45
530 40
531 45
532 35
533 40
534 50
535 35
536 50
537 70
538 40
539 45
540 50
541 55
542 45
543 50
544 50
545 40
546 40
547 45
548 55
549 35
550 35
551 50
552 65
553 55
554 50
555 65
556 40
557 40
558 45
559 50
560 60
561 45
562 45
563 45
564 45
565 60
566 40
567 40
568 40
569 70
570 35
571 40
572 40
573 75
574 65
575 40
576 65
577 70
578 70
579 70
580 70
581 45
582 35
583 45
584 35
585 45
586 35
587 55
588 75
589 55
590 45
591 40
592 50
593 55
594 35
595 50
596 40
597 45
598 35
599 45
600 55
601 40
602 55
603 50
604 70
605 35
606 60
607 70
608 60
609 60
610 40
611 45
612 65
613 45
614 55
615 35
616 50
617 40
618 40
619 50
620 60
621 40
622 75
623 35
624 40
625 75
626 65
627 35
628 75
629 45
630 35
631 40
632 55
633 50
634 35
635 40
636 50
637 40
638 35
639 65
640 65
641 70
642 35
643 55
644 55
645 60
646 70
647 65
648 65
649 35
650 65
651 50
652 35
653 65
654 50
655 50
656 45
657 50
658 60
659 65
660 40
661 55
662 70
663 35
664 40
665 65
666 55
667 35
668 70
669 50
670 35
671 40
672 70
673 35
674 55
675 50
676 35
677 60
678 75
679 35
680 40
681 40
682 45
683 60
684 50
685 40
686 50
687 45
688 45
689 70
690 35
691 35
692 35
693 60
694 40
695 40
696 35
697 45
698 35
699 60
700 50
701 50
702 60
703 35
704 35
705 55
706 40
707 70
708 40
709 40
710 75
711 40
712 45
713 55
714 35
715 45
716 40
717 35
718 55
719 50
720 35
721 40
722 40
723 60
724 75
725 40
726 50
727 40
728 50
729 40
730 35
731 65
732 50
733 40
734 45
735 60
736 65
737 60
738 40
739 40
740 40
741 35
742 35
743 45
744 50
745 35
746 50
747 45
748 70
749 45
750 50
751 40
752 35
753 35
754 60
755 35
756 70
757 45
758 50
759 50
760 40
761 35
762 35
763 60
764 45
765 65
766 55
767 40
768 45
769 35
770 50
771 65
772 35
773 70
774 35
775 45
776 35
777 70
778 40
779 40
780 50
781 55
782 40
783 55
784 40
785 50
786 45
787 70
788 45
789 50
790 35
791 45
792 60
793 40
794 55
795 40
796 60
797 75
798 60
799 35
800 35
801 55
802 75
803 45
804 35
805 40
806 35
807 55
808 55
809 75
810 55
811 45
812 40
813 45
814 35
815 65
816 50
817 45
818 60
819 40
820 35
821 40
822 45
823 45
824 60
825 60
826 35
827 45
828 70
829 40
830 55
831 35
832 65
833 40
834 70
835 55
836 40
837 40
838 45
839 50
840 40
841 65
842 40
843 45
844 50
845 65
846 60
847 40
848 50
849 50
850 55
851 40
852 55
853 45
854 35
855 50
856 40
857 45
858 35
859 50
860 45
861 40
862 70
863 40
864 35
865 60
866 45
867 55
868 45
869 35
870 75
871 40
872 75
873 35
874 50
875 60
876 50
877 40
878 50
879 40
880 45
881 35
882 40
883 35
884 35
885 75
886 35
887 55
888 50
889 40
890 70
891 75
892 55
893 70
894 45
895 70
896 35
897 60
898 50
899 70
900 40
901 75
902 40
903 45
904 50
905 35
906 35
907 45
908 55
909 40
910 75
911 35
912 35
913 40
914 40
915 40
916 40
917 75
918 45
919 45
920 45
921 35
922 65
923 55
924 55
925 35
926 35
927 35
928 60
929 50
930 50
931 35
932 35
933 60
934 35
935 45
936 65
937 55
938 40
939 40
940 40
941 65
942 50
943 40
944 65
945 45
946 40
947 60
948 60
949 40
950 70
951 50
952 35
953 75
954 50
955 35
956 40
957 55
958 75
959 40
960 40
961 50
962 50
963 60
964 35
965 50
966 50
967 45
968 35
969 40
970 35
971 60
972 50
973 60
974 60
975 65
976 55
977 50
978 35
979 45
980 40
981 35
982 60
983 50
984 35
985 35
986 50
987 55
988 35
989 50
990 55
991 40
992 50
993 35
994 45
995 35
996 35
997 60
998 55
999 40
1000 40
1001 35
1002 40
1003 40
1004 40
1005 60
1006 75
1007 35
1008 45
1009 50
1010 45
1011 55
1012 55
1013 60
1014 45
1015 35
1016 60
1017 50
1018 60
1019 35
1020 60
1021 45
1022 40
1023 40
1024 40
1025 45
1026 35
1027 65
1028 55
1029 35
1030 50
1031 55
1032 45
1033 35
1034 40
1035 40
1036 55
1037 40
1038 45
1039 60
1040 50
1041 35
1042 35
1043 55
1044 45
1045 65
1046 35
1047 45
1048 45
1049 50
1050 65
1051 45
1052 60
1053 70
1054 65
1055 60
1056 50
1057 45
1058 50
1059 45
1060 60
1061 45
1062 45
1063 65
1064 50
1065 60
1066 50
1067 45
1068 40
1069 70
1070 35
1071 45
1072 50
1073 65
1074 50
1075 40
1076 35
1077 50
1078 55
1079 35
1080 45
1081 45
1082 40
1083 40
1084 60
1085 35
1086 60
1087 65
1088 50
1089 35
1090 40
1091 65
1092 70
1093 40
1094 35
1095 60
1096 50
1097 55
1098 35
1099 35
1100 55
1101 75
1102 60
1103 40
1104 70
1105 45
1106 40
1107 45
1108 40
1109 40
1110 65
1111 40
1112 40
1113 75
1114 35
1115 40
1116 50
1117 45
1118 60
1119 45
1120 45
1121 35
1122 55
1123 65
1124 35
1125 35
1126 40
1127 40
1128 70
1129 55
1130 35
1131 55
1132 45
1133 55
1134 45
1135 45
1136 50
1137 50
1138 35
1139 40
1140 50
1141 55
1142 50
1143 35
1144 35
1145 35
1146 40
1147 60
1148 50
1149 45
1150 35
1151 35
1152 45
1153 40
1154 40
1155 35
1156 50
1157 40
1158 45
1159 50
1160 45
1161 35
1162 40
1163 50
1164 50
1165 60
1166 35
1167 45
1168 75
1169 65
1170 60
1171 75
1172 40
1173 45
1174 45
1175 40
1176 45
1177 70
1178 45
1179 70
1180 40
1181 50
1182 35
1183 65
1184 55
1185 75
1186 50
1187 40
1188 45
1189 55
1190 35
1191 55
1192 35
1193 35
1194 35
1195 55
1196 40
1197 70
1198 60
1199 45
1200 55
1201 35
1202 65
1203 55
1204 40
1205 40
1206 45
1207 50
1208 40
1209 50
1210 45
1211 65
1212 60
1213 35
1214 50
1215 55
1216 45
1217 75
1218 55
1219 35
1220 45
1221 60
1222 45
1223 50
1224 35
1225 60
1226 35
1227 55
1228 75
1229 50
1230 60
1231 35
1232 65
1233 40
1234 35
1235 45
1236 75
1237 50
1238 40
1239 65
1240 40
1241 45
1242 55
1243 35
1244 55
1245 35
1246 45
1247 75
1248 45
1249 50
1250 45
1251 40
1252 45
1253 45
1254 65
1255 45
1256 60
1257 45
1258 75
1259 35
1260 40
1261 40
1262 50
1263 75
1264 40
1265 45
1266 35
1267 65
1268 35
1269 45
1270 50
1271 35
1272 40
1273 50
1274 55
1275 75
1276 65
1277 45
1278 40
1279 45
1280 50
1281 55
1282 40
1283 35
1284 50
1285 45
1286 35
1287 55
1288 75
1289 45
1290 65
1291 50
1292 40
1293 45
1294 45
1295 40
1296 55
1297 55
1298 35
1299 35
1300 35
1301 40
1302 40
1303 45
1304 55
1305 35
1306 40
1307 65
1308 75
1309 75
1310 65
1311 65
1312 55
1313 45
1314 40
1315 40
1316 45
1317 40
1318 55
1319 35
1320 40
1321 45
1322 70
1323 65
1324 35
1325 45
1326 35
1327 35
1328 35
1329 35
1330 55
1331 60
1332 55
1333 40
1334 45
1335 40
1336 35
1337 60
1338 60
1339 55
1340 50
1341 50
1342 50
1343 55
1344 60
1345 45
1346 70
1347 55
1348 50
1349 35
1350 35
1351 60
1352 35
1353 50
1354 60
1355 55
1356 40
1357 40
1358 70
1359 45
1360 35
1361 55
1362 45
1363 35
1364 40
1365 50
1366 35
1367 75
1368 50
1369 65
1370 35
1371 35
1372 50
1373 55
1374 40
1375 45
1376 65
1377 70
1378 55
1379 65
1380 40
1381 65
1382 55
1383 45
1384 60
1385 50
1386 35
1387 65
1388 55
1389 40
1390 55
1391 40
1392 50
1393 40
1394 50
1395 50
1396 55
1397 45
1398 60
1399 45
1400 55
1401 40
1402 55
1403 45
1404 55
1405 35
1406 35
1407 40
1408 40
1409 50
1410 40
1411 50
1412 55
1413 70
1414 45
1415 45
1416 60
1417 35
1418 65
1419 40
1420 55
1421 45
1422 35
1423 60
1424 65
1425 65
1426 50
1427 65
1428 75
1429 45
1430 50
1431 35
1432 65
1433 60
1434 75
1435 50
1436 45
1437 35
1438 55
1439 60
1440 40
1441 50
1442 35
1443 55
1444 40
1445 55
1446 50
1447 45
1448 50
1449 60
1450 45
1451 60
1452 40
1453 45
1454 40
1455 65
1456 40
1457 55
1458 45
1459 35
1460 55
1461 45
1462 55
1463 75
1464 40
1465 50
1466 65
1467 55
1468 35
1469 75
1470 55
1471 45
1472 65
1473 35
1474 55
1475 60
1476 75
1477 65
1478 50
1479 55
1480 50
1481 50
1482 40
1483 35
1484 40
1485 75
1486 45
1487 55
1488 60
1489 65
1490 45
1491 65
1492 50
1493 50
1494 50
1495 40
1496 35
1497 70
1498 50
1499 50
1500 50
1501 45
1502 45
1503 65
1504 40
1505 35
1506 40
1507 40
1508 40
1509 60
1510 45
1511 55
1512 50
1513 45
1514 45
1515 35
1516 40
1517 45
1518 50
1519 60
1520 35
1521 65
1522 75
1523 55
1524 55
1525 40
1526 45
1527 40
1528 50
1529 35
1530 35
1531 35
1532 70
1533 60
1534 35
1535 45
1536 35
1537 65
1538 60
1539 40
1540 60
1541 45
1542 70
1543 45
1544 65
1545 35
1546 60
1547 55
1548 35
1549 70
1550 70
1551 50
1552 60
1553 45
1554 45
1555 35
1556 60
1557 60
1558 60
1559 45
1560 35
1561 40
1562 35
1563 40
1564 35
1565 55
1566 75
1567 40
1568 35
1569 40
1570 35
1571 50
1572 50
1573 45
1574 55
1575 60
1576 45
1577 70
1578 50
1579 45
1580 35
1581 40
1582 40
1583 60
1584 75
1585 50
1586 40
1587 40
1588 40
1589 40
1590 35
1591 40
1592 35
1593 55
1594 65
1595 45
1596 50
1597 40
1598 40
1599 40
1600 35
1601 50
1602 55
1603 35
1604 35
1605 60
1606 35
1607 40
1608 55
1609 50
1610 65
1611 35
1612 50
1613 45
1614 50
1615 60
1616 55
1617 40
1618 50
1619 40
1620 40
1621 35
1622 50
1623 65
1624 70
1625 40
1626 45
1627 50
1628 65
1629 50
1630 65
1631 35
1632 60
1633 45
1634 55
1635 50
1636 50
1637 40
1638 35
1639 75
1640 45
1641 70
1642 35
1643 40
1644 55
1645 35
1646 35
1647 50
1648 35
1649 60
1650 40
1651 60
1652 65
1653 35
1654 35
1655 65
1656 45
1657 35
1658 45
1659 35
1660 75
1661 45
1662 40
1663 50
1664 40
1665 55
1666 40
1667 35
1668 75
1669 40
1670 60
1671 60
1672 40
1673 35
1674 75
1675 40
1676 50
1677 50
1678 45
1679 40
1680 75
1681 50
1682 40
1683 40
1684 50
1685 60
1686 50
1687 50
1688 55
1689 70
1690 55
1691 55
1692 50
1693 35
1694 60
1695 35
1696 65
1697 35
1698 40
1699 40
1700 35
1701 35
1702 45
1703 40
1704 65
1705 55
1706 40
1707 40
1708 60
1709 50
1710 35
1711 35
1712 35
1713 65
1714 40
1715 35
1716 40
1717 35
1718 35
1719 40
1720 65
1721 70
1722 50
1723 55
1724 35
1725 40
1726 40
1727 55
1728 40
1729 75
1730 40
1731 60
1732 45
1733 45
1734 35
1735 45
1736 35
1737 40
1738 45
1739 45
1740 55
1741 65
1742 60
1743 40
1744 35
1745 40
1746 40
1747 35
1748 40
1749 55
1750 35
1751 65
1752 55
1753 50
1754 60
1755 45
1756 75
1757 45
1758 50
1759 35
1760 55
1761 45
1762 60
1763 70
1764 60
1765 45
1766 55
1767 60
1768 50
1769 40
1770 50
1771 40
1772 35
1773 40
1774 55
1775 65
1776 35
1777 50
1778 50
1779 45
1780 70
1781 45
1782 40
1783 40
1784 45
1785 45
1786 55
1787 65
1788 65
1789 35
1790 40
1791 60
1792 35
1793 65
1794 50
1795 35
1796 50
1797 50
1798 70
1799 40
1800 35
1801 35
1802 60
1803 60
1804 35
1805 55
1806 55
1807 40
1808 60
1809 40
1810 65
1811 70
1812 40
1813 55
1814 35
1815 75
1816 45
1817 35
1818 35
1819 45
1820 55
1821 40
1822 60
1823 35
1824 55
1825 50
1826 40
1827 45
1828 35
1829 35
1830 60
1831 45
1832 70
1833 50
1834 40
1835 45
1836 55
1837 55
1838 45
1839 55
1840 35
1841 50
1842 35
1843 35
1844 35
1845 40
1846 70
1847 65
1848 60
1849 35
1850 55
1851 65
1852 35
1853 70
1854 45
1855 40
1856 45
1857 40
1858 70
1859 35
1860 55
1861 35
1862 40
1863 40
1864 50
1865 50
1866 55
1867 45
1868 45
1869 70
1870 40
1871 65
1872 55
1873 45
1874 60
1875 35
1876 60
1877 35
1878 35
1879 60
1880 35
1881 55
1882 50
1883 40
1884 40
1885 55
1886 60
1887 50
1888 60
1889 40
1890 50
1891 60
1892 45
1893 55
1894 45
1895 60
1896 45
1897 35
1898 35
1899 60
1900 35
1901 45
1902 50
1903 50
1904 45
1905 40
1906 55
1907 45
1908 35
1909 65
1910 35
1911 65
1912 40
1913 55
1914 40
1915 60
1916 55
1917 70
1918 35
1919 50
1920 35
1921 70
1922 55
1923 70
1924 40
1925 40
1926 35
1927 35
1928 45
1929 55
1930 45
1931 60
1932 50
1933 55
1934 35
1935 50
1936 45
1937 70
1938 50
1939 45
1940 55
1941 35
1942 45
1943 50
1944 35
1945 35
1946 50
1947 40
1948 50
1949 50
1950 35
1951 40
1952 35
1953 45
1954 75
1955 45
1956 35
1957 50
1958 60
1959 40
1960 55
1961 50
1962 55
1963 40
1964 40
1965 35
1966 70
1967 50
1968 40
1969 55
1970 40
1971 55
1972 45
1973 75
1974 35
1975 40
1976 35
1977 45
1978 55
1979 75
1980 40
1981 55
1982 50
1983 40
1984 60
1985 40
1986 35
1987 55
1988 75
1989 55
1990 70
1991 45
1992 65
1993 60
1994 45
1995 40
1996 50
1997 50
1998 60
1999 40
2000 45
2001 35
2002 55
2003 40
2004 75
2005 35
2006 35
2007 70
2008 35
2009 35
2010 35
2011 50
2012 55
2013 60
2014 45
2015 45
2016 50
2017 55
2018 40
2019 60
2020 45
2021 60
2022 35
2023 55
2024 40
2025 35
2026 40
2027 55
2028 40
2029 45
2030 35
2031 50
2032 35
2033 60
2034 55
2035 50
2036 35
2037 75
2038 40
2039 45
2040 40
2041 35
2042 40
2043 35
2044 45
2045 40
2046 75
2047 35
2048 35
2049 55
2050 70
2051 45
2052 75
2053 50
2054 45
2055 40
2056 35
2057 50
2058 40
2059 40
2060 50
2061 55
2062 50
2063 45
2064 35
2065 65
2066 55
2067 55
2068 55
2069 45
2070 45
2071 50
2072 50
2073 60
2074 70
2075 60
2076 40
2077 75
2078 40
2079 55
2080 45
2081 45
2082 75
2083 55
2084 50
2085 40
2086 45
2087 45
2088 65
2089 60
2090 35
2091 40
2092 40
2093 60
2094 40
2095 60
2096 55
2097 40
2098 40
2099 45
2100 35
2101 35
2102 35
2103 45
2104 35
2105 35
2106 60
2107 35
2108 60
2109 35
2110 65
2111 40
2112 65
2113 35
2114 35
2115 60
2116 45
2117 35
2118 50
2119 35
2120 70
2121 40
2122 45
2123 75
2124 75
2125 40
2126 55
2127 35
2128 45
2129 65
2130 60
2131 50
2132 35
2133 45
2134 75
2135 35
2136 65
2137 50
2138 45
2139 35
2140 45
2141 45
2142 55
2143 45
2144 40
2145 65
2146 35
2147 45
2148 40
2149 35
2150 65
2151 45
2152 40
2153 40
2154 60
2155 45
2156 40
2157 45
2158 35
2159 55
2160 40
2161 35
2162 55
2163 40
2164 55
2165 35
2166 45
2167 60
2168 75
2169 35
2170 45
2171 50
2172 35
2173 50
2174 40
2175 60
2176 45
2177 40
2178 40
2179 35
2180 75
2181 50
2182 50
2183 35
2184 50
2185 50
2186 60
2187 65
2188 70
2189 75
2190 55
2191 35
2192 35
2193 40
2194 40
2195 55
2196 40
2197 40
2198 50
2199 65
2200 35
2201 45
2202 50
2203 45
2204 35
2205 35
2206 70
2207 55
2208 60
2209 40
2210 45
2211 60
2212 75
2213 50
2214 45
2215 45
2216 50
2217 50
2218 45
2219 55
2220 40
2221 60
2222 35
2223 45
2224 40
2225 65
2226 65
2227 50
2228 40
2229 50
2230 70
2231 45
2232 50
2233 40
2234 60
2235 60
2236 35
2237 35
2238 35
2239 35
2240 50
2241 35
2242 50
2243 55
2244 50
2245 50
2246 35
2247 55
2248 40
2249 35
2250 50
2251 35
2252 35
2253 35
2254 70
2255 45
2256 45
2257 60
2258 45
2259 35
2260 70
2261 45
2262 55
2263 35
2264 45
2265 45
2266 45
2267 50
2268 40
2269 35
2270 70
2271 60
2272 55
2273 45
2274 45
2275 35
2276 45
2277 65
2278 45
2279 45
2280 40
2281 40
2282 35
2283 50
2284 60
2285 35
2286 35
2287 40
2288 60
2289 45
2290 40
2291 45
2292 55
2293 50
2294 35
2295 35
2296 45
2297 40
2298 35
2299 50
2300 40
2301 50
2302 55
2303 60
2304 35
2305 70
2306 50
2307 40
2308 60
2309 60
2310 35
2311 55
2312 70
2313 35
2314 65
2315 50
2316 35
2317 75
2318 35
2319 45
2320 45
2321 45
2322 55
2323 35
2324 60
2325 45
2326 40
2327 75
2328 35
2329 40
2330 50
2331 40
2332 60
2333 50
2334 40
2335 45
2336 70
2337 50
2338 50
2339 65
2340 35
2341 45
2342 65
2343 35
2344 40
2345 35
2346 45
2347 55
2348 50
2349 65
2350 60
2351 55
2352 40
2353 35
2354 55
2355 40
2356 65
2357 35
2358 35
2359 45
2360 55
2361 65
2362 65
2363 60
2364 40
2365 35
2366 40
2367 70
2368 50
2369 35
2370 40
2371 35
2372 35
2373 35
2374 75
2375 60
2376 35
2377 40
2378 55
2379 35
2380 55
2381 50
2382 35
2383 40
2384 40
2385 35
2386 50
2387 60
2388 35
2389 35
2390 35
2391 50
2392 70
2393 35
2394 35
2395 35
2396 45
2397 40
2398 40
2399 60
2400 60
2401 65
2402 45
2403 45
2404 35
2405 40
2406 50
2407 45
2408 45
2409 65
2410 50
2411 40
2412 45
2413 40
2414 40
2415 55
2416 40
2417 45
2418 55
2419 55
2420 40
2421 45
2422 55
2423 35
2424 60
2425 65
2426 55
2427 35
2428 55
2429 35
2430 55
2431 60
2432 40
2433 35
2434 40
2435 50
2436 55
2437 40
2438 55
2439 50
2440 35
2441 40
2442 35
2443 40
2444 40
2445 45
2446 40
2447 35
2448 40
2449 55
2450 50
2451 40
2452 50
2453 35
2454 35
2455 45
2456 65
2457 40
2458 65
2459 60
2460 55
2461 35
2462 75
2463 40
2464 75
2465 35
2466 65
2467 35
2468 70
2469 35
2470 45
2471 45
2472 75
2473 35
2474 45
2475 50
2476 35
2477 60
2478 65
2479 50
2480 40
2481 60
2482 40
2483 50
2484 55
2485 35
2486 40
2487 35
2488 40
2489 35
2490 50
2491 50
2492 50
2493 55
2494 55
2495 50
2496 40
2497 60
2498 55
2499 35
2500 35

Problems

1.      Complete the table below (3 points) Hints: Note that probability is just the proportion or relative frequency.
Number of Calls per month per User (X) Frequency Probability: P(X=xi)
(Number of Users)
35
40
45
50
55
60
65
70
75
TOTAL
2.      What is the probability that a user would make at least 55 calls per month? (2 points)
Use an Excel formula to get your answer and place it in cell C40.
3.      What is the average number of calls that the telephone company can expect its customers to make each month when they sign up for the plan? (1 point)
Hint: Would the SUMPRODUCT function be useful here?
5.      What is the expected value of the monthly amount a customer pays to Tellucell? Make sure your formula uses the data referenced in the cells below and your answer to question 3. above. (1 point)
Hint: You might find the sheet Transformation of a R.V. taken from the lecture slides for Chapter 6
Price per call = $ 0.35
Fixed cost/month = $ 15.00
6.      If the standard deviation of the random variable, X, is σ(X) = 11.42 , what would be the standard deviation of the random variable, C? (2 points)
7. Consider the following two phone plans:
Plan 1: Each customer pays $ 240.00 for a new phone and is charged a flat rate = of $55 per month. $ 55.00 per month.
Plan 2: Each customer pays $ 500.00 for a new phone and is charged $ 0.50 per call plus a flat rate of $ 20.00 per month.
from its customers over two years.
Let R1 and R2 be the random variables denoting the per user revenue the company gets from its customers over two years.
A. In the cells below, enter formulas that compute the expected values of R1 and R2. (2 points)
E(R1) =
E(R2) =
B. In the cells below, enter the standard deviations of R1 and R2? (4 points)
SD(R1) =
SD(R2) =
C. Which plan is more enticing to the company? Justify your answer in one line below. (1 point)
Mean monthly data usage = 5.8 GB/month
StDev monthly data usage = 2.5 GB/month
A. What percentage of Tellucell data-plan users use less than 5 GB/month? [1]
B. What percentage of Tellucell data-plan users uses between 3 GB/month and 10GB/month? [1]
C. The top 10% of Tellucell users use at least how many GB of data per month? [1]
D. So-called "heavy users" use >= 9 GB/month. What is the probability a heavy user uses more than 10 GB/month? [1]
E. Two users are selected at random. What is the probability at least one of them is a heavy user? [2]
F. Five users are selected at random. What is the probability that at least one of them is a heavy user? [2]
Hint: It is much easier to solve this problem by considering the event E = "At least one of the five users is a heavy user" and finding the probability of the complement of E.

Cellular Service Contracts Until recently, most phone companies offered their costumers substantially discounted handsets in exchange for a two-year contract. When costumers signed up for a contract, they got a steep discount on their phone – known as a subsidy - but they were charged much higher monthly rates in order to make up for that subsidy. The phone companies priced the plans in anticipation of their customers’ cellular service usage to ensure they achieved their targeted profit. Although at first glance, the subsidy offers looked very appealing, the hidden extra charges on the plans added up to much more than the actual price of a phone. Recently, T-Mobile, AT&T and Verizon decided to eliminate the service contracts to provide customers more flexibility and a much clearer sense of what they are being charged for when they buy a phone and sign up for a mobile plan. This week, you will analyze the data collected by Regional cellular service provider Tellucell for 2,500 customers over the past several months. (The data is summarized in the table below.) Through this exercise, you will get some insight into how rates are set and how different phone plans should be compared. When data is given in cells with blue/gray fill, make sure to make cell references to the cells instead of copying the numbers in your formulas.

4. Cellular phone service costs to the company are, on average, $0.35 per call plus a fixed cost of $15 per user per month. Let C be the random variable denoting total cost per user per month and X denote the number of calls per month per user. Show the relationship between C and X by entering on the line below the equation which expresses C in terms of X. (1 point) _____________________________________________________

8. In our analysis before we had not considered any internet data plans. However, as all of you know, nowadays a data plan is an essential part of any phone plan. The phone companies employ analysts to estimate data usage for future months. Those estimates help them to make suitable arrangements for future data services. With that purpose in mind, solve the following problem. Based on data from the previous months you know that the monthly data usage of a random Tellucell user can be modeled quite well by a normal distribution with mean and standard deviation shown in the cells below. Using this model:

Transformation of a R.V.