Программа по учёту продаж и остатков в MS Excel 2007 для небольших магазинов

Файл-программа в MS Excel для учёта продаж и остатков для небольших магазинов. Данный файл был разработан по запросу одного магазина. Теперь он доступен для скачивания.

Перед использованием программы включите макросы (Параметры Excel -> Центр управления безопасностью -> Параметры центра управления безопасностью -> Параметры макросов -> Включить все макросы)!

Назначение программы:

  • Оприходование товара (приход от поставщика, возврат от клиента)
  • Расходование товара (продажа клиенту, списание недостач)
  • Партионный учёт
  • Актуальная информация о текущих остатках
  • История операций с товаром
  • Анализ финансовых показателей (сумма продаж, валовая прибыль, текущая себестоимость складских запасов)

Структура файла

Файл состоит из 5 листов:

  • Приход(ВНЕСЕНИЕ)
  • Продажа(ВНЕСЕНИЕ)
  • Движение_Товара
  • Валовая прибыль
  • Себестоимость склада

Лист «Приход(ВНЕСЕНИЕ)» предназначен для внесения товара в базу файла на основании приходных накладных от поставщиков (либо возврат от клиента). учёт продаж и остатков

 

 

Алгоритм проведения операций:

1.  Введите данные в столбиках, шапка которых закрашена жёлтым цветом (чтобы разблокировать ввод данных введите пароль – 1).

Пояснения

  • Информация в столбике «Дата внесения прихода» прописывается автоматически. Используется текущая дата и время на вашем компьютере.
  • Столбик «Операция» может содержать два значения: «Приход» или «Возврат». Тип операции Вы можете выбрать самостоятельно, нажав на кнопку вверху таблицы «ТИП ОПЕРАЦИИ». А также Вам будет задан контрольный вопрос по выбору типа операции перед проведением документа. «Приход» – операция по оприходованию товара от поставщика. «Возврат» – операция по возврату товара от клиента.
  • В столбиках «Артикул имеется в базе» и «Название в базе» автоматически будет выводиться информация, если вводимый артикул в столбике «Артикул» уже ранее завозился. Артикул должен быть текстового формата, то есть содержать буквы.

2.   Нажмите на кнопку «ВНЕСТИ», после чего введённые данные будут автоматически перенесены в базу программы на лист «Движение_Товара». После проведения операции строки будут очищены и вы получите сообщение об успешном окончании операции.

Расчёты в столбиках:

  • «Закуп.цена (базовая, у.е.)» – в этот столбик вносите свою закупочную цену, по которой производитель вам отгрузил товар,
  • «Закуп.цена (рабочая, у.е.)» – это вспомогательный столбик, в который вы можете внести корректировку по закупочной цене, если такова имеется. Если же никаких корректировок по базовой закупочной цене нет, тогда вводите такую же закупочную цену, как ввели в столбик «Закуп.цена (базовая, у.е.)»,
  • «Себестоимость, у.е.» – в этом столбике вы должны указать себестоимость продукции. Обычно в торговле себестоимость формируется из закупочной цены и стоимости доставки товара до склада компании. Если вы, например, закупили товар по 100 у.е. и потратили на доставку товара 25 у.е., тогда в данном столбике вам необходимо указать сумму – 125 у.е. Если же вы не высчитываете себестоимость, тогда просто продублируйте сумму рабочей закупочной цены.
  • «Рекоменд.розница,у.е.» – этот столбик вы можете использовать для прописывания с помощью формул рекомендуемой розницы. Например, вы знаете, что обычно нацениваете на себестоимость доставленной продукции 50%, тогда вы можете вписать в столбик форму – себестоимость (у.е.) * 1,50, то есть мы умножаем себестоимость на коэффициент наценки 1,50, тем самым прибавляя 50% желаемой наценки.
  • «Установлена розница, у.е.» – в этом столбике вы должны указать, какую розничную цену вы в конечном итоге установили для продажи товара своим клиентам. Этот столбик был создан специально для того, чтоб у вас была возможность назначить другую розничную цену, чем рекомендует вам та или иная формула. В дальнейшем все расчёты будут происходить от установленной розничной цен и себестоимости.

Ограничения:

  • Пользователь имеет возможность вносить информацию в столбики, шапка которых закрашена жёлтым цветом.
  • Пользователь может внести за один раз не больше 500 артикулов.
  • При внесении отрицательных значений программа будет выдавать сообщение о некорректном вводе данных.
  • При отсутствии заполненных строк (т.е. документ прихода пуст) выполнение операции будет заблокировано.
  • Если вы забудете заполнить какой-либо столбик, программа выдаст вам сообщение об ошибке.

Лист «Продажа(ВНЕСЕНИЕ)» предназначен для списания проданного товара и потерянного товара на складе магазина, который не был найден при инвентаризации склада.учёт продаж и остатков

 

 

 

