Actuarial Computing Excel Access VBA Repo
1.0.0
Computación actuarial (MAT 253, ISU)
Este laboratorio de código se centra en el uso de BUSCARV para completar las tablas enumeradas a continuación para el número de reclamos, haciendo referencia a la primera tabla en la pestaña de datos. Implica hacer uso de valores en filas para completar el tercer parámetro de la función BUSCARV. También implica el uso correcto de referencias de celdas absolutas y relativas para que se pueda copiar la misma función en toda el área AMARILLA.
Este laboratorio de código también se centra en el uso de BUSCARH para completar tablas usando datos de una segunda tabla de la pestaña Datos 1. Para el tercer parámetro de BUSCARH, utilizamos la función COINCIDIR con la clave de coincidencia adecuada y la referencia de matriz al vector con una lista de años disponibles.
El código implica seguir las instrucciones a continuación:
En este laboratorio de código, creamos gráficos que muestran la frecuencia real de los reclamos y la gravedad real de los reclamos en el eje y.
Debido a que la escala de cada una de estas series es tan diferente, utilizamos dos ejes diferentes para mostrar las diferentes series.
El eje x muestra el período # (columna A). Cada serie se muestra como puntos, con líneas de conexión.
Cada serie está etiquetada según su frecuencia o gravedad, según corresponda.
Usando declaraciones IF, calculamos el valor actual actuarial para cada una de las personas en la lista en la pestaña "problema 1". - La fórmula APV = Valor nominal * Ax - El Ax varía según el sexo y la condición de fumador y se puede encontrar en las 4 pestañas de cada caso. Para verificar la respuesta, el resultado de la primera póliza debe tener APV = 1,1238.0 En la pestaña “Problema 1”, la columna A contiene una cadena de texto que es una concatenación de 4 campos diferentes: Policy_Num, Effective_Date, Expiration_Date, Premium. Utilice una coma (,) como delimitador para separarlos en 4 columnas. Puede utilizar cualquier herramienta o función dentro de Excel para hacerlo.
Configuramos un informe de tabla dinámica en una nueva hoja de trabajo llamada "Problema 1" a partir de los datos de la pestaña 'Colección' (rango A1:D2771). Coloque el 'Número de cobro' en las etiquetas de las filas y cree 4 columnas: 1. Suma de la prima 2. Suma de la pérdida 3. Índice de pérdida = Pérdida/Prima 4. Recuento de pólizas, se muestra como % de la columna.
En la pestaña "Regresión", utilice la técnica de regresión lineal simple (y=a+bx) para predecir el peso de una persona utilizando su altura. Puede utilizar cualquier método que esté disponible en Excel para obtener las estimaciones de los parámetros.
Usted es actuario de precios para ABC Insurance Company, una pequeña aseguradora de automóviles de líneas personales con ingresos por primas de aproximadamente $300 millones al año. Una de sus responsabilidades laborales es desarrollar indicaciones periódicas del nivel de tarifas, así como ajustes a sus factores de calificación. Su jefe le pidió que elaborara un proceso para agilizar el proceso de indicaciones para desarrollar las tarifas indicadas para 2011. Para hacerlo, le proporcionó las siguientes instrucciones, así como una descripción general de cómo le gustaría que se viera la hoja de cálculo.
También le ha pedido que le proporcione una forma separada de estar atento a las tendencias de primas puras en todos los estados y compararlas con las tendencias a nivel nacional (CW). Le gustaría un punto simple y
Haga clic en el método para hacer esto, por lo que sugirió un gráfico dinámico para este propósito.
Desarrollar una indicación de tasa en ABC implica algunos pasos que incluyen: • Análisis de tendencias • Desarrollo de factores de proyección de pérdidas basados en tendencias • Desarrollar factores indicados de deducible y clase (edad y sexo) • Desarrollo del rendimiento de la inversión • Desarrollo de una indicación de tasa general
Para desarrollar las indicaciones de tarifas, se le ha proporcionado la siguiente información: • El departamento de TI ha proporcionado información detallada sobre primas y pérdidas para todas las pólizas 2007-2009 en un archivo de texto de ancho fijo. Este archivo tiene aproximadamente 1 millón de registros, por lo que primero debe procesarse en Access. • También tiene una copia de los últimos datos de tendencias de la industria Fast Track en una base de datos de Access. • Tiene una hoja de cálculo de Excel con las tenencias y compras de acciones de la empresa, así como los precios históricos de esas acciones durante los últimos 4 años.
-Su proceso de indicaciones de tarifas incluirá el siguiente resultado (que se explica con más detalle a continuación): • Una base de datos de Access que tiene consultas que generan datos que se pueden copiar en Excel para cada estado. • Una hoja de cálculo Excel que muestra el cálculo del rendimiento promedio de las inversiones para el período 2007-2009. • Una hoja de cálculo Excel que calcula el cambio de tarifa indicado, luego de pegar en ella el resultado de las consultas de acceso y el rendimiento de la inversión.
Esta hoja de cálculo debería permitir al usuario pegar el resultado de acceso para otro estado en Excel y generar automáticamente la tarifa indicada sin actualizaciones adicionales. • Una hoja de cálculo de Excel con un gráfico dinámico que muestra tanto la tendencia CW como la tendencia estatal. Hay un ejemplo de cómo debería verse el resultado de la hoja de cálculo de indicaciones de tasas.
Se proporciona una base de datos de Access. Esa base de datos ya contiene una tabla llamada TrendData, que tiene los datos de tendencias de la industria. También se le han proporcionado datos detallados de la política en Policydata.txt. El diseño del archivo de texto es el siguiente: Pos Campo 1-2 Teclas 3-4 Estado 5-8 Deducible 9-14 Código de clase 15-18 Año 19-24 Prima 25 Indicador de si la póliza tenía reclamo 26-35 Monto del reclamo
** Nota sobre el campo Claves **
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.
En Access, debe crear consultas que generen la siguiente información:
Información sobre primas/pérdidas de la empresa: ESTADO (Agrupar por) AÑO (Agrupar por) DEDUCIR (Agrupar por) CLASE (Agrupar por) Recuento de pólizas (Recuento) PREM (Suma) CLAIM_IND (Suma) LOSS_AMOUNT (Suma)
Debe configurar la consulta para que tenga una cláusula Where para el estado. Puede cambiar el estado a cualquier estado en el que esté trabajando. Información de tendencias de seguimiento rápido de la industria: STATE (Agrupar por) YYYYQ (Agrupar por) Cov (Agrupar por) CW_CARYEARS (Suma) CW_PDCOUNT (Suma) CW_PDAMT (Suma) STATE_CARYEARS (Suma) STATE_PDCOUNT (Suma) STATE_PDAMT (Suma)
Los campos CW son resúmenes basados en todos los datos de todos los estados. Los campos de resumen de ESTADO son sumas de los campos para el estado particular. Nuevamente, debe configurar la consulta para la cláusula Where para especificar el estado que se generará.
Tenga en cuenta que para obtener resúmenes de CW y resúmenes de ESTADO en la misma consulta, deberá combinar el resultado de dos consultas separadas (una a nivel estatal y otra a nivel de CW) y combinar los resultados por YYYQ y COV.
La hoja de cálculo proporcionada tiene dos tablas. Una tabla tiene los precios de las acciones a lo largo del tiempo para las acciones del S&P 500. ABC Company posee un subconjunto de esas acciones. El departamento de inversiones ha proporcionado un resumen de las acciones mantenidas a principios del año (BOY) 2006, así como de las acciones compradas el 1/1/2007, 1/1/2008 y 1/1/2009. Debe calcular el rendimiento de la inversión para 2007, 2008 y 2009, y el promedio aritmético del rendimiento a 3 años. En el folleto se incluye una demostración del cálculo. Debe completar la hoja de cálculo en el Cálculo del rendimiento de la inversión de la hoja de trabajo. El valor que calcule en esta hoja de trabajo se ingresará en la hoja de trabajo de Indicaciones de tarifas.
El resultado de Access debe pegarse en la pestaña Datos de entrada de la hoja de trabajo. No dude en agregar cualquier columna de índice a esta pestaña que pueda resultarle útil más adelante. También debería poder ingresar el nombre del estado en esa pestaña y hacer que el nombre del estado resultante fluya a todos los encabezados de la hoja de trabajo (de modo que si pega datos para un nuevo estado, solo tendrá que cambiar el nombre del estado una vez en la pestaña). hoja de trabajo, en lugar de tener que actualizar cada hoja). Tenga en cuenta que no deberían ser necesarios otros cambios al actualizar un estado. Piense en la posibilidad de que consultas para diferentes estados devuelvan un número diferente de filas. Es posible que necesite utilizar referencias más grandes a las tablas InputData que las que usaría para los datos de estado que ya están ahí. En el folleto se incluye un ejemplo de cómo debería verse la salida de Excel para las otras pestañas de la hoja de trabajo. He enumerado algunos consejos para completar cada hoja en el folleto.
Obtenga la información de tendencias del resultado de la consulta Fast Track. Su empresa utiliza únicamente datos de la industria para el análisis de tendencias y pondera la experiencia del estado con la experiencia de CW para desarrollar sus tendencias.
Utilice las fórmulas ESTIMACIÓN LINEAL e INTERCEPCIÓN para calcular los valores apropiados. Siéntase libre de poner el índice (1,2,3,…) en la columna A para sus valores X. Sus valores Y deben ser la columna Pure Premium. Recuerde, prima pura = monto de la pérdida / años del automóvil. Utilice estos valores para calcular las columnas de valores ajustados. El cambio anual es 4 x la pendiente (durante cuatro períodos). Exprese esto como una tendencia porcentual dividiendo la cantidad anual por el valor ajustado más reciente.
Cree un gráfico como se muestra en el folleto con 4 series, Estado y CW, ajustadas y reales.
Cree una exhibición de tendencias para todas las coberturas mostradas. Tenga en cuenta que puede copiar la primera pestaña que complete haciendo clic derecho sobre ella, diga mover o copiar y luego haga una copia. Si
Si codifica la primera pestaña correctamente, debería poder copiarla, cambiar la referencia de cobertura y no tendrá que repetir el trabajo restante.
Hoja de trabajo del factor de proyección de pérdidas Las tendencias calculadas para cada cobertura deben trasladarse a esta hoja de trabajo. Hay un cálculo de ponderación de credibilidad en esta hoja de cálculo. La credibilidad otorgada a la
La experiencia de un estado determinado se basa en el número de reclamaciones para ese estado en el período más reciente. (Por ejemplo, si el recuento de reclamaciones del estado para BI en el primer trimestre de 2010 es 123,245; el
El peso de credibilidad asignado debe ser 0,4). Estos deben extraerse de las hojas de cálculo de tendencias o de los datos sin procesar en la pestaña de datos de entrada.
La fórmula para la tendencia ponderada = Tendencia estatal * Ponderación de credibilidad + Tendencia CW * (1-Ponderación de credibilidad).
-También debe incluir el monto de la pérdida del período más reciente. Esto se utiliza para calcular una tendencia promedio ponderada para todas las coberturas (celda H13), basada en la
Distribución de cobertura estatal.
Obtenga el recuento de pólizas, la prima y la información de pérdidas de los tres años a partir de los datos de experiencia de la empresa en la pestaña de datos de entrada. Calcule el índice de siniestralidad, el cambio indicado y los factores de tasa indicados. El cálculo del cambio indicado se muestra en la hoja de cálculo. El factor indicador = Factor actual x (1 + Cambio indicado). En ambas hojas de trabajo, agregue un formato condicional a la columna de cambio indicada para resaltar las celdas que tienen un aumento mayor al 10 % o una disminución menor al -10 %.
Extraiga la información de primas y pérdidas de los datos de experiencia de la empresa en la pestaña de datos de entrada. Extraiga el LPF de la pestaña Factor de proyección de pérdida. Calcule las pérdidas proyectadas = Pérdidas reales x LPF.
Utilice el índice de siniestralidad proyectado para el período de 3 años en la fórmula de cambio indicada en la parte inferior de la hoja de trabajo. Ingrese manualmente el rendimiento de la inversión desde su hoja de trabajo de rendimiento de la inversión. Para los demás valores de la fórmula, utilice los valores del ejemplo adjunto.
-A su jefe también le gustaría tener una forma de realizar un seguimiento de las tendencias, sin tener que hacer todo el trabajo que implica configurar una hoja de trabajo de indicaciones. Ha aceptado crear un gráfico dinámico que muestre tendencias premium puras.
-Para generar los datos de origen para este gráfico dinámico, debería poder utilizar la misma consulta que utilizó para generar los datos de tendencias que pegó en la hoja de cálculo de Indicaciones. La principal diferencia es que debes eliminar el estado específico al ejecutar esa consulta. La consulta debe devolver los valores de todos los estados, así como las columnas que contienen los valores de CW. Pegue el resultado de la consulta en un nuevo libro de Excel.
El gráfico dinámico debe tener campos de página de cobertura y estado. El período de tiempo (YYYQ) debe mostrarse en la parte inferior del gráfico. Los elementos de datos en el área del gráfico deben incluir la prima pura estatal y la prima pura de CW.
