Riportok, jelentések

Lekérdezések és riportok készítése közvetlenül Excelbe az Ön ügyviteli rendszerének adataiból.

Gazdaságossági számítások

Értékesítési modellek, jövedelmezőségi és önköltség számítások készítése. Hozzon megalapozott döntést megbízható információk alapján!

Excel riport automatizálás

Spórolja meg a manualitást, készítsen riportokat gombnyomásra! Ami eddig fél óra volt, az most 1 perc lesz.

2012. december 5., szerda

Változások kiemelése idősorokban

Tegyük fel, hogy van egy táblázatunk, amely idősorokat tartalmaz. Ha a táblázat értékei időben viszonylag állandóak, kevéssé változnak, akkor az értékek monotonitása miatt nehezebben szúrjuk ki a megváltozott tételeket. Mert az esetek 99%-ában nem az az érdekes, ami állandó és stabil, hanem az átlagtól, a megszokottól eltérőek, a változások.

Létezik egy trükk, amivel mágnesként vonzhatjuk oda a figyelmünket a módosult értékekre. Az Excel feltételes formázását hívjuk segítségül és tekintetünk automatikusan a megváltozott adatokat fogja először észrevenni.

Vegyük példának a következőt. Van egy létszám táblánk a cégünk munkavállalóinak létszámával, részlegekre bontva. Itt szeretnénk kiemelni az időközben történt változásokat.

Jelöljük ki a táblázat második oszlopának első celláját (D5) a táblázatban (az egyes hónapok vízszintesen, egymás mellett helyezkednek el). Válasszuk a "feltételes formázás" menüt, ott pedig az "Új szabály" menüpontot.


A formázási szabály és az Excel munkalap tartalma alapján az alábbi ábrán szereplő szabályt kell létrehozni:


Fontos, hogy a C5 és D5 cellahivatkozásokból a $ jelet vegyük ki! A Formátum... gombra kattintva, majd a Kitöltés lapra navigálva válasszunk ki egy nekünk tetsző háttérszínt.



Az OK gombot lenyomva a D5-ös cella háttérszíne megváltozik, ha a D5-ös cella értéke eltérő a C5-ös cella értékétől. Hogy kiterjesszük ezt a többi cellára, ismételten menjünk a "Feltételes formázás" menübe, itt válasszuk a "Szabályok kezelése..." menüpontot. Fontos, hogy közben a D5-ös cella legyen aktív!


Az alábbi ablak jelenik meg előttünk:


Az Érvényesség mezőben szereplő mezőbe kattintva lehetővé válik, hogy az egérkurzorral kijelöljük azt a tartományt, amelyre szeretnénk a feltételes formázást alkalmazni.


Az OK gombra kattintva megkapjuk a végeredményt. Azon cellák háttere lett sárga, ahol az azonos sorbeli, bal oldali szomszédos cellában szereplő értékhez képest az aktuális érték megváltozott.



Természetesen nemcsak a háttérszínnel lehet játszani, hanem meg is vastagíthatjuk a megváltozott értékeket.

Szintén szemléletes, ha a változatlan értékeket "toljuk háttérbe", a betűszínük szürkére állításával (ekkor a képlet változik, a =C5=D5 kerül a szerkesztőlécbe).




2012. november 12., hétfő

Vízesés diagram

A vízesés diagram (angolul Waterfall chart) a prezentációk kedvelt eleme, mivel jól használható eszköz annak vizuális bemutatására, hogy egy érték milyen alkotóelemekből épül fel. A vízesés diagram szemléletesen mutatja be ezen alkotóelemek, kategóriák egymáshoz viszonyított arányát. Megmutatja, hogy 2 érték között milyen hatások okozták az eltérést.





Lehetséges felhasználási területek:



  • Értékesítés, profit változásának okai

    Az értékesítés és profit évről évre változik. Az évek közötti különbség több hatásból jön össze. Ezeket kategóriákba foglaljuk és bemutatjuk az egyes kategória értékek növekedésének és csökkenésének eredményre gyakorolt hatását.

    Ilyen kategóriák lehetnek:
    • Értékesítés
      • Volumen változás, Árszint változás, Értékesítési kampányok hatása
      • Hónapok
      • Termékek
      • Értékesítési csatornák
    • Profit
      • Bevétel és Költség elemek (Árbevétel, Termék beszerzési érték, Jutalék, Munkabér, Rezsi, Adminisztrációs ktg., stb.)
  • Cash Flow

    Egy vállalkozás életében a készpénztermelő képesség az egyik legfontosabb mutató. A vízesés diagrammal megmutatható, hogy például az egyes hónapok, vagy az egyes Cash Flow kategóriák esetében az időszakok közötti változások milyen hatással voltak a Cash Flow alakulására.
  • Ár felbontás

    A termék árából kiindulva, összetevőkre bontva eljuthatunk a termék profitjáig. (Ár, Beszerzési ktg., Szállítási ktg, ..., Profit
  • Költség összetétele

    Az egyes időszakok között az értékesítési, termelési, támogató osztályok, stb. költségei milyen irányban változtak.(Pl. ha nő az értékesítési volumen, akkor indokolt a termelési, az értékesítési ktg. növekedése, de ez már nem feltétlenül igaz a támogató (nonproduktív osztályok, pl. könyvelés, IT, cégvezetés, stb.) költségeire is.)
A fentiek csak példák arra, hogy mi mindenre alkalmas a vízesés diagram. 

Ha ötlete vagy kedve támadt kipróbálni, mire is lehet még jó ez a típusú chart, akkor ajánlom a saját készítésű diagramom, amely innen, vagy a Letöltések oldalról letölthető. 


Segítségével gyerekjáték lesz a vízesés diagram készítése a prezentációjához!




2012. szeptember 23., vasárnap

Bevétel és Költség nyilvántartás

Ingyenes bevétel és költség nyilvántartó szoftverek

Az alábbi szoftverek főleg magánszemélyek és kisebb vállalkozások pénzügyeinek nyilvántartására alkalmasak. Lehetőség van bankszámlák, értékpapír számlák nyilvántartására, eseti és ismétlődő, pénzmozgással járó tranzakciók kezelésére, megtervezésére. Sztenderd, beépített riportok elégítik ki jogos kíváncsiságunkat.


Money Manager Ex (magyar nyelven is)

A program funkcióiról képernyőmentések:
http://www.codelathe.com/mmex/mmex_screenshots.php

Windows verzió letöltése itt: Money Manager Ex letöltés

Szoftver honlapja letöltési lehetőséggel egyéb operációs rendszerekre: http://www.codelathe.com/mmex/mmex_download.php



A szoftverben ki lehet választani a magyar nyelvet, de a fordítás nem teljes. Innen lehet letölteni egy pár fokkal jobb nyelvi fájlt: magyar nyelvi fájl letöltés

Érdekesség: Aki ismeri az SQL adatlekérdező nyelvet, az saját lekérdezéseket is barkácsolhat, a szoftver adatbázisa SQLite alapú.


Microsoft Money (angol)

A Microsoft már nem fejleszti tovább ezt a termékvonalat, elvileg ingyenesen letölthető és telepíthető. A honlapjuk szerint régen aktiválni kellett a terméket, most erre már nincs szükség.

Itt lehet letölteni: Misrosoft Money Plus Sunset Home & Business letöltés

Angolul tudóknak:
  Termék leírása: http://support.microsoft.com/kb/2118008


GNU Cash (magyar nyelven is)

Nem töltöttem le és teszteltem le, nem tudok nyilatkozni a szoftver minőségéről. A honlapján szereplő adatok alapján körülbelül ugyanazokat tudja, mint az előző 2 verzió.

A program honlapja: http://www.gnucash.org/features.phtml?lang=hu_HU


Egyéb linkek a témában:
http://www.penzugyitanoda.hu/file/start/szoftverek.pdf
http://www.royalcashflow.hu/



2012. szeptember 22., szombat

Szeletelő használata Kimutatás diagramhoz


Az Excel 2010-es verzió egyik újítása a Szeletelő bevezetése. Segítségével felhasználóbarát módon szűrhetjük a Kimutatás tábla értékeit.
A Pivot táblára állva a (Kimutatáseszközök \ Beállítások \ ) Szeletelő beszúrása menüpontra kattintunk, majd kiválasztjuk, mely mező értékeire szűrve szűkítsük a kimutatásunkban megjelenő adatokat. A képen a Northwind mintaadatbázis Értékesítési elemzés nézetéből készített Kimutatást láthatjuk, mellette a Terméknév mezőből készített Szeletelőt. A Szeletelőben az adott mező választható értékei jelennek meg.





A Kimutatás táblához kapcsolódóan diagramokat tudunk készíteni, amelyek automatikusan visszatükröznek minden olyan módosítást, amit a Kimutatás táblán végzetünk. Ha megváltoztatunk egy adatmezőt, vagy például az oszlopokban cseréljük le az aktuális nézetet, a változtatás valós időben, manuális beavatkozás nélkül a diagramon is megjelenik.



A Szeletelő és Kimutatás diagram együttes használatával dekoratív és interaktív dashboard-ok, jelentések készíthetők.

Linkek:
http://www.excelguru.ca/content.php?263-Retrieving-Selections-From-A-PivotTable-Slicer





2012. szeptember 9., vasárnap

Cash Flow

Cash Flow definíció

Adott időszak alatti pénzbevételek és pénzkiadások különbsége. Levezetés, mely megmutatja a vállalkozás készpénztermelő képességét. Megmondja, hogy honnan származik a befolyt pénz és mire kerül felhasználásra.

Cash Flow kimutatás

A pénzügyi beszámoló részeként a cash flow kimutatás megmutatja, mennyi készpénzzel rendelkezik a vállalkozás. Elkészítése hozzátartozik az üzletmenethez, segítségével lehet nyomon követni a a vállalkozásba befolyó és kimenő pénzáramokat.
A vállalkozás vezetői ebből nyerhetnek számszerűsített információkat korábbi döntéseik pénzállományra gyakorolt hatásáról. Továbbá megmutatja, hogy a pénzbevételek fedezetet nyújtanak-e a vállalkozás működtetésére.

Logikailag 3 fő részre osztható a cash flow a pénzáramok keletkezése szerint:

  1. Operatív cash flow (a vállalkozás normál üzletmenetéből fakadó pénzmozgások)
  2. Befektetési cash flow (beruházási tevékenységhez kapcsolódó)
  3. Pénzügyi cash flow (finanszírozással összefüggő pénzáramok pl. hitelek, kamatok, osztalékok)


A kimutatást 2 módszer szerint lehet elkészíteni. Bár a megközelítési mód különböző, az eredménynek ugyanannyinak kell lennie.

  • Direkt (A pénzeszközöket növelő pénzmozgásokból levonjuk a pénzeszközöket csökkentő üzleti ráfordításokat.)
  • Indirekt (Kiindulási alapnak valamely számviteli eredmény kategóriát tekintjük, amit korrigálunk azokkal a tételekkel, amelyek az eredmény nagyságát ugyan befolyásolják, de nem járnak pénzmozgással.)

Cash Flow előrejelzés

Rendkívül fontos az üzleti eredményesség szempontjából, hogy azonosítsuk azokat a pontokat, ahol a cash flow negatívba megy át. Minden vállalkozásnak tisztában kell lennie pénzügyi helyzetével, főleg ha a rendelkezésre álló pénzről van szó.

Tulajdonképpen miről is szól egy cash flow terv? Igazából ez egy becslés arra vonatkozóan, hogy milyen bevételeink és kiadásaink lesznek, mindez az idő függvényében. Ez kulcsfontosságú ahhoz, hogy a vállalkozás tulajdonosai/vezetői képesek legyenek eldönteni, hogy jó irányba mennek-e a dolgok. (Lehet bármilyen csodálatos az üzleti stratégia, ha az nem jár együtt készpénztermelő képességgel, a vállalkozás halálra van ítélve!)

Egy cash flow előrejelzés a következő hetek, hónapok, évek (kritikus esetben napok) pénzáramainak összegzése. Vannak rendszeresen és egyenletesen felmerülő, és vannak csak bizonyos időszakokban jelentkező események. A jó előrejelzés figyelembe veszi a vevők, beszállítók, hitelek, adófizetési kötelezettségek ciklikusságait. Ha jól terveztünk, akkor mindig lesz annyi pénz a bankszámlán, hogy az esedékes kifizetéseket és kötelezettségeket teljesíteni tudjuk.

Egy jó terv birtokában a felelős azonosítani tudja a kritikus pontokat, még mielőtt azok bekövetkeznének. Az ismeret birtokában meg tudja tenni azokat az intézkedéseket, amelyekkel elkerülhetőek, vagy minimalizálhatóak a negatív hatások.

Cash flow terv elkészítése

A pénzforgalmi előrejelzésünk 2 fő részből áll össze. Bevételek és Kiadások. Ezt a 2 fő kategóriát kell úgy bontani, hogy a további részletező kategóriák a vállalkozásra jellemző bevételi és kiadási típusok legyenek. Például egy rendezvényszervező vállalkozásnál nem lesz jelentős a tétel az alapanyag beszerzése, de ugyanez egy termelő vállalatnál igen jelentős hányadot képvisel a költségek között. A tervezésnél a súlyának megfelelően kell megjeleníteni az egyes elemeket. Lesznek olyan elemek, amelyeket nem érdemes külön megtervezni, mivel hatásuk nem jelentős, viszont a tervezési időt növeli és az átláthatóságot rontja.

Az éves Cash Flow előrejelzés elkészítéséhez készítettem egy letölthető segédletet, itt érhető el:
Link1: Adatguru.hu - Letöltések - Cash Flow Terv sablon
Link2: Cash Flow Terv sablon
Link3: Cash Flow Terv sablon



2012. szeptember 8., szombat

TEAOR

Gazdasági Tevékenységek Egységes Ágazati Osztályozási Rendszere
( TEÁOR 08 )

Hivatalos TEÁOR:

Rendelések közt eltelt idő

Mit tudunk csinálni, ha szeretnénk látni ügyfelenként az egyes rendelések közt eltelt időt? Az adatainkat általában egy adatbázisban tároljuk, ezért mindenképpen lekérdezést kell írnunk. Az alábbi lehetőségek közül választhatunk:

  1. A lekérdezés eredményét kitesszük excelbe, sorba rendezzük (ha nem tettük volna meg a lekérdezésben), majd az így kapott adattábla utolsó oszlopa utáni cellákban kiszámítjuk az aktuális és az előző sorban szereplő dátumok közti különbséget.
  2. Már magában a lekérdezésben kiszámítjuk a rendelések közt eltelt időt. Ekkor az alábbihoz hasonló SQL lekérdezést kell írnunk:
    • Adott a rendeléseket tartalmazó tábla (view): Rendelesek

      SELECT partner_id,
                     rendeles_datum,
                     (rendeles_datum-elozo_rendeles_datum) eltelt_napok
        FROM (SELECT R1.partner_id,
                                     R1.rendeles_datum,
                                     (SELECT max(R3.rendeles_datum)
                                         FROM Rendelesek R3
                                       WHERE R3.rendeles_datum > R1.rendeles_datum
                                             AND R3.partner_id = R1.partner_id) elozo_rendeles_datum
                        FROM Rendelesek R1, Rendelesek R2)

      Persze ezt még meg lehet bolondítani azzal, hogy számoljuk ki a rendelések összegét, a pótrendeléseket (kis összegű, a normál megrendelést pár napon belül közvetlenül követő) ne vegyük figyelembe, stb.

    • Amennyiben csak az ügyfelek átlagos rendelési gyakoriságára vagyunk kíváncsiak, van egyszerűbb megoldás is. Vegyük a legkésőbbi és legkorábbi rendelési dátumot, és osszuk el a rendelések közti időszakok számával. Íme:

      SELECT partner_id,
                     ( max(rendeles_datum) - min(rendeles_datum) ) / ( count(*) - 1 )
         FROM Rendelesek
       GROUP BY partner_id
      HAVING COUNT (*) > 1 -- Az 1 rendeléssel rendelkezőknél nem értelmezett az átlagos gyakoriság!






Excel dizájn

Az Excelben való munka során gyakran van szükség komplex, bonyolult összefüggéseket tartalmazó modell felépítésére. A következő néhány tipp segít, hogy a szofisztikáltság ne menjen a használhatóság és átláthatóság kárára.

  • A modell felépítése során érdemes az alábbi általánosan elfogadott tagolást követnünk:

    1. Paraméterek megadása (inputs)
    2. Kalkuláció (calculation)
    3. Eredmény megjelenítése (output)

    Amennyiben a modellünk kevés változót és egyszerű számításokat tartalmaz, ez a hármas tagolás elférhet egy munkalapon is. Ugyanakkor ha sok paraméterrel és összetett kalkulációval van dolgunk, szükséges lehet az egyes részeket külön-külön munkalapokra elhelyezni.

    Személy szerint én a fenti klasszikus hármas bontást kiegészíteném egy 0. és egy 4. ponttal.
    0.   Használati útmutató. 
    Mindenki volt már abban a helyzetben, hogy újoncként vagy szabadságát töltő kollégájától kapott meg egy feladatot, és kellő rutin és ismeret hiányában nem, vagy csak szenvedéssel tudta befejezni. A felhasználóbarátság érdekében minden modellnek egy használati útmutatóval kellene kezdődnie!

    4.   Számítások eredményeinek ellenőrzése

    Összetett és bonyolult, szerkezetében gyakran változó modelleknél nagy az esély, hogy a képletek "elmásznak", ezáltal rossz értékeket kapunk. Az igazi baj akkor kezdődik, amikor ezt nem vesszük észre. Ha van egy olyan része munkafüzetünknek, ami ellenőrzi a főbb sarokszámokat, elejét vehetjük ennek a problémának.


  • Formázás használata

    Legfontosabb szabály: Kerüljük a színek túlzott használatát! A kevesebb néha több.

    1. A paramétereket tartalmazó cellák hátterét érdemes egy állandó színnel jelölni. 
    2. A kalkulációnál lehetőség szerint kerüljük el a színek használatát. A szegélyek használata indokolt lehet pl. egy közbenső eredmény kiemelésére. Ez egyben olvashatóvá, visszafejthetővé teszi számításunkat.
    3. Az eredmények megjelenítésénél bánjunk szűkmarkúan a formázással. Próbáljunk inkább az eredmények megfelelő elrendezésével kiemelni a lényeget.

  • Ellenőrző képletek

    Emberek vagyunk, hibázunk. Éppen ezért soha ne gondoljuk azt, hogy képleteink mindig jól számolnak. Akár egy plusz oszlop utólagos beszúrásával is hibássá válhat egy képlet eredménye.
    Minden esetben használjunk ellenőrző képleteket a fontosabb összegzésekre! Különösen fontos ez akkor, amikor munkafüzeten kívülről is veszünk adatokat, illetve fontos, hogy a számításunk eredménye más munkafüzet értékeivel összhangban legyen.

  • Paraméterek használata

    Mindig használjunk paramétereket, hivatkozásokat! Előnye, hogy csak egyszer kell megadni az inputot, és változásakor azt csak egy helyen kell módosítani. Ha beégetjük a paramétert a képleteinkbe és a paraméter értéke megváltozik, rémálom annak visszakeresése. Ráadásul gondoljunk megint csak arra, hogy esetleg nem csak mi fogjuk a jövőben a munkafüzetet használni!

  • Képletek egyszerűsítése

    Sok esetben nem tudjuk kikerülni, de ahol lehet, ne használjunk többszörösen egymásba ágyazott képletek használatát. Helyette emeljük ki a belső függvényt/képletet egy másik cellába, és erre a cellára hivatkozzunk. Illetve az Excel rengeteg beépített függvénnyel rendelkezik, és lehet találunk egy sokkal alkalmasabb függvényt az általunk eredetileg használni kívántnál.

  • Cella adattartalmáról tájékoztatás

    "Mennyi? 30. Mi 30? Mi mennyi?" Egy szám  önmagában egy külső szemlélőnek (és egy év múlva lehet már nekünk sem) nem sokat mond. Tegyük beszédessé a számainkat azzal, hogy
    • vagy elnevezzük a cellát,
    • vagy az értéket tartalmazó cella mellé/fölé megnevezést írunk
    • vagy megjegyzést szúrunk be a cellához

linkek:
http://www.auditexcel.co.za/Good%20Spread%20Design%20Project.html
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm


2012. szeptember 1., szombat

Összegzés Excelben feltételekkel

Az EXCEL egyik leggyakrabban használt összegző függvénye a SZUM() függvény. Ez használatos akkor, amikor egyszerűen csak össze szeretnénk adni 2 vagy több értéket, tartományt. De mi van akkor, ha az összegzést feltételhez, esetleg feltételekhez szeretnénk kötni? Erre több lehetőségünk is van, lássuk mik is ezek.

Az alábbiakban egy-egy rövid leírást adok a felhasználási lehetőségekről, a részletes ismertetők és példák az adott függvényekhez tartozó linkeken érhetőek el.

RÉSZÖSSZEG()
Egy függőleges listában tudunk pl. összegezni, átlagot, szórást, darabszámot számolni. A függvény első paramétere adja meg az összegzés módját (szum, darab, átlag, szórás, stb.). A második paraméter az a tartomány, amire szeretnénk az összegzést elvégezni.

A szűrési feltételek nem a függvény részei, helyette a második paraméterként megadott listát, tartományt kell szűrnünk.

link: Bejegyzés a RÉSZÖSSZEG függvényről a Microsoft oldalán

SZUMHA()
Egy lista, táblázat adatain végzünk összegzést 1 feltétel alapján. A függvény első paramétere a kiértékelendő tartomány. A második paraméter a szűrési feltétel. A harmadik paraméter az a tartomány, aminek az értékeit szeretnénk összegezni.

A szűrési feltétel a függvény része. Vagy direkt kifejezésként adom meg, vagy egy a feltételt tartalmazó cellára hivatkozom.
link: Bejegyzés a SZUMHA függvényről a Microsoft oldalán

SZUMHATÖBB()
Egy lista, táblázat adatain végzünk összegzést több feltétel alapján. A feltételek között ÉS kapcsolat van. A függvény első paramétere az összegzendő tartomány. A második és harmadik paraméter az első kiértékelendő tartomány és szűrési feltétel. A negyedik és ötödik paraméter a második kiértékelendő tartomány és szűrési feltétel. Legfeljebb 127 tartomány-feltétel pár használatára van lehetőség.

A szűrési feltétel a függvény része. Vagy direkt kifejezésként adom meg, vagy egy a feltételt tartalmazó cellára hivatkozom.

link: Bejegyzés a SZUMHATÖBB függvényről a Microsoft oldalán

SZORZATÖSSZEG()
Egy lista, táblázat adatain végezhetünk összesítést több feltétel alapján. A SZUMHATÖBB függvénytől eltérően nem csak ÉS kapcsolat használatára van lehetőség.

linkek:
http://www.sumproduct.com/thought/multiple-criteria
http://superuser.com/questions/97244/sumproduct-how-do-you-include-an-or-with-it




2012. augusztus 14., kedd

Excel billentyűparancsok

Az excelben lehetőség van billentyűkombinációkkal gyorsítani a munkát.

Az alábbi helyen található egy csokorra való billentyűkombináció: Billentyűparancsok Excel 2010