Алгоритм проведения операций:

1.   Введите данные в столбиках, шапка которых закрашена жёлтым цветом (чтобы разблокировать ввод данных введите пароль – 1):

а) введите полный или частичный артикул, в столбик «Найти Артикул», после чего в соседнем столбике «Артикул» высветится номер товара, найденный в базе на листе «Движение_Товара». Именно данный артикул будет списан в базе после проведения операции,

б) введите количество штук проданного или списанного товара в столбике «Продано, шт.»,

в) если вы решили продать товар по другой цене, чем ранее была установлена («Установлена розница, у.е.»), тогда в столбике «Фактическая цена продажи, у.е.» введите сумму, по которой товар был продан. Это позволяет вам давать дополнительные скидки клиентам. Если вы хотите списать товар, тогда вам необходимо здесь указать цену – 0, в этом случае в столбике «Операция» появится значение «Списание – 0%».

Пояснения

  • Информация в столбиках, шапка которых закрашена зелёным и серым цветами, подбрасывается автоматически с базы данных файла, которая находится на листе «Движение_Товара»
  • «Дата внесения расхода» прописывается автоматически. Используется текущая дата и время на вашем компьютере.
  • Столбик «Операция» может содержать два значения: «Расход» или «Списание – 0%». Тип операции присваивается автоматически в зависимости от продажной цены, которая проставлена в столбике «Фактическая цена продажи, у.е.». Если вы не пожелаете изменять установленную розничную цену и оставите столбик «Фактическая цена продажи, у.е.» или присвоите какую-либо другую продажную цену, тогда в столбике «Операция» автоматически будет прописывать значении «Расход». Если вы в столбике «Фактическая цена продажи, у.е.» установите цену 0 (ноль), тогда в столбике «Операция» будет отображаться значение «Списание – 0%».

2.    Нажмите на кнопку «ПРОВЕСТИ», после чего введённые данные будут автоматически перенесены в базу программы на лист «Движение_Товара». После проведения операции строки будут очищены и вы получите сообщение об успешном окончании операции.Расчёты в столбиках:

  • «Текущая наценка» – в этот столбике вы увидите текущую наценку на себестоимость продукции. Коэффициент наценки рассчитывается следующим образом  – цена продажи делить себестоимость. Если вы внесёте информацию в столбик «Фактическая цена продажи, у.е.», то она будет использоваться для расчёта наценки. Если цена продажи не будет изменена, то в расчёте коэффициента наценки будет участвовать ранее установленная розничная цена из столбика «Установлена розница, у.е.». Коэффициент наценки позволяет вам контролировать ситуацию с финансовой стороны. Видя коэффициент наценки, вы сразу понимаете, продаётся товар в убыток или нет. Если коэффициент наценки меньше 1, тогда товар продаётся в убыток – меньше себестоимости.
  • «Текущий остаток» – этот столбик отображает, какой текущий остаток товара по выбранной позиции имеется на складе. Стоит сказать, что в предложенном файле ведётся партионный учёт, т.е. продаётся сначала тот товар, который приехал первым. Если, например, было две поставки товара одна за другой по 4 и 6 штук и продаж у компании ещё не было, тогда текущий остаток будет отображён – 10 штук. Это позволяет вам видеть, сколько штук товара данной позиции сейчас храниться у вас на складе.
  • «[ИНФО]» Остаток партии» – в этом столбике отображается сколько штук товара на вашем складе находится из первой партии, которая ещё не была распродана. Если мы говорили ранее о том, что было 2 поставки по 4 и 6 штук и продаж товара не было, тогда в данном столбике высветится информация – 4 штуки. Это будет говорить нам о том, что первая партия товара ещё не была распродана и по ней на складе числиться ещё 4 штуки. Очень важно следить за информацией в этом столбике, так как вся информация о ценах отображается для текущей нераспроданной партии товаров.  Так как мы ведём  партионный учёт, мы должны списывать сначала первую партию со склада, затем вторую и так далее. Допустим, 4 штуки пришли компании по себестоимости 10 у.е., а 6 штук – уже по 15 у.е. Как вы понимаете, цена продажи для разных партий товара должна быть разной либо максимальной от наибольшей себестоимости, чтоб товар не был продан в убыток. Для первой партии  мы установили розницу 15 у.е., то есть наценка на себестоимость – 50%, а для второй партии – 22,5 у.е. (также наценка 50% к себестоимости). Если к вам пришёл клиент и захотел купить сразу 6 штук данного товара, то вам нужно обратить внимание, что 4 штуки на складе находятся по первой поставке, а ещё 2 штуки – уже по второй поставке. Цены продажи для обоих партий разные. Вам необходимо будет списать сначала 4 штуки товара одним документом, а затем ещё 2 штуки товара другим документом. Если вы захотите списать сразу 6 штук, то программа выдаст сообщение об ошибке – о некорректном вводе информации в поле «Продано, шт.». Также в это случае в столбике «Кол-во» вы увидите значение «ПАРТИЯ-» на красном фоне, что будет сообщать вам о превышении указанного количества над остатком текущей нераспроданной партии товара.
  • Столбики «[ИНФО] Дата прихода» и «№ партии» отображают информацию о текущей партии, которая ещё не была распродана.

