Cennik w Excelu z narzutem, VAT i rabatami: jak go zbudować

Gdy dostawca podnosi ceny, a w cenniku masz kilkadziesiąt pozycji, łatwo zapomnieć o kilku z nich. Wtedy sprzedajesz taniej, niż powinieneś, i dowiadujesz się o tym po miesiącu. Dobrze zbudowany cennik w Excelu liczy ceny automatycznie z ceny zakupu, więc zmieniasz jedną liczbę zamiast dziesięciu.

Pokażę, jak połączyć narzut, VAT, zaokrąglanie, promocje i ceny hurtowe w jednej tabeli oraz jak kontrolować, czy żadna cena nie spadła poniżej zakupu.

Narzut a marża

To dwie różne wielkości i warto je rozróżniać od początku. Narzut liczysz od ceny zakupu, a marżę od ceny sprzedaży.

• Zakup 100 zł, narzut 30%: cena sprzedaży 130 zł.
• Zysk to 30 zł, czyli 30% od zakupu (narzut) i około 23% od sprzedaży (marża).

Jeśli pomylisz te pojęcia, zarobisz mniej, niż zakładasz.

Krok 1: parametry

Wydziel osobny arkusz lub zakres z ustawieniami: domyślny narzut, stawka VAT (sprawdź aktualną wartość i swój sposób opodatkowania), krok zaokrąglania, narzuty dla kategorii oraz rabaty hurtowe. Zmiana parametru ma przeliczać cały cennik, a nie wymagać edycji formuł w każdym wierszu.

Krok 2: cena detaliczna

Przy założeniu, że zakup wpisujesz netto, cena brutto wynosi zakup × (1 + narzut) × (1 + VAT). W Excelu:

=ZAOKR(C2*(1+D2)*(1+Parametry!$B$2);2)

Jeśli sprzedajesz po okrągłych cenach, zaokrąglaj do kroku, na przykład do 0,50 zł lub pełnych złotych:

=ZAOKR.DO.WIELOKR(E2;0,5)

Narzut weź najpierw z kolumny „własny narzut” produktu, a jeśli jest pusta, z kategorii, a w ostateczności z wartości domyślnej:

=JEŻELI(F2<>"";F2;JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(B2;Kategorie!$A:$B;2;FAŁSZ);Parametry!$B$1))

Krok 3: promocje i ceny hurtowe

Cena promocyjna to cena detaliczna pomniejszona o rabat:

=ZAOKR(G2*(1-H2);2)

Ceny hurtowe możesz liczyć w trzech progach, każdy z własnym rabatem od ceny detalicznej lub niższym narzutem.

Krok 4: kontrola ceny poniżej zakupu

To najważniejsza kolumna. Porównaj cenę promocyjną i hurtowe z kosztem zakupu z VAT:

=JEŻELI(MIN(I2:L2)<C2*(1+Parametry!$B$2);"Poniżej zakupu";"OK")

Dzięki temu od razu widzisz, które pozycje po promocji przynoszą stratę.

Przykład

Produkt kupujesz za 50 zł netto. Narzut kategorii wynosi 40%, VAT 23%.

• 50 × 1,40 = 70 zł netto,
• 70 × 1,23 = 86,10 zł brutto,
• po zaokrągleniu do pełnych złotych w górę: 87 zł.

Promocja −20% daje 69,60 zł brutto. Koszt zakupu z VAT to 61,50 zł, więc promocja jest jeszcze nad kreską, ale rabat −30% dałby 60,90 zł i kontrola pokazałaby problem.

Typowe błędy

• Liczenie narzutu od ceny brutto.
• Ręczne nadpisywanie formuł w pojedynczych wierszach. Po miesiącu nikt nie wie, które ceny są policzone, a które wpisane.
• Brak kontroli promocji i cen hurtowych.
• Zapominanie o zaokrągleniach: cena 86,10 zł wygląda gorzej niż 89 zł.

Jak aktualizować ceny po podwyżce zakupu

Dobrze zbudowany cennik wymaga tylko zmiany kolumny z ceną zakupu. Wklej nowe ceny od dostawcy w miejsce starych, a pozostałe kolumny przeliczą się same. Następnie przefiltruj kolumnę kontroli i sprawdź, czy któraś pozycja nie spadła poniżej kosztu. Jeśli tak, popraw narzut kategorii lub wyłącz promocję dla tego produktu. Cały proces zajmuje kilka minut, a nie wieczór z kalkulatorem.

Warto też raz na kwartał porównać średni narzut z cenami konkurencji. Zbyt niski oznacza, że oddajesz zysk, zbyt wysoki, że tracisz klientów.

Gotowy szablon cennika

Jeśli wolisz gotowe narzędzie, skorzystaj z szablonu cennika z narzutem i rabatami. Jest płatny, a cenę zobaczysz na stronie produktu. W arkuszu „Parametry” włączasz lub wyłączasz VAT i ustawiasz jego stawkę, narzut domyślny, krok i sposób zaokrąglania, trzy poziomy hurtowe, do 12 kategorii z narzutem oraz jednostki miary.

Arkusz „Produkty” mieści do 1000 pozycji. Dla każdej wpisujesz SKU, nazwę, kategorię, zakup, ewentualną własną marżę i rabat promocji. Tabela liczy cenę detaliczną, promocyjną i trzy hurtowe, zysk w kwocie i procentach oraz pokazuje kontrolę ceny poniżej zakupu. „Podsumowanie” zawiera liczbę pozycji, średni narzut i marżę, średnie ceny według kategorii i poziomów oraz dwa wykresy.

W pakiecie jest pusta tabela i wypełniony przykład z fikcyjnymi danymi. Plik działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Obliczenia mają charakter orientacyjny.

FAQ

Czy zakup wpisywać netto, czy brutto?

Najwygodniej netto, a VAT doliczać w formule. Wtedy zmiana stawki nie wymaga ręcznego przeliczania zakupów.

Jaki narzut przyjąć?

To zależy od branży, konkurencji i kosztów stałych. Zacznij od policzenia, ile musisz zarobić na sztuce, by pokryć koszty, a dopiero potem porównaj z cenami rynku.

Jak zaokrąglać ceny?

Wybierz jeden krok dla całego cennika, na przykład do 1 zł lub 0,50 zł. Spójne końcówki wyglądają profesjonalnie i ułatwiają liczenie przy kasie.

Czy można pracować bez VAT?

Tak. Wystarczy wyłączyć VAT w parametrach, a ceny będą liczone netto.