Order Now
Uncategorized

MEDTERM Excel Project Exam

15 min read
Posted on 
March 21st, 2023
Home Uncategorized MEDTERM Excel Project Exam

ContractsSignedAfter8-1-14

3/1/19

2,000.00

1,250.00

Don't use plagiarized sources. Get Your Custom Essay on
MEDTERM Excel Project Exam
Just from $13/Page
Order Essay

1,250.00

2,500.00

1,500.00 5/28/19

8,000.00 6/22/19

8,500.00

5,500.00

5,000.00

8,000.00

5,500.00 9/30/19

2,000.00

2,000.00

8,000.00

1,500.00

3/11/20

5,000.00

2,000.00 4/8/20

3,500.00

1,000.00

5,000.00

2,500.00

2,500.00

3,000.00

4,500.00

2,500.00

3,000.00

1,500.00 7/25/20

1,500.00

2,500.00

1,000.00

1,000.00

4,000.00

3,000.00

2,000.00

3,000.00 6/22/19

Contract Num Contract Amt Signing Date
9111 2,000.00 2/6/19
9112 990.00 2/18/19
9115 2,500.00 3/1/19
9117 1,250.00
9120 3,500.00 2/19/19
9121 8,000.00 5/3/19
9122 4/14/19
9123 9,000.00 3/22/19
9125 5,500.00 3/25/19
9126 5,000.00 3/11/19
9127 4/17/19
9130 5,250.00 6/11/19
9131 8,500.00 6/3/19
9132 1,500.00 6/22/19
9133 7/8/19
9134 5/28/19
9135
9137
9138 4,000.00 6/25/19
9140 7/27/19
9143 10,000.00 8/11/19
9144 8/19/19
9145 8/23/19
9146 1,300.00 9/2/19
9148 4,500.00 9/23/19
9150 1

 1,000.00 8/27/19
9151 10/5/19
9153 1,375.00 9/17/19
9156 950.00 9/30/19
9157
9160 11/30/19
9161 3,800.00 12/28/19
9162 1/11/20
9163 750.00 1/20/20
9165 2/3/20
9168 2/7/20
9169 5,750.00 2/24/20
9170 3,000.00 3/3/20
9171 7,000.00 3/11/20
9172 6,000.00
9173 5/5/20
9175 10,500.00 4/8/20
9176
9177 5/17/20
9178 3,750.00 4/25/20
9180 1,800.00 4/14/20
9181 5/10/20
9182 6/1/20
9185 6/23/20
9186 10,250.00 7/11/20
9190 6/24/20
9193 7/25/20
9194 2,300.00 7/26/20
9195 6/29/20
9197 8/21/20
9198 7/15/20
9199
9200 8/8/20
9203 12,000.00 8/17/20
9204 7/28/20
9205 8/24/20
9208 8/22/20
9209 8/18/20
9210 8/25/20
9136 6/15/19
9141

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 Li

nks

Customers

Contracts

Invoices

Archived data Franklin National website Contact Franklin National

Add Invoice

Add Contract

Add Customer

Customers

GA

Atlanta GA

GA

GA

GA

GA

Li

Augusta GA

Dunwoody GA

Atlanta GA

son

Atlanta GA

GA

GA

Atlanta GA

Alpharetta GA

Atlanta GA

Augusta GA

College Park GA

ville

GA

GA

Decatur GA

Augusta GA

Atlanta GA

Atlanta GA

Douglas

GA

Alpharetta GA

Atlanta GA

GA

Augusta GA 30907

GA

GA

GA

Augusta GA

Alpharetta GA

Atlanta GA

GA

GA

Richard

Canton GA 30114

College Park GA 30337

Columbus GA 31998

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 30270 College Park
C-008 Chris Hackbart 404-985-

