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 .