Szablon prognozy sprzedaży: darmowy Google Sheet z win rate liczonym na prawdziwych danych lejka

Szablon prognozy sprzedaży zamienia dwa zestawy danych w jedną liczbę na następny kwartał: dzisiejsze otwarte transakcje i transakcje zamknięte w ostatnich miesiącach. Ten darmowy arkusz Google Sheets prognozuje kwartał na trzy sposoby z tego samego lejka: ważąc etapy, na podstawie historii i przez ekstrapolację run rate. Obok każdej prognozy widać lukę do celu. Zrób kopię arkusza, wpisz oba zestawy danych, a zakładki Forecast (prognoza) i Summary (podsumowanie) wypełnią się same.

Zakładka Win rates (wskaźniki wygranych) zawiera już historię samej firmy Spona: 344 zamknięte szanse sprzedaży z czterech niedawnych miesięcy, podzielone na wygrane, przegrane i bez decyzji. Te liczby pokazują, dlaczego ten sam lejek może dać dwie prognozy różniące się o 25%. Wszystko zależy od tego, jak liczysz wygraną. Arkusz jest po angielsku i przelicza się w Google Sheets, Excelu i LibreOffice.

Get thousands of leads in 4 minutes.

Stop stitching tools together.
Start with verified sales data, delivered in minutes.

SCALE SALES NOW

No contracts · No setup · No onboarding

Szablon prognozy sprzedaży: trzy metody obok siebie

Każda prognoza to mnożenie. Metoda określa, co mnożysz i przez co. Arkusz stosuje trzy metody do tych samych otwartych transakcji, a do tego dwie sumy, które handlowiec ustala sam. Rozbieżność widać, zanim przyjdzie koniec kwartału.

Stage-weighted to domyślna metoda HubSpota. HubSpot definiuje kwotę ważoną jako łączną kwotę na każdym etapie pomnożoną przez prawdopodobieństwo etapu. Domyślny lejek zaczyna się od 20% przy Appointment scheduled (umówione spotkanie) i kończy na 90% przy Contract sent (wysłana umowa).

Narzędzie do prognoz w HubSpocie domyślnie używa kwoty ważonej. Arkusz zaczyna od tych pięciu wartości domyślnych i podmienia je na twój własny wskaźnik dla każdego etapu, gdy zamknie się wystarczająco dużo transakcji.

Commit i Best case to ocena handlowca, nie arytmetyka. Microsoft Dynamics 365 definiuje Committed jako ustne lub umowne zobowiązanie o wysokiej pewności. Best case oznacza średnią pewność, na razie bez zobowiązania, a Omitted (pominięte) wypada ze wszystkich sum.

Domyślne kategorie HubSpota to Commit, Best case, Most likely (najbardziej prawdopodobne) i Pipeline. Arkusz używa kategorii Commit, Best case, Pipeline i Omitted, a z sum usuwa transakcję tylko Omitted.

Run rate to najprostsza metoda i najłatwiej się nią oszukać. AccountingTools definiuje ją jako ekstrapolację wyników finansowych na przyszłe okresy. W jego przykładzie kwartał z wynikiem 5 000 000 USD daje roczny run rate 20 000 000 USD. Faktyczny rok zamknął się kwotą 15 000 000 USD.

Arkusz liczy run rate w transakcjach na podstawie liczby twoich zamkniętych transakcji, a w przychodzie, gdy dodasz Won value (wygraną wartość) za każdy miesiąc.

Jaka metoda prognozowania sprzedaży jest najlepsza?

Najlepsza metoda to ta, którą mogą sprawdzić twoje własne zamknięte transakcje. Zacznij od metody opartej na historii: otwarta wartość zamykana w tym kwartale razy odsetek zamkniętych transakcji, które udało się wygrać. Jedynym szacunkiem są w niej daty zamknięcia.

Potem zestaw z nią prognozę ważoną etapami. Jeśli różnią się od siebie najwyżej o 20%, twoje prawdopodobieństwa etapów są mniej więcej uczciwe. Zakładka Forecast pokazuje to porównanie w wierszu 10 i wskazuje, co poprawić, gdy różnica jest większa.