1122 3478 Woodline Rd 31107 Dunwoody
C-012 Ramona Earley 770-454-9801 33 E Pine St Alpharetta 30004
C-014 Vernon Crowley 706-396-1972 566 Kent Rd Augusta 30917
C-015 Alberto Cruz 770-637-7783 456 Winchester St Canton 30114
C-020 Michelle McMurray 404-963-0808 815 Pulley Rd Decatur 30031
C-027 Karen 706-483-9244 402 S Melody Rd 30918
C-030 Stephen Rosner 770-393-1228 111 E 78th Ave 30346
C-031 Daniel Plummer 404-842-4603 29 Graham Blvd 30347
C-032 Laquan Richard 404-857-1276 80 Cedar St 30350
C-038 Clint Schmitt 706-482-1400 2268 N Jackson Columbus 31998
C-040 Victor Ramirez 770-988-0777 144 E Tower Ave Holly Springs 30115
C-042 Michael Levander 404-337-

 9208 10 Lincoln Dr 30336
C-043 Shana Liang 770-988-1320 40 Monroe St 30022
C-045 Jamal Kidd 404-343-7509 3047 Millbrook Ave 31156
C-048 Joel Holtman 706-637-6591 7320 E Elmwood 30919
C-049 Frances Heller 404-983-2255 828 Turner St 30337
C-053 Ahmad Greenwell 770-396-8401 614 Berman Rd Douglas 30134
C-054 Jacquie Rollins 770-355-3989 258 Briar Ln Woolsey 30214
C-055 Steve Tomashek 404-979-2004 1440 Beadle Rd 30036
C-058 Andrea Vander 706-857-1771 77 Robin Rd 30907
C-059 Harry Fraser 770-392-0015 8045 Jefferson Ave 30339
C-060 Anita Renteria 770-949-3862 75 Johnson St 30341
C-064 Shoemaker 706-487-4700 5478 N Grove Baldwin 30511
C-065 Raymond Ringdahl 770-393-0403 24 Samuel St 30009
C-067 Beth Raferty 404-985-1992 612 E Carolina Pl 30318
C-068 Jason Grady 770-454-4002 5230 Fulton St Carrollton 30119
C-070 Gordon Griffin 706-456-7093 912 Market St
C-071 Mia Watanabe 404-857-4424 82 Cambridge St Briarcliff 30329
C-072 Barbara Callahan 404-337-8200 5049 Hwy 36 Sandy Springs 30342
C-073 Randy Dixon 770-983-1116 131 Pleasant St Marietta 30090
C-075 Jim Benning 706-482-9932 2167 S Macon St 30168
C-079 Sarah Alvarez 770-940-3380 620 Pinewoody 30412
C-080 Timothy Jones 404-392-4629 249 W 11th St 30322
C-083 Shanissa Hoffman 706-337-0990 1286 Jennifer Dr Athens 30604
C-084 Cathy Wilson 770-866-4882 78 Sandy Springs St Buford 30515
C-085 Phillips 770-637-5408 39 Water St
C-087 Nancy Wanson 404-963-0190 1752 S Main St
C-088 Mickey Kervin 706-454-3327 348 N Oak St

Contracts

Home Security Systems

CustomerID

C-001

C-027

C-005

Indoor alarm system

C-012

3/1/21 Indoor alarm system

C-055

C-040

with video

C-043 $ 2,000.00

Indoor alarm system

C-070

Indoor and outdoor alarm system with video

C-083

C-038

C-008 $ 1,250.00

C-042

Indoor and outdoor alarm system

C-070

with video

C-014

for apartment building

C-005 $ 1,250.00

Indoor wireless alarm system

C-088 $ 2,500.00

with video

C-088 $ 1,500.00 5/28/21 Security plan

C-045 $ 8,000.00 6/22/21

and backup

C-045

C-068 $ 8,500.00

C-083

Indoor and outdoor system for apartment building

C-075 $ 5,500.00

Indoor and outdoor system with video

C-064 $ 5,000.00

with video

C-048

Security plan

C-030

C-015

C-032 $ 8,000.00

Indoor and outdoor system with video

C-049

C-075

Security plan

C-075 $ 5,500.00 9/30/21 Indoor and outdoor system

C-005 $ 2,000.00

C-070

C-053 $ 2,000.00

C-015

Security plan

C-079 $ 8,000.00

Indoor and outdoor system with video

C-049 $ 1,500.00

First floor system

C-030

C-079

