В данной статье описана модель в Excel для отслеживания движения товара в процессе продаж: закупка - передача в торговый зал (на склад) - продажа - остатки.
Для начала определим название столбцов, для нашей модели. Для примера возьмем такой шаблон
Так как процесс начинается с приобретения товара для продажи, заполним данные о первой покупке
Важно!!! Эта таблица будет обновляться ежедневно, на ее основе будут строиться сводные таблицы и аналитические формы, чтобы каждый раз не переписывать ссылки и формулы, нужно сделать её «умной». Как сделать таблицу "умной" я рассказывала в предыдущих статьях, на всякий случай напомню. Выделяем таблицу, входим в меню в раздел «Вставка» и нажимаем кнопку «Таблица», после чего таблица примет формат как на рисунке)
Курсы от Skillbox со скидкой до 65% по ссылке.
От себя лично, как автор канала про excel, я рекомендую курс "Excel + «Google Таблицы» с нуля до PRO" очень содержательный курс, замечательный лектор. Как бы хорошо вы не знали Excel, в этом курсе вы найдете много полезной информации для работы с электронными таблицами в Excel или Google. Каждый урок небольшое открытие. (Чтобы купить этот курс со скидкой нужно пройтись по этой ссылке и найти его в каталоге)
Теперь о заполнении данных таблицы.
Данные даты (число, месяц, год (если нужно)) лучше водить в разных столбцах, так потом легче работать с данными.
В нашем примере будет 2 товара, которым присваиваем числовой код, артикул (если нужно). В таблицу артикул подтягивается автоматически, аналогично ценам в столбце «Прейскурант», как это делать будет рассказано ниже по тексту.
В этом примере, в столбце Прейскурант отражена цена реализации с НДС. Она подтягивается автоматически. В этом примере цена продажи не пересчитывается после каждой закупки, а с определенной периодичностью утверждается в прейскуранте.
Чтобы цены из прейскуранта подтягивались автоматически, в рабочей книге Excel создаем отдельный лист, с названием «Прейскурант» и делаем на нем простую таблицу с наименованием товара и ценой. Важно!!! Наименования товара в таблице с моделью и прейскуранте должны быть абсолютно одинаковыми.
Для того чтобы подтянуть данные с листа «Прейскурант» в основную таблицу можно использовать формулу ВПР (для столбца «Код (артикул) используется аналогичная формула)
Вот эта формула крупным планом
Для заполнения формулы со ссылками на "умную таблицу" лучше пользоваться диалогом, так как ссылки сложны для заноса вручную.
На основании первичных документов заносится информация по поставщику, номеру документу, количеству, стоимости и ставке НДС.
Столбцы «Цена ед., с НСД» и «Цена ед., без НДС» расчетные, формулы там простые подробно расписывать не будут.
Далее необходимо передать купленный товар на склад или в торговый зал для дальнейшей продажи по ценам из прейскуранта. После этого начинается сам процесс продаж. Все эти события отражаться в таблице
При занесении данных формат «умной таблицы» распространяется на новую строку автоматически. В столбце «Признак» обязательно указывать характеристику движения товара «Покупка», «Зал» - передача в зал с наценкой, « Продажа». Названия признаков всегда должны заноситься одинаково.
На основании этой таблицы можно построить целый ряд сводных таблиц, например, таких
Но удобнее работать в постоянных формах, например таких
Формы с формулами (при написание формулы необходимо пользоваться диалогом)
Далее я покажу, как работают эти таблицы.
Добавим новую строку (стр. 11) и попробуем продать Товар 1 в количестве 30 единиц, т.е. больше остатка. Таблица «Движение товара (продажи)» сразу покажет ошибку – отрицательный остаток. (Выделение ошибки делается при помощи условного форматирования)
Примем Товар 1 от Поставщика 2. Поставщик 2 работает с НДС, поэтому цена с НДС у него выше, а без НДС ниже, чем у Поставщика 1
Как это отразится на наших аналитических таблицах
В таблице «Движение товара (закупка)» увеличились количество и сумма в части поступлений от поставщика. Но в зал это количество еще не оприходовано, о чем нам сообщает столбец остаток. Соотношение Поступило/Купили показывает отношение стоимости переданного в зал к стоимости купленного у поставщика. Это столбец является контрольным, в данном случае он показывает, что не все товары переданы на склад. Его также использовать как предварительный контроль за уровне наценки (предварительный, т.к. суммы с НДС, а наценка считается по суммам без НДС, но в случае роста цен или изменения ставок НДС у поставщиков динамику можно отследить, если задать критерий уровня этого соотношения)
Если внести строку передачи на склад, то таблицы изменятся следующим образом
После поступления товара в зал, все данные с ошибками исправились. Несмотря на то, что Товар 1 от Поставщика 2 поступил в зал позже, остатки товара в зале не показывают ошибку, так как эти таблицы не отслеживают остатки по датам, а только полностью по всей таблице.