Run rate to test zdrowego rozsądku, a nie prognoza. Jeśli prognoza oparta na historii wyraźnie przewyższa run rate, twój lejek jest pełniejszy niż zwykle albo daty zamknięcia są zbyt optymistyczne. Commit to liczba, którą raportujesz wyżej, gdy pozostałe trzy są zgodne. Traktuj Best case jako listę transakcji do pracy, a nie jako przychód.

Arkusz prognozy: pięć zakładek, pięć kroków

Do przygotowania prognozy sprzedaży potrzebujesz trzech rzeczy. Pierwsza to każda otwarta transakcja z kwotą, etapem i oczekiwaną datą zamknięcia. Druga to zamknięte transakcje z ostatnich miesięcy w podziale na miesiące, a trzecia to cel. Arkusz jest po angielsku i ma pięć zakładek, po jednej na krok: zrób kopię arkusza i przejdź przez nie po kolei.

1. Instructions (instrukcje): ustaw sześć parametrów. Najważniejsze są Quarter start (początek kwartału), Quarter target (cel kwartalny) i As-of date (data stanu). Trzy kolejne ustalają reguły. Otwarta transakcja staje się nieaktualna po 7 dniach bez aktywności. Prognoza bierze pod uwagę sześć miesięcy historii, a etap potrzebuje 10 zamkniętych transakcji, zanim twój własny wskaźnik zastąpi wartość domyślną. Tutaj znajdują się też listy rozwijane dla Outcome (wynik), Forecast category (kategoria prognozy), Owner (właściciel) i Code outcome (wynik przypisany do kodu), więc edytujesz je w jednym miejscu.
2. Pipeline (lista transakcji): jeden wiersz na transakcję. Wpisujesz jedenaście kolumn: Deal (nazwa transakcji), Owner, Amount, Stage, Expected close (oczekiwane zamknięcie), Forecast category, Next step (następny krok), Last activity (ostatnia aktywność), Close date moves (n) (liczba przesunięć daty), Outcome i Reason code (kod przyczyny). Sześć szarych kolumn liczy się samo: Stage no. (numer etapu), Probability, Weighted amount (kwota ważona), Close month (miesiąc zamknięcia), In forecast quarter (w kwartale prognozy) i Check (kontrola). Stage i Reason code to listy rozwijane z zakresu, które odrzucają wszystko spoza listy; Owner tylko ostrzega. Jeśli twoje transakcje są w naszym szablonie CRM, skopiuj Amount i Deal stage (etap transakcji), a Decision date (data decyzji) wpisz jako Expected close. Potem usuń 15 przykładowych wierszy.
3. Win rates: twoja historia, twoje kody, twoje etapy. Wpisz zamknięte transakcje z każdego miesiąca jako Won (n), Lost (n) i No decision (n) (wygrane, przegrane, bez decyzji), od najstarszego miesiąca, jeden miesiąc na wiersz. Closed (n) (zamknięte łącznie), Won / won + lost (odsetek wygranych wśród rozstrzygniętych) i Won / all closed (odsetek wygranych wśród wszystkich zamkniętych) liczą się dla każdego miesiąca i dla użytych miesięcy. Pod tabelą arkusz zamienia tę historię na kwartał w transakcjach: zamknięte w miesiącu, zamknięte w kwartale i wygrane przy każdym z dwóch wskaźników. Niżej znajduje się dziesięć kodów przyczyn i tabela etapów z domyślnymi prawdopodobieństwami HubSpota. Zastąp cztery przykładowe miesiące firmy Spona swoimi; formuły się nie zmieniają.
4. Forecast: trzy miesiące, sześć metod. Każdy wiersz to jeden miesiąc kwartału. Dla każdej otwartej transakcji, której Close month przypada na ten miesiąc, SUMIFS sumuje Amount i Weighted amount, a COUNTIFS liczy transakcje. Dwie kolumny History-based mnożą otwartą wartość przez każdy z dwóch wskaźników wygranych. Commit i Commit + Best case sumują kwoty według Forecast category. Wiersz Quarter (kwartał) sumuje trzy miesiące, a wiersz 10 porównuje prognozę ważoną etapami z prognozą opartą na historii.
5. Summary: strona, którą udostępniasz. Tabela metod pokazuje dla każdej metody Quarter forecast (prognozę kwartalną), Gap to target (lukę do celu) i Share of target (odsetek celu). Coverage (pokrycie) mówi, ile lejka masz w stosunku do celu i ile potrzebujesz przy swoim wskaźniku wygranych. Deal checks (kontrole transakcji) zlicza każde oznaczenie z zakładki Pipeline. Weekly forecast log (tygodniowy dziennik prognoz) po prawej przechowuje liczby, które wklejasz w każdy poniedziałek, oraz wiersz błędu, gdy znany jest wynik kwartału.