Indoor system

C-059

C-059

3/11/22 Indoor system with video

C-072 $ 5,000.00

Indoor and outdoor system

C-067

Indoor and outdoor system for apartment building

C-065 $ 2,000.00 4/8/22 Security plan

C-058 $ 3,500.00

Indoor system with video

C-020

Indoor system

C-001

Outdoor system

C-071

Front door system

C-060 $ 5,000.00

Indoor system with video

C-068 $ 2,500.00

Garage system

C-054

C-079 $ 2,500.00

Outdoor system

C-085 $ 3,000.00

Outdoor system with video

C-015

Indoor system

C-045 $ 4,500.00

Indoor system with video

C-080 $ 2,500.00

C-065 $ 3,000.00

Indoor system

C-067 $ 1,500.00 7/25/22 Security plan

C-031 $ 1,500.00

Front door system

C-087

Indoor and outdoor system for apartment building

C-073 $ 2,500.00

Indoor system

C-084 $ 1,000.00

Front door system

9208 C-045 $ 1,000.00

Security plan

C-059 $ 4,000.00

Indoor system with video

C-079 $ 3,000.00

Indoor system

C-088 $ 2,000.00

C-088 $ 3,000.00 6/22/21

ContractNum ContractAmt SigningDate ContractType
9111 $ 2,000.00 2/6/21 Indoor alarm system
9112 $ 990.00 2/18/21 Front door alarm system
9115 $ 2,500.00 3/1/21
9117 $ 1,250.00
9120 $ 3,500.00 2/19/21 Indoor alarm and video surveillance
9121 $ 8,000.00 5/3/21 Indoor and outdoor alarm system
9122 4/14/21
9123 $ 9,000.00 3/22/21
9125 $ 5,500.00 3/25/21 Indoor and outdoor alarm system with backup
9126 $ 5,000.00 3/11/21 Indoor wireless alarm system
9127 4/17/21 First floor alarm system
9130 $ 5,250.00 6/11/21
9131 $ 8,500.00 6/3/21 Indoor and outdoor system
9132 $ 1,500.00 6/22/21 Security plan
9133 7/8/21
9134 5/28/21 Garage system
9135
9137 Indoor and outdoor system with video
9138 $ 4,000.00 6/25/21 Indoor alarm system with video
9140 7/27/21 Indoor and outdoor system for apartment building
9143 $ 10,000.00 8/11/21
9144 8/19/21
9145 8/23/21 Indoor system
9146 $ 1,300.00 9/2/21
9148 $ 4,500.00 9/23/21 Safe room
9150 $ 11,000.00 8/27/21 Indoor and outdoor system with video and safe room
9151 10/5/21
9153 $ 1,375.00 9/17/21 Front door system
9156 $ 950.00 9/30/21
9157
9160 11/30/21 Outdoor system
9161 $ 3,800.00 12/28/21 Outdoor system with video
9162 1/11/22 First floor system
9163 $ 750.00 1/20/22
9165 2/3/22
9168 2/7/22
9169 $ 5,750.00 2/24/22 Outdoor system for apartment building
9170 $ 3,000.00 3/3/22
9171 $ 7,000.00 3/11/22 Indoor system with video
9172 $ 6,000.00
9173 5/5/22
9175 $ 10,500.00 4/8/22
9176
9177 5/17/22
9178 $ 3,750.00 4/25/22
9180 $ 1,800.00 4/14/22
9181 $ 1,000.00 5/10/22
9182 6/1/22
9185 6/23/22
9186 $ 10,250.00 7/11/22 Indoor and outdoor system with video and backup
9190 6/24/22
9193 7/25/22
9194 $ 2,300.00 7/26/22
9195 6/29/22
9197 8/21/22 Garage and outdoor system
9198 7/15/22
9199
9200 8/8/22
9203 $ 12,000.00 8/17/22
9204 7/28/22
9205 8/24/22
8/22/22
9209 8/18/22
9210 8/25/22
9136 6/15/21 Outdoor security system
9141 Indoor security system

Invoices

Home Security Systems

ContractNum

9111

Indoor alarm system

9111

