P&L report–ako urobiť jednoduchý summary

 

Ako som načrtol v predchádzajúcom článku, dnes sa pozrieme na isté prvky ActiveX, respektíve ako ich jednoducho použiť pri tvorbe prehľadov, teda financial summary. Na úvod si ukážeme, kde tieto prvky nájdeme:

Data – Controls – Insert -  Form Control alebo ActiveX Controls

Predtým než začneme, chcel by som poukázať na rozdiel medzi prvkami ActiveX a Form Control. V článku budem používať prvky Form Control. Dôvod? – sú jednoduchšie, a flexibilnejšie v prípade, že váš report bude používať tretia osoba, viac osôb – v prípade Form Control ich nebudú strašiť hlásenia typu “považovať tento dokument za dôveryhodný”. Je síce pravda, že ActiveX prvky sú lepšie, ak sa radi hráte s ich dizajnom. No report je report a ak mám byť úprimný, vo väčšine prípadov na “hrátky” s farbami nie je čas a nikto to neocení.

Vrátim sa k predchádzajúcemu článku, v ktorom som sa zamýšľal nad stavbou reportu. Táto štruktúra je použitá v dnešnom template.

Predchádzajúci obrázok znázorňuje list s parametrami (červená záložka), zdrojové listy podľa krajín (zelené záložky)  Výpočtový Summary.

List Parametre obsahuje štandardizované údaje ako mesiace, popisy stĺpcov, dátumy, menové kurzy a iné (viď obrázok). Obecne je vhodné dané oblasti pomenovať pomocou Defined Names, aby sa nám jednoduchšie manipulovalo s funkciami v Summary.

Listy CZ, SK a HUF sú štandardizované P&L podľa krajín.Všimnite si však popisy stĺpcov, kde mesiace, roky, menu a typ (ACTUAL / BUDGET) sú definované tak, aby korešpondovali s podkladmi v liste Parametre.

 

Poslednému listu Summary budem venovať zvyšok článku. Ako vidíte na obrázku, jedná sa o štandardizovaný prehľad istých ukazovateľov pre mesiac (Current Month) a obdobie od začiatku roka až po súčasnosť (Year to date).

Čo sa však stane v prípade, že máme k dispozícii viac mesiacov a chceme jednoducho vidieť prehľad napríklad od januára do marca? Niekto takúto situáciu rieši kopírovaním listov, v ktorých sú nastavené výpočty pre konkrétny mesiac, tzn. Summary JAN, Summary FEB, a Summary MAR.

No v prípade, že nechceme mať report zbytočne zahltený listami, ale v prípade potreby chceme vyhovieť užívateľom, použijeme Form Controls (Tlačidla v ľavom hornom rohu obrázku). Pomocou prvého “šípkového” si vyberáme aktuálny mesiac a pomocou roztváracej šípky divíziu.

Šípky:

Vložte šípky do listu a kliknite pravým tlačidlom na Format Control… .

Objaví sa Vám okno, v ktorom si nastavíte Minimálnu hodnotu 1 a maximálnu hodnoty 12, zmena hodnoty po kliknutí je 1, položka Current Value: je podľa vlastného uváženia, nemusíte si s ňou lámať hlavu. Dôležitý je však výstup z šípok, teda Cell link: V danej bunke sa uloží hodnota z aktívneho prvku.

