Nastaviti na WhatsApp?

Razgovor se otvara u WhatsAppu, u aplikaciji ili u novoj kartici. Pritom napuštate našu web stranicu, a podaci poput vaše IP adrese prenose se WhatsAppu (Meta).

EXCEL I POPISI

Usporedba cjenika u Excelu: pronađite svaku promjenu cijene dobavljača

Petak poslijepodne, a veletrgovac je poslao novi cjenik: 3.000 redaka, ništa označeno. Stari popis stavite pokraj, listate, provjeravate uzorke i ne možete se riješiti osjećaja da je negdje promaklo poskupljenje. Usporedba cjenika u Excelu red po red kod te količine teško je pouzdana. Poskupljenje ćete primijetiti tek kad nalog donese manje nego što je bilo kalkulirano.

Visokoregalno skladište s paletama i kutijama čije se nabavne cijene provjeravaju pri usporedbi cjenika dobavljača
Fotografija: CHUTTERSNAP na Unsplash

To košta dvaput: sate za usporedbu i maržu kad poskupljenje neprimijećeno uđe u vaše ponude. Ovaj članak pokazuje kako usporediti cjenike u Excelu, pronaći svaku promjenu cijene i izraziti je u eurima godišnje, kako biste nove cijene odobrili mirne savjesti.

Saznat ćete kako cijene najprije učiniti usporedivima, koje formule trebate i kako promjene ponderirati prema godišnjoj količini. Primjer iz elektroinstalaterskog obrta pokazuje gdje su zamke.

Ukratko: kako usporediti dva cjenika u Excelu?

Usporediti dva cjenika znači: svaki artikl sa starog popisa preko šifre artikla pridružujete istom artiklu na novom popisu, obje cijene preračunavate na istu jedinicu i računate promjenu u postocima. Nove i ukinute artikle prikazujete zasebno.

  1. Učinite cijene usporedivima

    Oba popisa preračunajte na istu cijenu po komadu. Pazite na jedinicu cijene, pakiranje te neto ili bruto.

  2. Pridružite preko šifre artikla

    S XLOOKUP ili VLOOKUP dohvatite novu cijenu za svaki artikl sa starog popisa.

  3. Izračunajte promjenu

    Izračunajte promjenu u eurima i u postocima i velike skokove označite bojom.

  4. Ponderirajte prema godišnjoj količini

    Promjenu pomnožite sa svojom nabavnom količinom. Tako vidite koja poskupljenja stvarno koštaju novac.

  5. Provjerite i odobrite

    Promjene iznad praga neka provjeri druga osoba prije nego što cijene uđu u vaše robno poslovanje.

Zašto cijene najprije učiniti usporedivima: jedinice, neto i bruto

Većina pogrešnih rezultata nastaje prije prve formule. Jedinica cijene pokazuje za koju količinu cijena vrijedi. Kabeli često koštaju po 100 metara, vijci po 1.000 komada. Ako dobavljač promijeni jedinicu cijene, jedva promijenjena cijena odjednom izgleda kao pad od 99 %. Takve lažne uzbune koštaju vrijeme i povjerenje u vlastitu analizu. Nekoliko minuta provjere unaprijed štedi vam oboje.

Što provjeriti prije usporedbe
Točka provjereTipičan problemRješenje u Excelu
Jedinica cijenecijena po 100 komada na starom, po komadu na novom popisuizračunati cijenu po komadu: cijena podijeljena jedinicom cijene
Pakiranjekutija od 50 umjesto kutije od 25preračunati na cijenu po komadu i usporediti i jedinicu
Neto ili brutokataloška cijena bruto, vaša cijena neto po rabatnoj skupinioba popisa preračunati na vašu nabavnu cijenu
Format brojeva12.40 umjesto 12,40, cijena spremljena kao tekstpretvoriti u broj s =NUMBERVALUE(A2,".",",")
Valjanostpopis vrijedi tek od kasnijeg datumazabilježiti datum početka valjanosti, stari popis arhivirati

Funkcija VALUE kod formata brojeva pomaže samo djelomično jer se ravna prema regionalnim postavkama vašeg računala. Kod NUMBERVALUE sami zadajete decimalni separator i separator tisućica. Funkcija postoji i u Excelu 2016 i Excelu 2019.