Indoor alarm system $ 1,500.00 TRUE

9112

Front door alarm system $ 990.00 TRUE

9120

Indoor alarm system $ 1,000.00 TRUE

9120 8/19/21 Indoor alarm system $ 1,000.00 TRUE

9120

Indoor alarm system $ 1,000.00 TRUE

9120

Indoor alarm system

TRUE

9115

Indoor alarm system $ 2,500.00 TRUE

9117 4/1/21 Indoor alarm system $ 1,250.00 TRUE

9126 3/11/22

$ 500.00 TRUE

9126

Indoor wireless system $ 1,000.00

9126

Indoor wireless system $ 1,000.00 FALSE

9126

Indoor wireless system $ 2,500.00 FALSE

9123

Indoor and outdoor alarm system $ 3,000.00 TRUE

9123

Indoor and outdoor alarm system $ 3,000.00 TRUE

9123

Indoor and outdoor alarm system $ 3,000.00 FALSE

9125

Indoor and outdoor alarm system $ 1,500.00 TRUE

9125

Indoor and outdoor alarm system $ 2,000.00 TRUE

9125 6/24/22 Indoor and outdoor alarm system $ 2,000.00 TRUE

9127

First floor alarm system $ 1,250.00 TRUE

9122

Indoor alarm system $ 500.00 TRUE

9122

Indoor alarm system $ 500.00 TRUE

9122

Indoor alarm system $ 1,000.00 TRUE

9121

Indoor and outdoor alarm system $ 2,000.00 TRUE

9121

Indoor and outdoor alarm system $ 2,000.00 TRUE

9121

Indoor and outdoor alarm system $ 2,000.00 FALSE

9121

Indoor and outdoor alarm system $ 2,000.00 FALSE

9134

$ 1,500.00 TRUE

1122 9134 12/28/21 Garage system with video $ 1,000.00 TRUE

9135

Security plan $ 500.00 TRUE

9135

Security plan $ 1,000.00 TRUE

9131 2/3/22 Indoor and outdoor alarm system $ 2,500.00 TRUE

9131

Indoor and outdoor alarm system $ 2,000.00 TRUE

9131

Indoor and outdoor alarm system $ 2,000.00 FALSE

9131

Indoor and outdoor alarm system $ 2,000.00 FALSE

9130 8/11/21 Indoor and outdoor alarm system $ 1,250.00 TRUE

9130

Indoor and outdoor alarm system $ 2,000.00 TRUE

9130

Indoor and outdoor alarm system $ 2,000.00 TRUE

9132

Security plan $ 500.00 TRUE

9132 10/12/21 Security plan $ 500.00 TRUE

9132 11/12/21 Security plan $ 500.00 TRUE

9137

Indoor and outdoor system $ 2,000.00 TRUE

9137

Indoor and outdoor system $ 2,000.00 TRUE

9137

Indoor and outdoor system $ 2,000.00 FALSE

9137

Indoor and outdoor system $ 2,000.00 FALSE

9138

Indoor system $ 1,000.00 TRUE

9138

Indoor system $ 1,000.00 TRUE

9138 6/23/22 Indoor system $ 1,000.00 TRUE

9138

Indoor system $ 1,000.00 FALSE

9133

Indoor wireless system $ 1,250.00 TRUE

9140

Indoor and outdoor system $ 2,500.00 TRUE

9140

Indoor and outdoor system $ 2,000.00 FALSE

9140 8/26/22 Indoor and outdoor system $ 4,000.00 FALSE

9143 1/11/22 Indoor and outdoor system $ 5,000.00 TRUE

9143

Indoor and outdoor system $ 2,500.00 TRUE

9143

Indoor and outdoor system $ 2,500.00 FALSE

9144

Indoor and outdoor system $ 2,500.00 TRUE

9144

Indoor and outdoor system $ 2,000.00 FALSE

9145

Indoor system $ 2,500.00 FALSE

9145

Indoor system $ 2,000.00 FALSE

9150 10/18/21 Indoor and outdoor system $ 11,000.00 TRUE

9146

Security plan $ 1,300.00 TRUE

9153