Takto zvolené parametre stačí potvrdiť (kliknutím na OK) a potom stačí použiť výstup v bunke A4 ako vstupný atribút do výpočtových častí. Napríklad popis mesiaca vedľa šípok je urobený cez funkciu INDIRECT, ktorá sa jednoducho odkazuje na bunky v liste Parametre (z toho dôvodu sú v stĺpci A v liste Parametre mesiace usporiadané tak, aby 1 – JAN, 2 – DEC, atď.

Rozklikávacia šípka:

Obdobne ako v predchádzajúcom prípade vložíme rozklikávaciu šípku a prejdeme na Format Control… .

Všimnite si, že v Input range: som miesto klasického odkazu na bunky použil pomenovanú oblasť DIV (čo sú divízie v liste parametre). Tým pádom, ak sa v budúcnosti bude používať ďalšia divízia, postačí ju do listu Parametre dopísať a aktualizovať pomenovanú oblasť DIV, ostatné prvky budú fungovať bez nutných úprav.

Drop down lines: som zvolil 3, lebo mám k dispozícii len 3 divízie a výstupnú oblasť som zvolil bunku A2.

Tým pádom pomocou užívateľom vybraných atribútov stačí prevzorcovať tabuľku summary.

 

Predchádzajúci obrázok ilustruje, ako som použil vyhľadávaciu funkciu VLOOKUP, ktorou som hľadal položku SALES – TOTAL, podľa zvolenej divízie a mesiaca. K nej som použil ešte menové ošetrenie tak aby výsledné hodnoty boli vždy v €.

Zvyšná časť summary je o zručnosti pri používaní funkcií a praxi. Každopádne celý template si môžete stiahnuť kliknutím na obrázok Download a “like” na FB kliknutím na logo .

 

           

4 praktické rady, ako automatizovať report

 

Pred nedávnom som mal tú možnosť prezrieť si zaujímavý report zo stavebného priemyslu. Musím sa priznať, bol to jeden z mála reportov, ktorý sa mi páčil svojim spracovaním a jednoduchosťou pre užívateľa.

Z tohto dôvodu som sa rozhodol napísať zopár inšpirujúcich rád, ako na automatizáciu reportu.

1) Roztrieďte si listy v reporte podľa nasledujúcich kategórií (parametrové – obsahujú konštanty ako sú mesiace v roku, roky, meny, divízie apod., databázové- obsahujú vstupné data z informačného systému, s ktorými sa obecne nemanipuluje, výpočtové, resp. Summary (Základný a vecný prehľad reportov) sú listy, ktoré používajú informácie z dátového a parametrového listu. Vytvára sa v nich výpočtová časť a zároveň kontrola či uvedené výpočty boli vytvorené správne.

2) pri používaní výpočtov a kontrol sa snažte vo vzorcoch používať čo najviac pomenované oblasti – Defined Names. Report sa Vám po čase, ak bude potrebné niečo zmeniť, bude čítať ľahšie.Je predsa rozdiel ak po pol roku pri nahliadnutí do vzorčeka vidíte prvý alebo druhý výpočtový proces (oba sú rovnaké, akurát zapísané inou formou).

Na druhú stranu, ak ovládate základy makier, môžete si vytvoriť procedúru, ktorá Vám po vložení aktualizuje veľkosť pomenovanej oblasti. Je to užitočné obzvlášť, ak sa Vám v dátovom liste kumulujú údaje po určitých intervaloch.

3) Kontrolné súčty – čím viac ich budete používať, tým lepšie pre Vás! Jedná sa o porovnávania celkového súčtu hodnôt s agregovanými súčtami podľa divízií apod.. Dôležité je používať pri porovnávaniach správne zaokrúhlenie čísel. Nie je to príjemné hľadať chybu, ak rozdiely v totáloch sú viac menej zanedbateľné.

Táto fáza zaberá veľké množstvo času, no ak sa s reportmi hrajú ešte tretie osoby, je potrebné aby sa im pri “uľahčovaní” práce zobrazilo masívne červené pole, ohlasujúce chyby. ľudia majú tendencie hľadať si skratky a obchádzať systém, čo môže v prípade reportov viesť niekedy ku katastrofe. Používajte pritom podmienené formátovanie s rôznymi výstražnými značkami a farbami.

4) Makra – veľmi účinný nástroj, avšak veľmi nebezpečný ak ho majú používať tretie osoby. Obecne je vhodné ich používať v prípade, že máte navrhnuté vlastné funkcie a taktiež jednoduché procedúry je možné aplikovať obecne. Pokiaľ však disponujete špecifickými, tzv. “case sensitive” procedúrami, odporúčam mať ich v separátnom súbore a do finálneho reportu vkladať výsledky z danej komplexnej procedúry.

Nabudúce si ukážeme zjednodušenú ukážku reportu, ktorý zjednocuje finančné výsledky divízií do jedného Summary a ukážeme si, ako použiť prvky ActiveX pri tvorbe Summary. Nezabúdajte na Facebook .

Funkcia SpecMatch – ako vyhľadať približné názvy v zozname

 

Tento článok je venovaný prevažne tým, ktorí ste si už ten rôčik vytrpeli pri spravovaní reportov a aj napriek tomu, že ovládate nahrať aké – také makro, časové dispozície Vám nedovoľujú špekulovať nad istými nástrahami pri práci.

Inak povedané deadline je deadline a niektoré rutinné veci sa stávajú zvykom alebo skôr otravou analytika.

Pre mňa osobne bola nočnou morou fakturácia. Asi každý finančný analytik (controller) sa s tým stretol. Máte zoznam dodávateľov – odberateľov, na základe ktorého fakturantky kontinuálne vystavujú faktúry, resp. evidujú platby.

Avšak žiaden z nás nie je robot a pri pracovnej vyťaženosti sa stane, že sa na faktúre / platbe firmy ABC s.r.o. vyskytne názov ABC_s.r.o alebo obdobná chybička. Tým pádom sa síce môžeme pokúsiť spoľahnúť na funkcie Match alebo Vlookup, ktoré pri zadávaní posledného parametru ponúkajú možnosť 0 – presné vyhľadávanie alebo 1 – približné vyhľadávanie. Avšak 1 - približné vyhľadávanie nie je dostatočne presné!. viď nasledujúca tabuľka:

