Как построить карту рисков в excel

4.2. Расчет рисков в среде Excel.

ППП EXCEL предлагает широкий набор средств автоматизации статистического моделирования данных от вычисления параметров описательной статистики до построения сложных прогнозных моделей. Для этих целей в нем реализована специальная группа статистических и математических функций, большинство из которых содержится в дополнении Пакет анализа. Список и форматы некоторых функций, использованных в процессе анализа рисков, приведены в табл. 1.1.

Наименование функции

НОРМОБР(вероятность; средн_энач; станд отклон)

НОРМРАСП (х; средн знач ; станд откл; интегральная)

4.3. Анализ рисков финансовых операций

Рассмотрим типовые задачи, которые можно решать с использованием стандартных функций ППП EXCEL.

Определение основных характеристик распределения случайной величины (СВ). Возможны два варианта расчетов: случай, когда вероятность осуществления случайного события не задана и , наоборот, вероятность осуществления случайного события задана явно.

Определение характеристик СВ при незаданной вероятности осуществления событий. В этом случае полагаем вероятность осуществления всех событий одина­ковой, т.е. p₁ = p₂ =…= p_n= 1/n и можем непосредственно применить статистические функций ППП EXCEL, вычисляющие основные характеристики распределения СВ (среднее значение М(Е), дисперсию VAR(E), стандартное отклонение (Е)). Продемонстрируем технику их расчетов с применением встроенных функций ППП EXCEL.

Подготовьте исходную таблицу (рис. 1.1. ) с данными следующего примера.

Пример 1.1. Рассмотрим возможность покупки акции недавно образованной фирмы «Н». Предполагается, что прогнозируется доходность по акциям этой фирмы через год будет зависеть от состояния спроса на ее продукцию в течение данного периода и соответственно равна: 12% — в случае повышенного спроса; 9% — при обычном спросе; 6% — при пониженном спросе.

Анализ рисков (акции фирмы «Н»)

Ожидаемая доходность (r )

Коэффициент вариации (CV)

Рис. 1.1. Исходная таблица для решения примера 1.1.

Осуществим анализ риска этой операции. Прежде всего определим среднюю доходность по акциям фирмы «Н». Поскольку наступление любого события в данном примере считается равновероятным, для расчета искомой величины можно воспользоваться функцией СРЗНАЧ (), указав ей в качестве аргументов блок ячеек В4, В6, содержащий предполагаемые значения доходности. Введите в ячейку В8 формулу: =СРЗНАЧ (В4: В6) (Результат 0,09, или 9%).

Для вычисления дисперсии и стандартного отклонения в ячейках В9 и В10 необходимо задать следующие формулы:

=ДИСПР(В4:В6) (Результат 0,0006)

=СТАНДОТКЛОНП (В4: В6) (Результат 0,0245, или 2,45%)

Теперь можно легко определить значение коэффициента ва­риации из соотношения (11). Для этого в ячейке В11 вычислим результат отношения стандартного отклонения (В10) к ве­личине среднего значения (В8):

=В10/В8 (Результат 0,27)

Полученные значения параметров позволяют сделать вывод о невысоком риске акций фирмы «Н».

Определение характеристик СВ при заданной вероятности осуществления событий.

Рассчитаем вероятность того, что доходность по акциям «Н» будет меньше величины а— (9 — 2,45 = 6,55). При этом будем исходить из предполо­жения, что величина доходности r распределена по нормальному закону Тогда из (10)

где Ф — функция Лапласа.

Для автоматизации расчетов, связанных с нормальным распределением вероятностей, в ППП EXCEL реализован ряд специальных функций. Мы будем использовать две функции — НОРМРАСП() и НОРМОБР().

Функция НОРМРАСП (х; средн_знач; станд_откл; интегральная)

Функция НОРМРАСП () имеет следующие параметры:

х — исследуемое значение случайной величины,

средн_знач — среднее значение;

станд_откл — стандартное отклонение;

интегральная — 0 или 1.

