Учет небольшого склада через Telegram-бот и Google-таблицу
Организовать удобный учет и оцифровать процессы можно при помощи простейших и доступных всем инструментов. В данной публикации мы поделимся исходниками своей наработки, которую можно настроить по описанной инструкции или использовать как идею для реализации в своем бизнесе.
О чем пойдет речь
Мы, команда разработчиков helpexcel.pro, постоянно создаем готовые решения в Гугл таблицах для управленческого учета в малом бизнесе и бесплатно публикуем их в открытом доступе.
В этот раз мы поделимся своими наработками для учета небольшого склада. Это мини-сервис для учета небольшого склада, созданный на основе Гугл таблицы с возможностью ввода данных через Телеграм-бота.
Решение подойдет для небольших интернет-магазинов или, например, для учета расходных материалов.
Основные функции таблицы
Таблица устроена так, что в ней есть область для внесения справочников, область для учета приходов, область для учета событий и область аналитики. В качестве аналитики формируются отчеты о дебиторской и кредиторской задолженности, остатки по складу и расчет себестоимости.
В классическом виде представлены ABC и XYZ анализы товаров, что дает аналитику о том, в какие товары стоит дальше вкладываться.
Ниже делимся ссылкой на таблицу, но советуем вам ознакомиться с описанием, чтобы понять принцип ее устройства и использовать 100% возможностей.
Чтобы полноценно воспользоваться таблицей создайте ее копию на своем Гугл диске.
Описание работы таблицы
Для нас это уже не первая версия складской таблицы, и мы предусмотрели потребности пользователей в разных способах подсчета закупочной цены.Для корректного подсчета прибыли, рентабельности и ценности товаров на складе нужно выбрать метод расчета закупочной цены. Настройка находится на листе “Справочник”:
В рамках функций таблицы заложено 3 вида расчета: по средней цене прихода, метод FIFO и метод LIFO.
По средней цене прихода
Этот метод принимает за себестоимость среднее значение за указанный вами период.
Возьмем для примера период в 1 месяц.
Месяц назад закупочная цена была 1000 рублей, 16 дней назад - 1050 рублей, а 3 дня назад - 1000 рублей.
Для данного метода принимается средняя закупочная цена - 1050 рублей.
Метод FIFO
«Первым пришёл — первым ушёл»
Разберем этот принцип на примере. Вы приобрели 2 принтера по 5 000 ₽ за шт. и ещё два абсолютно таких же, но уже по 6 000 ₽.
У вас хотят купить один из них. Для продавца не важно, какой из них взять - они одинаковые. Но бухгалтеру важна общая стоимость товара на складе.
По методу FIFO нужно взять принтер, который поступил на склад раньше всех. То есть за 5 000 рублей.
В таком случае на складе останется 1 принтер стоимостью 5 000 рублей и 2 принтера за 6 000 рублей.
Общая стоимость 3-х принтеров на складе - 17 000 рублей.
Метод LIFO
«Последним пришёл — первым ушёл»
Для удобства возьмем тот же пример с принтерами. Все суммы и количества оставим теми же.
По методу LIFO мы должны взять принтер, который поступил на склад позже всех. За 6 000 рублей.
Тогда на складе останется 2 принтера стоимостью 5 000 рублей и 1 принтер стоимостью 5 000 рублей.
Стоимость оставшихся 3-х принтеров - 16 000 рублей.
Продолжаем заполнять справочник таблицы переменными. Укажите ваш часовой пояс, заполните справочники товаров, поставщиков и покупателей.
Далее переходим к учетной части таблицы.
Принцип работы каждого листа рассмотрим по отдельности.
Лист закупки
Информация о всех пополнениях склада фиксируется на данном листе или через Телеграм-бота.
Некоторые поля таблицы расчетные, в них работают формулы - не заполняйте эти столбцы.
Для обеспечения хранения большого числа строк и возможности связи с ботом к таблице подключена внешняя база данных. Для просмотра исторической информации можно выбрать период и нажать кнопку “обновить”.За счет этой настройки мы ускорили работу таблицы и не выводим невостребованные строки с данными.
Если же какая-то позиция была введена по ошибке или уже не актуальна, то ее можно удалить. Для этого в последнем столбце выделите галочкой нужные строки и нажмите на кнопку “удалить”.
Для ввода данных через telegram-бота нужно перейти по ссылке https://t.me/help_excel_storage_bot. Если вы правильно выполнили настройку бота, то у вас появится блок из 4-х кнопок:
Выберите “Закупки”.
Далее, следуя диалогу бота, вы вводите информацию о закупке. Поля диалога бота идентичны полям таблицы.
Для отображения информации, введенной через бота, нужно нажать кнопку “обновить”.
Лист отгрузки
Поля таблицы отгрузок работают по тому же принципу, что и лист закупок.
В правой части таблицы представлена область расчета себестоимости и показателей дохода от реализации товара. Показатели рассчитываются при нажатии на кнопку “обновить” или “сохранить”.
Лист аналитики
Данный лист состоит из трех блоков.
Область расчета складских запасов:
Дебиторская и кредиторская задолженность:
Сумма задолженности отражается в столбце “задолженность” если вы не поставили галочку напротив суммы в столбце “статус оплаты”. Тогда таблица считает, что платеж еще не проведен.
Также, если вы поставили в столбце “дней отсрочки” количество дней, и эта дата уже прошла, то сумма будет отражена в столбце “просроченная задолженность”.
Лист ABC анализа
Все товары делятся на 3 группы::
А: приносят 80% продаж
В: приносят 15% продаж
С: приносят 5% продаж
Лист XYZ анализа
Отчет показывает колебания спроса, товары разбиваются на 3 группы:.
X — товары продаются стабильно в течение года.
Категория Y — товары сезонного спроса.
Категория Z — спрос нерегулярен, какие-либо тенденции отсутствуют.
Лист ABC XYZ
На данном листе объединяется информация ABC и XYZ анализа. Товарная матрица делится на 9 групп:
- AX – приносящие значительную долю выручки со стабильным спросом;
- AY – большая доля выручки, но спрос подвержен колебаниям;
- AZ – хорошо продаются, но спрос плохо поддается прогнозированию;
- BX – средние объемы выручки, низкие колебания спроса;
- BY – средние объемы выручки, колебания спроса в пределах нормы, например, сезонные;
- BZ – средние объемы выручки, сложно прогнозируемый спрос;
- CX – низкая доля в прибыли, стабильный уровень спроса;
- CY – невысокий уровень прибыли при средних колебаниях спроса;
- CZ – низкий уровень прибыли, высокие колебания спроса.
Пример:
Настройка таблицы
Для начала нужно создать копию таблицы, как показано на картинке ниже:
В таблицу встроен скрипт, который считает себестоимость товаров. Для того, чтобы таблица работала правильно, нужно авторизоваться. Для этого нажмите на значок обновления на листе “Отгрузки”:
У вас появится такое поле:
Нажмите на “Продолжить”.
Выберите свой google-аккаунт
В появившемся окне нажмите на “Дополнительные настройки”:
Затем, на “Перейти на страницу “script”:
В следующем окне пролистайте вниз и нажмите на кнопку “Разрешить”:
Отлично, авторизация скрипта пройдена!
Настройка Telegram-бота
Основное предназначение бота - ввод данных о приходах и отгрузках в таблицу. Он служит больше как вспомогательный интерфейс для возможности мобильного ввода информации.
Закупки и отгрузки можно создаются через telegram-бота. Найти его можно по ссылке: https://t.me/help_excel_storage_bot.
Для его настройки вам нужно на листе “Пользователи бота” добавить номера сотрудников, которые будут работать с ботом.
Номер телефона обязательно нужно ввести через цифру 7, без “+” и других дополнительных знаков. Так, как показано на скриншоте.
После нажатия на кнопку сохранить вам нужно будет принять соглашение об обработке данных.
Затем вам нужно перейти на страницу бота @help_excel_storage_bot.
В диалоге с ботом следует нажать на кнопку “Запустить” (если вы через ПК) или на кнопку “Старт” (если вы через телефон):
После этого также нужно будет принять соглашение об обработки данных и ввести свой телефон в формате +7##########.
На этом настройка чат-бота окончена. Подробное описание работы чат-бота и таблицы будет далее.
Спасибо, что полностью ознакомились с нашим руководством!
Если у вас еще останутся вопросы по таблице, то можете задать их в нашем телеграмм-чате:
Крутая эта штука гугл таблицы, пользуюсь ей как базой данных для тильды. Как говорит Дмитрий Потапенко - для бизнеса нужен только телефон и таблица excel
Решил чекнуть кто это такой, с самой первой ссылки начинается )
Это потому что он активно критикует власть. Думаю, не сложно будет найти, что он и младенцев ест.
Все может быть)
молодцы, занимаюсь разработкой подобных вещей
А нам нужны сейчас специалисты по Гугл таблицам в команду. Если интересно, напишите мне в лс.
Ещё актуально? Делаю таблицы, дашборды и пишу простые скрипты для уведомлений из Гугл таблиц в телеграм по событию
Напишите мне в лс, пожалуйста
Интересно!
Даже поностальгировал. Делал похожее решение на экселе в качестве временного, когда только запустили рознично-логистический бизнес. Но у нас ещё было регулярное перемещение товара между «складами» ... было весело!)
Больше пары месяцев так работать стало невозможным, и всё было перенесено в самодельное веб-приложение.
P.s. Отдельным развлечением были FIFO и LIFO, но я решил учитывать каждую отдельную единицу, и в первых итерациях формулы выглядели так:
По сложности чтения это похоже на многослойный тернарный оператор
В Экселе реально сложновато подобные решения реализуются. Гугл таблицы можно интегрировать с другими сервисами, подключать внешние интерфейсы ввода данных и хранилища. Что позволяет обрабатывать много информации и объединить слаженную работу большого числа пользователей.
Здорово, молодцы!
1)Сам когда-то занимался подобным в ecom: все стороны всегда в плюсе от таких оперативных в разработке полезных решений;)
2) подскажите, сколько по
примерно времени у вас уходит на разработку после выявления требований для подобных систем?
Таблица, которая приведена в статье устроена довольно просто. С момента описания идеи до готовности часов 10 требуется. А если рассматривать типовой проект на создание системы таблиц под какой то бизнес процесс, то в среднем требуется 30 часов разработки.
!
Что имеете ввиду?
Имхо, человек восхищён )
xD
Прочитал описание. Стало интересно. Хочу попробовать, но смущают требования и разрешения вашего скрипта:
Это приложение запрашивает разрешение на выполнение следующих действий:
Просмотр и изменение ваших таблиц (включая их настройки и метаданные)
Создание таблиц
Загрузка и скачивание ваших таблиц
Упорядочивание и удаление ваших таблиц
Просмотр имен и адресов электронной почты тех пользователей, которым вы предоставили доступ к таблицам
Предоставление и отмена доступа к вашим таблицам
Приложение сможет выполнять те же действия, которые доступны вам. Автором изменений при этом будете считаться вы.
Таблицы могут содержать конфиденциальную информацию, например финансовые сведения или личные списки.
Вы можете посмотреть исходный код скриптов предлагаемой таблицы и убедиться в том, что никаких функций кроме заявленных в инструкции в ней нет.
Такой список разрешений почти для любого скрипта необходим, так как скрипт это фактически отдельное приложение.
а почему используется данная конструкция в формулах аналитики?
=ЕСЛИ($A8 = " "; " ";
Каждый разрабатывает как может:)
А что такого в этой связке? Обычная проверка условий.
не, просто интересно почему так сделано и можно ли построить подобную формулу не через если)
(я только учусь экселю и sql)
ЕСЛИ удобно использовать для того, чтобы в зависимости от разных условий работали разные формулы внутри конструкции "если". И эти формулы выводят разные данные, в зависимости от каки-нибудь переключателей, например.
LIFO для чего?
У нас было ряд запросов на создание методов расчета себестоимости LIFO и FIFO, поэтому мы решили реализовать и в данном шаблоне.
Благодаря вам расширил кругозор: https://ru.wikipedia.org/wiki/FIFO_%D0%B8_LIFO
А я раньше думал, что это все придумали, чтобы товар не умер на полке магазина.
Метод ФИФО применяется, например, когда товар закупается партиями, чтобы списывать его по правильной цене и соответственно считать доход.
А можно сюда прикрутить модуль для считывания данных с маркетплейсов типа Озон, Амазон и т.д.?
Да, можно. У маркетплейсов есть api, что позволяет получать данные.
А какой же метод выбрать, если точно не известно на какую именно позицию будет заявка?
узко направленный бот под задачи автора - пытаются с умным видом толкать то что сделали уже какому-то заказчику а теперь масштабируют - удобства - никакова - типа писатели- расписатели в новой теме телеграмм ботов, насчет умности кода тоже вопросы.
Иван, спасибо за ваш комментарий. У этого бота и таблицы не малая аудитория постоянных пользователей.
Вот вы критикуете, но у меня к вам встречный вопрос. У вас есть хоть один продукт, который приносит пользу обществу?
1