MEDTERM Excel Project Exam

profileShahadA
SC_EX19_10a_ShahadAldahban_1.xlsm
Home>Information Systems homework help>MEDTERM Excel Project Exam

Documentation

Shelly Cashman Excel 2019 | Module 10: SAM Project 1a
Franklin National
ANALYZING DATA WITH POWER TOOLS AND CREATING MACROS
Author: Shahad Aldahban
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Home Page

Franklin National
Home Security Group
Links
Customers
Contracts
Invoices
Archived data
Franklin National website
Contact Franklin National

Add Invoice

Add Contract

Add Customer

Customers

Home Security Systems
CustomerID First Last Phone Address City State Zip Contracts by City
C-001 Margaret Alexander 770-866-3901 211 N Paterson Newnan GA 30263 Atlanta
C-005 Jeff Jordan 770-392-0622 2611 S Sara Dr Peachtree City GA 30270 College Park
C-008 Chris Hackbart 404-985-1122 3478 Woodline Rd Atlanta GA 31107 Dunwoody
C-012 Ramona Earley 770-454-9801 33 E Pine St Alpharetta GA 30004
C-014 Vernon Crowley 706-396-1972 566 Kent Rd Augusta GA 30917
C-015 Alberto Cruz 770-637-7783 456 Winchester St Canton GA 30114
C-020 Michelle McMurray 404-963-0808 815 Pulley Rd Decatur GA 30031
C-027 Karen Li 706-483-9244 402 S Melody Rd Augusta GA 30918
C-030 Stephen Rosner 770-393-1228 111 E 78th Ave Dunwoody GA 30346
C-031 Daniel Plummer 404-842-4603 29 Graham Blvd Atlanta GA 30347
C-032 Laquan Richardson 404-857-1276 80 Cedar St Atlanta GA 30350
C-038 Clint Schmitt 706-482-1400 2268 N Jackson Columbus GA 31998
C-040 Victor Ramirez 770-988-0777 144 E Tower Ave Holly Springs GA 30115
C-042 Michael Levander 404-337-9208 10 Lincoln Dr Atlanta GA 30336
C-043 Shana Liang 770-988-1320 40 Monroe St Alpharetta GA 30022
C-045 Jamal Kidd 404-343-7509 3047 Millbrook Ave Atlanta GA 31156
C-048 Joel Holtman 706-637-6591 7320 E Elmwood Augusta GA 30919
C-049 Frances Heller 404-983-2255 828 Turner St College Park GA 30337
C-053 Ahmad Greenwell 770-396-8401 614 Berman Rd Douglasville GA 30134
C-054 Jacquie Rollins 770-355-3989 258 Briar Ln Woolsey GA 30214
C-055 Steve Tomashek 404-979-2004 1440 Beadle Rd Decatur GA 30036
C-058 Andrea Vander 706-857-1771 77 Robin Rd Augusta GA 30907
C-059 Harry Fraser 770-392-0015 8045 Jefferson Ave Atlanta GA 30339
C-060 Anita Renteria 770-949-3862 75 Johnson St Atlanta GA 30341
C-064 Douglas Shoemaker 706-487-4700 5478 N Grove Baldwin GA 30511
C-065 Raymond Ringdahl 770-393-0403 24 Samuel St Alpharetta GA 30009
C-067 Beth Raferty 404-985-1992 612 E Carolina Pl Atlanta GA 30318
C-068 Jason Grady 770-454-4002 5230 Fulton St Carrollton GA 30119
C-070 Gordon Griffin 706-456-7093 912 Market St Augusta GA 30907
C-071 Mia Watanabe 404-857-4424 82 Cambridge St Briarcliff GA 30329
C-072 Barbara Callahan 404-337-8200 5049 Hwy 36 Sandy Springs GA 30342
C-073 Randy Dixon 770-983-1116 131 Pleasant St Marietta GA 30090
C-075 Jim Benning 706-482-9932 2167 S Macon St Augusta GA 30168
C-079 Sarah Alvarez 770-940-3380 620 Pinewoody Alpharetta GA 30412
C-080 Timothy Jones 404-392-4629 249 W 11th St Atlanta GA 30322
C-083 Shanissa Hoffman 706-337-0990 1286 Jennifer Dr Athens GA 30604
C-084 Cathy Wilson 770-866-4882 78 Sandy Springs St Buford GA 30515
C-085 Richard Phillips 770-637-5408 39 Water St Canton GA 30114
C-087 Nancy Wanson 404-963-0190 1752 S Main St College Park GA 30337
C-088 Mickey Kervin 706-454-3327 348 N Oak St Columbus GA 31998