U veleprodaji elektro i sanitarne opreme cjenici često stižu kao Datanorm datoteka, format za razmjenu podataka o artiklima i cijenama raširen na njemačkom govornom području. Vaš granski softver obično ih učitava izravno. Rabati su tamo često odvojeni u rabatne skupine. Poskupljenje se tada može kriti i u promijenjenoj rabatnoj skupini, iako kataloška cijena ostaje ista. U tom slučaju usporedite svoje neto cijene, ne samo kataloške.

Korak po korak: usporediti cijene u Excelu i označiti ih bojom

Oba popisa pretvorite u tablice s Ctrl+T i pod Table Design > Table Name nazovite ih „Stari“ i „Novi“. Formule pretpostavljaju da obje tablice imaju stupce Šifra, Cijena i JC (jedinica cijene), a tablica Stari dodatno i GodKoličina. Zatim dodajte ove stupce:

  1. Cijena po komadu: U tablici Stari stupac CijenaStara s =[@Cijena]/[@JC], u tablici Novi stupac PoKomadu s istom formulom.
  2. Nova cijena: U tablici Stari stupac CijenaNova s =XLOOKUP([@Šifra],Novi[Šifra],Novi[PoKomadu],"ukinuto"). XLOOKUP postoji u Microsoft 365, Excelu 2021 i Excelu 2024. U Excelu 2019 i starijima upotrijebite VLOOKUP s FALSE kao zadnjim argumentom, a artikle koji nedostaju uhvatite s IFNA. Kako pridruživanje radi u detalje, piše u članku Kako usporediti dvije tablice u Excelu.
  3. Promjena u postocima: U tablici Stari stupac Promjena s =IF(ISNUMBER([@CijenaNova]),ROUND([@CijenaNova],4)/ROUND([@CijenaStara],4)-1,""), oblikovan kao postotak. Zaokruživanje sprječava da se sitne računske razlike pojave kao promjena.
  4. Označiti bojom: Označite stupac Promjena i odaberite Home > Conditional Formatting > Highlight Cells Rules > Greater Than. S 0,03 obojite sva poskupljenja iznad 3 %. Drugo pravilo s Less Than i vrijednošću 0 pokazuje pojeftinjenja.
  5. Novi artikli: U tablici Novi =COUNTIF(Stari[Šifra],[@Šifra])=0 pokazuje sve artikle koji su novo dodani. Više o tome u članku pronaći unose koji nedostaju.

Koliko poskupljenje stvarno košta? Ponderirajte prema godišnjoj količini

Poskupljenje od 12 % kod artikla koji kupujete dvaput godišnje jedva se osjeti. Poskupljenje od 3 % kod vašeg najkupovanijeg kabela itekako. Zato godišnju količinu uzmite iz statistike nabave vašeg robnog poslovanja, u istoj jedinici kao cijena po komadu, i za svaki artikl izračunajte: =IFERROR(([@CijenaNova]-[@CijenaStara])*[@GodKoličina],0)

Zbroj tog stupca vaš je dodatni trošak godišnje uz istu količinu. Ako ga podijelite s dosadašnjim volumenom nabave, =SUMPRODUCT(Stari[CijenaStara],Stari[GodKoličina]), dobivate ponderirani rast cijena. S tim brojem dobro pripremljeni idete na razgovor s dobavljačem, a vaša kalkulacija opet štima.

Odredite pragove za odobrenje

Ne treba svaka promjena upit. Jednom pisano odredite što se preuzima automatski, a što netko mora pogledati. Primjer takvih pravila:

  • Promjene ispod 3 % i ispod 100 € dodatnog troška godišnje se preuzimaju.
  • Poskupljenja od 3 % ili od 100 € dodatnog troška nabava provjerava prije preuzimanja.
  • Poskupljenja od 10 % nabava razjašnjava izravno s dobavljačem.
  • Pojeftinjenja od 20 % smatraju se sumnjom na promijenjenu jedinicu cijene ili tipfeler i uvijek se provjeravaju.

Granice ovise o vašem asortimanu i maržama. Važno je da su utvrđene. Tada o tome koja će promjena upasti u oči ne odlučuje dnevna forma, a provjera radi i kad je nadležna kolegica na godišnjem odmoru.

