KPI to bez wątpienia parametry, które są ważne dla firmy, dla jej rozwoju i wzrostu. Wskaźniki można mnożyć, dopasowywać do tego, komu mają służyć, tworzyć ich warianty. Wybór kluczowego wskaźnika wydajności zależy od ciebie. Ten artykuł ułatwi ci jego obliczanie i prezentację za pomocą Power Pivot – dodatku do Excela. Dodatek Power Pivot można zainstalować do Excela od wersji 2013.
Funkcjonalność KPI w Power Pivot

KPI na podstawie wartości bezwzględnej

Zacznijmy od przykładu, w którym KPI będzie stosunek kosztów do przychodu. We wcześniejszej części artykułu zwracaliśmy uwagę, że możemy patrzeć na wskaźniki niejako z dwóch stron. W tym przykładzie zamiast na stosunek kosztów do przychodu moglibyśmy patrzeć na stosunek zysku do przychodu. W pierwszym przykładzie im mniejsza wartość wskaźnika, tym lepiej, w drugim odwrotnie, to wysoka wartość wskaźnika (stosunku zysku do przychodu) zagwarantuje nam sukces.

Dodawanie danych do modelu danych Power Pivot

Mamy przygotowane dane (rys. 1). Widać, że jedna z kolumn zawiera przychód, a inna koszty sprzedaży (KWS – koszt własny sprzedaży, czyli wszystkie koszty związane ze sprzedażą). Obie są nam potrzebne do obliczenia naszego wskaźnika, ale to obliczenie chcemy zrobić już w Power Pivot, a nie „zwykłym” Excelu, dlatego najpierw musimy nasze dane zaczytać do Power Pivot.

Rysunek 1. Dane sprzedażowe [Źródło: opracowanie własne]

Żeby to zrobić, zaznaczamy 1 komórkę z danych, a na karcie Power Pivot wybieramy polecenie Dodaj do modelu danych (rys. 2).

Rysunek 2. Polecenie Dodaj do modelu danych Power Pivot [Źródło: opracowanie własne]

Ponieważ nasze dane nie były tabelą w rozumienia Excela, a Power Pivot tego wymaga, żeby poprawnie je zaczytać do modelu danych, to pojawia się nam okno tworzenia tabeli (rys. 3), w którym musimy się upewnić, że jest zaznaczony prawidłowy obszar danych oraz to, że nasze dane mają nagłówki.

Rysunek 3. Okno tworzenia tabeli [Źródło: opracowanie własne]

Jeśli wszystko przebiegło poprawnie, to nasza tabela została zaczytana do modelu danych oraz otworzyło się okno Power Pivot (rys. 4).

Rysunek 4. Okno Power Pivot z zaczytanymi danymi [Źródło: opracowanie własne]

Ważne, żeby oprócz zaczytanej tabeli był widoczny Obszar obliczeń, czyli kratkowany obszar pod tabelą (rys. 4 – obszar obliczeń włączysz, klikając odpowiednie polecenie na karcie Narzędzia główne w oknie Power Pivot).

W Excelu tworzylibyśmy dodatkową kolumnę obliczającą stosunek kosztów do przychodu. W Power Pivot stworzymy tzw. miarę jawną (pole obliczeniowe). To już prawie będzie nasz KPI.

Tworzenie miary

Żeby stworzyć miarę w Power Pivot, musimy zaznaczyć dowolną komórkę w obszarze obliczeń, a następnie w pasku formuły napisać odpowiednią formułę. Formuła dla miary zaczyna się od jej nazwy. W naszym przykładzie niech to będzie „% Kosztów”. Dokładnie taką frazę wpisujemy na początek formuły, a następnie znak dwukropka i równa się. Teraz możemy zacząć pisać faktyczne obliczenia. Są one bardziej skomplikowane niż w Excelu, bo nie możemy po prostu podzielić kosztów ([KWS]) przez przychód ([Przychód]). Musimy je najpierw zsumować, czyli cała nasza formuła dla miary, będzie wyglądała tak:

% Kosztów:=SUM([KWS])/SUM([Przychód])

