Šablona prognózy prodeje: Google Sheet zdarma s mírou úspěšnosti z reálného pipeline

Šablona prognózy prodeje převede dva vstupy na jedno číslo pro příští čtvrtletí: dnešní otevřené obchody a obchody uzavřené v posledních měsících. Tato bezplatná tabulka Google Sheets v angličtině spočítá čtvrtletí na stejném pipeline třemi způsoby: váženě podle fází, podle historie a podle run rate (extrapolace dosavadního tempa). Vedle každé prognózy stojí rozdíl oproti cíli. Vytvořte si kopii tabulky, vyplňte oba vstupy a listy Forecast (prognóza) a Summary (souhrn) se doplní samy.

List Win rates (míry úspěšnosti) je předvyplněný vlastní historií Spona: 344 uzavřených příležitostí za čtyři nedávné měsíce, rozdělených na vyhrané, prohrané a bez rozhodnutí. Z těchto počtů je vidět, proč stejný pipeline může dát dvě prognózy lišící se o 25 %, podle toho, jak počítáte výhru. Tabulka se přepočítává v 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

Šablona prognózy prodeje: tři metody vedle sebe

Každá prognóza je násobení. Metoda určuje, co násobíte a čím. Tabulka počítá tři metody na stejných otevřených obchodech a k nim dva součty, které obchodník určuje ručně. Nesoulad tak uvidíte dřív, než čtvrtletí skončí.

Vážená prognóza podle fází je výchozí metoda HubSpotu. HubSpot definuje váženou částku jako celkovou částku v každé fázi vynásobenou pravděpodobností fáze. Jeho výchozí pipeline vede od 20 % u Appointment scheduled (schůzka domluvena) po 90 % u Contract sent (smlouva odeslána).

Nástroj pro prognózy v HubSpotu ve výchozím nastavení používá váženou částku. Tabulka začíná s těmito pěti výchozími hodnotami a jakmile se uzavře dost obchodů, nahradí je vaší vlastní mírou pro každou fázi.

Commit a Best case jsou úsudek obchodníka, ne aritmetika. Dynamics 365 od Microsoftu definuje Committed (potvrzeno) jako ústní nebo smluvní závazek s vysokou jistotou. Best case má střední jistotu a zatím žádný závazek, Omitted (vynecháno) vypadne ze všech součtů.

Výchozí kategorie HubSpotu jsou Commit, Best case, Most likely (nejpravděpodobnější) a Pipeline (rozpracováno). Tabulka používá Commit, Best case, Pipeline a Omitted a ze součtů obchod vyřadí jen Omitted.

Run rate je nejjednodušší metoda a nejsnáze se nechá oklamat. AccountingTools ho definuje jako extrapolaci finančních výsledků do budoucích období. V jejich příkladu se ze čtvrtletí za 5 000 000 USD stane roční run rate 20 000 000 USD. Skutečný rok skončil na 15 000 000 USD.

Tabulka počítá run rate v obchodech z vašich počtů uzavřených obchodů a v tržbách, jakmile doplníte Won value za měsíc.

Která metoda prognózy prodeje je nejlepší?

Nejlepší je ta, kterou vaše vlastní uzavřené obchody dokážou ověřit. Začněte prognózou podle historie: otevřená hodnota s uzavřením v tomto čtvrtletí krát podíl uzavřených obchodů, které jste vyhráli. Kromě dat uzavření v ní nic není odhad.

Pak si vedle ní přečtěte váženou prognózu. Pokud se od sebe liší nejvýš o 20 %, vaše pravděpodobnosti fází jsou zhruba poctivé. List Forecast toto srovnání vypíše na řádku 10 a při větším rozdílu pojmenuje nápravu.

Run rate je kontrola zdravým rozumem, ne prognóza. Když je prognóza podle historie výrazně nad run rate, je váš pipeline plnější než obvykle, nebo jsou vaše data uzavření optimistická. Commit je číslo, které hlásíte nahoru, až se ostatní tři shodnou. Best case berte jako seznam obchodů, na kterých je třeba pracovat, ne jako tržby.

Tabulka pro prognózu prodeje: pět listů, pět kroků

Pro prognózu prodeje potřebujete tři věci. První je každý otevřený obchod s částkou, fází a očekávaným datem uzavření. Druhá jsou vaše uzavřené obchody po měsících za poslední měsíce, třetí je cíl. Tabulka je v angličtině a má pět listů, jeden na každý krok: vytvořte si kopii tabulky a projděte je popořadě.