Front door system $ 1,375.00 TRUE

9148 12/28/21 Safe room $ 1,500.00 TRUE

9148

Safe room $ 1,000.00 TRUE

9148

Safe room $ 1,000.00 TRUE

9148

Safe room $ 1,000.00 FALSE

9156

Security plan

FALSE

9156

Security plan

FALSE

9157

Indoor and outdoor system $ 2,500.00 TRUE

9157 6/30/22 Indoor and outdoor system $ 2,500.00 TRUE

9157 3/30/23 Indoor and outdoor system $ 500.00 FALSE

9151

Indoor and outdoor system $ 3,000.00 TRUE

9151

Indoor and outdoor system $ 3,000.00 TRUE

9151

Indoor and outdoor system $ 2,000.00 FALSE

9160

Outdoor system $ 1,000.00 TRUE

9160

Outdoor system $ 1,000.00 TRUE

9161

Outdoor system

TRUE

9161

Outdoor system $ 1,000.00 FALSE

9161

Outdoor system $ 1,000.00 FALSE

9161

Outdoor system $ 1,000.00 FALSE

9162

First floor system $ 500.00 TRUE

9162

First floor system $ 500.00 FALSE

9162

First floor system $ 1,000.00 FALSE

9163

Security plan $ 750.00 TRUE

9165 6/3/22 Indoor and outdoor system $ 2,000.00 TRUE

9165

Indoor and outdoor system $ 2,000.00 FALSE

9165 8/3/23 Indoor and outdoor system $ 2,000.00 FALSE

9165

Indoor and outdoor system $ 2,000.00 FALSE

9168

Indoor and outdoor system $ 2,000.00 TRUE

9168 7/7/22 First floor system $ 1,500.00 TRUE

9169

Outdoor system

TRUE

9169

Outdoor system $ 1,000.00 TRUE

9169

Outdoor system $ 1,000.00 FALSE

9169

Outdoor system $ 2,000.00 FALSE

9170

Indoor system $ 500.00 FALSE

9170

Indoor system $ 500.00 FALSE

9170

Indoor system $ 1,000.00 FALSE

9170

Indoor system $ 1,000.00 FALSE

9171

Indoor system $ 2,000.00 FALSE

9171

Indoor system $ 1,000.00 FALSE

9171

Indoor system $ 1,000.00 FALSE

9171

Indoor system $ 3,000.00 FALSE

9172

Indoor system $ 2,000.00 FALSE

9172

Indoor system $ 2,000.00 FALSE

9172 9/26/22 Indoor system $ 2,000.00 FALSE

9175 7/8/22 Indoor and outdoor system $ 5,500.00 TRUE

9175

Indoor and outdoor system $ 2,500.00 FALSE

9175

Indoor and outdoor system $ 2,500.00 FALSE

9176

Security plan $ 2,000.00 TRUE

9180

Outdoor system $ 1,000.00 TRUE

9180

Outdoor system $ 500.00 FALSE

9180

Outdoor system $ 500.00 FALSE

9180 8/15/23 Outdoor system $ 800.00 FALSE

9178 6/24/22 Indoor system $ 1,750.00 TRUE

9178 10/24/22 Indoor system $ 1,000.00 FALSE

9178

Indoor system $ 1,000.00 FALSE

9173 9/5/23 Indoor and outdoor system $ 1,500.00 FALSE

9173

Indoor and outdoor system $ 1,000.00 FALSE

9173

Indoor and outdoor system $ 1,000.00 FALSE

9173

Indoor and outdoor system $ 1,500.00 FALSE

9181

Front door system $ 300.00 FALSE

9181

Front door system $ 300.00 FALSE

9181

Front door system

FALSE

9177

Indoor system $ 3,500.00 TRUE

9182 8/1/22 Indoor system $ 2,500.00 TRUE

9182

Indoor system $ 2,500.00 FALSE

9190

Outdoor system $ 500.00 FALSE

9190

Outdoor system $ 500.00 FALSE

9190

Outdoor system $ 500.00 FALSE

9190

Outdoor system $ 1,000.00 FALSE

9185

Garage system $ 500.00 FALSE