Odpowiedni wynik wstawi się w komórkę z obszaru obliczeniowego (rys. 5), która była zaznaczona, gdy zaczęliśmy tworzyć naszą miarę. Możemy go jeszcze sformatować jako procenty, żeby lepiej oddawał sens naszemu obliczeniu.

Rysunek 5. Wpisana formuła miary i jej wynik [Źródło: opracowanie własne]

Tabela przestawna

Teraz możemy stworzyć tabelę przestawną na podstawie naszej tabeli sprzedaży zaczytanej do modelu danych. Po prostu w oknie Power Pivot na karcie Narzędzia główne klikamy w odpowiednie polecenia. Pojawi nam się okno tworzenia tabeli przestawnej (rys. 6), w którym decydujemy, gdzie chcemy, żeby została utworzona nasza tabela przestawna. My chcemy ją utworzyć na nowym arkuszu.

Rysunek 6. Tworzenie tabeli przestawnej z modelu danych Power Pivot [Źródło: opracowanie własne]

Gdy na nowym arkuszu został przygotowany obszar pod tabelę przestawną, wystarczy, że przeciągniemy pole Województwo do obszaru etykiet wierszy, pole Sprzedawca do obszaru etykiet kolumn, a stworzoną przez nas miarę (% kosztów) do obszaru wartości (wyróżnia się ona, ponieważ zaczyna się ona od symbolu fx). Warto zmienić układ tabeli przestawnej na układ tabelaryczny i mamy wyliczone wskaźniki dla województw i sprzedawców (rys. 7), ale nie są to jeszcze kluczowe wskaźniki (KPI), ponieważ najważniejsze w kluczowych wskaźnikach efektywności jest określenie pożądanej wartości – sprowadza się to do wyznaczenia progów. W naszym przykładzie możemy przyjąć, że wartość % kosztów, z której jesteśmy zadowoleni, to 25%, poniżej 30% jest już OK, ale powyżej jest źle.

Rysunek 7. Wyliczone wskaźniki (miary) % kosztów dla województw i sprzedawców [Źródło: opracowanie własne]

Tworzenie KPI

Wystarczy, że zaznaczymy pojedyczną komórkę w tabeli przestawnej z obliczeniami i klikniemy polecenie Nowy wskaźnik KPI na karcie Power Pivot (rys. 8).

Rysunek 8. Polecenie Nowy wskaźnik KPI [Źródło: opracowanie własne]

Otworzy się okno tworzenia nowego KPI (rys. 9), w którym musimy zmienić niektóre ustawienia. Na samej górze, na liście rozwijanej, powinna być wybrana nasza miara (‘% kosztów’). Poniżej wybieramy opcję Wartość bezwzględna (opcję Miara omówimy w kolejnym przykładzie). W powiązanym z nim polu wpisujemy 0,2 (20%) – to jest cel, do którego dążymy, do takiego poziomu chcemy obniżyć nasze koszty. Następnie wybieramy kolorowy pasek, który kolor zielony ma po lewej stronie i odpowiednio ustawiamy progi na kolor żółty (0,25–25%, stan kosztów jest OK) i kolor czerwony (0,3–30%, poziom kosztów nie jest akceptowalny). Jeśli chcesz, możesz też poeksperymentować ze stylami ikon – największe różnice zobaczysz, gdy przejdziesz z zestawów składających się z trzech ikon, na te, które mają pięć – będzie wtedy więcej progów. My już możemy zatwierdzić nasz KPI przyciskiem OK.

Rysunek 9. Okno tworzenia nowego KPI [Źródło: opracowanie własne]

KPI w tabeli przestawnej

Stan KPI powinien dodać się od razu do tabeli przestawnej. Przeważnie domyślnie nie zaczyna się wyświetlać jako ikony, ale wystarczy go odznaczyć i zaznaczyć ponownie, żeby zaczęły się wyświetlać ikony (rys. 10), bo właśnie na tym nam zależało, żeby nasz KPI był wizualny.

