Програма з обліку продажів і залишків в 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.