1. Instructions (pokyny): nastavte šest hodnot. Nejdůležitější jsou Quarter start (začátek čtvrtletí), Quarter target (cíl čtvrtletí) a As-of date (datum stavu). Další tři určují pravidla. Otevřený obchod se počítá jako zastaralý po 7 dnech bez aktivity. Prognóza čte šest měsíců historie a fáze potřebuje 10 uzavřených obchodů, než výchozí hodnotu nahradí vaše vlastní míra. Jsou tu i rozevírací seznamy pro Outcome (výsledek), Forecast category (kategorie prognózy), Owner (vlastník) a Code outcome (výsledek kódu), takže je upravujete na jednom místě.
2. Pipeline (obchody v běhu): jeden řádek na obchod. Vyplňujete jedenáct sloupců: Deal (obchod), Owner, Amount, Stage, Expected close (očekávané uzavření) a Forecast category. Dále Next step (další krok), Last activity (poslední aktivita), Close date moves (n) (počet posunů data uzavření), Outcome a Reason code (kód důvodu). Šest šedých sloupců se počítá samo: Stage no. (číslo fáze), Probability, Weighted amount (vážená částka), Close month (měsíc uzavření), In forecast quarter (v prognózovaném čtvrtletí) a Check (kontrola). Stage a Reason code jsou rozevírací seznamy z rozsahu, které odmítnou cokoli mimo seznam; Owner jen varuje. Pokud máte obchody v naší šabloně CRM, zkopírujte Amount a Deal stage (fáze obchodu) a jako Expected close použijte Decision date (datum rozhodnutí). Pak smažte 15 ukázkových řádků.
3. Win rates: vaše historie, vaše kódy, vaše fáze. Uzavřené obchody za měsíc zapisujte jako Won (n) (vyhrané), Lost (n) (prohrané) a No decision (n) (bez rozhodnutí), od nejstaršího, jeden měsíc na řádek. Closed (n) (uzavřené), Won / won + lost (míra z rozhodnutých) a Won / all closed (míra ze všech uzavřených) se počítají za každý měsíc i za použité měsíce. Pod tabulkou list převede tuto historii na čtvrtletí v obchodech: uzavřené za měsíc, uzavřené za čtvrtletí a výhry podle každé míry. Níž je deset kódů důvodů a tabulka fází s výchozími pravděpodobnostmi HubSpotu. Čtyři ukázkové měsíce Spona nahraďte svými; vzorce se nemění.
4. Forecast: tři měsíce, šest metod. Každý řádek je jeden měsíc čtvrtletí. U každého otevřeného obchodu, jehož Close month do něj padne, SUMIFS sečte Amount a Weighted amount a COUNTIFS spočítá obchody. Dva sloupce History-based násobí otevřenou hodnotu každou z obou měr úspěšnosti. Commit a Commit + Best case sčítají částky podle Forecast category. Řádek Quarter (čtvrtletí) sečte tři měsíce a řádek 10 porovná váženou prognózu s prognózou podle historie.
5. Summary: stránka, kterou sdílíte. Tabulka metod ukazuje každou Quarter forecast (prognóza čtvrtletí) s jejím Gap to target (rozdíl k cíli) a Share of target (podíl na cíli). Coverage (pokrytí) říká, kolik pipeline máte vůči cíli a kolik ho při své úspěšnosti potřebujete. Deal checks (kontroly obchodů) spočítá každé upozornění na listu Pipeline. Weekly forecast log (týdenní záznam prognóz) vpravo obsahuje čísla, která vkládáte každé pondělí. Jakmile je známý skutečný výsledek čtvrtletí, přibude řádek s chybou.

Vzorce používají anglické názvy funkcí a Google Sheets je zachová, když zaškrtnete Always use English function names (vždy používat anglické názvy funkcí). Národní prostředí mění formát měny, data a čísel: v nabídce Soubor, Nastavení zvolte Česko a částky na listu Pipeline zadávejte v korunách (Kč). Na listu Win rates váží měsíce SUMPRODUCT a na listu Pipeline vyhledají INDEX a MATCH pravděpodobnost fáze každého obchodu. Protože tabulka nepoužívá QUERY, skripty ani doplňky, otevřete soubor i v Excelu přes Soubor, Stáhnout.

Modelový příklad: příští čtvrtletí z uzavřených obchodů Spona

Čtyři ukázkové měsíce Spona jsou skutečné. Každá příležitost se počítá jednou, v měsíci, kdy se uzavřela, a otevřené příležitosti zůstávají mimo. Jesse Chan ze společnosti Spona potvrzuje, které kódy důvodů se počítají jako výhra, prohra a bez rozhodnutí.

