Objednávkový systém v exceli

Dobry den, potrebovali by sme vytvorit tabulku (objednávkový systém), kde by sme mohli zadavat rozne udaje ohladne objednavok zakaznikov.

Mame predajnu, kde robime rozne ponuky a mali by sa vyhodnocovat nakupne ceny, predajne, prehlad marze, prehlad operatorov a podobne. Kazdy, kto robi nejake upravy, pripadne vytvara cenovu ponuku sa úkaze, ako spracovatel.

V riadku by malo byt toto: zakaznik, poznamky, kod produktu, dodavatel, nakupna cena bez DPH, predajna cena s DPH, marza, pocet kusov a oznacenie, ako vybavena objednavka. Stlpec dodavatel by mal obsahovat nasich dodavatelov v rozbalovacom menu, ale mal by sa dat zadat aj samostatne. Z nakupnej a predajnej ceny vieme urcit marzu. A tato by sa mala nejako farebne lisit podla vysky, aby zretelne bolo vidno kto a za kolko ponuky vytvara.

Pri marzi by sme potrebovali vidiet aj grafy a historiu. Proste aby sa operator vedel vyznat v tom, ako vytvara cenove ponuky. Bunka v riadku o vybaveni objednavky, kde by bolo rozbalovacie menu by mala byt moznost vybavena a nevybavena. Pri volbe moznosti vybavena, by sa cely riadok farebne oznacil, aby sa toto tiez dalo lahko vizualne oddelit.

Presne takýto dotaz sme dostali od pána zamestnávateľa. Úplne rozumieme problému. Rôznych údajov o všetkom-možnom máme v dispozícií v jednej excel tabuľke, ale nikto sa v nej poriadne nevyzná. Preto je potrebné si v takejto tabuľke upratať a sprehľadniť údaje tak, aby zamestnávateľ či vedúci oddelenia videl len to, čo chce a nemusel sa zakaždým zamýšľať, čo znamená každý údaj osobitne.

Či už ide o porovnávanie cenových ponúk, vyhodnocovanie nákupov, prehľad tržieb a výkonnosti zamestnávateľov. A to všetko automatizovane v prehľadnom reporte v exceli na dennej či mesačnej báze.

Keďže podobných zadaní dostávame v poslednom období viac, poďme sa pozrieť na vypracovanie zadanie pre objednávkový systém v exceli podrobne.

Hárok Tabuľka

Tabuľka objednávok pre objednávací systém v exceli

V tabuľke v stĺpcoch sa nachádza spracovateľ, zákazníci, poznámky, kód produktu, dodávateľ, nákupná cena bez DPH, predajná cena s DPH, marža, počet kusov, stav objednávky a dátum vytvorenia objednávky.

V stĺpci Spracovateľ sa zobrazí rozbaľovací zoznam, z ktorého si môžete vyberať preddefinovaných spracovateľov + samozrejme aj ručne zadaných nových spracovateľov. Zoznam spracovateľov si môžete zbierať v samostatnom hárku s názvom „dodávateľ“, stĺpec C.

Zoznam dodávateľov a spracovateľov

V stĺpci „Zákazník“ sa bude automaticky dopisovať opakujúci sa zákazník. To znamená, že ak už ste mali predtým tohto zákazníka, v exceli sa vám preddefinovane začne dopisovať jeho meno.

Automatické dopisovanie opakovacích záznamov

Pri stĺpci dodávateľ sa zobrazí rozbaľovací zoznam, z ktorého si môžete vyberať preddefinovaných dodávateľov + samozrejme aj ručne zadaných nových dodávateľov. Opäť aj tu si môžete zoznam dodávateľov zbierať v samostatnom hárku s názvom „dodávateľ“, stĺpec B.

Polia Poznámky, Kód produktu, Nákupná cena bez DPH, Predajná cena s DPH a Počet kusov sa zadávajú manuálne na základe vašich hodnôt a poznámok.

Marža sa bude dopočítavať automatizovane. Pre príklad:

Predajna cena s DPH je 156 EUR, nakupna cena bez DPH je 100 EUR. Nákupnú cenu vynásobíme 1,2 tak dostaneme nákupnú cenu s DPH t.j. 100×1,2 = 120 EUR. Pomerom predajnej ceny s DPH k nákupnej cene s DPH dostávame maržu, v tomto prípade 156/120 = 1,3. Čiže marža je 30 %.

Na základe výšky marže sa farebne podfarbí celý stĺpec marža, spracovateľ a marža, prípadne celý riadok. Všetko automatizovane tak, aby ste presne videli, ktoré objednávky sú veľmi úspešné z hľadiska tržieb.

