Wykorzystanie Excela jako narzędzia raportowania managerskiego

W procesach zarządzania najważniejsza jest informacja. W obecnych czasach znacznie wzrosły możliwości zbierania i przetwarzania danych ekonomicznych i finansowych. Na rynku jest wiele gotowych narzędzi umożliwiających efektywne raportowanie w oparciu o zgromadzone w bazach danych informacje. Wdrożenie takich systemów wymaga specjalnych procesów oraz bywa wysoce kosztochłonne.

Z drugiej strony arkusz kalkulacyjny Excel kojarzony jest głównie z narzędziem analitycznym a nie raportowym. W tym miejscu pojawia się niedoceniana funkcjonalność Excela, która została znacznie rozbudowana w wersji 2007. Zanim jednak przejdziemy do budowy raportu opartego na tabelach przestawnych, należy zwrócić uwagę na specyficzny układ danych pozwalający na efektywne wykorzystanie narzędzi raportowych.
Aby móc skorzystać w pełni z technik raportowych, dane powinny być ewidencjonowane w układzie bazodanowym. Układ ten zakłada istnienie tzw. rekordów, które opisane są polami. W naszym przykładzie rekordy to sprzedaż w poszczególnych województwach w Polsce, natomiast pola to:

• nazwa produktu, którego dotyczy wynik (A, B, C),
• miesiąc, w którym zaewidencjonowano wyniki (1-12),
• wartość przychodów dla produktu w danym miesiącu,
• wartość kosztów dla produktu w danym miesiącu.

Celem analizy jest przygotowanie tablicy wyników (dashboard), która dla zadanego miesiąca analizy pokazywałaby marże pokrycia (przychód-koszty zmienne)/przychód, dla każdego produktu i województwa w danym miesiącu oraz narastająco od początku roku.

W pierwszej kolejności należy określić wartości przychodów i kosztów dla wariantu miesięcznego i wariantu narastającego. W tym celu należy wykorzystać funkcje „JEŻELI”, aby w odrębnych polach wskazać wartości przychodów i kosztów przynależne do danego miesiąca oraz przynależne do wszystkich wcześniejszych miesięcy z bieżącym włącznie.
Indeks miesiąca raportowego wpisany został w arkuszu „Raport”, w komórce C5. W zależności od zgodności z faktycznym miesiącem, w którym zaewidencjonowano wyniki, w dodatkowych polach wykazana zostanie wartość przychodu/kosztu lub zero.

W ten sposób tabela danych w zakładce „Baza” została uzupełniona o dodatkowe pola:

• przychody dla danego miesiąca raportowego (P Msc),
• przychody od początku roku (P YTD),
• koszty dla danego miesiąca raportowego (K Msc),
• koszty od początku roku (K YTD).

Tak powstała baza danych jest podstawą utworzenia tabeli przestawnej. W tym celu z Menu „Wstawianie” należy wybrać ikonę „Tabela przestawna” i zaznaczyć zakres danych, z których tabela przestawna powstanie.
Dodatkowo wskazujemy miejsce, gdzie tabela przestawna zostanie ulokowana. Mamy do dyspozycji nowy arkusz oraz wskazane przez nas miejsce. W naszym przykładzie tabelę przestawną wstawiamy do arkusza „Raport”.

Po utworzeniu tabeli przestawnej w arkuszu „Raport” pojawi się widok jej konstrukcji. W oknie po prawej stronie widoczne są pola z bazy danych, które możemy umiejscowić jako:

• nagłówek wiersza,
• nagłówek kolumny,
• wartość danych,
• zmienną sekcji.

W tworzonym raporcie powinna pojawić się wartość marży pokrycia, dlatego w kolejnym kroku powinniśmy tę wartość obliczyć, korzystając z narzędzia „Formuły” w menu „Opcje” tabeli przestawnej.

Tworzymy nowe pole obliczeniowe „Marża Miesięcznie” i ustawiamy formułę (Przychód Miesięczny minus Koszt Miesięczny) dzielony przez Przychód miesięczny. Podobną funkcję przygotowujemy dla wartości pola „Marża Narastająco”.

Powstałe pola obliczeniowe dodajemy do siatki projektu w miejscu wartości danych, w pozycji nagłówki wiersza wstawiamy pole danych województwo, natomiast jako nagłówki kolumn wskazujemy nazwę produktu.
Powstałą tabelę przestawną musimy jeszcze sformatować, tak aby mogła stanowić oczekiwany raport. Do wyboru mamy zdefiniowane projekty w zakładce „Projektowanie”.

Warto zwrócić uwagę na nowo powstałą funkcję w wersji 2007, tj. układ kompaktowy, który zapewnia czytelną kompozycję raportu.

Na koniec pozostaje odpowiedni dobór opcji tabeli przestawnej, a przede wszystkim:

• Wyłączenie opcji automatycznego dopasowania szerokości kolumn, tak aby wygląd raportu pozostawał niezmienny.
• Oznaczenie opcji pokazywania pustych pól w przypadku błędnych wyników. Jest to szczególnie użyteczne w przypadku powstania wyniku „Dzielone przez zero”. Dzięki temu w wynikach tabeli pojawi się puste pole a nie zgłoszenie błędu.

W rezultacie otrzymamy raport w formie dashboardu.

Zmieniając w komórce C5 wartość miesiąca raportowego, możemy uzyskać pożądany raport poprzez odświeżenie danych. Co więcej, przygotowanie takiego raportu nie będzie wymagać od nas w przyszłości szczególnych nakładów pracy. Wystarczy ustawienie źródła danych i po wklejeniu wartości do zakładki „Baza” raport wygeneruje się automatycznie.
Możliwości raportowe Excela znacznie wykraczają poza powyższy przykład a opracowany dashboard jest jedynie częścią możliwego do uzyskania raportu. Technika tabel przestawnych umożliwia tworzenie wielowymiarowych zestawień oraz pełną integrację ze źródłami danych. Zamiast wklejania danych do zakładki „Baza” można ustanowić połączenie z Bazą Accesa lub poprzez ODBC z innymi bazami opartymi na technologii SQL Server, Oracle i innych.