Actuarial Computing Excel Access VBA Repo
1.0.0
Versicherungsmathematische Informatik (MAT 253, ISU)
Dieses Code-Labor konzentriert sich auf die Verwendung von VLOOKUPS zum Ausfüllen der unten aufgeführten Tabellen für die Anzahl der Ansprüche, wobei auf die erste Tabelle auf der Registerkarte „Daten“ verwiesen wird. Dabei werden Werte in Zeilen verwendet, um den dritten Parameter der VLOOKUP-Funktion zu vervollständigen. Dazu gehört auch die korrekte Verwendung der absoluten und relativen Zellreferenzierung, damit die gleiche Funktion über den gesamten GELBEN Bereich kopiert werden kann.
Dieses Code-Labor konzentriert sich auch auf die Verwendung von HLOOKUP zum Ausfüllen von Tabellen mit Daten aus einer zweiten Tabelle der Registerkarte „Daten 1“. Für den 3. Parameter des HLOOKUP verwenden wir die MATCH-Funktion mit dem entsprechenden Übereinstimmungsschlüssel und einem Array-Verweis auf den Vektor mit einer Liste der verfügbaren Jahre
Der Code beinhaltet das Befolgen der folgenden Anweisungen:
In diesem Code-Labor erstellen wir Diagramme, die die tatsächliche Schadenshäufigkeit und den tatsächlichen Schadensschweregrad auf der y-Achse anzeigen.
Da der Maßstab für jede dieser Serien so unterschiedlich ist, verwenden wir zwei unterschiedliche Achsen, um die verschiedenen Serien darzustellen.
Die x-Achse zeigt die Periode # (Spalte A). Jede Reihe wird als Punkte mit Verbindungslinien angezeigt.
Jede Serie ist je nach Häufigkeit oder Schweregrad gekennzeichnet.
Mithilfe von IF-Anweisungen berechnen wir den versicherungsmathematischen Barwert für jede der Personen in der Liste auf der Registerkarte „Problem 1“. - Die APV-Formel = Nennwert * Axt – Die Axt variiert je nach Geschlecht und Raucherstatus und ist für jeden Fall auf den 4 Registerkarten zu finden. Um die Antwort zu überprüfen, sollte das Ergebnis der ersten Richtlinie APV = 1.1238,0 haben. Auf der Registerkarte „Problem 1“ enthält Spalte A eine Textzeichenfolge, die eine Verkettung von 4 verschiedenen Feldern ist: Policy_Num, Effective_Date, Expiration_Date, Premium. Verwenden Sie Komma (,) als Trennzeichen, um sie in 4 Spalten zu unterteilen. Sie können dazu jedes beliebige Tool oder jede Funktion in Excel verwenden.
WIR haben einen PivotTable-Bericht in einem neuen Arbeitsblatt mit dem Namen „Problem 1“ aus den Daten auf der Registerkarte „Sammlung“ (Bereich A1:D2771) erstellt. Tragen Sie die „Anzahl der Einzüge“ in die Zeilenbeschriftungen ein und erstellen Sie 4 Spalten: 1. Summe der Prämien 2. Summe der Verluste 3. Verlustquote = Verlust / Prämie 4. Anzahl der Policen, Anzeige als % der Spalte.
Verwenden Sie auf der Registerkarte „Regression“ die einfache lineare Regressionstechnik (y=a+bx), um das Gewicht einer Person anhand ihrer Größe vorherzusagen. Sie können alle in Excel verfügbaren Methoden verwenden, um die Parameterschätzungen zu erhalten.
Sie sind ein Preisaktuar für die ABC Insurance Company, einen kleinen Kfz-Versicherer für Privatkunden mit Prämieneinnahmen von etwa 300 Millionen US-Dollar pro Jahr. Zu Ihren Aufgaben gehört die Entwicklung regelmäßiger Tarifniveauanzeigen sowie die Anpassung Ihrer Bewertungsfaktoren. Ihr Chef hat Sie gebeten, einen Prozess zusammenzustellen, um den Indikationsprozess für die Entwicklung der indizierten Tarife für 2011 zu optimieren. Dazu hat er die folgenden Anweisungen sowie eine Skizze bereitgestellt, wie die Tabelle seiner Meinung nach aussehen soll.
Er hat Sie außerdem gebeten, ihm eine separate Möglichkeit zur Verfügung zu stellen, um die reinen Premium-Trends in allen Bundesstaaten im Auge zu behalten und mit landesweiten Trends (CW) zu vergleichen. Er möchte einen einfachen Punkt und
Verwenden Sie dazu die Click-Methode. Sie haben daher ein PivotChart für diesen Zweck vorgeschlagen.
Die Entwicklung einer Tarifindikation bei ABC umfasst einige Schritte, darunter: • Trendanalyse • Entwicklung von Verlustprognosefaktoren auf der Grundlage von Trends • Entwicklung der angegebenen Selbstbehalts- und Klassenfaktoren (Alter und Geschlecht) • Entwicklung der Anlagerendite • Entwicklung einer allgemeinen Tarifindikation
Um die Tarifangaben zu entwickeln, wurden Ihnen die folgenden Informationen zur Verfügung gestellt: • Die IT-Abteilung hat detaillierte Prämien- und Verlustinformationen für alle Policen 2007–2009 in einer Textdatei mit fester Breite bereitgestellt. Diese Datei enthält etwa 1 Million Datensätze und muss daher zunächst in Access verarbeitet werden. • Sie verfügen außerdem über eine Kopie der neuesten Fast Track-Branchentrenddaten in einer Access-Datenbank. • Sie verfügen über eine Excel-Tabelle mit den Aktienbeständen und -käufen des Unternehmens sowie den historischen Kursen dieser Aktien in den letzten vier Jahren.
-Ihr Tarifanzeigeprozess umfasst die folgende Ausgabe (weiter unten ausführlicher erläutert): • Eine Access-Datenbank mit Abfragen, die Daten ausgeben, die für jeden Bundesstaat in Excel kopiert werden können. • Eine Excel-Tabelle, die die Berechnung der durchschnittlichen Anlagerendite für 2007-2009 zeigt. • Eine Excel-Tabelle, die die angezeigte Zinsänderung berechnet, nachdem die Ausgabe der Zugriffsabfragen und der Anlageertrag darin eingefügt wurden.
Diese Tabelle sollte es dem Benutzer ermöglichen, die Zugriffsausgabe für einen anderen Staat in Excel einzufügen und den angegebenen Tarif ohne zusätzliche Aktualisierungen automatisch zu generieren. • Eine Excel-Tabelle mit einem PivotChart, das sowohl den CW-Trend als auch den State-Trend anzeigt. Es gibt ein Beispiel dafür, wie die Ausgabe des Arbeitsblatts für Tarifangaben aussehen sollte.
Eine Access-Datenbank wird bereitgestellt. Diese Datenbank enthält bereits eine Tabelle mit dem Namen TrendData, die die Branchentrenddaten enthält. Detaillierte Richtliniendaten wurden Ihnen auch in der Datei „policydata.txt“ zur Verfügung gestellt. Das Layout für die Textdatei ist unten: Pos. Feld 1-2 Schlüssel 3-4 Status 5-8 Selbstbehalt 9-14 Klassencode 15-18 Jahr 19-24 Prämie 25 Indikator, ob die Police Anspruch hatte 26-35 Anspruchsbetrag
**Hinweis zum Schlüsselfeld**
Please use Access to add a primary key. The keys field in the input dataset is truncated. (Thus not unique to each record.) However, it will not impact your calculations.
You should import the text file with the policy data into an Access table.
In Access sollten Sie Abfragen erstellen, die die folgenden Informationen ausgeben:
Prämien-/Verlustinformationen des Unternehmens: STATE (Gruppierung nach) YEAR (Gruppierung nach) DEDUCT (Gruppierung nach) CLASS (Gruppierung nach) Policenanzahl (Anzahl) PREM (Summe) CLAIM_IND (Summe) LOSS_AMOUNT (Summe)
Sie sollten die Abfrage so einstellen, dass sie eine Where-Klausel für den Status enthält. Sie können den Status in den Status ändern, an dem Sie gerade arbeiten. Branchen-Fast-Track-Trendinformationen: STATE (Gruppieren nach) YYYYQ (Gruppieren nach) Cov (Gruppieren nach) CW_CARYEARS (Summe) CW_PDCOUNT (Summe) CW_PDAMT (Summe) STATE_CARYEARS (Summe) STATE_PDCOUNT (Summe) STATE_PDAMT (Summe)
Die CW-Felder sind Zusammenfassungen, die auf allen Daten für alle Bundesstaaten basieren. Die STATE-Zusammenfassungsfelder sind Summen der Felder für den jeweiligen Bundesstaat. Auch hier sollten Sie die Abfrage für die Where-Klausel einrichten, um den Status anzugeben, der ausgegeben werden soll.
Beachten Sie, dass Sie, um sowohl CW-Zusammenfassungen als auch STATE-Zusammenfassungen für dieselbe Abfrage zu erhalten, die Ausgabe zweier separater Abfragen (eine auf Landesebene und eine auf CW-Ebene) zusammenführen und die Ergebnisse nach YYYQ und COV zusammenführen müssen.
Die bereitgestellte Tabelle enthält zwei Tabellen. In einer Tabelle sind die Aktienkurse der Aktien des S&P 500 im Zeitverlauf aufgeführt. Die ABC Company besitzt einen Teil dieser Aktien. Die Investmentabteilung hat eine Zusammenfassung der zu Beginn des Jahres (BOY) 2006 gehaltenen Aktien sowie der am 1.1.2007, 1.1.2008 und 1.1.2009 erworbenen Aktien bereitgestellt. Sie müssen die Anlagerendite für 2007, 2008 und 2009 sowie den arithmetischen Durchschnitt der 3-Jahres-Rendite berechnen. Eine Demonstration der Berechnung ist im Handout enthalten. Sie sollten die Tabelle zur Berechnung des Anlageertrags des Arbeitsblatts ausfüllen. Der Wert, den Sie in diesem Arbeitsblatt berechnen, wird in das Arbeitsblatt „Ratenangaben“ eingegeben.
Die Ausgabe von Access sollte in die Registerkarte „Eingabedaten“ des Arbeitsblatts eingefügt werden. Fühlen Sie sich frei, dieser Registerkarte beliebige Indexspalten hinzuzufügen, die Ihnen später nützlich sein könnten. Sie sollten auch in der Lage sein, den Statusnamen auf dieser Registerkarte einzugeben und den resultierenden Statusnamen in alle Arbeitsblattüberschriften im Arbeitsblatt einfließen zu lassen (wenn Sie also Daten für einen neuen Status einfügen, müssen Sie den Statusnamen nur einmal im ändern). Arbeitsblatt, anstatt jedes Blatt aktualisieren zu müssen). Beachten Sie, dass beim Aktualisieren eines Status keine weiteren Änderungen erforderlich sein sollten. Denken Sie über die Möglichkeit nach, dass Abfragen für verschiedene Zustände eine unterschiedliche Anzahl von Zeilen zurückgeben. Möglicherweise müssen Sie größere Verweise auf die InputData-Tabellen verwenden, als Sie es für die bereits darin enthaltenen Statusdaten tun würden. Im Handout ist ein Beispiel dafür enthalten, wie die Excel-Ausgabe für die anderen Arbeitsblattregisterkarten aussehen sollte. Ich habe einige Tipps zum Ausfüllen der einzelnen Blätter im Handout aufgelistet.
Rufen Sie die Trendinformationen aus der Ausgabe der Fast Track-Abfrage ab. Ihr Unternehmen verwendet für die Trendanalyse ausschließlich Branchendaten und gewichtet die Erfahrungen des Bundesstaats mit den CW-Erfahrungen, um seine Trends zu entwickeln.
Verwenden Sie die Formeln LINEST und INTERCEPT, um die entsprechenden Werte zu berechnen. Geben Sie für Ihre X-Werte gerne den Index (1,2,3,…) in Spalte A ein. Ihre Y-Werte sollten der Spalte „Pure Premium“ entsprechen. Denken Sie daran: Reine Prämie = Schadensbetrag / Autojahre. Verwenden Sie diese Werte, um die angepassten Wertespalten zu berechnen. Die jährliche Änderung beträgt das Vierfache der Steigung (für vier Perioden). Drücken Sie dies als prozentualen Trend aus, indem Sie den Jahresbetrag durch den zuletzt angepassten Wert dividieren
Erstellen Sie ein Diagramm wie im Handout gezeigt mit 4 Serien, Zustand und CW, angepasst und tatsächlich.
Erstellen Sie eine Trendausstellung für alle gezeigten Abdeckungen. Denken Sie daran, dass Sie die erste ausgefüllte Registerkarte kopieren können, indem Sie mit der rechten Maustaste darauf klicken, „Verschieben“ oder „Kopieren“ sagen und dann eine Kopie erstellen. Wenn
Wenn Sie die erste Registerkarte richtig codieren, sollten Sie sie einfach kopieren, die Coverage-Referenz ändern können und keine der verbleibenden Arbeiten wiederholen müssen.
Arbeitsblatt zum Verlustprognosefaktor Die für jede Deckung berechneten Trends sollten in dieses Arbeitsblatt einfließen. Diese Tabelle enthält eine Berechnung der Glaubwürdigkeitsgewichtung. Die Glaubwürdigkeit, die dem gegeben wird
Die Erfahrung eines bestimmten Staates basiert auf der Anzahl der Schadensfälle für diesen Staat im letzten Zeitraum. (Wenn beispielsweise die Anspruchszahl des Staates 2010 im ersten Quartal 2010 für BI 123.245 beträgt; die
Die zugewiesene Glaubwürdigkeitsgewichtung sollte 0,4 betragen.) Diese sollten entweder den Trendarbeitsblättern oder den Rohdaten auf der Registerkarte „Eingabedaten“ entnommen werden.
Die Formel für den gewichteten Trend = Landestrend * Glaubwürdigkeitsgewichtung + CW-Trend * (1-Glaubwürdigkeitsgewichtung).
-Sie sollten auch den Schadensbetrag für den letzten Zeitraum angeben. Dies wird verwendet, um einen gewichteten durchschnittlichen Trend für alle Abdeckungen (Zelle H13) basierend auf zu berechnen
staatliche Deckungsverteilung.
Erhalten Sie Informationen zu Policenanzahl, Prämie und Schaden für alle drei Jahre aus den Unternehmenserfahrungsdaten auf der Registerkarte „Eingabedaten“. Berechnen Sie die Verlustquote, die angezeigte Änderung und die angegebenen Ratenfaktoren. Die angegebene Änderungsberechnung wird in der Tabelle angezeigt. Der Indikatorfaktor = Aktueller Faktor x (1 + angegebene Änderung). Fügen Sie in beiden Arbeitsblättern ein bedingtes Format zur angegebenen Änderungsspalte hinzu, um Zellen hervorzuheben, die einen Anstieg von mehr als 10 % oder einen Rückgang von weniger als -10 % aufweisen.
Ziehen Sie die Prämien- und Verlustinformationen aus den Unternehmenserfahrungsdaten auf der Registerkarte „Eingabedaten“ ab. Ziehen Sie den LPF aus der Registerkarte Verlustprojektionsfaktor. Berechnen Sie die prognostizierten Verluste = tatsächliche Verluste x LPF.
Verwenden Sie die prognostizierte Verlustquote für den Dreijahreszeitraum in der angegebenen Änderungsformel am Ende des Arbeitsblatts. Geben Sie die Anlagerendite manuell aus Ihrem Anlagerendite-Arbeitsblatt ein. Für die anderen Werte in der Formel verwenden Sie die Werte im beigefügten Beispiel.
-Ihr Chef möchte auch eine Möglichkeit haben, Trends im Auge zu behalten, ohne sich die ganze Arbeit leisten zu müssen, die mit der Erstellung eines Arbeitsblatts für Hinweise verbunden ist. Sie haben zugestimmt, ein PivotChart zu erstellen, das reine Premium-Trends zeigt.
-Um die Quelldaten für dieses PivotChart zu generieren, sollten Sie in der Lage sein, dieselbe Abfrage zu verwenden, die Sie zum Generieren der Trenddaten verwendet haben, die Sie in das Arbeitsblatt „Indikationen“ eingefügt haben. Der Hauptunterschied besteht darin, dass Sie den spezifischen Status entfernen sollten, wenn Sie diese Abfrage ausführen. Die Abfrage sollte die Werte für alle Zustände sowie Spalten zurückgeben, die die CW-Werte enthalten. Fügen Sie die Ausgabe der Abfrage in eine neue Excel-Arbeitsmappe ein.
Das PivotChart sollte die Seitenfelder „Abdeckung“ und „Status“ enthalten. Der Zeitraum (YYYQ) sollte unten im Diagramm angezeigt werden. Die Datenelemente im Diagrammbereich sollten die reine Staatsprämie und die reine CW-Prämie umfassen.