Formuły używają angielskich nazw funkcji, a Google Sheets je zachowuje, gdy zaznaczysz Always use English function names (zawsze używaj angielskich nazw funkcji). Ustawienia regionalne zmieniają formaty waluty, daty i liczb: w menu Plik, Ustawienia wybierz Polskę, a kwoty w zakładce Pipeline wpisuj w złotych. W zakładce Win rates SUMPRODUCT waży miesiące, a w zakładce Pipeline INDEX i MATCH wyszukują prawdopodobieństwo etapu dla każdej transakcji. Bez QUERY, bez skryptów, bez dodatków, więc plik otworzysz też w Excelu przez Plik, Pobierz.

Przykład prognozy sprzedaży: następny kwartał z zamkniętych szans firmy Spona

Cztery przykładowe miesiące firmy Spona są prawdziwe. Każda szansa liczy się raz, w miesiącu, w którym została zamknięta, a otwarte szanse są pominięte. Jesse Chan z firmy Spona potwierdza, które kody przyczyn liczą się jako wygrana, przegrana i brak decyzji.

Z tych samych liczb wychodzą dwa wskaźniki wygranych. Won / won + lost wynosi 82,5%, bo usuwa z mianownika 69 transakcji bez decyzji. Won / all closed wynosi 66,0%. Żaden z nich nie jest punktem odniesienia dla twojego zespołu; sedno tkwi w różnicy między nimi.

Z tych liczb wynika, że Spona zamykała średnio 86 szans miesięcznie, czyli 258 na kwartał. Przy 66,0% historia prognozuje 170 wygranych. Przy 82,5% prognozuje 213.

To 43 wygrane więcej, czyli 25,1%, z tych samych transakcji i tego samego lejka. Zmienia się tylko to, czy transakcja zakończona bez decyzji liczy się jako zamknięta.

Brak decyzji to nie błąd zaokrąglenia. Tak skończyło się 69 z 344 zamkniętych szans. Spośród 117 transakcji, których Spona nie wygrała, 59,0% zakończyło się brakiem decyzji, a nie przegraną.

Dixon and McKenna przeanalizowali na potrzeby badania JOLT 2,5 mln nagranych rozmów sprzedażowych. Odkryli, że od 40 do 60% przegranych transakcji kończyło się brakiem decyzji. Historia, która zna tylko wygrane i przegrane, ukrywa każdą z nich.

Przykładowe wiersze w zakładce Pipeline są zmyślone. W kwartale zamyka się dziewięć otwartych transakcji o otwartej wartości 198 000 przy Quarter target równym 150 000. Kwoty nie mają waluty, więc wpisz swoje w walucie, w której sprzedajesz.

Stage-weighted na wartościach domyślnych HubSpota prognozuje 125 400. History-based przy wskaźniku Won / all closed firmy Spona prognozuje 130 657. Obie wartości dzieli 4,0%, więc zakładka Forecast wyświetla „Methods agree within 20%” (metody różnią się o mniej niż 20%).

Po przełączeniu na Won / won + lost prognoza skacze do 163 440 i jako jedyna metoda przekracza cel. Commit wynosi 57 000, a Commit + Best case 129 000, oba poniżej celu.

Wolumen zmienia się bardziej niż wskaźnik. Liczba zamkniętych szans w firmie Spona wynosiła od 73 do 98 miesięcznie. To wahanie o 34%. Odsetek wygranych zmieniał się tylko między 64,3% a 68,1%.