9185

Garage system $ 1,000.00 FALSE

9185 8/26/22 Garage system $ 1,000.00 FALSE

9195 8/25/22 Indoor system $ 1,500.00 TRUE

9195

Indoor system $ 1,000.00 FALSE

9195

Indoor system $ 2,000.00 FALSE

9186 10/11/22 Indoor and outdoor system $ 5,250.00 FALSE

9186 3/12/23 Indoor and outdoor system $ 2,500.00 FALSE

9186

Indoor and outdoor system $ 2,500.00 FALSE

9198

Indoor system $ 500.00 FALSE

9198

Indoor system $ 1,000.00 FALSE

9198

Indoor system $ 1,000.00 FALSE

9198 7/15/22 Indoor system $ 500.00 FALSE

9193 8/25/22 Outdoor system $ 1,500.00 TRUE

9193

Outdoor system $ 1,500.00 FALSE

9199

Security plan $ 1,500.00 FALSE

9194 8/25/22 Indoor system $ 2,000.00 TRUE

9194

Indoor system $ 300.00 FALSE

9204

Indoor system $ 1,500.00 FALSE

9204

Indoor system $ 1,000.00 FALSE

9197 10/3/22 Garage and outdoor system $ 1,500.00 FALSE

9197

Garage and outdoor system $ 500.00 FALSE

9197

Garage and outdoor system $ 500.00 FALSE

9208

Security plan

FALSE

9208

Security plan $ 250.00 FALSE

9208 2/15/22 Security plan $ 250.00 FALSE

9208

Security plan $ 250.00 FALSE

9200

Front door system $ 1,500.00 FALSE

9203

Indoor and outdoor system $ 3,000.00 FALSE

9203

Indoor and outdoor system $ 3,000.00 FALSE

9203

Indoor and outdoor system $ 3,000.00 FALSE

9203

Indoor and outdoor system $ 3,000.00 FALSE

9209

Indoor system $ 2,000.00 FALSE

9209 10/18/23 Indoor system $ 1,000.00 FALSE

9209 9/23/22 Indoor system $ 1,000.00 FALSE

9205 10/24/22 Front door system $ 500.00 FALSE

9205

Front door system $ 500.00 FALSE

9210 12/23/22 Indoor system $ 4,000.00 FALSE

9210 2/24/23 Indoor system $ 1,000.00 FALSE

9210

Indoor system $ 1,000.00 FALSE

9210 5/1/23 Indoor system $ 1,000.00 FALSE

InvoiceNum InvoiceDate InvoiceItem InvoiceAmt InvoicePaid
1011 2/27/21 $ 500.00 TRUE
1012 5/10/21
1021 2/25/21
1031 4/19/21
1032
1033 9/20/21
1034 10/20/21 $ 50,000.00
1041 4/1/21
1051
1061 Indoor wireless system
1062 9/12/22 FALSE
1063 7/11/23
1064 4/4/23
1071 11/22/21
1072 7/22/22
1073 9/21/23
1081 7/23/21
1082 12/20/21
1083
1091 6/7/21
1101 7/14/21
1102 11/15/21
1103 7/14/22
1111 10/12/21
1112 11/12/21
1113 9/2/22
1114 9/5/23
1121 9/28/21 Garage system with video
1131 10/18/21
1132 3/28/22
1141
1142 6/3/22
1143 10/3/22
1144 8/3/23
1151
1152 10/15/21
1153 3/30/22
1161 9/10/21
1162
1163
1171 2/22/22
1172 6/22/22
1173 2/24/23
1174 3/20/22
1181 10/22/21
1182 12/22/21
1183
1184 4/27/23
1191 9/29/21
1201 1/12/22
1202 8/26/22
1203
1211
1212 6/20/22
1213 3/30/23
1221 6/17/22
1222 5/1/23
1231 12/22/22
1232 8/15/23
1241
1251 9/9/21
1261 9/22/21
1271
1272 2/28/22
1273 6/30/22
1274 9/26/22
1281 9/30/22 $ 300.00
1282 8/14/23 $ 650.00
1291 1/31/22
1292
1293
1301 3/7/22
1302 7/7/22
1303 5/3/23
1311 12/21/21
1312 5/27/22
1321 6/28/22 $ 800.00
1322 10/26/22
1323 4/25/23
1324 6/27/22
1331 6/10/22
1332 11/14/22
1333 5/11/23
1341 3/21/22
1351
1352 12/5/22
1353
1354 8/1/22
1361 4/21/22
1362
1371 6/9/22 $ 1,750.00
1372 7/29/22
1373 9/23/22
1374 10/24/22
1381 9/6/22
1382 1/3/22
1383 11/5/23
1384 7/8/22
1391 12/9/22
1392 3/12/23
1393 3/11/23
1394 3/27/23
1401 11/10/22
1402 9/13/23
1403
1411
1412 4/9/23
1413 9/27/23
1421 6/8/22
1431 8/3/22
1432 9/14/22
1433 2/15/23
1434
1441
1442
1443 6/25/23
1451
1452 5/6/22
1453 5/5/23
1454 8/18/23
1461 10/11/22
1462 5/10/23
1463 10/18/23 $ 200.00
1471 8/23/22
1481
1482 10/21/22
1491 10/14/22
1492 2/16/23
1493 11/16/23
1494 9/9/22
1501 2/23/23
1502 10/23/23
1503
1511
1512 3/29/23
1513 4/30/22
1521
1522
1523 10/4/23
1531 1