В зависимости от заданного параметра интегральная — О (ложь) или 1 (истина) — она возвращает плотность распределения (х) или значение кумулятивной функции распределения вероятностей F(x) для нормальной случайной величины.

Определим искомую вероятность р (r <. 6,55) Для этого в ячейку В14 введем формулу:

=НОРМРАСП(6,55; 9; 2,45; 1) (Результат 0,1586), или

=НОРМРАСП(В8-В10; В8; В10; 1) (Результат 0,1586)

Таким образом, эта вероятность приблизительно равна 16%. Соответственно вероятность Р(r > 6,55) будет равна:

=1 — НОPМРАСП(В8-В10; В8; В10; 1) (Результат 0,8414)

На рис. 1.2. приведен фрагмент ЭТ с расчетами вероятностей для различных значений ставки доходности r. Выполнить эти расчеты самостоятельно.

Построить графики плотности и кумулятивной функции распределения вероятностей для примера 1.1. Для построения графиков необходимо предварительно выполнить табуляцию функций (х) на интервале [а ± 3] и F(x). Для определения значений (х) также используется функция НОРМРАСП (), однако значение параметра интегральная при этом задается равным 0 (ложь).

Анализ рисков (акции фирмы «Н»)

Ожидаемая доходность (r )

Коэффициент вариации (CV)

рис. 1.2. Анализ риска (пример 1.1)

По графикам убедиться, функция распределения F(x) возрастает на интервале от 0 до 1. Согласно правилу сложения вероятностей при x₁<x₂ вероятность попадания значения случайной величины Е в интервал (x₁; x₂) равна приращению функции распределения вероятностей:

Определим вероятность попадания r в интервал (а + ):

=НОРМРАСП(В8+В10; В8; В10;1) — НОЕМРАСП(В8; В8; В10;1)

Соответственно вероятность попадания r в интервал (а ± ) будет равна:

=НОЕМРАСП(В8+В10;В8;В10;1) — НОБМРАСП(В8-В10; В8; В10;1)

Вероятность попадания г в интервал (а ± 2) и (а ± З) определите самостоятельно.

Полученные результаты служат числовой иллюстрацией правила трех сигм для нормального закона распределения.

Функция НОРМОБР (вероятность; средн_энач; станд__откл)

Функция имеет следующие параметры:

вероятность —вероятность нормального распределения;

средн_знач —среднее значение;

станд_откл —стандартное отклонение.

Она возвращает обратное нормальное распределение для указанного среднего и стандартного отклонения. Другими словами, она позволяет по заданной вероятности определить величину исследуемой переменной (в нашем примере доходности).

Определим предельную величину доходности для вероятности 84%:

=НОРМОБР (0,84 ;В8 ;В10) (Результат: 11,45%).

Таким образом, для заданной вероятности величина доходности составит не более 11,45%: р(х 0,1145) = 0,84.

Функции ППП EXCEL, определяющие значения параметров распределения М(Е), VAR(E) и (Е), следует применять только в тех случаях, когда вероятности событий равны. Если же распределение вероятностей задано (например, известно из преды­дущего опыта или получено методом экспертных оценок), среднее значение, дисперсия и стандартное отклонение рассчитываются путем непосредственной реализации средствами ППП EXCEL соответствующих соотношений — (4), (6), (7). Продемонстрируем один из вариантов подобной реализации на решении примера 1.2.

Пример 1.2. Рассматривается возможность приобретения акций двух фирм «А» и «В». Полученные экспертные оценки предполагаемых значений доходности по акциям и их вероятности представлены в таблице 1.2.

Подготовьте исходную таблицу с данными примера, как показано на рис. 1.3.

Анализ рисков (акции фирмы «А»)

Взвешенные квадраты отклонений

Ожидаемая доходность (r )

Коэффициент вариации (CV)

Рис. 1.3. Исходная таблица для решения примера 1.2.

Прежде всего необходимо определить среднюю величину доходности (соотношение (4)). Наиболее простой способ — последовательно перемножить каждую ячейку блока В5. В7 на соответствующую ей ячейку блока С5. С7 и суммировать полученные значения. Нетрудно заметить, что данная последовательность действий представляет собой операцию нахождения суммы произведений элементов двух матриц. Поскольку матричные операции достаточно часто встречаются в прикладном анализе, для автоматизации их выполнения в ППП EXCEL реализована специальная группа математических функции..

В частности, для выполнения необходимой нам операции удобно использовать функцию СУММПРОИЗВ (). Как следует из табл. 1.3., аргументами функции являются матрицы одинакового размера. Введем в ячейку и формулу:

=СУММПРОИЗВ (В5: В7; С5: С7) (Результат: 0,15, или 15%)

Для определения величины стандартного отклонения необходимо сперва вычислить дисперсию. Из (6) следует, что дисперсия случайной величины представляет собой сумму квадратов отклонений от среднего, взвешенных на соответствующие вероятности. Зададим в ячейке D5 формулу вычисления дисперсии для первого события:

=В5* (С5-$В$9)  2 (Результат: 0,2165).

Обратите внимание на то, что для задания ячейки, содержащей среднее значение (В9), используется способ абсолютной адресации. Это позволяет безболезненно скопировать данную формулу в ячейки D6,D7 (в противном случае адрес ячейки, содержащей среднее значение, был бы настроен неправильно). Теперь можно вычислить величину стандартного отклонения, которая равна квадратному корню из дисперсии (суммы ячеек D5:D7). Для этого воспользуемся функцией КОРЕНЬ () (см. табл. 1.3.). Введите в ячейку В10:

=КОРЕНЬ(СУММ(D5:D7)) (Результат: 0,6584, или 65,84%).

Вычисление коэффициента вариации не представляет особых трудностей. Для этого достаточно просто разделить значение ячейки В10 на значение В9. Введите в ячейку В11:

=В10/В9 (Результат: 4,39).

Вычислив основные параметры распределения случайной величины, можно определить вероятность ее попадания в некоторый интервал. В приведенной на рис. 1.4. таблице границы первого интервала задаются в ячейках В16 и С 16. Определим вероятность того, что значение доходности попадет в интервал (-70; 0). Введите границы анализируемого интервала в ячейки В16 и С16. Формула вычисления вероятности в ячейке D16 реализована с использованием уже известной нам функцией НОРМРАСП () и имеет следующий вид:

=НОРМРАСП (С16;$В$9;$В$10;1)-НОРМРАСП (В16;$В$9;$В$10;1)

Снова обращаем внимание на использование абсолютной адресации при задании в формулах ячеек, содержащих среднее значение и стандартное отклонение.

Как построить карту рисков в excel

Карта рисков — это наглядное представление рисков предприятия. Карта оценки рисков показывает опасности и риски, присущие организации, визуализирует вероятность их возникновения и общую оценку рисков, а также демонстрирует меры по реагированию на риск.

При этом, необходимо иметь ввиду, что на практике единого определения «карты рисков» нет и нередко под этим термином (англ. — risk map) может подразумеваться сразу несколько различных инструментов визуализации или видов отчетов, таких к примеру, как:

• сводный отчет по рискам организации (карта идентификации, оценки и управления рисками)
• отчет по рискам в разрезе направлений деятельности или организационной структуры
• матрица соответствия рисков бизнес-процессам организации
• карта оценки рисков (часто также называемая матрицей рисков (risk matrix) или тепловой картой рисков (risk heat map))
• и т.д.

Примеры карт рисков

Образец сводной карты, в табличном виде агрегирующей данные по идентификации рисков, их оценки и мерах по управлению рисками (митигации), приведен на картинке:

Карта идентификации, оценки и управления рисками

Скачать образец данной карты рисков в формате Excel можно здесь.

Еще один пример визуализации, которую часто называют «картой рисков» (также известной как «матрица рисков» или «тепловая карта рисков») приведен на картинке:

Тепловая карта рисков (матрица рисков)

Эта матрица иллюстрирует существенность рисков организации в максимально наглядном виде, лучше всего подходящем для представления топ-менеджменту организации.

Иногда также используются другие варианты визуализации, к примеру диаграмма-паутинка (полярная карта рисков или радарная диаграмма):

Полярная карта рисков (радарная диаграмма рисков)

В любом случае, при использовании всех вариаций карт рисков, необходимо понимать, что итоговый результат и выбранный вид представления будет зависеть от области деятельности организации и присущих ей рисков — карта рисков банка может существенно отличаться от карты рисков другого типа предприятия, например, производственного или из сферы розничной торговли.

Также процесс формирования карты, ее представление и содержание может зависеть и от типа риска, который рассматривается. По этой причине карта комплаенс-рисков может существенно отличаться от карты коррупционных рисков организации, а карта рисков безопасности будет отличаться от карт оценки профессиональных рисков и карты рисков экономической безопасности. Что ведет к необходимости составлять множество карт риска, различного вида.

Общим же и основным преимуществом подобного вида визуализаций является их наглядность, и доступность для понимания топ-менеджерами предприятия и прочими сотрудниками. В случае, если необходимо визуально представить более детальный анализ по конкретному риску, карты рисков можно дополнить диаграммами «галстук-бабочка».

Использование карты рисков для их выявления

Упрощенно технология управления в концепции приемлемого риска воспринимается как последовательность трех больших этапов выявления, оценки и минимизации. Предположим, что в ходе реализации первого этапа руководством сформулированы цели и поставлены задачи риск-менеджмента компании. Следующим шагом предстоит выявить и идентифицировать основные угрозы текущей и перспективной деятельности. Одним из действенных и наглядных инструментов такой работы является карта рисков.

Этап картографирования рисков

Самостоятельная борьба с рисками в бизнесе, как правило, начинается с традиционного SWOT-анализа и описания угроз. К этому подключается анализ документации: нормативной, финансовой, управленческой, маркетинговой, договорной. Исследуются действующие политики, регламенты, результаты сессионной стратегической деятельности. В ходе исследований и коллегиальной работы формируется состав внешних и внутренних факторов, способных оказать влияние на уровень рисков.

В результате выявленные угрозы подлежат сведению в единую таблицу, представляющую собой систему факторов риска с их перечнем, иногда именуемым профилем факторов риска. Помимо сводной таблицы целесообразно также разработать классификационную схему факторов с выделенными взаимосвязями между ними. Более конкретной формой выявления факторов служит их идентификация. Идентификация рисков предполагает выявление самых значимых качественных и количественных их характеристик, в состав которых входят:

• вероятность проявления;
• размер потенциального ущерба;
• место возникновения;
• уровень взаимосвязей между факторами и т.п.

Иными словами, риск необходимо сопоставить с указанными параметрами. В момент, когда мы начинаем осмыслять размер ущерба, возникает переход на второй этап технологии управления – стадию оценки. Измерение риска в рамках идентификации факторов и первичной оценки инструментально производится сначала качественно, а затем количественно.

Вторым инструментом измерения является картографирование. Когда мы только начинаем работу с факторами, мы стремимся описать их на уровне: вероятно – не вероятно, опасно – не опасно и насколько опасно. На этой основе можно осуществить построение карты с осями абсцисс, по которой выстроена шкала опасности, и ординат, с размещением на ней шкалы вероятности риска. Факторы находят отражение на созданном поле и получают на нем визуальное позиционирование.

Каждая компания сама устанавливает понятие опасности и единицы ее измерения. Для руководителей одной компании под ней понимается упущенная прибыль, для других – доход. Для примера можно предположить, что опасность в пределах потери прибыли до 33% является неопасной, в диапазоне от 33% до 67% опасность допустима, а свыше 67% уже неприемлема. Некоторые авторы полагают, что опасным может быть фактор, если он может привести к потерям прибыли полностью (100%). Диапазон вероятности от 0 до 1 делится на три или более группы, предположим:

• от 0 до 0,2 – маловероятно;
• от 0,21 до 0,65 – вероятно;
• свыше 0,65 – весьма вероятно.

Представленный выше пример разбиения диапазонов не является догмой, в каждом конкретном случае подход индивидуален. Далее ответственные сотрудники, взяв данные заполненной таблицы факторов риска (форма размещена ниже), переносят каждый фактор на карту риска с учетом вероятности и опасности. В зависимости от сектора матрицы, в который попадают факторы, можно увидеть на карте, к какой зоне риска они принадлежат.

Анализ карт рисков

Построение или коррекцию карты рекомендуется делать один раз в квартал. Каждый раз после такой работы следует проводить анализ. Он позволяет отсечь группу рисков, являющихся опасными (выше проведенной красной линии на карте). Кроме того, очевидными становятся неопасные риски, попавшие в квадранты ниже синей прочерченной линии. Карта рисков в ходе анализа дает возможность сделать следующие выводы.

1. По группе рисков выше красной черты следует разработать план немедленных (первоочередных) мероприятий.
2. По группе рисков, входящих в зону между красной и синей чертой, требуется разработка плана годовых мероприятий.
3. По рискам, расположенным ниже синей черты, необходимо создать план контролируемых мероприятий для того, чтобы со временем они не перешли в разряд допустимых или даже опасных.

Выше показан пример иного представления карты. Внутри кругов проставлены значения вероятности фактора. В самой верхней части карты мы наблюдаем два риска, которые можно с уверенностью назвать ключевыми. Под ключевыми рисками следует понимать такие угрозы, которые способны нанести непоправимый, катастрофический ущерб бизнесу. Среди ущерба подобного типа можно назвать остановку непрерывного производства в условиях рисков техногенных катастроф, например, в металлургии, или даже потерю самого бизнеса при угрозе появления так называемых «убийц технологий».

Карты рисков могут быть сформированы не только в графической, но и в табличной форме. Ниже вашему вниманию представлен пример такой карты. По строкам размещены факторы риска, а шкалы вероятности и степени опасности последовательно размещены в столбцах. Таблица заполняется путем проставления «+» в ячейках, соответствующих факторам риска по двум основным параметрам оценки. В зону самых опасных рисков попадают факторы, имеющие отметку в каждом третьем столбце. В нашем примере это «рост себестоимости производства продукции». Контролируемые риски, наоборот, имеют отметки в каждом первом столбце. В примере к таким относятся «рост запасов» и «текучесть персонала».

При построении карты рисков возникает резонный вопрос: «Можем ли мы ошибаться?». Конечно! Ошибка может заключаться в выборе экспертов. И сами эксперты способны совершать ошибки, разворачивая ситуацию в субъективной оценке факторов. Но делая оценку регулярно и вводя ее результаты в фокус своего внимания, лица, принимающие решения, раз за разом учатся выявлять застарелые проблемы и находить новые угрозы. Помимо этого, формируется навык грамотно расставлять приоритеты и своевременно минимизировать риски. В любом случае, настоящий инструмент эффективен сам по себе.

Построение карты рисков в Microsoft Excel

Как уже писали здесь, карта рисков – баловство для конечных пользователей. В консультационных проектах один из авторов навострился изображать эти карты вручную в PowerPoint буквально за 10-15 минут, вместе с красивыми кружочками и легендой. Но ручной труд методологически неправилен, так как повышает вероятность ошибки. Исходя из этого и решили все-таки сделать карту рисков в формате Microsoft Excel.

В качестве технического задания взяли наиболее наглядную карту рисков в терминологии страницы «Подходы к визуализации рисков». Из существующих типов диаграмм в Microsoft Excel можно использовать пузырьковую и точечную (XY-диаграмму). Результат на примере карты рисков условного предприятия в Excel2016 – вот. В предыдущих версиях сделать автоматическую нумерацию не получается (собственно, поэтому раньше и статьи такой не было).

Последовательность действий для людей, знающих Microsoft Excel:

• создаем диаграмму. При этом выбираем по оси абсцисс – ущерб, по оси ординат – вероятность, в качестве имени ряда в источнике данных добавляем номера рисков. Для пузырьковой диаграммы дополнительно рассчитываем долю математического ожидания каждого риска в общем математическом ожидании. Целесообразно использовать именно этот параметр, так как размер должен отражать что-то новое, а привязка к ущербу либо вероятности приведет к банальности в виде того, что чем правее и выше риск, тем больше кружочек. Отметим, что для правильного размера кружочков риски должны быть независимыми друг от друга, что достигается автоматически, если следовать рекомендациям соответствующего раздела сайта;
• добавляем подписи данных, помещаем эти подписи сверху ряда. В качестве подписи отображаем номера рисков. Чтобы кружочки были одинаковыми по размеру, номера рисков с 1 по 9 заменяем на текстовый формат « 1», « 2» и т.д. (извините за извращение, но другого варианта не нашли);
• опционально оформляем диаграмму: добавляем название, подписываем оси, выбираем градиентную заливку и играемся с градиентами, добавляем логарифмическую шкалу по ущербу, украшаем ряды данных и пр.

Какие проблемы при автоматическом построении карты рисков в Microsoft Excel нужно дополнительно решить:

1. При одинаковых или близких по значению рисках отображается только один из них. Например, на картах рисков условного предприятия не видны риски под №9 и №22. Варианты устранения: (1) немного изменить исходные данные, то есть вместо двух рисков с вероятностью 50% и ущербом в 100 млн. руб. нарисовать риски с вероятностями 48 и 52% и ущербом 96 и 104 млн. руб., (2) дорисовывать кружочки руками или же (3) увеличить размер диаграммы (как в рассматриваемом случае, когда нет одинаковых рисков по вероятности и ущербу).
2. Линия толерантности в виде линии в автоматическом режиме не отражается. Варианты устранения: (1) использовать градиентную заливку, в Microsoft Excel 2016 она автоматически принимает нужные цвета (кстати, особенно красиво выглядит, если по ущербу поставить логарифмическую ось) или же (2) дорисовывать линию руками.
3. Для наиболее наглядной карты рисков кружочки с рисками, не попадающими на карту, необходимо дорисовать руками.
4. Практически невозможно вставить в легенду названия рисков. На самом деле проблемой это не является, так как автоматические средства Microsoft Excel не очень экономно расходуют пространство в диаграмме, и в результате диаграмма при печати оказывается мельче, чем могла бы быть.

Несмотря на указанные недостатки, задача выполнена. Пользоваться можно если не для представления уважаемым людям, то в качестве контрольной процедуры.

Будут предложения по совершенствованию – пишите. Для упрощения процедуры сделаем эксперимент и откроем комментарии к странице.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

How to Create a Risk Heatmap in Excel – Part 1

Risk heat maps are commonly used in operational risk management and are specially useful to represent a firm’s risks in a visual manner, highlighting the ones that need to be managed more closely. When assessing operational risk, the risk manager will typically use a spreadsheet to record its firm’s key risks and rate the impact and likelihood (or probability) assessment scores for each risk.

Although many firms have specific risk management systems which offer this functionality, some firms still use spreadsheets to manage wider risks and display heat maps, which are usually included in management information reports for senior management or other senior executives.

The overall risk score corresponds to the product of the likelihood (or probability) rating scores and the impact rating scores. The simple formula to calculate risk score is:

Risk Score = Likelihood Score x Impact Score

When the list of risks is extensive, most of the times crossing several departments or business areas, there is a big challenge for the risk manager to plot these risks in a heat map, assuring all relevant risks are correctly displayed. The risks will be plotted on a heat map according to its score. The risks in the heat map will range from red, amber and green (RAG), according to their individual score.

The first example below shows how a complete range of risks can be plotted intelligibly into a chart using Excel (the data sheet feeding into this chart has more than 100 risks).

The second example shows a summarised heat map in a form of a table, also using Excel.

In the next article I will explain how to create each of these heat maps, with a special focus on the first example which requires some custom VBA code in order to achieve the aligned risks layout. I will also include the source files for the examples displayed above. Please keep tuned for the next article – now available here.