Ze stejných počtů vycházejí dvě míry úspěšnosti. Won / won + lost je 82,5 %, protože ze jmenovatele vypouští 69 obchodů bez rozhodnutí. Won / all closed je 66,0 %. Ani jedna míra není benchmark pro váš tým; podstatný je rozdíl mezi nimi.

Spočteno z těchto počtů: Spona uzavřela v průměru 86 příležitostí měsíčně, tedy 258 za čtvrtletí. Při 66,0 % historie předpovídá 170 výher. Při 82,5 % předpovídá 213.

To je o 43 výher víc, tedy o 25,1 %, ze stejných obchodů a stejného pipeline. Jediná změna je, jestli se obchod, který skončil bez rozhodnutí, počítá jako uzavřený.

No decision není zaokrouhlovací chyba. Tímto výsledkem skončilo 69 z 344 uzavřených příležitostí. Ze 117 obchodů, které Spona nevyhrála, skončilo 59,0 % bez rozhodnutí, ne prohrou.

Dixon and McKenna pro svůj výzkum JOLT analyzovali 2,5 milionu nahraných obchodních rozhovorů. Zjistili, že 40 až 60 % prohraných obchodů skončilo bez rozhodnutí. Historie, která zná jen Won a Lost (vyhráno a prohráno), každý z nich skryje.

Ukázkové řádky na listu Pipeline jsou smyšlené. Ve čtvrtletí se uzavírá devět otevřených obchodů s otevřenou hodnotou 198 000 proti Quarter target 150 000. Částky nemají měnu, takže je zadejte v té, ve které prodáváte.

Vážená prognóza podle výchozích hodnot HubSpotu dává 125 400. Prognóza podle historie s mírou won / all closed Spona dává 130 657. Liší se od sebe o 4,0 %, takže list Forecast vypíše „Methods agree within 20%“ (metody se shodují do 20 %).

Přepněte na won / won + lost a prognóza vyskočí na 163 440, jako jediná metoda nad cílem. Commit dává 57 000 a Commit + Best case 129 000, obojí pod cílem.

Objem se hýbe víc než míra. Počet uzavřených obchodů Spona se pohyboval od 73 do 98 měsíčně. To je výkyv o 34 %. Podíl výher kolísal jen mezi 64,3 % a 68,1 %.

Riziko prognózy tedy leží v tom, kolik obchodů se uzavře, ne v míře, kterou násobíte. Sloupec Open deals closing (n) (otevřené obchody k uzavření) na listu Forecast sledujte stejně pozorně jako hodnotu.

Poctivá prognóza prodeje: pravidla fází, posuny termínů a týdenní aktualizace

Tabulka prognózy je jen tak poctivá jako tři pole, která do ní obchodníci vyplňují: Stage, Expected close a Forecast category. Tři pravidla je drží poctivá a většinu z nich hlídá sloupec Check.

Pravidla fází, která obstojí

Pro každou fázi napište výstupní pravidlo, které musí splnit kupující, ne obchodník. Presentation scheduled (prezentace naplánována) znamená termín v kalendáři kupujícího. Decision maker bought-in (rozhodovatel získán) znamená, že ekonomický kupující řekl na hovoru ano.

Náš průvodce po B2B obchodním pipeline ukazuje, jak taková pravidla napsat. Bez výstupních pravidel znamená pravděpodobnost 60 % jen to, že má obchodník dobrý pocit.

Commit potřebuje kupujícího se jménem. V datech Spona byl No active buying owner (chybí aktivní vlastník nákupu) hlavním důvodem u 28 z 69 obchodů bez rozhodnutí. Obchod, kde nikdo nemůže podepsat, je nanejvýš Best case, ať je v jakékoli fázi. Otázka na ekonomického kupujícího z MEDDIC patří před změnu kategorie na Commit.

Výchozích hodnot se vzdejte. Tabulka fází na listu Win rates počítá uzavřené obchody, které došly do každé fáze, a výhry mezi nimi. Jakmile má fáze 10 uzavřených obchodů, Probability used (použitá pravděpodobnost) přepne z výchozí hodnoty HubSpotu na vaši vlastní míru a sloupec Based on (založeno na) to uvede. S ukázkovými řádky ukazují všechny fáze zatím „Default: fewer than 10 closed deals“ (výchozí: méně než 10 uzavřených obchodů).

Pravidla pro posunutá data uzavření