Ryzyko prognozy leży więc w tym, ile transakcji się zamknie, a nie we wskaźniku, przez który mnożysz. Obserwuj kolumnę Open deals closing (n) (liczba zamykanych otwartych transakcji) w zakładce Forecast równie uważnie jak wartość.

Prognozowanie sprzedaży bez złudzeń: zasady etapów, przesunięte terminy, cotygodniowa aktualizacja

Arkusz prognozy jest tylko tak uczciwy, jak trzy pola, które wypełniają handlowcy: Stage, Expected close i Forecast category. Trzy zasady utrzymują je w ryzach, a kolumna Check egzekwuje większość z nich.

Zasady etapów, które się sprawdzają

Dla każdego etapu zapisz kryterium wyjścia, które musi spełnić kupujący, a nie handlowiec. Presentation scheduled (umówiona prezentacja) oznacza termin w kalendarzu kupującego. Decision maker bought-in (decydent przekonany) oznacza, że osoba decydująca o budżecie powiedziała „tak” podczas rozmowy.

Nasz przewodnik po lejku sprzedaży B2B pokazuje, jak je spisać. Bez kryteriów wyjścia prawdopodobieństwo 60% oznacza tylko, że handlowiec ma dobre przeczucie.

Commit wymaga kupującego z imienia i nazwiska. W danych firmy Spona No active buying owner (brak aktywnego właściciela zakupu) był głównym powodem w 28 z 69 transakcji bez decyzji. Transakcja bez nikogo, kto może podpisać, to co najwyżej Best case, niezależnie od etapu. Pytanie o economic buyera z MEDDIC trzeba zadać, zanim kategoria zmieni się na Commit.

Odpuść wartości domyślne. Tabela etapów w zakładce Win rates liczy zamknięte transakcje, które dotarły do każdego etapu, i wygrane wśród nich. Gdy etap ma 10 zamkniętych transakcji, Probability used (użyte prawdopodobieństwo) przełącza się z wartości domyślnej HubSpota na twój własny wskaźnik, a kolumna Based on (podstawa) to pokazuje. Przy przykładowych wierszach każdy etap wciąż ma Default: fewer than 10 closed deals (domyślne: mniej niż 10 zamkniętych transakcji).

Zasady dla przesuniętych dat zamknięcia

Przesunięta data zamknięcia to jeden z najsilniejszych sygnałów ostrzegawczych, jakie może dać arkusz. Benchmark Ebsta obejmujący 57 mld USD przeanalizowanych przychodów wykazał, że wskaźniki wygranych spadają o 83%, gdy transakcje się przesuwają. Już przesunięcie o jeden do czterech tygodni obniża wskaźnik wygranych o 34%.

Ebsta podaje też, że odsetek transakcji, które przekraczają datę zamknięcia, spadł z 44% do 36% w ciągu roku. Ebsta odczytuje ten spadek jako oznakę ściślejszej kontroli nad transakcjami.

Arkusz ma dwie zasady dotyczące przesunięć. Za każdym razem, gdy data się przesuwa, dodaj 1 w Close date moves (n). Przy drugim przesunięciu transakcji w Commit kolumna Check wyświetla „Moved twice: not Commit” (przesunięta dwa razy: nie Commit), a transakcja potrzebuje nowej kategorii.

Gdy Expected close przypada przed As-of date, kolumna Check wyświetla „Close date passed” (data zamknięcia minęła). Transakcja z datą zamknięcia w przeszłości nie może być w prognozie.

Trzecia zasada dotyczy zamykania transakcji, nie przesuwania ich. Project deferred (projekt odłożony) to kod 41 z 69 transakcji bez decyzji w firmie Spona. Gdy kupujący odkłada projekt, zamknij transakcję jako No decision z jej kodem przyczyny, zamiast trzeci raz przesuwać datę.

Nasz wpis o analizie win loss pokazuje, jak zakodować każdą zamkniętą transakcję jednym głównym powodem. Kolumna Check oznacza kod, który nie pasuje do wyniku.

Cotygodniowa rutyna aktualizacji

Xactly zapytał 405 menedżerów sprzedaży i finansów z Ameryki Północnej o ich prognozy. Cotygodniowe aktualizacje były pierwszym wyborem dla 41% z nich, comiesięczne dla 26%. Codzienne zajęły trzecie miejsce z 22%.