2/15/22
1532 3/15/23
1533 1/15/22
1534
1541
1542 2/27/23
1551 12/23/22
1561
1562 3/23/23
1571 9/28/22
1572 1/27/23
1581
1582 12/2/22
1583 4/20/23
1591 1/6/23 $ 250.00
1592 4/13/23
1593
1594 3/31/23
1601 3/8/23
1611 8/17/23
1612 8/16/22
1613 12/17/23
1614 9/30/23
1621 4/18/23
1622
1623
1641
1642 5/25/23
1651
1652
1653 12/21/23
1654

Shelly Cashman Excel 2019 | Module 10: SAM Project 1a

Franklin National

Shelly Cashman Excel 2019 | Module 10: SAM Project 1a

ANALYZING DATA WITH POWER TOOLS AND CREATING MACROS

GETTING STARTED

Open the file SC_EX19_10a_FirstLastName_1.xlsm, available for download from the SAM website.

Save the file as SC_EX19_10a_FirstLastName_2.xlsm by changing the “1” to a “2”.

If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

Support_EX19_10a_Archive.xlsx

With the file SC_EX19_10a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

To complete this project, you need to display the Power Pivot and Developer tabs. If these tabs do not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the following check boxes:

Developer

Layout

Power Pivot
Click the OK button to close the Excel Options dialog box and add the Developer tab, the Power Pivot tab, and the buttons for the Power tools to the ribbon.

PROJECT STEPS

Ravi Mehta is the finance director for the Home Security Group of Franklin National, a North American manufacturing company. Ravi is developing an Excel workbook that his managers can use to track and interpret data about customers, contracts, and invoices. He asks for your help in setting up tools to analyze the data and to automate the workbook to make it easier to use.
Go to the Home Page worksheet. Ravi wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary.
Remove the hyperlink from the text in cell C4.

Ravi wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:

In cell C5, insert a link to cell A1 of the Customers worksheet.

In cell C6, insert a link to cell A1 of the Contracts worksheet.

In cell C7, insert a link to cell A1 of the Invoices worksheet.

Ravi also wants users to be able to access archived contract data as they prepare new contracts. Ravi stores the archived data in an Excel workbook. Add a link to the file as follows:

In cell C8, insert a link to the file Support_EX19_10a_Archive.xlsx.

Add the following text as a ScreenTip that explains what happens when users click the link:
Open an archive of contract data.

The Franklin National website provides information Ravi’s managers use every day. In cell C9, add a link to the www.fn.example.com website.

Ravi also wants to include a way that managers can request information from the home office of Franklin National. In cell C10, add a link to the info@fn.example.com email address.

