Actuarial Computing Excel Access VBA Repo
1.0.0
Calcul actuariel (MAT 253, ISU)
Cet atelier de code se concentre sur l'utilisation de RECHERCHEV pour remplir les tableaux répertoriés sous le nombre de revendications, en faisant référence au premier tableau de l'onglet Données. Il s'agit d'utiliser des valeurs en lignes afin de compléter le 3ème paramètre de la fonction RECHERCHEV. Cela implique également l'utilisation correcte du référencement absolu et relatif des cellules afin de pouvoir copier la même fonction dans toute la zone JAUNE.
Cet atelier de code se concentre également sur l'utilisation de HLOOKUP pour remplir des tableaux à l'aide des données d'un deuxième tableau de l'onglet Données 1. Pour le 3ème paramètre de HLOOKUP, nous utilisons la fonction MATCH avec la clé de correspondance appropriée et la référence de tableau au vecteur avec une liste des années disponibles
Le code implique de suivre les instructions ci-dessous :
Dans cet atelier de code, nous créons des graphiques qui montrent la fréquence réelle des sinistres et la gravité réelle des sinistres sur l'axe Y.
L’échelle de chacune de ces séries étant si différente, nous utilisons deux axes différents pour montrer les différentes séries.
L'axe des x indique la période # (colonne A). Chaque série s'affiche sous forme de points, avec des lignes de connexion.
Chaque série est classée en fréquence ou en gravité, selon le cas.
À l'aide des déclarations IF, nous calculons la valeur actuelle actuarielle pour chacune des personnes de la liste dans l'onglet « problème 1 ». - La formule APV = Valeur faciale * Hache - La Hache varie selon le sexe et le statut de fumeur et se retrouve sur les 4 onglets de chaque cas. Afin de vérifier la réponse, le résultat de la première politique doit avoir APV = 1,1238,0. Dans l'onglet « Problème 1 », la colonne A contient une chaîne de texte qui est une concaténation de 4 champs différents : Policy_Num, Effective_Date, Expiration_Date, Premium. Utilisez la virgule (,) comme délimiteur pour les séparer en 4 colonnes. Vous pouvez utiliser n’importe quel outil ou fonction d’Excel pour le faire.
NOUS avons configuré un rapport de tableau croisé dynamique dans une nouvelle feuille de calcul appelée « Problème 1 » à partir des données de l'onglet « Collection » (plage A1 : D2771). Mettez le « Nombre de recouvrement » dans les étiquettes de ligne et créez 4 colonnes : 1. Somme de la prime 2. Somme des pertes 3. Taux de perte = Perte / Prime 4. Nombre de polices, affiché en % de la colonne.
Dans l'onglet « Régression », utilisez la technique de régression linéaire simple (y=a+bx) pour prédire le poids d'une personne en utilisant sa taille. Vous pouvez utiliser toutes les méthodes disponibles dans Excel pour obtenir les estimations des paramètres.
Vous êtes actuaire en matière de tarification pour ABC Insurance Company, un petit assureur automobile des particuliers dont les primes s'élèvent à environ 300 millions de dollars par an. L'une de vos responsabilités professionnelles consiste à élaborer des indications périodiques sur les niveaux de taux, ainsi que des ajustements à vos facteurs de tarification. Votre patron vous a demandé d'élaborer un processus visant à rationaliser le processus d'indication pour l'élaboration des taux indiqués pour 2011. Pour ce faire, il a fourni les instructions suivantes ainsi qu'un aperçu de ce à quoi il aimerait que la feuille de calcul ressemble.
Il vous a également demandé de lui fournir un moyen distinct pour garder un œil sur les tendances pures des primes dans tous les États et les comparer aux tendances à l'échelle nationale (CW). Il aimerait un point simple et
cliquez sur la méthode pour ce faire, vous avez donc suggéré un graphique croisé dynamique à cet effet.
L'élaboration d'une indication de taux chez ABC implique quelques étapes, notamment : • Analyse des tendances • Développement de facteurs de projection des pertes basés sur les tendances • Développement des facteurs de franchise et de classe (âge et sexe) indiqués • Développement du rendement des investissements • Développement d'une indication de taux globale
Pour élaborer les indications tarifaires, vous avez reçu les informations suivantes : • Le service informatique a fourni des informations détaillées sur les primes et les sinistres pour toutes les polices 2007-2009 dans un fichier texte de largeur fixe. Ce fichier contient environ 1 million d'enregistrements, il doit donc d'abord être traité dans Access. • Vous disposez également d'une copie des dernières données sur les tendances de l'industrie Fast Track dans une base de données Access. • Vous disposez d'une feuille de calcul Excel avec les actions et les achats de l'entreprise, ainsi que les prix historiques de ces actions au cours des 4 dernières années.
-Votre processus d'indications de taux comprendra le résultat suivant (expliqué plus en détail ci-dessous) : • Une base de données Access contenant des requêtes qui génèrent des données pouvant être copiées dans Excel pour chaque état. • Une feuille de calcul Excel qui montre le calcul du rendement moyen des investissements pour 2007-2009. • Une feuille de calcul Excel qui calcule le changement de taux indiqué, après y avoir collé le résultat des requêtes d'accès et le rendement de l'investissement.
Cette feuille de calcul devrait permettre à l'utilisateur de coller le résultat d'accès pour un autre état dans Excel et de générer automatiquement le taux indiqué sans aucune mise à jour supplémentaire. • Une feuille de calcul Excel avec un graphique croisé dynamique qui affiche à la fois la tendance CW et la tendance State. Il existe un exemple de ce à quoi devrait ressembler le résultat de la feuille de calcul des indications de taux.
Une base de données Access est fournie. Cette base de données contient déjà une table nommée TrendData, qui contient les données sur les tendances du secteur. Des données détaillées sur les politiques vous ont également été fournies sur Policydata.txt. La présentation du fichier texte est la suivante : Champ Pos 1-2 Clés 3-4 État 5-8 Franchise 9-14 Code de classe 15-18 Année 19-24 Prime 25 Indicateur si la police a fait l'objet d'une réclamation 26-35 Montant de la réclamation
** Remarque sur le champ Clés **
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.
Dans Access, vous devez créer des requêtes qui génèrent les informations suivantes :
Informations sur les primes/pertes de l'entreprise : ÉTAT (Grouper par) ANNÉE (Grouper par) DEDUCT (Grouper par) CLASS (Grouper par) Nombre de polices (Compte) PREM (Somme) CLAIM_IND (Somme) LOSS_AMOUNT (Somme)
Vous devez définir la requête pour qu'elle ait une clause Where pour l'état. Vous pouvez changer l'état selon l'état sur lequel vous travaillez. Informations sur les tendances accélérées du secteur : STATE (Regrouper par) YYYYQ (Regrouper par) Cov (Regrouper par) CW_CARYEARS (Somme) CW_PDCOUNT (Somme) CW_PDAMT (Somme) STATE_CARYEARS (Somme) STATE_PDCOUNT (Somme) STATE_PDAMT (Somme)
Les champs CW sont des résumés basés sur toutes les données de tous les États. Les champs récapitulatifs STATE sont des sommes de champs pour un état particulier. Encore une fois, vous devez configurer la requête pour la clause Where afin de spécifier l'état qui doit être généré.
Notez que pour obtenir à la fois les résumés CW et les résumés STATE sur la même requête, vous devrez fusionner la sortie de deux requêtes distinctes (une au niveau de l'État et une au niveau CW) et fusionner les résultats par YYYQ et COV.
La feuille de calcul fournie comporte deux tableaux. Un tableau présente les cours des actions au fil du temps pour les actions du S&P 500. La société ABC possède un sous-ensemble de ces actions. Le service d'investissement a fourni une synthèse des actions détenues au début de l'année (BOY) 2006, ainsi que des actions achetées au 1/1/2007, 1/1/2008 et 1/1/2009. Vous devez calculer le rendement des investissements pour 2007, 2008 et 2009, ainsi que la moyenne arithmétique du rendement sur 3 ans. Une démonstration du calcul est incluse dans le document. Vous devez remplir la feuille de calcul sur le calcul du rendement des investissements de la feuille de calcul. La valeur que vous calculez dans cette feuille de calcul sera saisie dans la feuille de calcul Indications de taux.
La sortie d’Access doit être collée dans l’onglet Données d’entrée de la feuille de calcul. N'hésitez pas à ajouter à cet onglet des colonnes d'index qui pourraient vous être utiles ultérieurement. Vous devriez également pouvoir saisir le nom de l'état sur cet onglet et faire en sorte que le nom de l'état résultant soit transmis à tous les en-têtes de la feuille de calcul (donc si vous collez des données pour un nouvel état, vous n'avez qu'à modifier le nom de l'état une seule fois dans le champ). feuille de calcul, plutôt que d'avoir à mettre à jour chaque feuille). Gardez à l’esprit qu’aucune autre modification ne devrait être nécessaire lors de la mise à jour d’un état. Pensez à la possibilité que des requêtes pour différents états renvoient un nombre différent de lignes. Vous devrez peut-être utiliser des références plus grandes aux tables InputData que vous ne le feriez pour les données d'état qui s'y trouvent déjà. Le document contient un exemple de ce à quoi devrait ressembler la sortie Excel pour les autres onglets de la feuille de calcul. J'ai répertorié quelques conseils pour remplir chaque feuille du document.
Obtenez les informations de tendance à partir de la sortie de la requête Fast Track. Votre entreprise utilise uniquement les données du secteur pour l'analyse des tendances et pondère l'expérience de l'État avec l'expérience CW pour développer ses tendances.
Utilisez les formules LINEST et INTERCEPT pour calculer les valeurs appropriées. N'hésitez pas à mettre l'index (1,2,3,…) dans la colonne A pour vos valeurs X. Vos valeurs Y doivent être la colonne Pure Premium. N'oubliez pas que Pure Premium = Montant de la perte / Années de voiture. Utilisez ces valeurs pour calculer les colonnes de valeurs ajustées. La variation annuelle est de 4 x la pente (pour quatre périodes). Exprimez cela sous forme de tendance en % en divisant le montant annuel par la valeur ajustée la plus récente.
Créez un graphique comme indiqué dans le document avec 4 séries, État et CW, ajustées et réelles.
Créez une exposition de tendances pour toutes les couvertures affichées. Gardez à l'esprit que vous pouvez copier le premier onglet que vous complétez en cliquant dessus avec le bouton droit, en disant déplacer ou copier, puis en faire une copie. Si
vous codez correctement le premier onglet, vous devriez simplement pouvoir le copier, modifier la référence de couverture et vous n'aurez pas à répéter le travail restant.
Feuille de calcul du facteur de projection des sinistres Les tendances calculées pour chaque couverture doivent figurer dans cette feuille de calcul. Il y a un calcul de pondération de crédibilité sur cette feuille de calcul. La crédibilité accordée au
L’expérience d’un État donné est basée sur le nombre de réclamations concernant cet État au cours de la période la plus récente. (Par exemple, si le nombre de réclamations pour BI au premier trimestre 2010 de l'État est de 123 245 ; le
le poids de crédibilité attribué doit être de 0,4.) Ceux-ci doivent être extraits soit des feuilles de calcul des tendances, soit des données brutes de l'onglet des données d'entrée.
La formule pour la tendance pondérée = State Trend * Pondération de crédibilité + Tendance CW * (1-pondération de crédibilité).
-Vous devez également inclure le montant de la perte pour la période la plus récente. Ceci est utilisé pour calculer une tendance moyenne pondérée pour toutes les couvertures (cellule H13), basée sur la
répartition de la couverture de l'État.
Obtenez les informations sur le nombre de polices, les primes et les pertes pour les trois années à partir des données d'expérience de l'entreprise dans l'onglet Données d'entrée. Calculez le taux de sinistres, la variation indiquée et les facteurs de taux indiqués. Le calcul du changement indiqué est affiché sur la feuille de calcul. Le facteur indicateur = Facteur actuel x (1 + changement indiqué). Dans les deux feuilles de calcul, ajoutez un format conditionnel à la colonne de modification indiquée pour mettre en évidence les cellules qui présentent une augmentation supérieure à 10 % ou une diminution inférieure à -10 %.
Extrayez les informations sur les primes et les pertes à partir des données d'expérience de l'entreprise dans l'onglet Données d'entrée. Extrayez le LPF de l’onglet Loss Projection Factor. Calculez les pertes projetées = Pertes réelles x LPF.
Utilisez le taux de sinistres projeté pour la période de 3 ans dans la formule de changement indiquée au bas de la feuille de calcul. Saisissez manuellement le rendement de l'investissement à partir de votre feuille de calcul de rendement de l'investissement. Pour les autres valeurs de la formule, utilisez les valeurs de l'exemple ci-joint.
-Votre patron aimerait également avoir un moyen de suivre les tendances, sans avoir à faire tout le travail nécessaire à la mise en place d'une feuille de travail d'indications. Vous avez accepté de créer un graphique croisé dynamique qui montre les tendances pures des primes.
-Pour générer les données sources de ce graphique croisé dynamique, vous devez pouvoir utiliser la même requête que celle utilisée pour générer les données de tendance que vous avez collées dans la feuille de calcul Indications. La principale différence est que vous devez supprimer l'état spécifique lors de l'exécution de cette requête. La requête doit renvoyer les valeurs de tous les états, ainsi que les colonnes contenant les valeurs CW. Collez le résultat de la requête dans un nouveau classeur Excel.
Le graphique croisé dynamique doit avoir les champs de page Couverture et État. La période de temps (AAAA) doit être affichée en bas du graphique. Les éléments de données dans la zone du graphique doivent inclure la prime pure de l’État et la prime pure CW.
