Actuarial Computing Excel Access VBA Repo
1.0.0
Computação Atuarial (MAT 253, ISU)
Este codelab se concentra no uso de VLOOKUPS para preencher as tabelas listadas abaixo para o número de declarações, referenciando a primeira tabela na guia de dados. Envolve a utilização de valores em linhas para completar o 3º parâmetro da função VLOOKUP. Também envolve o uso correto de referência absoluta e relativa de células para que possa ser copiada a mesma função em toda a área AMARELA.
Este codelab também se concentra no uso de HLOOKUP para preencher tabelas usando dados de uma segunda tabela da guia Dados 1. Para o terceiro parâmetro do HLOOKUP, utilizamos a função MATCH com a chave de correspondência apropriada e referência de array ao vetor com uma lista de anos disponíveis
O código envolve seguir as instruções abaixo:
Neste codelab, criamos gráficos que mostram a frequência real da reclamação e a gravidade real da reclamação no eixo y.
Como a escala de cada uma destas séries é muito diferente, utilizamos dois eixos diferentes para mostrar as diferentes séries.
O eixo x mostra o período # (cloumn A). Cada série é exibida como pontos, com linhas de conexão.
Cada série é rotulada como frequência ou gravidade, conforme apropriado.
Usando declarações IF, calculamos o valor presente atuarial para cada uma das pessoas da lista na guia “problema 1”. - A fórmula APV = Valor Nominal * Ax - O Ax varia de acordo com o sexo e o status de fumante e pode ser encontrado nas 4 abas de cada caso. Para verificar a resposta, o resultado da primeira política deve ter APV = 1.1238,0 Na aba “Problema 1”, a coluna A contém uma string de texto que é uma concatenação de 4 campos diferentes: Policy_Num, Effective_Date, Expiration_Date, Premium. Use vírgula (,) como delimitador para separá-los em 4 colunas. Você pode usar qualquer ferramenta ou função do Excel para fazer isso.
Configuramos um relatório de tabela dinâmica em uma nova planilha chamada “Problema 1” a partir dos dados da guia 'Coleção' (intervalo A1:D2771). Coloque o 'Número de Cobrança' nos rótulos das linhas e crie 4 colunas: 1. Soma do Prêmio 2. Soma da Perda 3. Taxa de Perda = Perda / Prêmio 4. Contagem de apólices, exibida como% da coluna.
Na aba “Regressão”, use a técnica de regressão linear simples (y=a+bx) para prever o peso de uma pessoa usando sua altura. Você pode usar qualquer método disponível no Excel para obter as estimativas dos parâmetros.
Você é atuário de preços da ABC Insurance Company, uma pequena seguradora de automóveis de linhas pessoais com receita de prêmios de aproximadamente US$ 300 milhões anualmente. Uma de suas responsabilidades de trabalho é desenvolver indicações periódicas de nível de taxas, bem como ajustes em seus fatores de classificação. Seu chefe pediu que você montasse um processo para agilizar o processo de indicações para o desenvolvimento das taxas indicadas para 2011. Para isso, ele forneceu as seguintes instruções, bem como um esboço de como gostaria que a planilha fosse.
Ele também pediu que você fornecesse uma maneira separada para ele ficar de olho nas tendências premium puras em todos os estados e comparar com as tendências nacionais (CW). Ele gostaria de um ponto simples e
click para fazer isso, então você sugeriu um gráfico dinâmico para essa finalidade.
O desenvolvimento de uma indicação de taxa no ABC envolve algumas etapas, incluindo: • Análise de tendências • Desenvolvimento de fatores de projeção de perda com base em tendências • Desenvolvimento de fatores dedutíveis e de classe (idade e sexo) indicados • Desenvolvimento do rendimento do investimento • Desenvolvimento de indicação de taxa geral
Para desenvolver as indicações de taxas, você recebeu as seguintes informações: • O departamento de TI forneceu informações detalhadas sobre prêmios e perdas para todas as apólices de 2007 a 2009 em um arquivo de texto de largura fixa. Este arquivo possui cerca de 1 milhão de registros, portanto deve ser processado primeiro no Access. • Você também tem uma cópia dos dados de tendências mais recentes do setor Fast Track em um banco de dados Access. • Você tem uma planilha Excel com as participações e compras de ações da empresa, bem como os preços históricos dessas ações nos últimos 4 anos.
-Seu processo de indicação de taxas incluirá o seguinte resultado (explicado com mais detalhes abaixo): • Um banco de dados Access que possui consultas que geram dados que podem ser copiados no Excel para cada estado. • Uma planilha Excel que mostra o cálculo do rendimento médio do investimento para 2007-2009. • Planilha Excel que calcula a variação da taxa indicada, após colar nela o resultado das consultas de acesso e rendimento do investimento.
Esta planilha deve permitir ao usuário colar a saída de acesso de outro estado no Excel e gerar automaticamente a taxa indicada sem quaisquer atualizações adicionais. • Uma planilha Excel com um gráfico dinâmico que exibe a tendência CW e a tendência estadual. Há um exemplo de como deve ser a saída da planilha de indicações de taxas.
Um banco de dados Access é fornecido. Esse banco de dados já contém uma tabela chamada TrendData, que contém os dados de tendências do setor. Você também recebeu dados detalhados sobre políticas em policydata.txt. O layout do arquivo de texto está abaixo: Campo Pos 1-2 Chaves 3-4 Estado 5-8 Franquia 9-14 Código de classe 15-18 Ano 19-24 Prêmio 25 Indicador se a apólice tinha sinistro 26-35 Valor do sinistro
**Nota no campo Chaves**
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.
No Access, você deve criar consultas que produzam as seguintes informações:
Informações sobre prêmios/perdas da empresa: ESTADO (Agrupar por) ANO (Agrupar por) DEDUCT (Agrupar por) CLASS (Agrupar por) Contagem de apólices (Contagem) PREM (Soma) CLAIM_IND (Soma) LOSS_AMOUNT (Soma)
Você deve definir a consulta para ter uma cláusula where para o estado. Você pode alterar o estado para qualquer estado em que esteja trabalhando. Informações sobre tendências de fast track do setor: STATE (Agrupar por) YYYYQ (Agrupar por) Cov (Agrupar por) CW_CARYEARS (Soma) CW_PDCOUNT (Soma) CW_PDAMT (Soma) STATE_CARYEARS (Soma) STATE_PDCOUNT (Soma) STATE_PDAMT (Soma)
Os campos CW são resumos baseados em todos os dados de todos os estados. Os campos de resumo do ESTADO são somas dos campos do estado específico. Novamente, você deve configurar a consulta para a cláusula Where para especificar o estado que será gerado.
Observe que para obter resumos CW e resumos STATE na mesma consulta, você terá que mesclar a saída de duas consultas separadas (uma no nível estadual e outra no nível CW) e mesclar os resultados por YYYQ e COV.
A planilha fornecida possui duas tabelas. Uma tabela apresenta os preços das ações ao longo do tempo para ações do S&P 500. A ABC Company possui um subconjunto dessas ações. O departamento de investimentos forneceu um resumo das ações detidas no início do ano (BOY) de 2006, bem como das ações adquiridas em 01/01/2007, 01/01/2008 e 01/01/2009. Você precisa calcular o rendimento do investimento para 2007, 2008 e 2009 e a média aritmética do rendimento de 3 anos. Uma demonstração do cálculo está incluída na apostila. Você deverá preencher a planilha do Cálculo do Rendimento do Investimento da planilha. O valor que você calcula nesta planilha será inserido na planilha Indicações de Taxa.
A saída do Access deve ser colada na guia Dados de entrada da planilha. Sinta-se à vontade para adicionar quaisquer colunas de índice a esta guia que possam ser úteis para você mais tarde. Você também deve ser capaz de inserir o nome do estado nessa guia e fazer com que o nome do estado resultante flua para todos os cabeçalhos da planilha (portanto, se você colar dados para um novo estado, só precisará alterar o nome do estado uma vez no planilha, em vez de ter que atualizar todas as planilhas). Tenha em mente que nenhuma outra alteração deverá ser necessária ao atualizar um estado. Pense na possibilidade de consultas para estados diferentes retornarem um número diferente de linhas. Talvez seja necessário usar referências maiores para as tabelas InputData do que para os dados de estado que já estão lá. Incluído no folheto está um exemplo de como deve ser a aparência da saída do Excel para as outras guias da planilha. Listei algumas dicas sobre como preencher cada folha da apostila.
Obtenha as informações de tendência da saída da consulta Fast Track. Sua empresa usa apenas dados do setor para análise de tendências e pondera a experiência do estado com a experiência da CW para desenvolver suas tendências.
Use as fórmulas PROJ.LIN e INTERCEPT para calcular os valores apropriados. Sinta-se à vontade para colocar o índice (1,2,3,…) na coluna A para seus valores X. Seus valores Y devem ser da coluna Pure Premium. Lembre-se, Prêmio Puro = Valor da Perda / Anos do Carro. Use esses valores para calcular as colunas de valores ajustados. A variação anual é 4 x a inclinação (durante quatro períodos). Expresse isso como uma tendência percentual dividindo o valor anual pelo valor ajustado mais recente
Crie um gráfico conforme mostrado na apostila com 4 séries, Estado e CW, ajustado e real.
Crie uma exposição de tendências para todas as coberturas mostradas. Lembre-se de que você pode copiar a primeira guia preenchida clicando com o botão direito nela e dizer mover ou copiar e, em seguida, fazer uma cópia. Se
Se você codificar a primeira guia corretamente, poderá apenas copiá-la, alterar a referência de cobertura e não precisará repetir nenhum trabalho restante.
Planilha do Fator de Projeção de Perda As tendências calculadas para cada cobertura devem ser extraídas desta planilha. Há um cálculo de ponderação de credibilidade nesta planilha. A credibilidade dada ao
a experiência de um determinado estado baseia-se no número de reclamações desse estado no período mais recente. (Por exemplo, se a contagem de reclamações do primeiro trimestre de 2010 para BI for 123.245; o
o peso de credibilidade atribuído deve ser 0,4.) Eles devem ser extraídos das planilhas de tendências ou dos dados brutos na guia de dados de entrada.
A fórmula para tendência ponderada = Tendência do Estado * Peso de credibilidade + Tendência CW * (1-Peso de credibilidade).
-Você também deve incluir o valor da perda do período mais recente. Isto é usado para calcular uma tendência média ponderada para todas as coberturas (célula H13), com base na
distribuição de cobertura do estado.
Obtenha informações sobre contagem de apólices, prêmios e perdas para todos os três anos a partir dos dados de experiência da empresa na guia de dados de entrada. Calcule a taxa de perda, a mudança indicada e os fatores de taxa indicados. O cálculo de alteração indicado é mostrado na planilha. O fator indicador = Fator Atual x (1 + mudança indicada). Em ambas as planilhas, adicione um formato condicional à coluna de alteração indicada para destacar as células que apresentam um aumento superior a 10% ou uma diminuição inferior a -10%.
Extraia as informações de prêmios e perdas dos dados de experiência da empresa na guia de dados de entrada. Extraia o LPF da guia Fator de projeção de perda. Calcule as perdas projetadas = Perdas reais x LPF.
Use a taxa de sinistralidade projetada para o período de 3 anos na fórmula de mudança indicada na parte inferior da planilha. Insira manualmente o rendimento do investimento em sua planilha de rendimento do investimento. Para os outros valores da fórmula, use os valores do exemplo anexo.
-Seu chefe também gostaria de acompanhar as tendências, sem ter que fazer todo o trabalho de montar uma planilha de indicações. Você concordou em criar um gráfico dinâmico que mostra tendências premium puras.
-Para gerar os dados de origem para este Gráfico Dinâmico, você poderá usar a mesma consulta usada para gerar os dados de tendência colados na planilha Indicações. A principal diferença é que você deve remover o estado específico ao executar essa consulta. A consulta deve retornar os valores de todos os estados, bem como colunas que contêm os valores CW. Cole a saída da consulta em uma nova pasta de trabalho do Excel.
O Gráfico Dinâmico deve ter campos de Página Cobertura e Estado. O período de tempo (AAAA) deve ser exibido na parte inferior do gráfico. Os elementos de dados na área do gráfico devem incluir o prêmio puro do estado e o prêmio puro do CW.
