KHIZHNYAKOVBudżet projektu w Excelu: plan a wykonanie, odchylenia i prognoza
Budżet projektu: plan a wykonanie — szablon Excel — Pobierz za darmo →
Prawie każdy projekt – remont biura, wdrożenie systemu, organizacja konferencji – ma budżet. Rzadko który kończy się dokładnie w nim. Problem nie polega na tym, że plan się nie sprawdza, tylko na tym, że przekroczenie zauważa się za późno, gdy nic już nie da się zrobić.
Zestawienie „plan a wykonanie” (w żargonie: plan-fakt) pokazuje odchylenia na bieżąco, miesiąc po miesiącu i pozycja po pozycji. Poniżej opisuję, jak je zbudować w Excelu, jak poprawnie liczyć odchylenia i jak prognozować koszt projektu na koniec.
Krok 1: struktura budżetu
Zrób dwie tabele o identycznym układzie: „Plan” i „Wykonanie”. W wierszach pozycje (osobno przychody, osobno koszty), w kolumnach miesiące. Kluczowe: nazwy pozycji i kolejność muszą być takie same w obu tabelach. Najprościej wpisać je raz i w drugiej tabeli odwołać się formułą (=Plan!A5).
Przykład: remont lokalu usługowego na 6 miesięcy.
| Pozycja | Plan (cały projekt) | Plan mies. 1–3 | Wykonanie mies. 1–3 |
|---|---|---|---|
| Materiały | 60 000 zł | 36 000 zł | 39 600 zł |
| Robocizna | 45 000 zł | 22 500 zł | 21 000 zł |
| Projekt i nadzór | 12 000 zł | 6 000 zł | 6 000 zł |
Krok 2: odchylenie liczone „do dziś”
To najważniejsza zasada. Jeśli po trzech miesiącach porównasz wykonanie (39 600 zł) z planem na cały projekt (60 000 zł), materiały wyglądają na oszczędność. W rzeczywistości są przekroczone o 10%. Porównuj więc wykonanie z planem za te same miesiące.
Plan narastająco do bieżącego miesiąca (miesiące w C:H, numer bieżącego miesiąca w B1):
=SUMA(PRZESUNIĘCIE(C5;0;0;1;$B$1))
Odchylenie kwotowe: =Wykonanie-Plan → 39 600 − 36 000 = +3 600 zł.
Odchylenie procentowe: =JEŻELI(Plan=0;"";(Wykonanie-Plan)/Plan) → +10%.
Dla kosztów plus oznacza przekroczenie, dla przychodów – minus oznacza niedobór. Warto to rozróżnić w formatowaniu warunkowym, inaczej czerwony kolor będzie mylący.
Krok 3: tolerancja
Nie każde odchylenie wymaga reakcji. Ustal próg, np. 5%. Status pozycji:
=JEŻELI(MODUŁ.LICZBY(F5)<=$B$2;"W normie";JEŻELI(F5>0;"Przekroczenie";"Oszczędność"))
MODUŁ.LICZBY to polska nazwa funkcji wartości bezwzględnej (w angielskim Excelu ABS), a F5 to odchylenie procentowe.
Robocizna: (21 000 − 22 500) / 22 500 = −6,7% – oszczędność poza tolerancją, warto sprawdzić, czy prace nie są po prostu opóźnione.
Krok 4: prognoza na koniec projektu
Są dwie proste metody i każda pasuje do innych pozycji.
Tempo realizacji planu: plan całkowity × (wykonanie / plan do dziś). Materiały: 60 000 × 39 600 / 36 000 = 66 000 zł. Dobra dla pozycji nieregularnych, jednorazowych.
Średnie wykonanie miesięczne: wykonanie / liczba miesięcy × czas trwania. Materiały: 39 600 / 3 × 6 = 79 200 zł. Tu metoda zawodzi, bo większość materiałów kupuje się na początku. Za to dla stałych pozycji, jak najem kontenera czy abonament, sprawdza się świetnie.
Wniosek: metodę dobieraj do charakteru pozycji, a prognozę traktuj jako sygnał ostrzegawczy, nie wyrocznię.
Krok 5: sumy narastające i wykres
Dodaj wiersz z kosztami narastająco dla planu i wykonania i zrób wykres liniowy. Dwie linie, które się rozjeżdżają, mówią więcej niż tabela z liczbami – świetnie sprawdzają się na spotkaniach.
Najczęstsze błędy
- Porównywanie częściowego wykonania z planem całego projektu.
- Różne nazwy pozycji w planie i wykonaniu – SUMA.JEŻELI ich nie połączy.
- Jeden kolor dla wszystkich odchyleń, niezależnie od tego, czy to koszt, czy przychód.
- Zmienianie planu „po cichu”. Jeśli budżet zostaje zaktualizowany, zachowaj pierwotną wersję w osobnej kolumnie.
Gotowy szablon: budżet projektu – plan a wykonanie
Nasz darmowy szablon ma wszystkie te mechanizmy wbudowane:
- „Parametry”: nazwa, pierwszy miesiąc, czas trwania do 24 miesięcy, tolerancja odchylenia i metoda prognozy do wyboru.
- „Plan” i „Wykonanie”: do 10 pozycji przychodów i 20 pozycji kosztów w miesiącach; nazwy pozycji wpisujesz tylko raz.
- „Plan-wykonanie”: plan i wykonanie do bieżącego miesiąca, odchylenie kwotowo i w %, stopień realizacji planu, prognoza na koniec projektu i status każdej pozycji.
- „Odchylenia”: różnica według pozycji i miesięcy z czerwonym i zielonym wyróżnieniem.
- „Narastająco” i „Podsumowanie”: sumy narastające, wynik projektu, największe przekroczenia i wykresy „plan a wykonanie”.
Nadmiarowe miesiące stają się szare i nie wchodzą do obliczeń. Plik działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Obliczenia mają charakter orientacyjny.
Pytania i odpowiedzi
Jak często aktualizować wykonanie?
Najlepiej raz w miesiącu, po zamknięciu faktur. Przy krótkich i intensywnych projektach – co tydzień, ale wtedy plan też powinien być tygodniowy.
Czy budżet projektu liczyć netto czy brutto?
Jeśli firma odlicza VAT, liczy się netto, bo VAT nie jest kosztem. Jeśli nie odlicza – brutto. Ważne, by plan i wykonanie były w tej samej konwencji.
Co zrobić z wydatkami, których nie było w planie?
Dodaj pozycję „Nieprzewidziane” z planem zero albo z rezerwą 5–10% budżetu. Wtedy takie koszty nie znikają w innych pozycjach i widać, ile ich było.
Czy taki arkusz zastąpi program do zarządzania projektami?
Dla budżetu małego i średniego projektu w zupełności wystarcza. Harmonogram zadań czy przydział ludzi lepiej prowadzić osobno.
