Actuarial Computing Excel Access VBA Repo
1.0.0
Актуарные вычисления (MAT 253, ISU)
В этой лаборатории кода основное внимание уделяется использованию VLOOKUPS для заполнения таблиц, перечисленных ниже, для количества заявок, ссылаясь на первую таблицу на вкладке данных. Он предполагает использование значений в строках для заполнения третьего параметра функции ВПР. Это также предполагает правильное использование абсолютных и относительных ссылок на ячейки, чтобы одну и ту же функцию можно было скопировать по всей ЖЕЛТОЙ области.
В этой лабораторной работе с кодом также основное внимание уделяется использованию HLOOKUP для заполнения таблиц с использованием данных из второй таблицы вкладки «Данные 1». Для третьего параметра HLOOKUP мы используем функцию ПОИСКПОЗ с соответствующим ключом соответствия и ссылкой на массив вектора со списком доступных лет.
Код включает в себя следующие инструкции:
В этой лаборатории кода мы создаем графики, которые показывают фактическую частоту претензий и фактическую серьезность претензий по оси Y.
Поскольку масштаб каждой из этих серий сильно различается, мы используем две разные оси, чтобы показать разные серии.
Ось X показывает период № (группа A). Каждая серия отображается в виде точек с соединительными линиями.
Каждая серия помечена как частота или тяжесть в зависимости от ситуации.
Используя операторы ЕСЛИ, мы рассчитываем актуарную текущую стоимость для каждого человека в списке на вкладке «Задача 1». - Формула APV = номинальная стоимость * Ax. - Ax зависит от пола и статуса курильщика и его можно найти на 4 вкладках для каждого случая. Чтобы проверить ответ, результат первой политики должен иметь APV = 1,1238,0. На вкладке «Проблема 1» столбец A содержит текстовую строку, которая представляет собой объединение 4 разных полей: Policy_Num, Effect_Date, Expiration_Date, Premium. Используйте запятую (,) в качестве разделителя, чтобы разделить их на 4 столбца. Для этого вы можете использовать любой инструмент или функцию Excel.
МЫ настроили отчет сводной таблицы на новом листе под названием «Проблема 1» на основе данных на вкладке «Коллекция» (диапазон A1: D2771). Поместите «Количество взыскания» в метки строк и создайте 4 столбца: 1. Сумма страховых взносов 2. Сумма убытков 3. Коэффициент убытков = Убыток / Премии 4. Количество полисов, отображается в процентах от столбца.
На вкладке «Регрессия» используйте метод простой линейной регрессии (y=a+bx), чтобы спрогнозировать вес человека по его росту. Для получения оценок параметров можно использовать любые методы, доступные в Excel.
Вы являетесь актуарием по ценообразованию в страховой компании ABC, небольшой компании по личному автострахованию с доходом от премий около 300 миллионов долларов в год. Одной из ваших должностных обязанностей является разработка периодических показателей уровня ставок, а также корректировка ваших рейтинговых факторов. Ваш начальник попросил вас разработать процесс, позволяющий оптимизировать процесс определения индикаторных ставок на 2011 год. Для этого он предоставил следующие инструкции, а также пример того, как, по его мнению, должна выглядеть электронная таблица.
Он также попросил вас предоставить ему отдельный способ следить за тенденциями чисто премиум-класса во всех штатах и сравнивать их с тенденциями по всей стране (CW). Ему хотелось бы простой точки зрения и
click, чтобы сделать это, поэтому вы предложили для этой цели сводную диаграмму.
Разработка показателя ставки в ABC включает в себя несколько этапов, включая: • Анализ тенденций • Разработка коэффициентов прогнозирования убытков на основе тенденций • Разработка указанной франшизы и факторов класса (возраст и пол) • Определение инвестиционной доходности • Разработка общего показателя ставки
Для разработки показателей ставок вам была предоставлена следующая информация: • ИТ-отдел предоставил подробную информацию о премиях и убытках по всем полисам за 2007–2009 годы в текстовом файле фиксированной ширины. В этом файле около 1 миллиона записей, поэтому его необходимо предварительно обработать в Access. • У вас также есть копия последних данных отраслевых тенденций Fast Track в базе данных Access. • У вас есть электронная таблица Excel с запасами и покупками акций компании, а также историческими ценами на эти акции за последние 4 года.
-Ваш процесс определения скорости будет включать в себя следующие выходные данные (подробнее поясняется ниже): • База данных Access, содержащая запросы, выводящие данные, которые можно скопировать в Excel для каждого штата. • Таблица Excel, показывающая расчет средней доходности инвестиций за 2007-2009 годы. • Электронная таблица Excel, которая рассчитывает указанное изменение курса после вставки в нее результатов запросов доступа и доходности инвестиций.
Эта электронная таблица должна позволять пользователю вставлять выходные данные доступа для другого штата в Excel и автоматически генерировать указанную ставку без каких-либо дополнительных обновлений. • Электронная таблица Excel со сводной диаграммой, отображающей как CW-тренд, так и тренд штата. Ниже приведен пример того, как должны выглядеть выходные данные таблицы показателей ставок.
Предоставляется база данных Access. Эта база данных уже содержит таблицу с именем TrendData, содержащую данные о тенденциях в отрасли. Вам также были предоставлены подробные данные о политике в файле policydata.txt. Структура текстового файла приведена ниже: Позиция поля 1-2 Ключи 3-4 Состояние 5-8 Франшиза 9-14 Код класса 15-18 Год 19-24 Страховая премия 25 Индикатор наличия претензии 26-35 Сумма претензии
** Примечание к полю «Ключи»**
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.
В Access вам следует создавать запросы, которые выводят следующую информацию:
Информация о премии/убытках компании: STATE (группировать по) YEAR (группировать по) DEDUCT (группировать по) CLASS (группировать по) Количество полисов (Count) PREM (Сумма) CLAIM_IND (Сумма) LOSS_AMOUNT (Сумма)
Вам следует настроить запрос так, чтобы он содержал предложениеwhere для состояния. Вы можете изменить состояние на любое состояние, над которым вы работаете. Информация об отраслевых тенденциях ускоренного режима: STATE (Группировать по) YYYYQ (Группировать по) Cov (Группировать по) CW_CARYEARS (Сумма) CW_PDCOUNT (Сумма) CW_PDAMT (Сумма) STATE_CARYEARS (Сумма) STATE_PDCOUNT (Сумма) STATE_PDAMT (Сумма)
Поля CW представляют собой сводные данные, основанные на всех данных для всех состояний. Поля сводки STATE представляют собой суммы полей для конкретного состояния. Опять же, вам следует настроить запрос для предложения Where, чтобы указать состояние, которое должно быть выведено.
Обратите внимание: чтобы получить сводки CW и сводки STATE по одному и тому же запросу, вам придется объединить выходные данные двух отдельных запросов (один на уровне состояния и один на уровне CW) и объединить результаты по YYYQ и COV.
Представленная электронная таблица состоит из двух таблиц. В одной таблице представлены цены на акции из индекса S&P 500 с течением времени. Компания ABC владеет частью этих акций. Инвестиционный отдел предоставил краткую информацию об акциях, имевшихся на начало 2006 года (BOY), а также об акциях, приобретенных 01.01.2007, 01.01.2008 и 01.01.2009. Вам необходимо рассчитать доходность инвестиций за 2007, 2008 и 2009 годы, а также среднее арифметическое трехлетней доходности. Демонстрация расчета включена в раздаточный материал. Вам следует заполнить электронную таблицу расчета инвестиционной доходности. Значение, которое вы рассчитываете в этом рабочем листе, будет введено в рабочий лист «Индикации ставок».
Выходные данные Access следует вставить на вкладку «Входные данные» рабочего листа. Не стесняйтесь добавлять на эту вкладку любые индексные столбцы, которые могут пригодиться вам в дальнейшем. Вы также должны иметь возможность ввести имя состояния на этой вкладке и получить полученное имя состояния для всех заголовков рабочего листа на листе (поэтому, если вы вставляете данные для нового состояния, вам нужно будет изменить имя состояния только один раз в лист, вместо того, чтобы обновлять каждый лист). Имейте в виду, что при обновлении состояния не требуется никаких других изменений. Подумайте о возможности того, что запросы для разных состояний возвращают разное количество строк. Возможно, вам придется использовать более крупные ссылки на таблицы InputData, чем на уже имеющиеся в них данные о состоянии. В раздаточный материал включен пример того, как должны выглядеть выходные данные Excel для других вкладок листа. Я перечислил несколько советов по заполнению каждого листа раздаточного материала.
Получите информацию о тенденциях из результатов запроса Fast Track. Ваша компания использует только отраслевые данные для анализа тенденций и сравнивает опыт штата с опытом CW для разработки своих тенденций.
Используйте формулы ЛИНЕЙН и ОТРЫВ, чтобы вычислить соответствующие значения. Не стесняйтесь помещать индекс (1,2,3,…) в столбец A для ваших значений X. Ваши значения Y должны быть в столбце Pure Premium. Помните: чистая премия = сумма убытков / год выпуска автомобиля. Используйте эти значения для расчета столбцов подобранных значений. Годовое изменение в 4 раза превышает наклон (за четыре периода). Выразите это как процентную тенденцию, разделив годовую сумму на самое последнее установленное значение.
Создайте график, как показано в раздаточном материале, с 4 сериями: состояние и CW, установленными и фактическими.
Создайте таблицу тенденций для всех показанных покрытий. Имейте в виду, что вы можете скопировать первую заполненную вкладку, щелкнув ее правой кнопкой мыши и сказав «Переместить» или «Копировать», а затем скопировать. Если
если вы правильно закодируете первую вкладку, вы сможете просто скопировать ее, изменить ссылку на покрытие, и вам не придется повторять оставшуюся работу.
Таблица коэффициентов прогнозирования убытков Тенденции, рассчитанные для каждого покрытия, должны быть отражены в этой таблице. В этой таблице есть расчет достоверности. Авторитет, оказанный
Опыт данного государства основан на количестве претензий к этому государству за самый последний период. (Например, если количество претензий штата в первом квартале 2010 года к BI составляет 123 245;
Присвоенный вес достоверности должен составлять 0,4.) Их следует брать либо из таблиц тенденций, либо из необработанных данных на вкладке входных данных.
Формула взвешенного тренда = Тенденция штата * Вес достоверности + Тренд CW * (1-Вес достоверности).
-Вы также должны указать сумму убытков за последний период. Это используется для расчета средневзвешенной тенденции для всех покрытий (ячейка H13) на основе
Распределение покрытия штата.
Получите информацию о количестве полисов, премиях и убытках за все три года из данных об опыте работы компании на вкладке входных данных. Рассчитайте коэффициент потерь, указанное изменение и указанные коэффициенты скорости. Указанный расчет изменения показан в электронной таблице. Коэффициент индикатора = Текущий коэффициент x (1 + указанное изменение). На обоих листах добавьте условный формат в указанный столбец изменений, чтобы выделить ячейки, которые имеют увеличение более чем на 10 % или уменьшение менее чем на -10 %.
Получите информацию о премиях и убытках из данных об опыте компании на вкладке входных данных. Извлеките LPF из вкладки «Коэффициент прогнозирования потерь». Рассчитайте прогнозируемые потери = Фактические потери x ФНЧ.
Используйте прогнозируемый коэффициент убытков за трехлетний период в указанной формуле изменения внизу таблицы. Вручную введите доходность инвестиций из таблицы доходности инвестиций. Для других значений в формуле используйте значения из приложенного примера.
-Вашему начальнику также хотелось бы иметь возможность отслеживать тенденции без необходимости выполнять всю работу, связанную с созданием таблицы показаний. Вы согласились создать сводную диаграмму, которая отображает тенденции только премиум-класса.
-Чтобы сгенерировать исходные данные для этой сводной диаграммы, вы должны иметь возможность использовать тот же запрос, который вы использовали для создания данных тренда, которые вы вставили в рабочий лист «Показания». Основное отличие состоит в том, что вам следует удалить определенное состояние при выполнении этого запроса. Запрос должен возвращать значения для всех состояний, а также столбцы, содержащие значения CW. Вставьте выходные данные запроса в новую книгу Excel.
Сводная диаграмма должна иметь поля страницы «Охват» и «Состояние». Период времени (ГГГК) должен отображаться в нижней части диаграммы. Элементы данных в области диаграммы должны включать чистую премию штата и чистую премию CW.