Ruke za stolom kalkulatorom pokraj tipkovnice ponovno izračunavaju promjene cijena
Fotografija: Towfiqu barbhuiya na Unsplash

Primjer iz nabave instalaterskog obrta

Izvadak iz usporedbe u primjeru, cijene neto
ArtiklStara cijenaNova cijenaPromjenaGodišnja količinaDodatni trošak godišnjeNalaz
10-4711 kabel s plaštem, po 100 m68,20 €71,90 €+5,4 %120 × 100 m444,00 €iznad praga, provjeriti
22-1050 razvodna kutija1,12 €1,15 €+2,7 %2.40072,00 €ispod praga
31-0207 prekidač4,80 €4,80 €0,0 %6000,00 €nepromijenjeno
40-9001 vezice za kabele9,50 € po 1000,10 € po komadu+5,3 % nakon preračuna50 × 10025,00 €promijenjena jedinica cijene, provjeriti

Kako takve cijene zatim ulaze u ponude, pokazuje naš primjer iz obrta.

Kako usporediti više dobavljača u Excelu? Usporedni pregled ponuda

Ponekad nije riječ o starom i novom, nego o tri ponude za istu potrebu. Za to vam ne treba predložak. Za svaki artikl napravite redak, a za svakog dobavljača stupac s preračunatom cijenom po komadu, na primjer od C do E, s nazivima dobavljača u retku 1.

  • Najniža cijena: =MIN(C2:E2)
  • Najpovoljniji dobavljač: =XLOOKUP(MIN(C2:E2),C2:E2,$C$1:$E$1) vraća naziv iz retka zaglavlja.
  • Usporedite i uvjete: Rabat, skonto, troškovi prijevoza, minimalna količina i rok isporuke idu u zasebne stupce. Najniža cijena po komadu nije uvijek najpovoljnija nabava.

Tipične pogreške pri usporedbi cjenika

  • Uspoređivati retke jedan pokraj drugoga: Novi i ukinuti artikli pomiču sve retke. Uvijek uspoređujte preko šifre artikla, nikad prema položaju retka.
  • Previdjeti jedinicu cijene: Prijelaz s „po 100“ na „po komadu“ stvara prividna pojeftinjenja od gotovo 99 %. Uvijek preračunajte na cijenu po komadu.
  • Jednostavan prosjek: Prosjek svih promjena malo govori o vašim troškovima. Ponderirajte prema godišnjoj količini.
  • Dvostruke šifre artikala: Ako je artikl dvaput na popisu s različitim cijenama, XLOOKUP bez upozorenja vraća prvi pogodak. Šifre prije toga provjerite s COUNTIF.
  • Preuzeti cijene bez odobrenja: Inače tipfeler dobavljača izravno završava u vašim ponudama. Odredite prag od kojeg provjerava druga osoba.

Kada je Excel dovoljan, a kada pomaže AI pomoćnik?

Za jedan popis godišnje Excel je dovoljan. Teško postaje kod više dobavljača s vlastitim formatima, a često samo jedna osoba u kući poznaje točan postupak. Kad je ona na godišnjem odmoru, novi popis čeka. Oprezan izračun: četiri dobavljača svako tromjesečje šalju nove popise, a svaki traži oko tri sata za pripremu, usporedbu i preuzimanje. To je 48 sati godišnje. Uz pretpostavljenih 38 € internog troška po satu, dakle plaća plus dodatni troškovi, kako je zadano u kalkulatoru troškova, to je 1.824 €. Tome se pribraja previđeni skok cijene u tekućim ponudama. Vlastite vrijednosti izračunajte kalkulatorom troškova uredskog rada.

Samu usporedbu preuzima besplatna usporedba cjenika. Ondje postavljate svoj prag, zadano je 5 %, a rezultat preuzimate kao Excel datoteku s dosadašnjom cijenom, novom cijenom, razlikom i promjenom u postocima. Dvije stvari ostaju na vama: alat ne preračunava jedinice cijene, pa se prijelaz s „po 100“ na „po komadu“ tamo prikazuje kao veliko pojeftinjenje. A prikazani prosjek jednostavan je prosjek svih artikala na oba popisa. Za dodatni trošak u Excel datoteci dodajte godišnju količinu i izračunajte razliku puta količina, kako je gore opisano.