Táto skutočnosť nás núti upozorniť fakturantky na lepšiu precíznosť alebo ručne upravovať názvy firiem v záznamoch tak, aby priebežne mohli byť použiteľné pre tvorbu finančného prehľadu, čo sa v prípade, že spravujete veľké množstvo faktúr môže stať nočnou morou.

V prípade, že máte názvy pozostávajúce z rôznorodých znakov, dlhé názvy firiem apod. môžete použiť proces, ktorý:

1) Očistí text o diakritiku

2) Zmení všetky znaky na malé (veľké)

3) Vyčistí názov o nežiaduce znaky ako bodky, čiarky, bodkočiarky, pomlčky, atd.

4) takto očistený text porovná s hodnotami zo zoznamu a priradí im pravý názov

Túto funkciu som nazval SpecMatch, kvázi špeciálna verzia štandardnej funkcie Match. VBA kód pripraví vyhľadávanú hodnotu s hodnotami zo zoznamu a porovnáva počet jednotlivých povolených znakov (celkovo ich je 36).

Inak povedané: spočíta koľko znakov “a” sa nachádza v hľadanom slove a porovná s počtom znakov “a” v prvom slove zo zoznamu. Ak sa počty zhodujú prejde na ďalší znak. V prípade, že procedúra dosiahne zhodu 36 znakov – funkcia vráti slovo zo zoznamu ako originál. VBA kód vyzerá nasledovne:

Function SpecMatch(text1 As String, RNG As Range)

'Deklarácia premenných
Dim Stare As String
Dim Nove As String
Dim i
Dim j
Dim r1
Dim r2
Dim text2
Dim text2c

'Deklarácia konštant
Const Znaky = "abcdefghijklmnopqrstuvxyzw1234567890"
Const SpinavePismenka = "áćĆĎéÉíĹĺńŃóÓŕśŔŚťúÚýźŹÁčČďěĚÍĽľňŇôÔřšŘŠŤůŮÝžŽ"
Const CistePismenka = "acCDeEiLlnNoOrsRStuUyzZAcCdeEiLlnNoOrsRSTuUYzZ"
Const NevhodneZnaky = ",.; &@%_=-/" 'Znaky, podľa vlastných potrieb - skúseností

'Očistíme hľadané slovo
For i = 1 To Len(SpinavePismenka)
    Stare = Mid(SpinavePismenka, i, 1)
    Nove = Mid(CistePismenka, i, 1)
    text1 = Replace(text1, Stare, Nove)
Next
For i = 1 To Len(NevhodneZnaky)
    Stare = Mid(NevhodneZnaky, i, 1)
    Nove = ""
    text1 = Replace(text1, Stare, Nove)
Next

text1 = LCase(text1)

'Prehľadávanie
For Each text2 In RNG

'Očisťujeme každé slovo z RNG pred porovnaním s hľadaným slovom
    text2c = text2

    For i = 1 To Len(SpinavePismenka)
        Stare = Mid(SpinavePismenka, i, 1)
        Nove = Mid(CistePismenka, i, 1)
        text2c = Replace(text2c, Stare, Nove)
    Next
   
    For i = 1 To Len(NevhodneZnaky)
        Stare = Mid(NevhodneZnaky, i, 1)
        Nove = ""
        text2c = Replace(text2c, Stare, Nove)
    Next
   
    text2c = LCase(text2c)
    r1 = 0
    r2 = 0
    For i = 1 To 36
        'Nájde množstvo znaku(i) v text1
        For j = 1 To Len(text1)
            If InStr(j, text1, Mid(Znaky, i, 1)) > 0 Then
                j = InStr(j, text1, Mid(Znaky, i, 1))
                r1 = r1 + 1
            End If
        Next j
        'Nájde množstvo znaku(i) v text2c
        For j = 1 To Len(text2c)
            If InStr(j, text2c, Mid(Znaky, i, 1)) > 0 Then
                j = InStr(j, text2c, Mid(Znaky, i, 1))
                r2 = r2 + 1
            End If
        Next j
        'Ak sa počet znakov nezhoduje, prejde na ďalšie slovo z RNG
        If r1 <> r2 Then
            GoTo DalsizRng
        End If
        'Ak nastala zhoda pre všetkých 36 možných znakov, vráti hodnotu z RNG
        If i = 36 Then
            SpecMatch = text2
            Exit Function
        End If
    Next i

DalsizRng:

Next text2

'V prípade, že nenájde žiadnu zhodu, vráti #VALUE!
SpecMatch = "#VALUE!"

End Function

Vytvorenú funkciu použijeme nasledovne: =SpecMatch(“adresa bunky”;”Adresa zoznamu”), v template súbore je napr.:

 

Výsledok – správny názov potom môžete nahradiť pôvodným a pomocou štandardných vyhľadávacích funkcií spracovať potrebné analýzy. Hotový template si môžete stiahnuť kliknutím na Download a nezabudnite na like na FB.