Ограничения:

  • Пользователь имеет возможность вносить информацию в столбики, шапка которых закрашена жёлтым цветом.
  • Пользователь может внести за один раз не больше 100 артикулов.
  • При отсутствии заполненных строк (т.е. документ прихода пуст) выполнение операции будет заблокировано.
  • Если вы забудете заполнить какой-либо столбик, программа выдаст вам сообщение об ошибке.
  •  Сообщения об ошибке:

−    если вы забудете внести информацию в столбик «Продано, шт.», программа вам выдаст сообщение об ошибке и в столбике «Кол-во» будет отображено сообщение «ВНЕСТИ!!!»

−    если вы в столбик «Продано, шт.» внесёте больше штук, чем находится на складе по первоначально несписанной партии (столбик «[ИНФО] Остаток партии»), программа вам выдаст сообщение об ошибке и в столбике «Кол-во» будет отображено сообщение «ПАРТИЯ»

−    если вы в столбик «Продано, шт.» внесёте больше штук, чем находится на складе по всем не распроданным партиям (столбик «Текущий остаток»), программа вам выдаст сообщение об ошибке и в столбике «Кол-во» будет отображено сообщение «НЕДОСТ.!»

Лист «Движение_Товара»  – это база данных программы.учёт продаж и остатков

 

 

Структура таблицы

  • По каждому артикулу информация выводится одним блоком, в котором видна история поступлений и продаж товара. Блоки артикулов отделены между собой строкой с жёлтым фоном.
  • В столбике «Текущий остаток» напротив нераспроданных партий товара отображается текущий остаток данного артикула. Также в столбике «Себестоимость склада, у.е.» отображается сумма себестоимости складских запасов по нераспроданным партиям товара. Значения из столбика «Себестоимость склада, у.е.» используются на листе «Себестоимость склада», на котором находится отчёт по текущей себестоимости складских запасов компании.
  • В столбиках «Валовая прибыль, у.е.» и «Сумма продаж, у.е.» высчитывается валовая прибыль и сумма продаж по каждой операции расхода или списания. Валовая прибыль (у.е.) = Сумма продажи (у.е.) – Кол-во * Себестоимость, у.е. Значения данных столбиков затем используются на листе «Валовая прибыль», на котором находится отчёт по продажам.

Алгоритм проведения операций:

Возврат товара от клиента

Если вам необходимо провести возврат товара от клиента, тогда вы должны найти строку продажи на листе «Движение_Товара» и скопировать данные в диапазоне столбиков E:L, начиная со столбика «Артикул» и заканчивая столбиком «Установлена розница, у.е.». Затем вам необходимо вставить данные на листе «Приход(ВНЕСЕНИЕ)», выбрав при этом тип операции «Возврат». После проведения операции возврат будет оприходован на склад компании в виде отдельной партии товара.

Поиск товара в справочнике

Чтобы найти необходимый артикул в базе, используйте «Окно поиска» (нажатие клавиш Ctrl+F).

Удаление товара из справочника

Если какая-либо операция была введена некорректно, единственный путь удалить операцию – найти строку проведённой операции на листе «Движение_Товара» и удалить данные, начиная со столбика «Артикул» и заканчивая столбиком «Операция» (шапка зелёного фона). Если вы укажите дополнительные столбики, то программа выдаст вам сообщение о невозможности удалять информацию в заблокированных столбиках.

Ограничения:

  • Программно можно внести 50 000 строк на листе «Движение_Товара».

ЗАПРЕЩАЕТСЯ:

  • Переименовывать листы файла и сам файл (возможен сбой работы макросов),
  • Удалять или добавлять столбики и строки на листах файла (возможен сбой работы макросов).

ВАЖНО!!!

Перед тем, как применять данную программу на практике в своём магазине, изначально протестируйте её. Автор программы не несёт ответственность за её работоспособность и окончательную корректность расчётов. Данный файл был разработан по заказу одного магазина и предоставляется для скачивания на бесплатной основе всем желающим. В первую очередь цель  предоставления данного файла  – показать и ознакомить пользователей с обширными возможностями MS Excel.

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


Наши материалы сайта помогут вашему развитию!

Ваш e-mail:


дистанционное обучение In course Стыгарь бесплатный сертификат тест по ms excel открытое тестирование по закупкам, excel, ценообразованию