Rysunek 10. Tabela przestawna z dodanym stanem KPI [Źródło: opracowanie własne]

W oknie pól tabeli przestawnej nastąpiły istotne zmiany (rys. 10) – zostały dodane nowe pola Cel i Stan. Są one powiązane z miarą % Kosztów i wszystkie razem zostały zgrupowane pod KPI (światła drogowe). Pole Stan pozwala nam wyświetlać w tabeli przestawnej ikonę powiązaną ze stanem wartości KPI.

Pole Cel jest mniej interesujące, ponieważ przechowuje wszędzie taką samą wartość (0,2–20%, czyli stan kosztów, do jakiego zmierzamy). Może być przydatne, jeśli chcielibyśmy przedstawić nasze dane na wykresie, żeby był widoczny poziom KPI, do jakiego zmierzamy.

Jeśli stwierdzilibyśmy, że chcemy zmodyfikować KPI, albo całkiem go usunąć, możemy rozwinąć opcję Wskaźniki KPI na karcie Power Pivot (rys. 8) i wybrać polecenie Zarządzaj wskaźnikami KPI. Otworzy się wtedy okno (rys. 11), w którym wybieramy wskaźnik, który chcemy zmienić, i klikamy odpowiednio przycisk Edytuj lub Usuń, w zależności od akcji, którą chcemy przeprowadzić. Przycisk edytuj przeniesie nas do znanego już nam okna Kluczowych wskaźników wydajności (rys. 9), gdzie możemy zmodyfikować jego parametry.

Rysunek 11. Okno zarządzania wskaźnikami KPI [Źródło: opracowanie własne]

W tym przykładzie omówiliśmy już wszystkie najistotniejsze elementy. Chcemy omówić jeszcze jeden przykład, żeby pokazać, jak oprzeć KPI na podstawie innej miary.

KPI na podstawie obliczonej miary

Posłużymy się prostym przykładem, gdzie mamy koszty zaplanowane i faktyczne koszty (rys. 12). Nasz KPI ma pokazać, jak dobrze szacujemy, czyli jak procentowo koszt faktyczny różni się od planowanego.

Rysunek 12. Dane pokazujące koszty zaplanowane i koszty faktyczne [Źródło: opracowanie własne]

W pierwszej kolejności musimy dodać naszą tabelę do modelu danych. To już robiliśmy (rys. 2). Po dodaniu tabeli do modelu danych potrzebujemy dodać do nich dwie miary. Tym razem postąpimy nieco inaczej niż w poprzednim przykładzie (rys. 5) – nie będziemy pisać miary sami, ponieważ potrzebujemy tylko sum po odpowiednich kolumnach. Dlatego najpierw zaznaczamy kolumnę Koszty, a później rozwijamy opcje Autosumowanie na karcie Narzędzia główne (rys. 13) i wybieramy sumę.

Rysunek 13. Tworzenie miary za pomocą opcji autosumowania [Źródło: opracowanie własne]

Co najwyżej, możemy zmienić nazwę miary, ale nie jest to najistotniejsze dla naszego przykładu. Analogicznie dodajemy miarę dla kolumny Koszty faktyczne. Warto zmienić formatowanie miar na wolutowe, żeby od razu pokazywało się tak sformatowane w tabeli przestawnej.

Gdy mamy dwie miary, zaznaczamy miarę obliczającą sumę kolumny Koszty planowane i w oknie Power Pivot w karcie Narzędzia główne klikamy na polecenie Utwórz wskaźnik KPI (rys. 14).

Rysunek 14. Dodawanie KPI w oknie Power Pivot [Źródło: opracowanie własne]

Otworzy się znane już nam okno, ale kilka rzeczy ustawimy w nim inaczej (rys. 15). Po pierwsze, opcję Zdefiniuj wartość docelową opieramy o miarę Sum of Koszty faktyczne. Następnie wybieramy pasek kolorów z zielonym w środku i odpowiednio ustawiamy progi procentowo do wskazanej przez nas wcześniej miary – Sum of Koszty faktyczne. Oznacza to, że jeśli wartość miary Sum of Koszty faktyczne jest w przedziale od 95% do 105% miary Sum of Koszty planowane, to nasze KPI jest dobre (zielone) i nasze planowanie sprawdza się.

