Как не надо «усовершенствовать» чужие модели в Эксель или программист опасен своим креативом

Усовершенствование на пустом месте!
исходные данные

       Ниже приведена компиляция двух постов с хабры, как некие программисты усовершенствовали «финансовую модель» некой компании — самое смешное, что вся возня вокруг «модели» — на полном серьезе — «кол-во клиентов х средний чек = выручка«. По видимому, сторонние «программисты» напугали «Бабаем неэффективности» невежественного заказчика и » в 10 раз увеличили производительность» — и не нашли ничего  лучше, чем похвастаться этим на хабре)))))) При этом,  такими штуками, как «для чего нужна сводная таблица» и «прогнозирование по выборке» — очевидно по религиозным соображениям — воспользоваться им не повезло)))))). Спасибо, что хоть ВПР и ДВССЫЛ грамотно использовали))): типично программистское  «у заказчика все неправильно, а вот мы как все круто бы сделали, если бы «бы» не мешала». В общем и смех и грех.

Собственно, сам текст:

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

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

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

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

Это все отличные намерения, но тут мы сталкиваемся с суровой реальностью: методологические и технические навыки для выполнения этих задач в конкретных предприятиях откровенно слабы. Модели неудобные, быстро не изменяемые, не обновляемые, легко ничего не объясняется, файлы не удобные, а разрезы получить невозможно или очень долго. Давайте посмотрим конкретный пример, где всё плохо и как это можно исправить.

Где будет строить модель типичный сотрудник отдела финансов? Конечно в Excel. Для этого есть несколько по настоящему хороших причин:

  1. Опыт работы есть только с этим средством.
  2. Результат работы легко передать заказчику.
  3. Можно изменить любую деталь в модели.
  4. Excel довольно просто интегрирует в себя получение, хранение, обработку, прогнозирование, представление и визуализацию данных.
  5. Можно сделать так, чтобы потребители вашей работы легко разбирались в том, что вы им передали.
  6. Позволяет организовать простой интерактив и работу с моделью.

Одна из самых простых и одна из самых распространенных моделей прогнозирования выручки это очень простая формула: количество клиентов за период * средний чек = выручка.
То, что было до нас выглядело так (данные, конечно же заменены на демонстрационные):

Как не надо "усовершенствовать" чужие модели в Эксель или программист опасен своим креативом

Какие проблемы есть на этом листе:

  1. Исходные данные по 2017 и 2018 году введены как значения. Подразумевается, что для реализации различных версий бюджета будут напрямую исправляться эти значения. Это большой объем работы (т.к. таких листов около 30 по различным подразделениям), в котором наверняка будут сделаны ошибки, перепутаны столбцы и колонки.
  2. Хотя 2018 год введен как значение, он еще не закончился, поэтому там введено значение с учетом прогноза, который сделан где-то отдельно.
  3. Прогноз сделан на целый год. Среди требований к прогнозированию было составить помесячный прогноз с одной стороны, а с другой стороны отобразить его в годовом разрезе, т.к. помесячный прогноз трудно анализировать и оценивать из-за обилия цифр. Соответственно одна из трудностей для этой модели — построить поверх еще одну модель, которая разбивает данные по месяцам с учетом сезонности. Все «задом наперед», не годовой результат из месячных, а месячные из годового.
  4. Исходные данные для модели нигде явно не фигурируют, нет на них ссылок и не ясно, верны ли те цифры, что мы видим, верно ли они извлечены из хранилища и суммированы.
  5. Если появится желание сделать перегруппировку состава отделов, то эта модель будет полностью выброшена.
  6. В следующем году тут мало что можно переиспользовать.

Этот лист это прогнозирование по одному из подразделений предприятия. Всего таких листов около 30. Все эти листы объединяются на лист всего предприятия в двух разрезах: по подразделения и по типам отделов. Грубо говоря у вас в каждом подразделении есть отдел, по производству упаковки. Вы хотели бы видеть и общий результат по подразделениям, а отдельно общий результат в разбивке по разным специализациям, типа производства упаковки. Лист выглядит концептуально так же, но он является суммой результатов на прошлых 30 листах.
Это суммирование реализовано самым простым способом: перебором всех ячеек нужных для суммирования. Т.к. не каждое подразделение содержит все отделы и положение строк в 30 листах отделов может быть разное, то для сборки совокупной выручки по отделам сотрудник должен был составить десятки формул, в которых явным образом указал, какие ячейки он хочет складывать.

Какие проблемы мы видим на обобщенных листах?

  1. Явный перебор ячеек для суммирования, а значит если кто-то подменит значения или смысл ячейки, на которую мы ссылаемся при суммировании, мы это не заметим и нам придется долго эту ошибку искать.
  2. При изменении структуры компании нам придется не только изменять листы подразделений, который были затронуты изменениями, но и исправлять лист с общей сборкой, ведь там не будет новых объектов, а удаленные будут выдавать ошибки.
  3. При изменении разреза, под которым мы хотим посмотреть на подразделение эта модель вообще ничего не может сделать и просто не работает. Если в отделах появится какая-то большая детализация, то по сути нам придется создавать еще один такой же обобщающий лист (который будет иметь уже в 10 раз больше прямых ссылок, которые надо «протыкать», где-то уже от 1000 до 10000).
  4. Модель вообще полностью разрушается, если изменения в компании приведут к другой группировке сущностей. Вся эта работа просто идет на выброс.

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

Почему-то многие компании такие задачи готовы «закидывать телами» трудолюбивых сотрудников, кому не хватает и опыта и навыков сделать все проще, быстрее и удобнее. При этом это даже не вопрос денег. Реализация такого файла отнимает около 2 месяцев работы человека и то, без удовлетворения всех требований. А более разумный подход к организации работы с данными потребует у вас 1 недели не напряженной работы! За 2 месяца подготовки «плохим» способом вы теряете не только больше денег, но и кучу времени и нервов, т.к. принимая результат будете десятки раз ловить ошибки. Это тупейшая растрата ресурсов. Это как раз тот случай, когда простыми шагами, вы можете получить 10-ти кратный рост производительности труда!

О том, как мы добились повышения производительности труда в 10 раз и переделали модель см. ниже.

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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