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

Какие ключевые проблемы в описанной модели:

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

Что мы предложили:

В начальной модели нигде не фигурировали исходные данные на которых она была построена. Мы предложили внести эти данные в формате 2-ой нормальной формы в сам файл Excel на 2 отдельных листа (продажи и кол-во клиентов). Благо, данные по продажам в нашей агрегации по месяцам — это всего лишь десятки тысяч строк, а не миллионы. Так же мы настроили получение этих данных при помощи Power Query напрямую из базы данных.
Мы создали лист моделирования, который состоит из 3-х блоков:
- Сводная таблица выручки
- Сводная таблица кол-ва клиентов
- Расчетная таблица средних чеков
  Каждая сводная это сводная таблица построенная на исходных данных в нужной для текущего моделирования детализации по отделам и подразделениям, в нужной детализации по периодам (месяцам).
В Листе моделирования мы построили простые модели прогнозирования на базе исторических временных рядов. Мы продлевали ряды кол-ва клиентов и средний чек, а общую прогнозную выручку считали как произведение этих величин. Просмотрев данные мы придумали 3 модели прогнозирования: медиана по прошлым периодам, экспоненциальное тройное сглаживание и обнуление (когда нам нужен 0 прогноз).
Вычисления среднего чека (факта) и выручки (прогноза) производится не путем ссылки на ячейки, а с использованием ВПР и отметки смещения, что позволяет сделать расчеты устойчивым к изменениям в исходных данных.
Понятно, что теперь модель не читабельная пользователем, т.к. там слишком много значений. Для этого мы построили отдельные листы подразделений. Каждый лист имеет ячейку, которая выбирает, какие данные должны быть просуммированы в этот лист. На основе ВПР мы подтягиваем в лист данные с модельного листа.
Само создание 30 листов по подразделениям сделано по специальной процедуре. Сначала создается первый лист, одного из подразделений, который содержит все возможные названия отделов. Если какого-то отдела в подразделении нет, то формулы подтягивают туда 0. Чтобы сделать все 30 подразделений, мы создаем дубликаты и меняем названия в управляющей ячейке (она используется для формирования ключа ВПР) и у нас оказываются нужные данные в форме представления. ВПР умеет использовать в качества ключа более 1 ячейки, если вы используете трюк: объедините нужные вам ячейки в одну при помощи конкатинации (функция СЦЕПИТЬ или символ &).
В форму представления добавлен элемент, позволяющий управлять моделью: простой множитель к прогнозным значениям среднего чека и кол-ва. Этот элемент собирается на специальный технический лист при помощи функции ДВССЫЛ, которая позволяет использовать сгенерированную ссылку. С этого технического листа все эти корректировки при помощи ВПР переносятся на лист с моделью.
Листы обобщения больше не является суммированием листов представления, а строятся точно так же, как и все остальные листы — путем суммирования данных на листе с моделью. В итоге представления являются чистыми представлениями и не имеют зависимостей между собой.

Что мы получили:

Всегда понятно из каких цифр мы получили данные (т.к. сохранился запрос power Query).
Мы можем изменить данные не сломав модель.
Изменения в структуре и иерархии потребует небольших доработок (нужно изменить только названия в 1 листе представления и потом сделать его дубликаты).
Мы радикально сократили кол-во потенциальных ошибок, т.к. большая часть данных заполняется при помощи формул, ссылок и ключей.
Заказчик получил интерактивный прогноз, в котором он может менять значения сам и тут же получать прогноз.
Смогли одновременно удовлетворить требованиям о том, что нам нужны данные и в годовом и месячном разрезе.
Можно использовать в следующем бюджетном периоде.
Можно менять модели прогнозирования, если эти нам покажутся не подходящими.

Почему мы решили остаться в экселе, а не переделали это на какие-то другие технологии?

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

Сколько нам понадобилось времени: примерно 5 рабочих дней, где 1 человек тратил по 2-4 часа в день и по итогам дня мы с ним делали ревью результатов.

Как мы переделывали плохое прогнозирование на чуть более хорошее (продолжение)