Rysunek 15. Okno tworzenia KPI na podstawie innej miary [Źródło: opracowanie własne]

Gdy już dodamy KPI, możemy, podobnie jak w pierwszym przykładzie, stworzyć tabelę przestawną na nowym arkuszu i odpowiednio przeciągać pola (rys. 16). Ważne, że w tabeli przestawnej chcemy korzystać ze stworzonych przez nas miar (symbole fx przed nazwami), a nie z polami (nazwami kolumn) tabeli przestawnej.

Rysunek 16. Tabela przestawna kosztów faktycznych do kosztów planowanych [Źródło: opracowanie własne]

Jeśli poćwiczysz, jak działają KPI w Power Pivot, to na pewno pozwolą ci one zwrócić uwagę, w których obszarach działalność twojej firmy idzie w dobrym kierunku, a w których trzeba popracować nad kluczowymi wskaźnikami wydajności. |

Możesz zobaczyć ten artykuł, jak i wiele innych w naszym portalu Controlling 24. Wystarczy, że klikniesz tutaj.

Pobierz pliki:

Ulubione Drukuj

Zobacz również

Ciągi w Excelu

Ciągi w Excelu

Przy tworzeniu raportów bądź podsumowań, często korzysta się z różnego rodzaju ciągów. Programiści Microsoftu zdają sobie z tego sprawę, dlatego w Excelu można tworzyć różnorodne ciągi.

Czytaj więcej

Wizualizacja KPI w Excelu, czyli jak zbudować gauge chart

Wizualizacja KPI w Excelu, czyli jak zbudować gauge chart

Wizualizacja, obok analizy danych, jest niezwykle istotną częścią pracy kontrolera finansowego. To, jak wizualizujemy dane ma znaczny wpływ na to, jak je zrozumieją menedżerowie. Jednym z ciekawszych, choć bardziej pracochłonnych wykresów jest gauge chart (miernik), który pokazuje graficznie performance wybranego przez nas KPI. Niniejszy artykuł opisuje jak krok po kroku stworzyć taki wykres, a także jak powinny wyglądać dane wejściowe, by taki wykres skutecznie zbudować.

Czytaj więcej

Polecamy

Przejdź do

Partnerzy

Reklama

Polityka cookies

Dalsze aktywne korzystanie z Serwisu (przeglądanie treści, zamknięcie komunikatu, kliknięcie w odnośniki na stronie) bez zmian ustawień prywatności, wyrażasz zgodę na przetwarzanie danych osobowych przez EXPLANATOR oraz partnerów w celu realizacji usług, zgodnie z Polityką prywatności. Możesz określić warunki przechowywania lub dostępu do plików cookies w Twojej przeglądarce.

Usługa Cel użycia Włączone
Pliki cookies niezbędne do funkcjonowania strony Nie możesz wyłączyć tych plików cookies, ponieważ są one niezbędne by strona działała prawidłowo. W ramach tych plików cookies zapisywane są również zdefiniowane przez Ciebie ustawienia cookies. TAK
Pliki cookies analityczne Pliki cookies umożliwiające zbieranie informacji o sposobie korzystania przez użytkownika ze strony internetowej w celu optymalizacji jej funkcjonowania, oraz dostosowania do oczekiwań użytkownika. Informacje zebrane przez te pliki nie identyfikują żadnego konkretnego użytkownika.
Pliki cookies marketingowe Pliki cookies umożliwiające wyświetlanie użytkownikowi treści marketingowych dostosowanych do jego preferencji, oraz kierowanie do niego powiadomień o ofertach marketingowych odpowiadających jego zainteresowaniom, obejmujących informacje dotyczące produktów i usług administratora strony i podmiotów trzecich. Jeśli zdecydujesz się usunąć lub wyłączyć te pliki cookie, reklamy nadal będą wyświetlane, ale mogą one nie być odpowiednie dla Ciebie.