Contracts

Home Security Systems
ContractNum CustomerID ContractAmt SigningDate ContractType
9111 C-001 $ 2,000.00 2/6/21 Indoor alarm system
9112 C-027 $ 990.00 2/18/21 Front door alarm system
9115 C-005 $ 2,500.00 3/1/21 Indoor alarm system
9117 C-012 $ 1,250.00 3/1/21 Indoor alarm system
9120 C-055 $ 3,500.00 2/19/21 Indoor alarm and video surveillance
9121 C-040 $ 8,000.00 5/3/21 Indoor and outdoor alarm system with video
9122 C-043 $ 2,000.00 4/14/21 Indoor alarm system
9123 C-070 $ 9,000.00 3/22/21 Indoor and outdoor alarm system with video
9125 C-083 $ 5,500.00 3/25/21 Indoor and outdoor alarm system with backup
9126 C-038 $ 5,000.00 3/11/21 Indoor wireless alarm system
9127 C-008 $ 1,250.00 4/17/21 First floor alarm system
9130 C-042 $ 5,250.00 6/11/21 Indoor and outdoor alarm system
9131 C-070 $ 8,500.00 6/3/21 Indoor and outdoor system with video
9132 C-014 $ 1,500.00 6/22/21 Security plan for apartment building
9133 C-005 $ 1,250.00 7/8/21 Indoor wireless alarm system
9134 C-088 $ 2,500.00 5/28/21 Garage system with video
9135 C-088 $ 1,500.00 5/28/21 Security plan
9137 C-045 $ 8,000.00 6/22/21 Indoor and outdoor system with video and backup
9138 C-045 $ 4,000.00 6/25/21 Indoor alarm system with video
9140 C-068 $ 8,500.00 7/27/21 Indoor and outdoor system for apartment building
9143 C-083 $ 10,000.00 8/11/21 Indoor and outdoor system for apartment building
9144 C-075 $ 5,500.00 8/19/21 Indoor and outdoor system with video
9145 C-064 $ 5,000.00 8/23/21 Indoor system with video
9146 C-048 $ 1,300.00 9/2/21 Security plan
9148 C-030 $ 4,500.00 9/23/21 Safe room
9150 C-015 $ 11,000.00 8/27/21 Indoor and outdoor system with video and safe room
9151 C-032 $ 8,000.00 10/5/21 Indoor and outdoor system with video
9153 C-049 $ 1,375.00 9/17/21 Front door system
9156 C-075 $ 950.00 9/30/21 Security plan
9157 C-075 $ 5,500.00 9/30/21 Indoor and outdoor system
9160 C-005 $ 2,000.00 11/30/21 Outdoor system
9161 C-070 $ 3,800.00 12/28/21 Outdoor system with video
9162 C-053 $ 2,000.00 1/11/22 First floor system
9163 C-015 $ 750.00 1/20/22 Security plan
9165 C-079 $ 8,000.00 2/3/22 Indoor and outdoor system with video
9168 C-049 $ 1,500.00 2/7/22 First floor system
9169 C-030 $ 5,750.00 2/24/22 Outdoor system for apartment building
9170 C-079 $ 3,000.00 3/3/22 Indoor system
9171 C-059 $ 7,000.00 3/11/22 Indoor system with video
9172 C-059 $ 6,000.00 3/11/22 Indoor system with video
9173 C-072 $ 5,000.00 5/5/22 Indoor and outdoor system
9175 C-067 $ 10,500.00 4/8/22 Indoor and outdoor system for apartment building
9176 C-065 $ 2,000.00 4/8/22 Security plan
9177 C-058 $ 3,500.00 5/17/22 Indoor system with video
9178 C-020 $ 3,750.00 4/25/22 Indoor system
9180 C-001 $ 1,800.00 4/14/22 Outdoor system
9181 C-071 $ 1,000.00 5/10/22 Front door system
9182 C-060 $ 5,000.00 6/1/22 Indoor system with video
9185 C-068 $ 2,500.00 6/23/22 Garage system
9186 C-054 $ 10,250.00 7/11/22 Indoor and outdoor system with video and backup
9190 C-079 $ 2,500.00 6/24/22 Outdoor system
9193 C-085 $ 3,000.00 7/25/22 Outdoor system with video
9194 C-015 $ 2,300.00 7/26/22 Indoor system
9195 C-045 $ 4,500.00 6/29/22 Indoor system with video
9197 C-080 $ 2,500.00 8/21/22 Garage and outdoor system
9198 C-065 $ 3,000.00 7/15/22 Indoor system
9199 C-067 $ 1,500.00 7/25/22 Security plan
9200 C-031 $ 1,500.00 8/8/22 Front door system
9203 C-087 $ 12,000.00 8/17/22 Indoor and outdoor system for apartment building
9204 C-073 $ 2,500.00 7/28/22 Indoor system
9205 C-084 $ 1,000.00 8/24/22 Front door system
9208 C-045 $ 1,000.00 8/22/22 Security plan
9209 C-059 $ 4,000.00 8/18/22 Indoor system with video
9210 C-079 $ 3,000.00 8/25/22 Indoor system
9136 C-088 $ 2,000.00 6/15/21 Outdoor security system
9141 C-088 $ 3,000.00 6/22/21 Indoor security system