28 комментариев

  • Роман

    Добрый день, а где же ссылка для скачивания? =)

    • Роман, ссылка на скачивание файла появится после того, как введёте краткие данные о себе. Вводите данные под надписью “Ссылка на скачивание файла появится после внесения кратких данных о Вас”.

  • Светлана

    Спасибо!

  • Анжелика

    Здравствуйте, не получается провести проданный товар, выскакивает надпись (( 1) не внесли позиции либо 2) не внесли корректно кол-во штук. Все заполнила согласно инструкции. Что означает ” не внесли позицию”??.

  • Татьяна

    Здравствуйте, не получается провести проданный товар, выскакивает надпись (( 1) не внесли позиции либо 2) не внесли корректно кол-во штук. Все заполнила согласно инструкции. Что означает » не внесли позицию»??.

  • Виктор

    Скажите, могу ли я в данном файле подписывать пустые ячейки на против операций? (Подписывать на какого клиента была данная продажа)

    • Виктор, такой возможности нет в данной версии файла. Нужно дорабатывать его под данную потребность.

  • Станислав

    хотелось бы получить ссылку на программу.

    • Станислав, выслал вам ссылку на e-mail. Она должна появляться после заполнения формы в начале статьи. Видно, у вас форма не загрузилась.

  • Павел

    Дбрый день при любом внсением данных, требует пароль(((((

  • Виолетта

    Здравствуйте, Андрей!

    Спасибо за отличную программу! Все просто и удобно. Хотела бы узнать, можно ли к продажам товара указывать в отдельном столбце ФИО клиента, в последующем – город, третий столбец с общей суммой от покупок клиента, а четвертый – количество всех купленных позиций ?
    Насколько это сложно? Могу ли я сама, опираясь на теоретические материалы дополнить данную программу вышеперечисленными столбцами?
    Заранее спасибо

    • Добрый день, Виолетта. Программа разработана под розницу, где обычно данные покупателя не фиксируются. То что вы хотите реализовать (ФИО, город…) не предусмотрено в этой программе и требует доработки с моей стороны. Самостоятельно доделать у вас не получиться. Там довольно сложная работа макросов, в которой вы не разберетесь.

  • Денис

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

  • Геннадий

    Здравствуйте, Андрей!
    Спасибо за отличную программу!
    Не могли бы Вы подредактировать программу?
    вместо ” НАЙТИ АРТИКУЛ” заменить на ПОИСК по
    наименованию товара.
    Спасибо!

  • Алексей

    При вводе пароля 1 не снимает защиту с листа ПРОДАЖА(ВНЕСЕНИЕ)

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

  • Добрый день Андрей, у меня возникла небольшая проблема своими силами не могу ее решить. Ваша программа очень помогает, но из-за того что № партии не прописывается над каждой операцией в листе “Движение товара”, а только на приходах, очень тяжело отфильтровать прибыль полученную от разных партий. Не могли бы вы сделать так, чтобы с листа “Продажа(Внесение)” № партии копировался и добавлялся где нибудь в конце таблицы к соответствующей операции на листе “Движение товара”. За ранее спасибо!

    • Виталий, на странице “движение” снимаете защиту и в ячейке Р2 вместо формулы =ЕСЛИ(N2=”Приход”;СЧЁТЕСЛИ(B$2:B2;B2);””) пишите формулу =СЧЁТЕСЛИ(B$2:B2;B2). Копирую вниз на место старых формул. Эту же операцию нужно сделать в ячейке Р50002 – просто измените формулу на =СЧЁТЕСЛИ(B$2:B50002;B50002). Может помочь.

      • Добрый день Андрей, я так уже пробовал, эти формулы не могут обеспечить адекватный подсчет партий. При таком раскладе каждая операция считается за партию((( Я думаю лучший способ это копировать номер партии с листа “Продажи(внесение) в момент проведения операции на лист “Движение товара” где нибудь в самом конце таблицы. Но с макросами у меня туговато.

  • Добрый день Андрей! программа супер! Но для небольшого продуктового магазинчика с большой текучкой мелких товаров с отсутствием кассового аппарата, посчитать все продажи проблематично. В магазине 1 раз в месяц ведется подсчет остатков товара. Разница прихода и остатков= продажа. Разница розничной цены продаж и закупочной цены= валовая прибыль за месяц. У меня вопрос. Можно ли данную программу адаптировать под схему работы магазина, т. е. вносить в таблицу не продажи , а остатки. А на основании остатков считать объем и сумму продаж за определенный период? Спасибо

    • Добрый день, Елена.
      Спасибо за вопрос. К сожалению, переделывать долго. И данную версию программы я оставляю в текущем виде без адаптации под каждый конкретный случай.

  • Наталья Беларусь

    Спасибо простая и понятная программа для небольшого магазина.

Добавить комментарий