Podmienené formátovanie podľa výšky marže

Označenie stavu objednávky o vybavení resp. nevybavení sme urobili ako políčko s dvomi možnosťami na výber. Celý riadok by sa na základe tejto hodnoty automaticky podfarbil, prípadne prečiarkol, ak by bola zákazka vybavená (modrou farbou) resp. nevybavená (červenou farbou).

Pre urýchlenie zadávania sme nastavili objednávkový systém v exceli tak, že hneď, ako zadáte nové meno spracovateľa v novom riadku, hodnota stavu objednávky sa zmení na stav „Nové“.

Rozbaľovací zoznam stavu objednávky v exceli

Zadávanie dátumu pre zjednodušenie je možné na základe rozbaľovacieho zoznamu (rolovacieho okna) s dnešným dátumom alebo s rozsahom dní vami určených.

Zrýchlenie zadávania dátumu je určené na to, aby spracovateľ nemusel zakaždým ručne zadávať dátum. Po kliknutí na bunku dátumu sa vyrolujú možnosti dátumu. Aby rolovacie okno nebolo dlhé až po rok 2018, vieme to v exceli navzorcovať tak, aby ste v možnosti výberu dátumu mali na výber dnešný dátum a 7 predošlých dní až dnešný dátum a 7 nasledujúcich dní. Samozrejme môžeme nastaviť aj väčšie/menšie časové rozmedzie dátumov.

Rozbaľovací zoznam dátumov v exceli

Hárok Analýza

Kontingenčná tabuľka a graf pre vývoj objednávok

Z tejto veľkej tabuľky sme vytvorili pomocou kontingenčnej tabuľky prehľadné reporty, v ktorých si môžete na základe výberu spracovateľa pozrieť jeho výsledky.

Spracovateľov si môžete pozerať samostatne alebo všetkých naraz. Filtračnou tabuľou si môžete kliknutím vybrať spracovateľa a analyzovať jeho výkonnosť v v kontingenčnej tabuľke a v grafe.

Filtračná tabuľa do kontingenčnej tabuľky

Zároveň si môžete pomocou bežca (timeline na filtrovanie času) prispôsobovať časové obdobie, ktoré chcete analyzovať.

Filter času v exceli

Súbor pracuje tak, že zo zoznamu by ste si vybrali meno spracovateľa a všetko by sa automatizovane zobrazilo v reporte aj s grafmi. Takto by ste si mohli pozerať výsledky spracovateľov, prípadne výšky marže a podobne.

Kontingenčný graf pri objednávkach

Hárok Spracovatelia

V hárku Spracovatelia si môžete pozrieť výkonnosť zamestnancov v prehľadnom koláčovom grafe.

Analýza zamestnancov v exceli aj s koláčovým grafom

Hárok Kalendár

Pre lepšiu orientáciu objednávok sme vytvorili kalendár na rok, v ktorom by ste si len klikli na dátum a vám by sa vyrolovali všetky objednávky v ten daný deň, na ktorý ste si v kalendári klikli. Takto budete mať prehľad, na ktorých objednávkach sa pracuje dnes, zajtra či v ktorýkoľvek iný dátum.

Kalendár objednávok v exceli

Hárok Kalkulačka

Kalkulačka na výpočet marže pre rôzne cenové hladiny. Prehľadná administratíva nad výškou marže. Napriklad pre sumu od 0 do 10€ by bola marza 40%, pre sumu od 10 do 100€ marza 30%, pre sumu vyssiu ako 100€ marza 20%. Máte možnosť zadávať ľubovoľný počet cenových hladín a priraďovať k ním marže.

Cenové hladiny marží je možné zadávať na samostatný hárok, na ktorý by sa nedostal nikto bez hesla na otvorenie hárku v exceli. Vieme vám zaheslovať hárok v exceli s prístupom len pre toho, kto je držiteľom hesla. Po kliknutí na zaheslovaný hárok vám vyskočí upozornenie na vloženie hesla.

Zabezpečený hárok v exceli

V kalkulačke sa už len dopočíta predajná cena na základe cenovej hladiny, ktorú si vy určíte v zaheslovanom hárku.

Samotný dokument je možné zdieľať, čo by znamenalo, že by ho mohol mať otvorený ľubovoľný počet užívateľov.

Ak máte pocit, že aj Vám by takýto systém objednávania v exceli dokázal ušetriť množstvo času, potom nás neváhajte kontaktovať tu alebo emailom na info@candyman.sk. Radi vám zodpovieme na Vaše otázky.