Narzędzie do prognoz w HubSpocie może oznaczać handlowców, którzy nie zaktualizowali prognozy, a ten wskaźnik domyślnie ustawiony jest na osiem dni. Reguła nieaktualności w arkuszu wynosi 7 dni z tego samego powodu: transakcja, której nikt nie ruszył przez tydzień, to transakcja, nad którą nikt nie pracuje.

Poniedziałkowa rutyna to pięć wpisów na handlowca. Wpisz dzisiejszą datę w As-of date w zakładce Instructions. Sprawdź Stage, Expected close i Last activity przy każdej otwartej transakcji. Dodaj 1 w Close date moves (n) tam, gdzie data się przesunęła.

Ustaw Outcome i Reason code przy każdej transakcji, która się zamknęła. Potem wklej wiersz Quarter z zakładki Forecast do Weekly forecast log w zakładce Summary.

To dziennik sprawia, że arkusz z czasem działa coraz lepiej. Gdy kwartał się kończy, wpisz wynik w Actual won this quarter (faktycznie wygrane w tym kwartale). Wiersz błędu pokaże wtedy, o ile pomyliła się każda metoda w pierwszym zapisanym tygodniu. Po dwóch kwartałach wiesz, której metodzie ufać w twoim zespole.

Ile lejka potrzeba na kwartał

Pokrycie to otwarta wartość zamykana w kwartale podzielona przez cel. Potrzebne pokrycie to jeden podzielony przez twój wskaźnik wygranych.

Reguła 3x zakłada wskaźnik wygranych na poziomie 33%. Na blogu Salesforce Jason Jordan pisze, że działa ona tylko przy wskaźniku wygranych równym dokładnie 33% i cyklu sprzedaży trwającym dokładnie rok. Przy wskaźniku 10% lejek musi wynosić 10x celu. Przy 50% wystarczy 2x.

Jego przykład zaczyna się od celu 100 000 USD i wskaźnika wygranych 25%. Po podzieleniu przez liczbę 60-dniowych cykli sprzedaży w roku wychodzi 65 757 USD lejka.

Przy wskaźniku Won / all closed firmy Spona, czyli 66,0%, kwartał potrzebuje otwartej wartości równej 1,52x celu. Przy wskaźniku Won / won + lost, czyli 82,5%, wystarczy 1,21x. Niższy wskaźnik jest tym uczciwym, bo odłożony projekt to wciąż transakcja, której nie udało się wygrać.

Zakładka Summary liczy to za ciebie. Przykładowy lejek pokrywa 1,32x celu, a potrzebuje 1,52x. Wiersz Status brzmi „Short of pipeline: add 29 313” (za mały lejek: dodaj 29 313).

Zanim kupisz ten brakujący lejek, sprawdź w naszym kalkulatorze wartości leada, ile jest dla ciebie wart jeden lead. Przy średniej transakcji z przykładu, 22 000, ta luka to dwie dodatkowe szanse, a nie dwadzieścia.

Gdzie pasuje Spona, gdy brakuje lejka

Spona nie jest narzędziem do prognozowania. Nie czyta twojego lejka, nie ustala twoich wskaźników wygranych i nie zbiera prognoz całego zespołu. Jeśli pracujesz w HubSpocie albo Dynamics 365, ich narzędzia do prognoz są lepsze od każdego arkusza w zbieraniu i zgłaszaniu prognoz. Narzędzie HubSpota zaczyna nawet od tej samej kwoty ważonej, której używa ten szablon.

Spona wchodzi do gry przy wierszu Status. Gdy Summary pokazuje „Short of pipeline”, luka to nowe szanse sprzedaży, a właśnie je buduje Spona.

Opisujesz swojego idealnego klienta na czacie, a Spona zwraca zweryfikowaną, ocenioną listę leadów. Płacisz za lead, od 0,008 do 2,00 USD, zależnie od rodzaju wyszukiwania i planu. Każde wyszukiwanie zaczyna się od darmowej próbki leadów. Dla zespołów, których transakcje są w HubSpocie, dostępna jest integracja z HubSpotem.