Posunuté datum uzavření je jeden z nejsilnějších varovných signálů, které tabulka může dát. Benchmark společnosti Ebsta, který analyzoval tržby za 57 mld. USD, zjistil, že míra úspěšnosti klesá o 83 %, když se obchody posunou. Už samotný posun o jeden až čtyři týdny sníží míru úspěšnosti o 34 %.

Ebsta také uvádí, že podíl obchodů, které přejdou své datum uzavření, klesl ze 44 % na 36 % za jeden rok. Ebsta pokles vykládá jako známku přísnější kontroly obchodů.

Tabulka má dvě pravidla pro posuny. Pokaždé, když se datum posune, přičtěte 1 v Close date moves (n). Při druhém posunu obchodu v kategorii Commit vypíše sloupec Check „Moved twice: not Commit“ (posunuto dvakrát: není Commit) a obchod potřebuje novou kategorii.

Když je Expected close dřív než As-of date, sloupec vypíše „Close date passed“ (datum uzavření uplynulo). Obchod nemůže zůstat v prognóze s datem uzavření v minulosti.

Třetí pravidlo se týká uzavírání obchodů, ne jejich posouvání. Project deferred (projekt odložen) byl kódem u 41 z 69 obchodů Spona bez rozhodnutí. Když kupující projekt odloží, uzavřete obchod jako No decision s jeho kódem důvodu, místo abyste datum posunuli potřetí.

Náš článek o win loss analýze ukazuje, jak každý uzavřený obchod zakódovat jedním hlavním důvodem. Sloupec Check označí kód, který neodpovídá výsledku.

Týdenní rutina aktualizace

Společnost Xactly se na prognózy zeptala 405 severoamerických lídrů obchodu a financí. Týdenní aktualizace byly první volbou pro 41 % z nich, měsíční pro 26 %. Denní skončily třetí s 22 %.

Nástroj pro prognózy v HubSpotu umí označit obchodníky, kteří prognózu neaktualizovali, a tento ukazatel má výchozí hodnotu osm dní. Pravidlo zastaralosti v tabulce má ze stejného důvodu 7 dní: na obchodu, na který týden nikdo nesáhl, nikdo nepracuje.

Pondělní rutina znamená pět zápisů na obchodníka. Do As-of date na listu Instructions zadejte dnešní datum. U každého otevřeného obchodu zkontrolujte Stage, Expected close a Last activity. Kde se datum posunulo, přičtěte 1 v Close date moves (n).

U každého obchodu, který se uzavřel, vyplňte Outcome a Reason code. Pak vložte řádek Quarter z listu Forecast do Weekly forecast log na listu Summary.

Díky záznamu se tabulka zlepšuje. Až čtvrtletí skončí, zadejte výsledek do Actual won this quarter (skutečně vyhráno ve čtvrtletí). Řádek s chybou pak ukáže, o kolik se každá metoda spletla v prvním zaznamenaném týdnu. Po dvou čtvrtletích víte, které metodě u svého týmu věřit.

Kolik pipeline čtvrtletí potřebuje

Pokrytí je otevřená hodnota s uzavřením ve čtvrtletí dělená cílem. Potřebné pokrytí je jedna dělená vaší mírou úspěšnosti.

Orientační pravidlo 3x předpokládá úspěšnost 33 %. Na blogu Salesforce Jason Jordan píše, že funguje jen při úspěšnosti přesně 33 % a prodejním cyklu dlouhém přesně jeden rok. Při úspěšnosti 10 % potřebujete v pipeline 10x cíl. Při 50 % stačí 2x.

Jeho příklad vychází z cíle 100 000 USD a úspěšnosti 25 %. Vydělením počtem 60denních prodejních cyklů v roce vyjde 65 757 USD pipeline.

Při míře won / all closed Spona, tedy 66,0 %, potřebuje čtvrtletí otevřenou hodnotu 1,52x cíle. Při míře won / won + lost, tedy 82,5 %, stačí 1,21x. Poctivá je nižší míra, protože odložený projekt je pořád obchod, který jste nevyhráli.

List Summary to spočítá za vás. Ukázkový pipeline má 1,32x cíle a potřebuje 1,52x. Řádek Status ukazuje „Short of pipeline: add 29 313“ (chybí pipeline: doplňte tuto částku).

Než si ten pipeline koupíte, ověřte si s naší kalkulačkou hodnoty leadu, kolik pro vás lead má cenu. Při průměrném obchodu z příkladu, 22 000, ten rozdíl představuje dvě příležitosti navíc, ne dvacet.

Kde Spona pomůže, když pipeline nestačí

