Рассказываю, как я придумал калькулятор для бизнеса в Экселе и до чего дошел в итоге

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

Для тех, кто впервые видит меня на VC, представлюсь. Меня зоут Саша. Свой опыт я описал в посте-знакомстве. Один из проектов, которым я сейчас занимаюсь — развитие производства рольставней, ворот и шкафов на парковочное место. Этот бизнес я начинал с партнером еще 6 лет назад, и на тот момент никто не из нас понимал, как посчитать точную себестоимость изделия.

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

Проблема расчета стоимости нашей продукции в том, что конфигурация конкретного изделия меняется нелинейно в зависимости от размера.

Если мы говорим о конструкции размером 2 на 2 метра, набор комплектующих у нее один. Если мы говорим о конструкции 2 метра 60 см на 2 метра 60 см, то за счет увеличения площади роллетного полотна изделия меняются такие параметры:

— технические характеристики пружины

— толщина вала

— параметр намотки полотна в коробе (соответственно размер короба увеличивается)

— и еще куча мелочей

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

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

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

Но это решение нам не подошло:

— это долго;

— не совсем практично при выездной работе;

— интерфейс довольно сложный;

— все ломается и не работает там, где отсутствует интернет.

Да, помимо самой роллеты у нас есть стеллаж, материалы боковых стенок и т. д. Их стоимость тоже нужно учитывать. Тупик.

Я заполучил у другого дилера прайсы — мне выдали архив с сеточными прайс-листами. Вспоминаем морской бой. Ширина 2000, высота 2000! Выстрел! На пересечении координат — цена изделия. Правда шаг — от 50 до 100 миллиметров. И точность — в молоко.

Долгое время мы все-таки приучались к «сетке» пока у меня возник гениальный замысел.

Соответсвенно я могу преобразовать сетки, в таблицу формата ширина | высота | цена. И сделать на этой основе калькулятор на сайте.

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

Прицепом к ТЗ я отдал готовую SQL базу со всеми вариациями, нужно было лишь написать оболочку к нему.

Когда разработчик отдал мне результат, я осознал, что разницы с тем, что есть у дилера и у меня — нет. Это был провал.

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

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

Благо у меня на тот момент был весь массив исходных данных из сеточных прайс-листов в привычном табличном виде. Я вычислил квадратуру и построил график.

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

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

Дальше эту формулу я внедрил в нашу CRM-систему Planfix. Там есть отличный функционал по использованию вычисляемых полей. Я добавил внутри CRM-системы сущность как шкаф с возможностью установить параметры.

Ширина, высота, количество стенок и так далее. И в необходимые поля я вставил формулу в зависимости от того, что мы рассчитываем.

Вот формулы, с которыми работает Planfix:

Страшно, но терпимо. Настраивать нужно всего один раз. Правда, ошибаться нельзя. Как сапер — одна лишняя скобка и пиши пропало. Я даже специально выделял время на формулы по утрам, чтобы максимизировать вероятность успеха. Получилось.

Для инфляционных рисков я заложил коэффициенты, которые вынес во внешний конфиг. В код теперь не лезу.

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

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

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

Буду рад, если мой опыт кому-то будет полезен. Заходите на мой Telegram-канал. Там анонсы и всякие файлы из статей.