Ograniczenia są jasne. Spona dostarcza górę lejka, a nie kolejne etapy. Leady wciąż potrzebują pierwszej rozmowy, kryterium wyjścia dla każdego etapu i poniedziałkowej aktualizacji, jak każdy inny wiersz. A za cenę leada kupujesz kontakty, nie wskaźnik wygranych: o Probability used decyduje twoja historia, nie historia firmy Spona.

Get thousands of leads in 4 minutes.

Stop stitching tools together.
Start with verified sales data, delivered in minutes.

SCALE SALES NOW

No contracts · No setup · No onboarding

Szablon prognozy: najczęstsze pytania

Jak zrobić prognozę sprzedaży w Excelu?

Pobierz ten szablon jako plik .xlsx przez Plik, Pobierz, Microsoft Excel. Jego formuły używają tylko standardowych funkcji, takich jak SUMIFS, COUNTIFS i SUMPRODUCT. Excel przelicza je bez żadnych zmian.

Do prognozy trendu miesięcznego przychodu Excel ma też Forecast Sheet (arkusz prognozy) na karcie Dane. Domyślnie używa on wersji AAA wygładzania wykładniczego z przedziałem ufności 95%. Wymaga równych odstępów między punktami danych i toleruje do 30% brakujących punktów.

Ta funkcja przedłuża linię z wcześniejszych sum; twoich otwartych transakcji nie widzi. Funkcja FORECAST w Google Sheets robi to samo za pomocą regresji liniowej. Użyj którejś z nich do trendu miesięcznego, a tego szablonu do kwartału, który masz przed sobą.

Jaki jest wzór na prognozę sprzedaży?

Trzy wzory wystarczają większości zespołów. Ważony etapami: suma kwot wszystkich otwartych transakcji pomnożonych przez prawdopodobieństwo ich etapu. Oparty na historii: otwarta wartość zamykana w okresie razy twój wskaźnik wygranych, liczony jako wygrane podzielone przez wszystkie zamknięte transakcje. Run rate: wygrana wartość miesięczna razy liczba miesięcy w okresie.

Arkusz liczy je jako SUMIFS na Weighted amount, SUMIFS na Amount razy wskaźnik wygranych oraz Won value miesięcznie razy trzy.

Jak trafne są prognozy sprzedaży?

Mniej trafne, niż przyznaje większość zespołów. W badaniu Xactly 4 na 5 menedżerów w ostatnim roku nie trafiło z prognozą kwartalną, a ponad połowa pomyliła się dwa razy lub częściej. 52% menedżerów sprzedaży stwierdziło, że ich prognoza rozminęła się z wynikiem o 10% lub więcej.

Gartner ustalił, że tylko 45% menedżerów sprzedaży i sprzedawców ma duże zaufanie do trafności prognoz swojej organizacji. Weekly forecast log w tym arkuszu zapisuje liczbę z każdego tygodnia, więc twój własny poziom błędu przestaje być domysłem.

Ile historii potrzebuje szablon?

Ustawienie Months of history to use (liczba miesięcy historii) zaczyna się od 6, a kolumna Used in forecast (użyte w prognozie) oznacza najnowsze miesiące do tej liczby. Cztery przykładowe miesiące firmy Spona wystarczą, by pokazać metodę; twoja własna historia powinna obejmować co najmniej kwartał.

Zespół, który zamyka kilka transakcji miesięcznie, powinien wpisać cały rok. Wtedy ustaw Months of history to use na 12. Prawdopodobieństwa etapów przełączają się na twój własny wskaźnik dopiero wtedy, gdy etap osiągnie 10 zamkniętych transakcji. Małe zespoły dłużej zostają przy wartościach domyślnych.

Czy szablonu można używać bez CRM?

Tak. Zakładka Pipeline jest CRM-em dla prognozy: jeden wiersz na transakcję z jedenastoma kolumnami do wpisania. Jeśli nie masz żadnej listy transakcji, zacznij od naszego szablonu CRM i utrzymuj obie listy Stage identyczne. Expected close jest najważniejsze, bo zakładka Forecast odczytuje transakcje według Close month.