Jeden z czytelników mojego bloga tworzy tabelę raportującą sprzedaż w danym miesiącu. Zwrócił się do mnie z następującym problemem:

(…) planuję zrobić tabelę targetową do swojego sklepu. Mam wszystko, ale brakuje mi formuły, która zwracałaby wartość średniego braku do wykonania planu na dzień bez zbędnych tabelek gdzie wpisywałoby się dni do przepracowania itd. Chodzi mi o to, że pracownik wpisuje kwotę utargu 10 dnia miesiąca i otrzymuję w innej komórce odpowiedź: ile przez następne 20 dni musi średnio zrobić dziennie, aby wyrobić plan. W drugiej komórce pokazuje się na ile procent targetu pracownik idzie w danym dniu.

W tym artykule pokażę wam jak obliczyć:
1. dzienny plan sprzedaży tworzony na podstawie dotychczasowej sprzedaży i
2. estymację wykonania planu na podstawie dotychczasowej sprzedaży

Formatka wygląda następująco:

Najpierw obliczę liczbę dni roboczych w miesiącu. Tabela z danymi zawiera numery tych dni w kolumnie Days. Można to policzyć oczywiście na wiele innych sposobów – ja wybrałam taki (wpisz w komórce B2):

=ILE.LICZB(A5:A24)

Dzienny plan sprzedaży tworzony na podstawie dotychczasowej sprzedaży

Obliczę tutaj ile każdego dnia powinnam sprzedać, aby na koniec miesiąca zrealizować plan. Ustalę zatem dzienny plan sprzedaży, biorący pod uwagę dotychczasową sprzedaż w danym miesiącu.

Plan ten (dzienny) będzie to różnica między planem miesięcznym (target) a sumą dotychczasowej sprzedaży, podzielona przez pozostałą liczbę dni roboczych w danym miesiącu. Matematycznie wzór wygląda następująco:

A w Excelu należy wpisać następującą formułę do komórki C5:

=($B$1-SUMA($B$5:B5))/($B$2-A5)

Można tę formułę oczywiście stuningować:

1. aby wartości wyświetlane były jedynie w przypadku, gdy danego dnia jest jakakolwiek sprzedaż,
2. aby były zaokrąglane w górę do pełnych złotych,
3. aby w przypadku ostatniego dnia sprzedaży pozostały plan wynosił tyle, ile brakuje do realizacji planu miesięcznego. Jeśłi plan został zrealizowany – zostanie wyświetlone zero (0) – o to zadba funkcja MAX

W tym celu należny wpisać wpisać taką formułę:

=JEŻELI.BŁĄD(JEŻELI(B5>0;ZAOKR.GÓRA(($B$1-SUMA($B$5:B5))/($B$2-A5);0);"");MAX(0;$B$1-SUMA($B$5:B5)))

Zaokrąglam, ponieważ przy ustalaniu planów nie interesują mnie grosze, a jedynie pełne złotówki (często nawet spotykam się z zaokrąglaniem do pełnych dziesiątek złotych!). Polecam zaokrąglić w górę, ponieważ mamy wtedy pewność, że dzięki temu na pewno uzyskamy w sumie wartość pokrywającą plan. A „od przybytku głowa nie boli” 🙂

Wyświetlanie wartości jedynie wtedy, gdy w kolumnie Sales jest wartość sprzedaży jest konieczna, ze względu na fakt, że Excel liczy wtedy faktycznie zrealizowaną sprzedaż i ignoruje puste komórki (gdyby nie dać tego warunku – puste komórki zostałyby potraktowane jako zerowa sprzedaż). Takie rozwiązanie utrudnia odczytanie raportu „na pierwszy rzut oka”, trzeba się w niego wczytywać, a mi zależy na czytelności raportu.

A oto wynik:

I wersja wideo:

Estymacja wykonania planu na podstawie dotychczasowej sprzedaży

Tutaj (kolumna EOM Target %) obliczę „na ile planu idę”. To znaczy, jaki procent planu wykonam na koniec miesiąca, jeżeli do tej pory uzyskałam określoną sprzedaż. Oczywiście jest to wartość estymowana.

Istnieje wiele sposobów na obliczenie tej wartości. Wiele zależy od specyfiki branży, w jakiej się pracuje (np. większa/mniejsza sprzedaż na koniec/początek miesiąca), od motywacji przedstawicieli handlowych (np. na koniec roku „dopychają sprzedaż”, ponieważ mają wtedy rozliczane premie) i wielu innych. Polecam ci dostosowanie metody do swoich potrzeb – ja, pokażę ci metodę uniwersalną. Postanowiłam skorzystać po prostu z proporcji, gdyż jest odporna na wahania trendu sprzedaży.

Szukany procent wykonania planu to szacowana sprzedaż na koniec miesiąca podzielona przez plan (target). Pytanie więc jak oszacować sprzedaż na koniec miesiąca?

Użyję do tego prostej proporcji. Moje rozumowanie wygląda tak: jeżeli przez 10 dni zarobiłam 4 252 zł, to ile zarobię w ciągu 20 dni?

Matematycznie wzór wygląda tak:

Oczywiście standardowo „wrzucę” powyższy wzór w funkcję JEŻELI, aby wyświetlać wynik jedynie dla dni, w których była jakakolwiek sprzedaż. Formułę wpisz w komórkę D5:

=JEŻELI(B5>0;((SUMA($B$5:B5)*$B$2)/A5)/$B$1;"")

Skopiuj do pozostałych komórek i…

Gotowe!

 Pobierz plik

MalinowyExcel_tabela-targetowa.xlsx