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.
Żeby to zrobić, zaznaczamy 1 komórkę z danych, a na karcie Power Pivot wybieramy polecenie Dodaj do modelu danych (rys. 2).
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.
Jeśli wszystko przebiegło poprawnie, to nasza tabela została zaczytana do modelu danych oraz otworzyło się okno Power Pivot (rys. 4).
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.
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.
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.
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).
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.
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.
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.
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.
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ę.
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).
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ę.
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.
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. |