Ravi’s plan is that when users click the Add Customer button in the range D3:E3, the Customers worksheet opens and Excel adds a new, blank record to the Customer table, where users can insert the new customer information. Record a macro to automate the Add Customer button as follows:

Enable all macros in the workbook, and then display the Developer tab on the ribbon.

Record a macro stored in this workbook using Add_Customer as the name of the macro.

While recording the macro, display the Customers worksheet.

Also, while recording the macro, add a new blank record to the end of the Customers table.

Stop recording the macro.

Assign the Add_Customer macro to the Add Customer button and then run the Add_Customer macro to make sure it inserts a new record at the bottom of the Customers table on the Customers worksheet.

Ravi has already created similar macros to add a new contract record and a new invoice record. Return to the Home Page worksheet, and assign the macros to buttons as follows:

Assign the Add_Contract macro to the Add Contract button.

Assign the Add_Invoice macro to the Add Invoice button.

Go to the Customers worksheet. Ravi wants to create PivotTables that include customer, contract, and invoice data. He already added the tables containing this data to the Data Model. Ravi wants to list and total each contract amount by city and signing date.
Create a PivotTable that displays this information as follows:

Use Power Pivot to create a PivotTable on a new worksheet, using Contracts by City as the name of the worksheet.

Display the City field values from the Query – Customer data source as row headings.

Display the SigningDate field values from the Query – Contract data source as column headings.

Add the ContractAmt field from the Query – Contract data source to the Values area to sum the field values.

Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.)

Ravi also wants to list the paid and unpaid invoices for each month and then filter them by contract type. Create a PivotTable that displays this information as follows:

Use Power Pivot to create a PivotTable on a new worksheet, using Invoices by Month as the name of the worksheet.

Display the SigningDate field values from the Query – Contract data source as row headings.

Display the InvoicePaid field values from the Query – Invoice data source as column headings.

Sum the InvoiceAmt field values from the Query – Invoice data source.

Use the ContractType field from the Query – Contract data source as a filter.

Group the signing dates to display them as years and months only.

To properly combine data from the Customer, Contract, and Invoice tables, create relationships between tables as follows:

In the Power Pivot for Excel window, create a relationship between the Customer and Contract tables using the CustomerID column to relate the tables.

Create a relationship between the Contract and Invoice tables using the ContractNum column to relate the tables.

Ravi wants to show invoices for security plans only and want to make the PivotTable easier to interpret. Modify the PivotTable as follows to meet Ravi’s requests:

Use Unpaid as the column heading in cell C4.

Use Paid as the column heading in cell D4.

Filter the PivotTable to display invoices for the Security plan and Security plan for apartment building contract types.

Return to the Customers worksheet. In the range J2:K5, Ravi wants to list the total contract amounts for three cities. Extract this information from the PivotTable on the Contracts by City worksheet as follows:

In cell K3, use the GETPIVOTDATA function to display the total contract amount for Atlanta from the PivotTable on the Contracts by City worksheet (cell J9). (Hint: Type an equal sign in cell K3 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.)

In cell K4, use the GETPIVOTDATA function to display the total contract amount for College Park from the PivotTable on the Contracts by City worksheet (cell J16).

In cell K5, use the GETPIVOTDATA function to display the total contract amount for Dunwoody from the PivotTable on the Contracts by City worksheet (cell J20).
Save the workbook, making sure to save it as a macro-enabled workbook.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Home Page Worksheet

Final Figure 2: Invoices by Month Worksheet

Final Figure 3: Contracts by City Worksheet

Final Figure 4: Customers Worksheet

Final Figure 5: Contracts Worksheet

Final Figure 6: Invoices Worksheet

2

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order
Calculate the price
Pages (275 words)
$0.00
Paper Answers
Company
Legal
How Our Service is Used:
Paper Answers essays are NOT intended to be forwarded as finalized work as it is only strictly meant to be used for research and study purposes. Paper Answers does not endorse or condone any type of plagiarism.
Subscribe
No Spam
© 2023 Paper Answers. All rights reserved.
Paper Answers will be listed as ‘Paper Answers’ on your bank statement.

Order your essay today and save 30% with the discount code ESSAYHELP

Order Now