Monitoring wskaźników działów w Excelu: plan, wykonanie, semafor, ranking

Każdy dział ma swoje wskaźniki: sprzedaż, liczbę obsłużonych zgłoszeń, czas realizacji, poziom reklamacji. Zwykle każdy prowadzi je we własnym pliku, a na koniec miesiąca ktoś zbiera liczby, ujednolica formaty i sprawdza, kto jest w planie. To praca, którą da się zautomatyzować.

Poniżej opisuję, jak w Excelu zbudować system monitoringu: tabelę planu, tabelę wartości rzeczywistych, formuły wykonania, semafor i ranking z wagami. Sprawdzi się w firmie, urzędzie i jednostce budżetowej.

Trzy elementy systemu

Słownik wskaźników

Dla każdego wskaźnika zapisz: nazwę, jednostkę, kierunek (więcej = lepiej albo mniej = lepiej), sposób kumulacji (suma lub średnia) i wagę. Kierunek jest kluczowy. Sprzedaż ma rosnąć, a czas reakcji maleć, i oba trzeba oceniać odwrotnie.

Plan

Wiersz to para „dział – wskaźnik”, kolumny to miesiące z wartościami planowanymi.

Wartości rzeczywiste

Tu wpisujesz tylko liczby z kolejnych miesięcy. Dział i wskaźnik niech uzupełniają się z planu, żeby nie dublować danych.

Jak liczyć wykonanie

Dla wskaźników, w których więcej znaczy lepiej:

=JEŻELI.BŁĄD(Rzeczywiste/Plan;"")

Dla wskaźników, w których mniej znaczy lepiej, dzielisz odwrotnie: plan przez wartość rzeczywistą. Aby jedno ogromne przekroczenie nie zafałszowało całości, wprowadź górne ograniczenie wykonania (np. 120%) w osobnej komórce i użyj MIN(wynik;limit).

Narastająco od początku roku

Dla wskaźników o charakterze sumy (np. sprzedaż) sumujesz plany i wykonania od stycznia do miesiąca raportowego:

=SUMA(INDEKS(C2:N2;1):INDEKS(C2:N2;$B$1))

gdzie B1 to numer miesiąca raportowego. Dla wskaźników o charakterze średniej (np. czas reakcji) liczysz średnią z miesięcy, w których wpisano wartość rzeczywistą.

Semafor

Ustal dwa progi: zielony (np. 100%) i żółty (np. 90%). Status:

=JEŻELI(E2>=$B$1;"Wykonany";JEŻELI(E2>=$B$2;"Ryzyko";"Opóźnienie"))

Progi trzymaj w osobnych komórkach i ustal je według własnych dokumentów. Do kolorowania użyj formatowania warunkowego.

Przykład

Dział sprzedaży: plan narastająco na koniec marca 300 000 zł, wykonanie 276 000 zł, czyli 92%. Przy progach 100% i 90% to status „Ryzyko”. Dział obsługi: plan czasu reakcji 4 godziny, rzeczywisty 5 godzin. Wykonanie to 4 / 5 = 80%, czyli „Opóźnienie”.

Ranking z wagami

Wskaźniki nie są równie ważne, więc ranking działów liczy się jako średnia ważona wykonań: SUMA.ILOCZYNÓW(wykonanie;waga)/SUMA(waga). Kolejność ustalisz funkcją POZYCJA. Pamiętaj, aby wagi sumowały się w sensowny sposób i były uzgodnione z kierownictwem, bo od nich zależy, kto jest pierwszy.

Dynamika

Porównanie z poprzednim miesiącem, czyli różnica wykonania między bieżącym a poprzednim miesiącem, pozwala zauważyć trend, zanim semafor zmieni kolor.

Najczęstsze błędy

1. Jeden kierunek oceny dla wszystkich wskaźników.
2. Brak limitu wykonania, więc jeden dział z 300% zawyża cały ranking.
3. Dane rzeczywiste wpisywane w kilku miejscach.
4. Zmiana progów w trakcie roku bez zapisu.
5. Ocenianie działów po jednym wskaźniku.

Gotowy szablon monitoringu wskaźników

Płatny szablon zawiera cały ten system. Wpisujesz plan i wartości rzeczywiste według miesięcy, a Excel liczy wykonanie narastająco od początku roku, ocenia je semaforem, porównuje z poprzednim miesiącem i buduje ranking działów z wagami.

Arkusze:

• „Słowniki”: rok, miesiąc raportowy, progi semafora, działy i wskaźniki (jednostka, kierunek, kumulacja, waga).
• „Plan”: do 200 wierszy „dział – wskaźnik” z wartościami planowanymi według miesięcy.
• „Wartości rzeczywiste”: wpisujesz tylko wartości, dział i wskaźnik uzupełniają się same.
• „Obliczenia”: wykonanie narastająco, semafor i dynamika.
• „Podsumowanie”: ranking działów, liczba wskaźników w strefach zielonej, żółtej i czerwonej, dynamika według miesięcy i dwa wykresy.

Wynik jest ograniczony parametrem „Maksimum wliczane”. Szablon przyda się kierownikom i analitykom w firmach, urzędach i jednostkach budżetowych. Działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. W zestawie jest pusta tabela i wypełniony przykład z fikcyjnymi danymi. Plany, progi i wagi ustaw według własnych dokumentów, a obliczenia mają charakter orientacyjny.

FAQ

Ile wskaźników powinien mieć dział?

Kilka kluczowych. Przy kilkunastu wskaźnikach trudno ocenić, co jest naprawdę ważne, a ranking traci sens.

Jak ustalić wagi?

Wspólnie z kierownictwem, zanim pojawią się wyniki. Wagi zmieniane po fakcie podważają zaufanie do rankingu.

Czy semafor wystarczy do oceny działu?

To sygnał, a nie ocena. Czerwony wskaźnik wymaga rozmowy o przyczynach, a nie automatycznych wniosków.

Co z miesiącami bez danych?

Zostaw komórki puste. Wskaźniki średnie liczą się tylko z miesięcy z wpisaną wartością, więc puste pola nie obniżają wyniku.