Учет небольшого склада через Telegram-бот и Google-таблицу

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

О чем пойдет речь

Мы, команда разработчиков helpexcel.pro, постоянно создаем готовые решения в Гугл таблицах для управленческого учета в малом бизнесе и бесплатно публикуем их в открытом доступе.

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

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

Основные функции таблицы

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

В классическом виде представлены ABC и XYZ анализы товаров, что дает аналитику о том, в какие товары стоит дальше вкладываться.

Ниже делимся ссылкой на таблицу, но советуем вам ознакомиться с описанием, чтобы понять принцип ее устройства и использовать 100% возможностей.

Чтобы полноценно воспользоваться таблицей создайте ее копию на своем Гугл диске.

Описание работы таблицы

Для нас это уже не первая версия складской таблицы, и мы предусмотрели потребности пользователей в разных способах подсчета закупочной цены.Для корректного подсчета прибыли, рентабельности и ценности товаров на складе нужно выбрать метод расчета закупочной цены. Настройка находится на листе “Справочник”:

Учет небольшого склада через Telegram-бот и Google-таблицу

В рамках функций таблицы заложено 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-бот и Google-таблицу

Далее переходим к учетной части таблицы.

Принцип работы каждого листа рассмотрим по отдельности.

Лист закупки

Учет небольшого склада через Telegram-бот и Google-таблицу

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

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

Для обеспечения хранения большого числа строк и возможности связи с ботом к таблице подключена внешняя база данных. Для просмотра исторической информации можно выбрать период и нажать кнопку “обновить”.За счет этой настройки мы ускорили работу таблицы и не выводим невостребованные строки с данными.

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

Для ввода данных через telegram-бота нужно перейти по ссылке https://t.me/help_excel_storage_bot. Если вы правильно выполнили настройку бота, то у вас появится блок из 4-х кнопок:

Учет небольшого склада через Telegram-бот и Google-таблицу

Выберите “Закупки”.

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

Для отображения информации, введенной через бота, нужно нажать кнопку “обновить”.

Лист отгрузки

Учет небольшого склада через Telegram-бот и Google-таблицу

Поля таблицы отгрузок работают по тому же принципу, что и лист закупок.

В правой части таблицы представлена область расчета себестоимости и показателей дохода от реализации товара. Показатели рассчитываются при нажатии на кнопку “обновить” или “сохранить”.

Учет небольшого склада через Telegram-бот и Google-таблицу

Лист аналитики

Данный лист состоит из трех блоков.

Область расчета складских запасов:

Учет небольшого склада через Telegram-бот и Google-таблицу

Дебиторская и кредиторская задолженность:

Учет небольшого склада через Telegram-бот и Google-таблицу

Сумма задолженности отражается в столбце “задолженность” если вы не поставили галочку напротив суммы в столбце “статус оплаты”. Тогда таблица считает, что платеж еще не проведен.

Учет небольшого склада через Telegram-бот и Google-таблицу

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

Лист ABC анализа

Учет небольшого склада через Telegram-бот и Google-таблицу

Все товары делятся на 3 группы::

А: приносят 80% продаж

В: приносят 15% продаж

С: приносят 5% продаж

Лист XYZ анализа

Учет небольшого склада через Telegram-бот и Google-таблицу

Отчет показывает колебания спроса, товары разбиваются на 3 группы:.

X — товары продаются стабильно в течение года.

Категория Y — товары сезонного спроса.

Категория Z — спрос нерегулярен, какие-либо тенденции отсутствуют.

Лист ABC XYZ

На данном листе объединяется информация ABC и XYZ анализа. Товарная матрица делится на 9 групп:

  • AX – приносящие значительную долю выручки со стабильным спросом;
  • AY – большая доля выручки, но спрос подвержен колебаниям;
  • AZ – хорошо продаются, но спрос плохо поддается прогнозированию;
  • BX – средние объемы выручки, низкие колебания спроса;
  • BY – средние объемы выручки, колебания спроса в пределах нормы, например, сезонные;
  • BZ – средние объемы выручки, сложно прогнозируемый спрос;
  • CX – низкая доля в прибыли, стабильный уровень спроса;
  • CY – невысокий уровень прибыли при средних колебаниях спроса;
  • CZ – низкий уровень прибыли, высокие колебания спроса.

Пример:

Учет небольшого склада через Telegram-бот и Google-таблицу

Настройка таблицы

Для начала нужно создать копию таблицы, как показано на картинке ниже:

Учет небольшого склада через Telegram-бот и Google-таблицу

В таблицу встроен скрипт, который считает себестоимость товаров. Для того, чтобы таблица работала правильно, нужно авторизоваться. Для этого нажмите на значок обновления на листе “Отгрузки”:

Учет небольшого склада через Telegram-бот и Google-таблицу

У вас появится такое поле:

Учет небольшого склада через Telegram-бот и Google-таблицу

Нажмите на “Продолжить”.

Выберите свой google-аккаунт

В появившемся окне нажмите на “Дополнительные настройки”:

Учет небольшого склада через Telegram-бот и Google-таблицу

Затем, на “Перейти на страницу “script”:

Учет небольшого склада через Telegram-бот и Google-таблицу

В следующем окне пролистайте вниз и нажмите на кнопку “Разрешить”:

Учет небольшого склада через Telegram-бот и Google-таблицу

Отлично, авторизация скрипта пройдена!

Настройка Telegram-бота

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

Закупки и отгрузки можно создаются через telegram-бота. Найти его можно по ссылке: https://t.me/help_excel_storage_bot.

Для его настройки вам нужно на листе “Пользователи бота” добавить номера сотрудников, которые будут работать с ботом.

Учет небольшого склада через Telegram-бот и Google-таблицу

Номер телефона обязательно нужно ввести через цифру 7, без “+” и других дополнительных знаков. Так, как показано на скриншоте.

После нажатия на кнопку сохранить вам нужно будет принять соглашение об обработке данных.

Учет небольшого склада через Telegram-бот и Google-таблицу

Затем вам нужно перейти на страницу бота @help_excel_storage_bot.

В диалоге с ботом следует нажать на кнопку “Запустить” (если вы через ПК) или на кнопку “Старт” (если вы через телефон):

Учет небольшого склада через Telegram-бот и Google-таблицу

После этого также нужно будет принять соглашение об обработки данных и ввести свой телефон в формате +7##########.

На этом настройка чат-бота окончена. Подробное описание работы чат-бота и таблицы будет далее.

Спасибо, что полностью ознакомились с нашим руководством!

Если у вас еще останутся вопросы по таблице, то можете задать их в нашем телеграмм-чате:

3535
33 комментария

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

4
Ответить

Решил чекнуть кто это такой, с самой первой ссылки начинается )

Ответить

молодцы, занимаюсь разработкой подобных вещей

3
Ответить

А нам нужны сейчас специалисты по Гугл таблицам в команду. Если интересно, напишите мне в лс.

3
Ответить

Интересно!
Даже поностальгировал. Делал похожее решение на экселе в качестве временного, когда только запустили рознично-логистический бизнес. Но у нас ещё было регулярное перемещение товара между «складами» ... было весело!)
Больше пары месяцев так работать стало невозможным, и всё было перенесено в самодельное веб-приложение.

P.s. Отдельным развлечением были FIFO и LIFO, но я решил учитывать каждую отдельную единицу, и в первых итерациях формулы выглядели так:

2
Ответить

По сложности чтения это похоже на многослойный тернарный оператор

2
Ответить

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

1
Ответить