Kad svako tromjesečje stiže više popisa, AI pomoćnik, dakle mali program koji postavljamo upravo za taj zadatak, može dohvatiti datoteke iz poštanskog sandučića, preračunati jedinice, ponderirati prema godišnjoj količini i na odobrenje dati samo promjene iznad vašeg praga. Odluka ostaje kod vas: cijene ulaze u vaše robno poslovanje tek nakon vašeg odobrenja. Radi s vašim postojećim programima, nitko ne mora učiti novi softver. Više o tome na stranici Automatizacija rada u Excelu. Isplati li se to, ovisi o tome koliko dobavljača i popisa imate. Napišite to u besplatnu provjeru procesa i za 1–2 radna dana dobit ćete osobnu, besplatnu procjenu. Male projekte nakon usklađivanja procesa provodimo za 3–7 dana, po fiksnoj cijeni od 1.990 € bez PDV-a.

Izvori i dodatne poveznice

ČESTA PITANJA

Kratki odgovori.

Kako usporediti dva cjenika u Excelu?

Artikle pridružite preko šifre artikla, a ne prema retku. Prije toga oba popisa preračunajte na istu cijenu po komadu. Zatim s XLOOKUP ili VLOOKUP dohvatite novu cijenu za stari popis i izračunajte promjenu u postocima s =Novi/Stari-1. Nove i ukinute artikle prikažite zasebno.

Kako izračunati poskupljenje u postocima?

Novu cijenu podijelite sa starom i oduzmite 1: =Novi/Stari-1. Ćeliju zatim oblikujte kao postotak. Iz 68,20 € stare i 71,90 € nove cijene dobiva se poskupljenje od 5,4 %. Pritom uvijek uspoređujte cijene u istoj jedinici cijene, inače rezultat nema vrijednost.

Kako u Excelu usporediti cijene i označiti ih bojom?

Promjenu izračunajte u zasebnom stupcu i upotrijebite uvjetno oblikovanje. Odaberite Home > Conditional Formatting > Highlight Cells Rules > Greater Than, na primjer s 0,03 za sva poskupljenja iznad 3 %. Drugo pravilo s Less Than 0 ističe pojeftinjenja drugom bojom.

Postoji li Excel predložak za usporedbu dobavljača?

Gotov predložak vam ne treba. Dovoljni su redak po artiklu, stupac po dobavljaču s preračunatom cijenom po komadu i formula =MIN() za najnižu cijenu. Dodajte stupce za uvjete kao što su skonto, prijevoz, minimalna količina i rok isporuke, jer najniža cijena po komadu nije uvijek najpovoljnija nabava.

Zašto moja usporedba pokazuje pojeftinjenja od gotovo 100 %?

Najčešće je dobavljač promijenio jedinicu cijene, na primjer s „po 100 komada“ na „po komadu“. Sama cijena tada je jedva drukčija. Oba popisa preračunajte na cijenu po komadu tako da cijenu podijelite jedinicom cijene. Nakon toga usporedba pokazuje stvarnu promjenu, često samo nekoliko posto.

RAZGOVARAJMO O VAŠEM PROCESU

Pišite nam. Javljamo se osobno.

Pitanje, ideja ili zadatak koji svaki tjedan oduzima vrijeme? Dovoljno je nekoliko rečenica. Odgovor dobivate od ljudi koji će vaš pomoćnik i izraditi.

Ivan, Lukas i JanOdgovor u roku od 1–2 radna dana

  • Osobni odgovor
  • Bez obveze
  • Iz Beča, uz poštivanje GDPR-a
Fotografija: Ivan
Radije razgovarate izravno?Ivan, vaša kontakt osoba+43 676 59 40 027office@pebblebyte.comPon do Pet 08:00–18:00
Imate konkretan zadatak? Besplatna provjera procesa

Vaša poruka

Molimo ne unosite pristupne podatke ni povjerljive podatke o klijentima. Vaše podatke koristimo samo za obradu vašeg upita. Bez newslettera, bez prosljeđivanja u promidžbene svrhe. Pojedinosti u obavijesti o zaštiti podataka.