help with capstone project

Attachments

Documentation

Illustrated Excel 2019 | Modules 5-8: SAM Capstone Project 1a
CTC Casualty Insurance
MANAGE FORMULAS, DATA, AND TABLES
Author: Tyler Redd
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.

Auto Insurance

CTC Casualty Insuarcne
Coverage ID Column1 Coverage Limit Type Underwriter
CL220 Collision $30,000 Standard United
CO110 Comprehensive $35,000 Standard North American
LI200 Injury liability $100,000 Standard VUA Group
LI205 Property liability $35,000 Standard VUA Group
UN104 Uninsured motorist $100,000 Standard North American
UN107 Underinsured motorist $100,000 Standard United
CA200 Classic car $50,000 Add-on North American
ME300 Medical payments $5,000 Add-on North American
ME310 Personal injury $5,000 Add-on VUA Group
RE105 Rental reimbursement $500 Add-on United
Deductible Monthly payment Premiums Premiums
$ 100 $ 250
$ 250 $ 182
$ 500 $ 129
$ 1,000 $ 89
$ 2,000 $ 84

Gwen Rayburn

Premiums

CTC Casualty Insuarcne
State Age State Minimum Liability Only Full Coverage Age
IL 20 $1,102 $1,261 $3,214 <35
IL 25 $608 $691 $1,745
IL 30 $575 $673 $1,596 State Age State Minimum Liability Only Full Coverage
IL 35 $552 $627 $1,564 IL 20 $1,102 $1,261 $3,214
IL 45 $505 $617 $1,553 IL 25 $608 $691 $1,745
IL 55 $494 $560 $1,363 IL 30 $575 $673 $1,596
IL 65 $515 $585 $1,402 MI 20 $1,176 $1,280 $3,301
IL 75 $630 $718 $1,651 MI 25 $625 $702 $1,788
MI 20 $1,176 $1,280 $3,301 MI 30 $585 $683 $1,605
MI 25 $625 $702 $1,788 MN 20 $1,076 $1,160 $3,278
MI 30 $585 $683 $1,605 MN 25 $609 $698 $1,705
MI 35 $522 $620 $1,557 MN 30 $568 $635 $1,521
MI 45 $502 $588 $1,450 WI 20 $1,100 $1,158 $3,305
MI 55 $490 $560 $1,360 WI 25 $615 $704 $1,750
MI 65 $525 $580 $1,475 WI 30 $580 $620 $1,500
MI 75 $632 $720 $1,684
MN 20 $1,076 $1,160 $3,278
MN 25 $609 $698 $1,705
MN 30 $568 $635 $1,521
MN 35 $518 $618 $1,500
MN 45 $487 $569 $1,407
MN 55 $480 $549 $1,344
MN 65 $530 $560 $1,299
MN 75 $637 $728 $1,690
WI 20 $1,100 $1,158 $3,305
WI 25 $615 $704 $1,750
WI 30 $580 $620 $1,500
WI 35 $520 $605 $1,478
WI 45 $457 $550 $1,402
WI 55 $450 $549 $1,384
WI 65 $535 $560 $1,307
WI 75 $640 $725 $1,710

Premiums Pivot

Row Labels Average of State Minimum Sum of Full Coverage Average of Full Coverage2
IL $623 14088 $1,761
WI $612 13836 $1,730
Grand Total $617 27924 $1,745

Average Premiums by State

Average of State Minimum IL WI 622.625 612.125 Sum of Full Coverage IL WI 14088 13836 Average of Full Coverage2 IL WI 1761 1729.5

Clients

CTC Casualty Insuarcne Premiums
Client ID Client Start Date Policy Number Policy Type Bundled? Per Month Payment Client ID VA2118
CU2107 Addison Cushman 6/7/21 14-586 Auto No $98 Client name ERROR:#REF!
RE2109 Amy Remont 9/3/21 18-672 Auto Yes $52 Policy Type
PA1733 Asmir Papke 8/16/21 20-560 Classic car No $49
BR2215 Brent Bridgewell 2/5/21 19-456 Auto No $95 Payments
RE1165 Christine Reale 12/9/21 14-098 Motorcycle No $101 Policies
SP1378 Clare Ann Spahn 5/15/21 14-238 Auto No $22
VO2020 Cliff Voltz 4/2/21 16-214 Auto Yes $53 Auto
WI1992 Danielle Winkley 5/8/21 14-587 RV/Trailer Yes $81 Classic car
YO1522 Dennis Yoder 9/5/21 18-673 Auto No $53 Motorcycle
MA2177 Duane Maki 11/6/21 20-561 Auto Yes $96 RV/Trailer
CH1643 Emily Charlson 6/12/21 19-457 Auto No $87
GU2732 Erika Gutierrez 10/18/21 14-378 Motorcycle No $72
ST1488 Erin Steffin 4/7/21 14-518 Auto No $49
MO1596 Felipe Morales 1/30/21 14-587 Auto No $50
CO2341 Greta Conner 3/31/21 18-673 Auto Yes $94
TA1334 Ingrid Talcott 8/19/21 20-561 Auto No $79
GU1082 Janice Guitarro 11/9/21 19-457 Auto Yes $51
CH1525 Jing Chee 10/19/21 14-378 Motorcycle No $73
SH2109 Joe Sheldon 7/28/21 14-518 Classic car No $84
AT2317 John Athas 1/16/21 16-215 Auto Yes $96
BR1966 Justin Bruns 5/20/21 14-588 Auto No $98
WU2304 Kendall Wu 11/30/21 18-674 RV/Trailer No $99
JA1983 Kristin Jackson 7/15/21 20-562 Auto No $44
KO1743 Kyle Kohlenberg 12/20/21 19-458 Auto Yes $41
HU2087 Lamar Hubbard 9/3/21 14-658 Auto Yes $52
CO2037 Lance Collins 10/3/21 14-587 Motorcycle No $72
KA1969 Lucinda Kalar 3/22/21 18-673 Auto Yes $41
HE2081 Luis Henriques 4/20/21 20-561 Auto Yes $42
MA1696 Maria Marquez 1/25/21 19-457 Auto Yes $43
RA1380 Mark Rabin 2/6/21 14-378 Auto No $47
LI1482 Michael Lightner 8/30/21 14-518 Motorcycle Yes $69
TH2331 Michelle Thomson 11/4/21 16-215 Auto Yes $44
TH1927 Miriam Thalman 9/3/21 14-588 Auto No $52
KA1767 Noriko Kana 10/3/21 18-674 Auto No $51
VA2118 Peter Vang 3/22/21 14-587 Auto No $44
CH2201 Ramiro Chavez 4/20/21 18-673 Classic car Yes $75
JO2337 Remi Johnson 1/25/21 20-561 Auto No $52
WA1336 Rodney Walsh 2/6/21 19-457 Auto Yes $53
IV1868 Shaniqua Ivers 8/30/21 14-378 Auto Yes $98
YO1752 Shin Yong 9/3/21 14-518 Auto Yes $56
BU2245 Tamara Burroughs 10/3/21 16-215 Auto No $47
GR2376 Todd Greaney 3/22/21 14-588 Motorcycle Yes $80
WA1873 Tom Wang 4/20/21 18-674 Classic car No $74
EN1875 Troy Enderes 1/25/21 20-562 Auto Yes $55
KH2022 Veronica Khan 2/6/21 19-458 Auto No $53
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *