Расчёт динамики с отрицательными значениями

Расчёт динамики с отрицательными значениями

Финансисты периодически сталкиваются с расчетом процентного изменения от периода к периоду. Рассчитать такое изменение, что может быть проще?

Стандартная формула расчета динамики

Нужно вбить одну из двух формул:

  1. (Новое_значение – Старое_значение) / Старое_значение
  2. (Новое_значение / Старое_значение) -1

Обе формулы приводят к одному и тому же результату только когда все числа положительные. В этом случае совсем не важно какую формулу выбирать.

Казалось бы, всё просто!

Да, но нет! 🙂

Все трудности начинаются в тот момент, когда нужно посчитать изменение на отрицательных данных. Классическая ситуация чистая прибыль сменяется убытком и наоборот. Для публичных компаний это оборачивается газетными заголовками о том, что чистая прибыль компании по МСФО упала на 500%(!!!) и так далее и тому подобное.

Да, действительно при смене значений с положительных на отрицательные Excel обычно «буксует» и начинает выдавать не корректные значения. Ещё бы, ведь Excel парень простой, ему вбили одну из двух формул, и он по ним считает. Приведу пример, чтобы было понятно о чём идёт речь:

Стандартная формула работает почти безотказно, но есть как минимум несколько проблем:

  1. В первом периоде 20Х1 ошибка #VALUE!, т.к. нет базы для расчета.
  2. В третьем периоде 20Х3 формула выдала падение на 100%, хотя по смыслу прибыль явно выросла
  3. В четвертом периоде формула выдает ошибку деления на ноль #DIV/0!

Получается, что наиболее часто используемая формула фактически работает только с значениями больше 0.

Как быть в такой ситуации?

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

Метод абсолютных значений

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

Таким образом формула становится примерно такой:

(Новое_значение – Старое_значение) / ABS ( Старое_значение )

Формула ABS() в Excel используется для расчета модуля.

Из открыых источников мне даже удалось узнать, что авторитетнейшее финансовое издание The Wall Street Journal использует этот метод. Почему бы не взглянуть на него?

Итак, я рассчитал изменение с учётом метода абсолютных значений.

Соответственно применение данной формулы даёт некое улучшение в периоде 20Х3, но особо легче не стало. Как и раньше остаются проблемы с первым периодом и периодом 20х4.

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

Пример №1. Переход от отрицательного к положительному значению

В примере наглядно видно, что чем меньше отрицательное значение (имеется в виду что -60 меньше -10), тем меньше % изменение. Не знаю как вам, но мне кажется это немного странным. Получается, что в абсолютных величинах изменение больше, а в процентах меньше. Мистика!

Пример №2. Переход от положительного к отрицательному значению

В этом примере также получается немного странно. Большее отрицательное изменение не означает большего процентного изменения. Всё это нужно учитывать работая с Excel, ведь не всегда можно получить релевантные результаты. И с этим можно жить, если держать это в уме.

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

Отображение положительных и отрицательных изменений

Альтернативное решение методу абсолютных значений – это отображение положительного и отрицательного изменения в случае, если одно из значений будет положительным.

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

Для отображения положительного и отрицательного изменений пришлось применить достаточно комплексную формулу:

= ЕСЛИОШИБКА ( ЕСЛИ ( МИН (Старое_значение ; Новое_значение )<=0; ЕСЛИ( (Новое_значение  - Старое_значение) > 0; "Позит."; "Отриц." ); (Новое_значение / Старое_значение) -1; "н.п.")

Что делает формула:

  1. Проверяет выдает ли формула ошибку и выдает «н.п.» (не применимо). В частности деление на ноль и ошибка первого периода.
  2. В случае если одно из значений отрицательно, то формула рассчитывает положительное или отрицательное изменение и выдает значения "Позит." или "Отриц."
  3. Если отрицательных значений нет, то применяется стандартная формула расчета динамики.

Вывод

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

Только вам, уважаемый читатель решать хотите ли вы использовать предложенные в этой статье подходы, но если вам понравилось или есть что добавить – оставляйте свои комментарии или делитесь статьёй в социальных сетях.

Удачного дня!

No Comments

Post a Reply

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

s2Member®