KHIZHNYAKOVEwidencja sprzedaży sklepu w Excelu: przychód, zysk, narzut i analiza ABC
Ewidencja sprzedaży i przychodów sklepu w Excelu: wg dni, towarów, sprzedawców, analiza ABC — szablon Excel — Zobacz szablon →
Kasa fiskalna pokazuje, ile sprzedałeś. Nie powie jednak, czy na tym zarobiłeś, który towar ciągnie cały obrót i kiedy w tygodniu sklep naprawdę żyje. Do tego potrzebna jest prosta ewidencja z kosztem własnym, a Excel w zupełności wystarcza.
W tym poradniku zbudujemy rejestr sprzedaży, policzymy zysk i narzut oraz zrobimy analizę ABC, czyli podział asortymentu według wkładu w przychód. Na końcu będziesz wiedzieć, które towary trzymać w pierwszej kolejności, a które tylko zamrażają pieniądze.
Krok 1: słownik towarów
Zacznij od listy towarów: kod (SKU), nazwa, kategoria, cena zakupu i cena detaliczna. To jedyne miejsce, w którym wpisujesz ceny. Dzięki temu w rejestrze sprzedaży wystarczy wybrać towar z listy, a cena i koszt podstawią się same:
=WYSZUKAJ.PIONOWO(B2;Towary!A:E;5;FAŁSZ)
Uwaga: gdy zmienisz cenę w słowniku, przeliczą się też dawne sprzedaże. Jeśli chcesz zachować historię, dodaj towar z nowym kodem.
Krok 2: rejestr sprzedaży
Jeden wiersz to jedna sprzedaż: data, towar, ilość, rabat, sprzedawca i forma płatności. Resztę wylicza arkusz:
- Przychód:
=ilość*cena*(1-rabat) - Koszt własny:
=ilość*cena_zakupu - Zysk:
=przychód-koszt - Narzut:
=zysk/koszt
Przykład: sprzedajesz 3 sztuki towaru z ceną zakupu 40 zł i ceną detaliczną 60 zł, bez rabatu. Przychód to 180 zł, koszt 120 zł, zysk 60 zł, narzut 50%.
Narzut a marża
To dwa różne wskaźniki i ciągle się je myli. Narzut liczysz od kosztu: 60 / 120 = 50%. Marżę od przychodu: 60 / 180 = 33%. Gdy mówisz „mam 50% marży”, a kupiłeś z narzutem 50%, w rzeczywistości zostaje Ci jedna trzecia przychodu. Zawsze ustal, o którym wskaźniku mówicie z dostawcą czy księgową.
Krok 3: sumy i dni tygodnia
Przychód z danego miesiąca policzysz funkcją SUMA.WARUNKÓW na kolumnie dat. Dni tygodnia wyłuskasz funkcją DZIEŃ.TYG(A2;2) i zsumujesz przychód dla każdego z nich osobno. Często okazuje się, że piątek i sobota dają ponad połowę tygodniowego obrotu, co ma wpływ na grafik i zamówienia.
Podobnie zsumujesz sprzedaż według sprzedawców: =SUMA.JEŻELI(F:F;"Anna";G:G).
Krok 4: analiza ABC
To klasyczny sposób, żeby zobaczyć, gdzie jest Twój przychód:
- Zsumuj przychód dla każdego towaru.
- Posortuj malejąco.
- Policz udział każdego towaru w całości i udział skumulowany.
- Przypisz grupy: A, dopóki skumulowany udział nie przekroczy 80%; B, do 95%; C, reszta.
| Towar | Przychód | Udział | Skumulowany | Grupa |
|---|---|---|---|---|
| Towar 1 | 12 000 zł | 40% | 40% | A |
| Towar 2 | 9 000 zł | 30% | 70% | A |
| Towar 3 | 4 500 zł | 15% | 85% | B |
| Towar 4 | 2 400 zł | 8% | 93% | B |
| Towar 5 | 2 100 zł | 7% | 100% | C |
W małym sklepie zwykle kilkanaście procent towarów robi większość obrotu. Grupa A to Twoje filary: pilnuj, by ich nie zabrakło. Grupa C to kandydaci do wyprzedaży lub rezygnacji. Progi 80% i 95% to konwencja, możesz je zmienić.
Częste błędy
- Patrzenie tylko na obrót. Towar z dużym obrotem i niskim narzutem może zarabiać mniej niż mniejszy, ale droższy.
- Zapominanie o rabatach. Wpisuj je, inaczej przychód będzie zawyżony.
- Mieszanie narzutu z marżą.
- Jedno wielkie zestawienie bez dat. Rób osobne okresy, by porównywać miesiące.
- Brak kopii rocznej. Po roku zapisz plik i zacznij nowy.
Gotowy szablon: ewidencja sprzedaży sklepu
Jeśli nie chcesz budować tego od podstaw, jest gotowy płatny szablon Excel. Arkusz „Słowniki” zawiera towary (SKU, nazwa, kategoria, cena zakupu i detaliczna), sprzedawców, formy płatności i progi grup ABC. W „Sprzedaż” wpisujesz do 3000 wierszy: data, towar z listy, ilość, rabat, sprzedawca i płatność, a cena, przychód, koszt własny, zysk i narzut liczą się same.
„Analiza ABC” tworzy ranking towarów według przychodu z udziałem, udziałem skumulowanym i grupą A, B lub C. „Podsumowanie” pokazuje sumy, wyniki według dni tygodnia, miesięcy, sprzedawców i form płatności, top 10 towarów i cztery wykresy. Mieści 200 towarów. Dostajesz pustą tabelę i wypełniony przykład z fikcyjnymi danymi. Działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr, a poprawki i nowe wersje są bezpłatne. To arkusz ewidencyjny, nie program księgowy.
Przyda się właścicielom małych sklepów, punktów handlowych i sklepów internetowych, jednoosobowym firmom i freelancerom.
FAQ
Czy ewidencja w Excelu zastępuje kasę fiskalną?
Nie. Kasa jest wymagana przepisami tam, gdzie obowiązuje. Arkusz służy do analizy: pokazuje zysk i strukturę sprzedaży. Wymogi prawne sprawdź w obowiązujących przepisach.
Jaki narzut jest dobry?
Zależy od branży, kosztów i konkurencji, więc nie ma jednej liczby. Porównuj własne towary między sobą i patrz, które po uwzględnieniu rabatów faktycznie zostawiają zysk.
Jak często robić analizę ABC?
Raz na kwartał lub po sezonie. Zbyt rzadko traci sens, a zbyt często łapie przypadkowe wahania.
Czy 3000 wierszy to wystarczająco?
Dla małego sklepu często na rok lub dłużej, zależnie od tego, czy wpisujesz każdy paragon, czy sprzedaż zbiorczo dziennie. Po zapełnieniu zachowaj kopię i zacznij nowy plik.
