text
Читать свежий номер бесплатно >>>
Финансовый директор

Как оперативно ранжировать риски с помощью Excel

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

Как с помощью модели в Excel ранжировать риски

Модель Excel для ранжирования рисков состоит из двух таблиц. В первую предстоит ввести исходные данные (см. рисунок 1. Форма таблицы с исходными данными о рисках компании).

Елена Сережкина , генеральный директор ООО «АРСЭ Консалтинг»

Модель ранжирования в Excel может понадобиться при инвентаризации основных средств и дебиторской задолженности. С помощью нее можно оперативно выявить самые дорогостоящие активы предприятия и определить самых крупных должников компании.

В первой графе этой таблицы нужно указать номер риска, во второй – его название или описание, в третьей – причину его возникновения. Далее следует указать значения возможного ущерба в трех возможных сценариях развития событий (пессимистическом, наиболее вероятном и оптимистическом – в гр. 4, 6 и 8) и вероятности реализации каждого из сценариев. Последние должны быть отражены в графах 5, 7 и 9 (в сумме равны единице). Значение вероятности третьего сценария вносить не нужно, оно рассчитается автоматически. Средний ущерб, который может причинить каждый из выявленных рисков, также определяется автоматически.

Ранги рискам присваиваются во второй таблице на листе tabl2 (см. рисунок 2. Расчетная часть модели ранжирования рисков).

В этой таблице графы 2 и 3 (описание риска и причина возникновения) дублируют информацию из первой таблицы (см. рисунок 1. Форма таблицы с исходными данными о рисках компании). В графе 4 на основе исходных данных таблицы 1 по формуле (см. ячейку D5 на рисунке 2. Расчетная часть модели ранжирования рисков) автоматически определяется ожидаемое значение оценки риска (подробнее о том, как рассчитать эту величину, см.  Как учесть несколько сценариев при расчете риска). В графе 5 таблицы 2 на основании данных о суммах ущерба по каждому из трех сценариев (см. таблицу 1 на рисунке 1. Форма таблицы с исходными данными о рисках компании), а также средней величины вероятного убытка по расчетной формуле автоматически определяется отклонение сумм ущерба в рамках этих сценариев от средней (см. рисунок 3. Расчет отклонения от средней суммы ущерба).

Далее в графе 6 автоматически определяется итоговая оценка риска: сумма данных граф 4 и 5 (см. рисунок 2. Расчетная часть модели ранжирования рисков).

Итоговая расчетная величина, вычисляемая автоматически, – ранг риска (гр. 7 таблицы 2 на рисунке 2. Расчетная часть модели ранжирования рисков). Он определяется с помощью функции РАНГ. Например, для первого риска (в строке 1) эта формула выглядит так:
=РАНГ(F5;$F$5:$F$36;), где:
F5 – итоговая оценка этого риска;
$F$5:$F$36 – диапазон (все значения гр. 6 таблицы 2).

В рамках этой функции программа сравнивает значения из ячейки F5 с остальными данными из этого диапазона и по результатам сравнения присваивает ей ранг (см. рисунок 4. Использование функции ранжирования). По той же схеме ранжируются все остальные значения.

Как ранжировать данные без использования готовой модели Excel

Если понадобится проранжировать данные вне готовой модели, к примеру, суммы задолженности перед кредиторами (см. рисунок 5. Заполнение данных для ранжирования), можно вручную задать параметры функции РАНГ. Для этого достаточно в пустую ячейку (например, соседнюю со списком ранжируемых значений) ввести функцию РАНГ, в ней указать исходное число (В3). И через точку с запятой указать диапазон значений, с которыми нужно его сравнить (см. рисунок 5. Заполнение данных для ранжирования). После диапазона снова ввести точку с запятой, указать 0 или это место оставить пустым (если предстоит ранжирование на убывание). Если необходимо сравниваемому числу присвоить ранг по возрастанию (т. е. первый ранг присвоить наименьшему числу), то вместо слова «порядок» нужно указать любое число от 1. Затем остается зафиксировать от изменений выделенный диапазон (В3:В34) при помощи клавиши F4 (в итоге получится, например, диапазон $В3$:$В$15).

Далее предстоит скопировать формулу вниз до уровня последнего значения диапазона В3:В15 (см. рисунок 6. Применение функции ранжирования). Так каждая сумма из первого столбца (В) будет проранжирована в порядке убывания (т. е. первый ранг будет присвоен максимальной сумме 5939, а последний ранг 13 – минимальной 462).

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

Далее на вкладке «Данные» в группе «Сортировка и фильтр» нужно выбрать кнопку «Сортировка» (см. рисунок 8. Настройка параметров сортировки).

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.