Spona není nástroj na prognózy. Nečte váš pipeline, nestanovuje vaši úspěšnost a nesčítá prognózy za tým. Pokud používáte HubSpot nebo Dynamics 365, jejich nástroje pro prognózy jsou na sčítání a odevzdávání prognóz silnější než jakákoli tabulka. Ten od HubSpotu dokonce vychází ze stejné vážené částky jako tato šablona.

Spona přichází na řadu u řádku Status. Když Summary hlásí Short of pipeline, chybí nové příležitosti, a právě ty Spona vytváří.

Ideálního zákazníka popíšete v chatu a Spona vrátí ověřený a obodovaný seznam leadů. Platíte za lead, od 0,008 do 2,00 USD, podle typu vyhledávání a tarifu. Každé vyhledávání začíná bezplatnou ukázkou leadů. Integrace s HubSpotem je k dispozici pro týmy, jejichž obchody jsou vedené v HubSpotu.

Limity jsou jasné. Spona dodává vrchol pipeline, ne fáze po něm. Leady pořád potřebují první hovor, výstupní pravidlo pro každou fázi a pondělní aktualizaci jako každý jiný řádek. A za cenu leadu kupujete kontakty, ne míru úspěšnosti: Probability used určuje vaše historie, ne historie 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

Časté otázky k šabloně prognózy

Jak udělat prognózu prodeje v Excelu?

Stáhněte si tuto šablonu prognózy jako soubor .xlsx přes Soubor, Stáhnout, Microsoft Excel. Její vzorce používají jen standardní funkce, jako jsou SUMIFS, COUNTIFS a SUMPRODUCT. Excel je přepočítá bez úprav.

Pro trendovou prognózu měsíčních tržeb má Excel také Forecast Sheet (List prognózy) na kartě Data. Ve výchozím nastavení používá verzi AAA exponenciálního vyrovnávání s intervalem spolehlivosti 95 %. Potřebuje konzistentní intervaly mezi datovými body a snese až 30 % chybějících bodů.

Promítá tak čáru z minulých součtů; vaše otevřené obchody nevidí. Funkce FORECAST v Google Sheets dělá totéž pomocí lineární regrese. Pro měsíční trend použijte kteroukoli z nich, pro čtvrtletí před vámi tuto šablonu.

Jaký je vzorec pro prognózu prodeje?

Většině týmů stačí tři vzorce. Vážená podle fází: součet částky každého otevřeného obchodu krát pravděpodobnost jeho fáze. Podle historie: otevřená hodnota s uzavřením v období krát vaše úspěšnost, kde míra je počet výher dělený všemi uzavřenými obchody. Run rate: vyhraná hodnota za měsíc krát počet měsíců v období.

Tabulka je počítá jako SUMIFS nad Weighted amount, SUMIFS nad Amount krát míra úspěšnosti a Won value za měsíc krát tři.

Jak přesné jsou prognózy prodeje?

Méně, než většina týmů přizná. V průzkumu Xactly 4 z 5 lídrů za poslední rok minuli čtvrtletní prognózu a víc než polovina z nich dvakrát nebo vícekrát. Celkem 52 % obchodních lídrů uvedlo, že se jejich prognóza spletla o 10 % nebo víc.

Gartner zjistil, že jen 45 % obchodních lídrů a prodejců má vysokou důvěru v přesnost prognóz své organizace. Weekly forecast log v této tabulce zaznamená číslo každého týdne, takže vaše vlastní chybovost přestane být odhadem.

Kolik historie šablona potřebuje?

Nastavení Months of history to use (počet měsíců historie) začíná na 6 a sloupec Used in forecast (použito v prognóze) označí nejnovější měsíce až do tohoto počtu. Čtyři ukázkové měsíce Spona stačí k předvedení metody; vaše vlastní historie by měla pokrýt aspoň čtvrtletí.

Tým, který uzavře jen pár obchodů měsíčně, by měl zadat celý rok. Pak nastavte Months of history to use na 12. Pravděpodobnosti fází se přepnou na vaši vlastní míru, až když do fáze dojde 10 uzavřených obchodů. Malé týmy zůstanou u výchozích hodnot déle.

Dá se šablona používat bez CRM?

Ano. List Pipeline je pro prognózu CRM: jeden řádek na obchod s jedenácti ručně vyplňovanými sloupci. Pokud nemáte vůbec žádný seznam obchodů, začněte naší šablonou CRM a oba seznamy Stage udržujte totožné. Nejvíc záleží na Expected close, protože list Forecast čte obchody podle Close month.