Invoices

Home Security Systems
InvoiceNum ContractNum InvoiceDate InvoiceItem InvoiceAmt InvoicePaid
1011 9111 2/27/21 Indoor alarm system $ 500.00 TRUE
1012 9111 5/10/21 Indoor alarm system $ 1,500.00 TRUE
1021 9112 2/25/21 Front door alarm system $ 990.00 TRUE
1031 9120 4/19/21 Indoor alarm system $ 1,000.00 TRUE
1032 9120 8/19/21 Indoor alarm system $ 1,000.00 TRUE
1033 9120 9/20/21 Indoor alarm system $ 1,000.00 TRUE
1034 9120 10/20/21 Indoor alarm system $ 50,000.00 TRUE
1041 9115 4/1/21 Indoor alarm system $ 2,500.00 TRUE
1051 9117 4/1/21 Indoor alarm system $ 1,250.00 TRUE
1061 9126 3/11/22 Indoor wireless system $ 500.00 TRUE
1062 9126 9/12/22 Indoor wireless system $ 1,000.00 FALSE
1063 9126 7/11/23 Indoor wireless system $ 1,000.00 FALSE
1064 9126 4/4/23 Indoor wireless system $ 2,500.00 FALSE
1071 9123 11/22/21 Indoor and outdoor alarm system $ 3,000.00 TRUE
1072 9123 7/22/22 Indoor and outdoor alarm system $ 3,000.00 TRUE
1073 9123 9/21/23 Indoor and outdoor alarm system $ 3,000.00 FALSE
1081 9125 7/23/21 Indoor and outdoor alarm system $ 1,500.00 TRUE
1082 9125 12/20/21 Indoor and outdoor alarm system $ 2,000.00 TRUE
1083 9125 6/24/22 Indoor and outdoor alarm system $ 2,000.00 TRUE
1091 9127 6/7/21 First floor alarm system $ 1,250.00 TRUE
1101 9122 7/14/21 Indoor alarm system $ 500.00 TRUE
1102 9122 11/15/21 Indoor alarm system $ 500.00 TRUE
1103 9122 7/14/22 Indoor alarm system $ 1,000.00 TRUE
1111 9121 10/12/21 Indoor and outdoor alarm system $ 2,000.00 TRUE
1112 9121 11/12/21 Indoor and outdoor alarm system $ 2,000.00 TRUE
1113 9121 9/2/22 Indoor and outdoor alarm system $ 2,000.00 FALSE
1114 9121 9/5/23 Indoor and outdoor alarm system $ 2,000.00 FALSE
1121 9134 9/28/21 Garage system with video $ 1,500.00 TRUE
1122 9134 12/28/21 Garage system with video $ 1,000.00 TRUE
1131 9135 10/18/21 Security plan $ 500.00 TRUE
1132 9135 3/28/22 Security plan $ 1,000.00 TRUE
1141 9131 2/3/22 Indoor and outdoor alarm system $ 2,500.00 TRUE
1142 9131 6/3/22 Indoor and outdoor alarm system $ 2,000.00 TRUE
1143 9131 10/3/22 Indoor and outdoor alarm system $ 2,000.00 FALSE
1144 9131 8/3/23 Indoor and outdoor alarm system $ 2,000.00 FALSE
1151 9130 8/11/21 Indoor and outdoor alarm system $ 1,250.00 TRUE
1152 9130 10/15/21 Indoor and outdoor alarm system $ 2,000.00 TRUE
1153 9130 3/30/22 Indoor and outdoor alarm system $ 2,000.00 TRUE
1161 9132 9/10/21 Security plan $ 500.00 TRUE
1162 9132 10/12/21 Security plan $ 500.00 TRUE
1163 9132 11/12/21 Security plan $ 500.00 TRUE
1171 9137 2/22/22 Indoor and outdoor system $ 2,000.00 TRUE
1172 9137 6/22/22 Indoor and outdoor system $ 2,000.00 TRUE
1173 9137 2/24/23 Indoor and outdoor system $ 2,000.00 FALSE
1174 9137 3/20/22 Indoor and outdoor system $ 2,000.00 FALSE
1181 9138 10/22/21 Indoor system $ 1,000.00 TRUE
1182 9138 12/22/21 Indoor system $ 1,000.00 TRUE
1183 9138 6/23/22 Indoor system $ 1,000.00 TRUE
1184 9138 4/27/23 Indoor system $ 1,000.00 FALSE
1191 9133 9/29/21 Indoor wireless system $ 1,250.00 TRUE
1201 9140 1/12/22 Indoor and outdoor system $ 2,500.00 TRUE
1202 9140 8/26/22 Indoor and outdoor system $ 2,000.00 FALSE
1203 9140 8/26/22 Indoor and outdoor system $ 4,000.00 FALSE
1211 9143 1/11/22 Indoor and outdoor system $ 5,000.00 TRUE
1212 9143 6/20/22 Indoor and outdoor system $ 2,500.00 TRUE
1213 9143 3/30/23 Indoor and outdoor system $ 2,500.00 FALSE
1221 9144 6/17/22 Indoor and outdoor system $ 2,500.00 TRUE
1222 9144 5/1/23 Indoor and outdoor system $ 2,000.00 FALSE
1231 9145 12/22/22 Indoor system $ 2,500.00 FALSE
1232 9145 8/15/23 Indoor system $ 2,000.00 FALSE
1241 9150 10/18/21 Indoor and outdoor system $ 11,000.00 TRUE
1251 9146 9/9/21 Security plan $ 1,300.00 TRUE
1261 9153 9/22/21 Front door system $ 1,375.00 TRUE
1271 9148 12/28/21 Safe room $ 1,500.00 TRUE
1272 9148 2/28/22 Safe room $ 1,000.00 TRUE
1273 9148 6/30/22 Safe room $ 1,000.00 TRUE
1274 9148 9/26/22 Safe room $ 1,000.00 FALSE
1281 9156 9/30/22 Security plan $ 300.00 FALSE
1282 9156 8/14/23 Security plan $ 650.00 FALSE
1291 9157 1/31/22 Indoor and outdoor system $ 2,500.00 TRUE
1292 9157 6/30/22 Indoor and outdoor system $ 2,500.00 TRUE
1293 9157 3/30/23 Indoor and outdoor system $ 500.00 FALSE
1301 9151 3/7/22 Indoor and outdoor system $ 3,000.00 TRUE
1302 9151 7/7/22 Indoor and outdoor system $ 3,000.00 TRUE
1303 9151 5/3/23 Indoor and outdoor system $ 2,000.00 FALSE
1311 9160 12/21/21 Outdoor system $ 1,000.00 TRUE
1312 9160 5/27/22 Outdoor system $ 1,000.00 TRUE
1321 9161 6/28/22 Outdoor system $ 800.00 TRUE
1322 9161 10/26/22 Outdoor system $ 1,000.00 FALSE
1323 9161 4/25/23 Outdoor system $ 1,000.00 FALSE
1324 9161 6/27/22 Outdoor system $ 1,000.00 FALSE
1331 9162 6/10/22 First floor system $ 500.00 TRUE
1332 9162 11/14/22 First floor system $ 500.00 FALSE
1333 9162 5/11/23 First floor system $ 1,000.00 FALSE
1341 9163 3/21/22 Security plan $ 750.00 TRUE
1351 9165 6/3/22 Indoor and outdoor system $ 2,000.00 TRUE
1352 9165 12/5/22 Indoor and outdoor system $ 2,000.00 FALSE
1353 9165 8/3/23 Indoor and outdoor system $ 2,000.00 FALSE
1354 9165 8/1/22 Indoor and outdoor system $ 2,000.00 FALSE
1361 9168 4/21/22 Indoor and outdoor system $ 2,000.00 TRUE
1362 9168 7/7/22 First floor system $ 1,500.00 TRUE
1371 9169 6/9/22 Outdoor system $ 1,750.00 TRUE
1372 9169 7/29/22 Outdoor system $ 1,000.00 TRUE
1373 9169 9/23/22 Outdoor system $ 1,000.00 FALSE
1374 9169 10/24/22 Outdoor system $ 2,000.00 FALSE
1381 9170 9/6/22 Indoor system $ 500.00 FALSE
1382 9170 1/3/22 Indoor system $ 500.00 FALSE
1383 9170 11/5/23 Indoor system $ 1,000.00 FALSE
1384 9170 7/8/22 Indoor system $ 1,000.00 FALSE
1391 9171 12/9/22 Indoor system $ 2,000.00 FALSE
1392 9171 3/12/23 Indoor system $ 1,000.00 FALSE
1393 9171 3/11/23 Indoor system $ 1,000.00 FALSE
1394 9171 3/27/23 Indoor system $ 3,000.00 FALSE
1401 9172 11/10/22 Indoor system $ 2,000.00 FALSE
1402 9172 9/13/23 Indoor system $ 2,000.00 FALSE
1403 9172 9/26/22 Indoor system $ 2,000.00 FALSE
1411 9175 7/8/22 Indoor and outdoor system $ 5,500.00 TRUE
1412 9175 4/9/23 Indoor and outdoor system $ 2,500.00 FALSE
1413 9175 9/27/23 Indoor and outdoor system $ 2,500.00 FALSE
1421 9176 6/8/22 Security plan $ 2,000.00 TRUE
1431 9180 8/3/22 Outdoor system $ 1,000.00 TRUE
1432 9180 9/14/22 Outdoor system $ 500.00 FALSE
1433 9180 2/15/23 Outdoor system $ 500.00 FALSE
1434 9180 8/15/23 Outdoor system $ 800.00 FALSE
1441 9178 6/24/22 Indoor system $ 1,750.00 TRUE
1442 9178 10/24/22 Indoor system $ 1,000.00 FALSE
1443 9178 6/25/23 Indoor system $ 1,000.00 FALSE
1451 9173 9/5/23 Indoor and outdoor system $ 1,500.00 FALSE
1452 9173 5/6/22 Indoor and outdoor system $ 1,000.00 FALSE
1453 9173 5/5/23 Indoor and outdoor system $ 1,000.00 FALSE
1454 9173 8/18/23 Indoor and outdoor system $ 1,500.00 FALSE
1461 9181 10/11/22 Front door system $ 300.00 FALSE
1462 9181 5/10/23 Front door system $ 300.00 FALSE
1463 9181 10/18/23 Front door system $ 200.00 FALSE
1471 9177 8/23/22 Indoor system $ 3,500.00 TRUE
1481 9182 8/1/22 Indoor system $ 2,500.00 TRUE
1482 9182 10/21/22 Indoor system $ 2,500.00 FALSE
1491 9190 10/14/22 Outdoor system $ 500.00 FALSE
1492 9190 2/16/23 Outdoor system $ 500.00 FALSE
1493 9190 11/16/23 Outdoor system $ 500.00 FALSE
1494 9190 9/9/22 Outdoor system $ 1,000.00 FALSE
1501 9185 2/23/23 Garage system $ 500.00 FALSE
1502 9185 10/23/23 Garage system $ 1,000.00 FALSE
1503 9185 8/26/22 Garage system $ 1,000.00 FALSE
1511 9195 8/25/22 Indoor system $ 1,500.00 TRUE
1512 9195 3/29/23 Indoor system $ 1,000.00 FALSE
1513 9195 4/30/22 Indoor system $ 2,000.00 FALSE
1521 9186 10/11/22 Indoor and outdoor system $ 5,250.00 FALSE
1522 9186 3/12/23 Indoor and outdoor system $ 2,500.00 FALSE
1523 9186 10/4/23 Indoor and outdoor system $ 2,500.00 FALSE
1531 9198 12/15/22 Indoor system $ 500.00 FALSE
1532 9198 3/15/23 Indoor system $ 1,000.00 FALSE
1533 9198 1/15/22 Indoor system $ 1,000.00 FALSE
1534 9198 7/15/22 Indoor system $ 500.00 FALSE
1541 9193 8/25/22 Outdoor system $ 1,500.00 TRUE
1542 9193 2/27/23 Outdoor system $ 1,500.00 FALSE
1551 9199 12/23/22 Security plan $ 1,500.00 FALSE
1561 9194 8/25/22 Indoor system $ 2,000.00 TRUE
1562 9194 3/23/23 Indoor system $ 300.00 FALSE
1571 9204 9/28/22 Indoor system $ 1,500.00 FALSE
1572 9204 1/27/23 Indoor system $ 1,000.00 FALSE
1581 9197 10/3/22 Garage and outdoor system $ 1,500.00 FALSE
1582 9197 12/2/22 Garage and outdoor system $ 500.00 FALSE
1583 9197 4/20/23 Garage and outdoor system $ 500.00 FALSE
1591 9208 1/6/23 Security plan $ 250.00 FALSE
1592 9208 4/13/23 Security plan $ 250.00 FALSE
1593 9208 2/15/22 Security plan $ 250.00 FALSE
1594 9208 3/31/23 Security plan $ 250.00 FALSE
1601 9200 3/8/23 Front door system $ 1,500.00 FALSE
1611 9203 8/17/23 Indoor and outdoor system $ 3,000.00 FALSE
1612 9203 8/16/22 Indoor and outdoor system $ 3,000.00 FALSE
1613 9203 12/17/23 Indoor and outdoor system $ 3,000.00 FALSE
1614 9203 9/30/23 Indoor and outdoor system $ 3,000.00 FALSE
1621 9209 4/18/23 Indoor system $ 2,000.00 FALSE
1622 9209 10/18/23 Indoor system $ 1,000.00 FALSE
1623 9209 9/23/22 Indoor system $ 1,000.00 FALSE
1641 9205 10/24/22 Front door system $ 500.00 FALSE
1642 9205 5/25/23 Front door system $ 500.00 FALSE
1651 9210 12/23/22 Indoor system $ 4,000.00 FALSE
1652 9210 2/24/23 Indoor system $ 1,000.00 FALSE
1653 9210 12/21/23 Indoor system $ 1,000.00 FALSE
1654 9210 5/1/23 Indoor system $ 1,000.00 FALSE