Прил. 1 к занятию 1. Система контроля финмодели

Приложение №1 к первому занятию курса обучения финансовому моделированию в Excel.

Организация системы контроля за корректностью финансовой модели.

Первое занятие нашего курса обучения финансовому моделированию в Excel получилось довольно-таки объемным – более 6-ти часов. Поэтому мы решили несколько важных блоков этого занятия выделить в отдельные приложения или дополнительные практические уроки.

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

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

Смотрите видео
об организации системы контроля в финансовой модели

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

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

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

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

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

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

Для организации эффективной системы контроля за корректностью финансовой модели в Excel мы предлагаем следующий алгоритм действий.

Шаг 1. Для каждого листа Excel-файла с финмоделью фиксируем ячейку с одним и тем же адресом вне зависимости от листа, в которую будет сведена вся информация об ошибках данного листа.

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

Шаг 3. В каждом листе Excel-файла финмодели рядом с ячейкой контроля внутри этого листа организовываем ячейку, в которую при помощи простой прямой ссылки на итоговую ячейку контроля в контрольном листе всей модели подтягиваем актуальную информацию об обнаружении ошибок в Excel-файле финансовой модели в целом.

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

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

Шаг 5. Контрольные значения на нижнем уровне, т.е. в местах (ячейках), где мы планируем контролировать появление ошибок, настраиваем через формулу

=ЕСЛИ(нет ошибки; то пишем «0»; иначе «1»).

То есть если в том месте, где должно выполняться контрольное соотношение мы поблизости в какой-либо из ячеек контрольного столбца разместим такую функцию «ЕСЛИ()», то в случае выполнения контрольного соотношения, значение в контрольной ячейке будет равно нулю «0», а если не будет выполнено контролируемое соотношение, то значение станет равным единице «1». Далее все контрольные значения суммируются по контрольным столбцам в контрольную строку нашего листа, после чего, суммируя все значения по контрольной строке в одну выделенную на шаге 1 контрольную ячейку листа, мы получим значение общего контроля листа. И, как не сложно догадаться, если контрольное значение будет равно нулю «0», то это будет означать, что, как минимум, среди того набора контролируемых мест в листе нашей финмодели ошибок нет.

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

Шаг 6. Для удобства работы с финансовой моделью желательно настроить условное форматирование ячеек с контрольными значениями так, чтобы при появлении в этих ячейках значений отличных от нуля «0», они окрашивались в ярко красный цвет с белым шрифтом, который отражал бы количество ошибок.

Это позволит пользователю легко визуально замечать появление ошибок в финансовой модели.

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

Для начала обратим внимание на то, что согласно шагу 1 алгоритма создания системы контроля за ошибками в финмодели мы в качестве ячеек с контрольными значениями листов выбрали ячейки с адресом E14. Согласно шагу 2 мы создали вкладку «Cntrl», в которой из ячеек с адресом E14 всех листов финмодели собираются контрольные значения всех этих листов и суммируются в ячейке M17.

Согласно шагу 3 мы в ячейках с адресом D14 в каждой вкладке финансовой модели отобразили значение ячейки M17 из листа «Cntrl» с итоговым контролем всего Excel-файла.

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

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

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

А потом предположим произошла такая ситуация, что пользователь зашел во вкладку «Lists» и по какой-то причине переименовал в списке допустимых периодов расчетов финмодели значение «месяц» на «месяцы». Смотрим, что получилось.

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

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

Теперь Вам необходимо найти и исправить ошибку. Для этого необходимо зайти в лист общего контроля финмодели, а именно во вкладку «Cntrl», и в списке всех листов определить, в каких именно произошла ошибка, визуально отметив те из них, напротив которых также «загорелись красные флажки».

Лист с нарушенным контролем идентифицирован – это лист «Periods», идем в него и смотрим, что там не так.

Обнаруживаем, что в контрольном столбце S зафиксировано две ошибки: во-первых, в ячейке Q16 стоит значение, которого нет в выпадающем списке, по причине того, что мы сами это значение поправили, а, во-вторых, эта вполне естественная ошибка повлекла за собой еще одну ошибку со значением в ячейке Q33, выпадающий список которой связан со значением в ячейке Q16 (см. занятие 1 курса обучения финмоделированию).

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

Вот как-то так и создается система контроля за корректностью финансовой модели.

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

Следующие уроки: Прил. 2 к занятию 1. Выпадающие списки финмодели