Excel で信頼性の高い販売パイプライン予測を作成する – ステップバイステップ ガイド

編集者による画像 |旅の途中
 

販売パイプラインの予測は、企業が販売目標到達プロセス内の取引のステータスに基づいて将来の収益を予測するための重要なプロセスです。このモデルは、将来の売上を予測、分析、追跡するのに役立ち、売上に集中するのに役立ちます。  この記事では、Excel で販売パイプライン予測モデルを構築する方法を説明します。

ステップ 1:Excel でデータを準備する

予測モデルを構築するには、構造化された販売データを確保する必要があります。販売パイプラインには次のデータが含まれている必要があります。

• 取引 ID :各取引の一意の識別子。
• 取引額 :取引による推定収益
• セールスステージ :取引のステータス (例:検討中、交渉中、提案送信済み、成立済み、成立不成立)。
• 成約の確率 :過去のデータに基づいて推定される取引成功の可能性
• 完了予定日 :取引が完了する予定日

計算フィールド:

• コンバージョン率に基づく予想収益 :確率と取引額を考慮して収益を予測します。
• 加重累積収益 :一定期間にわたる加重収益の累計。
• 推定完了日カテゴリ :期間に基づいた取引の分類
• 勝敗ステータス :取引が勝ったか負けたかを示します。

クライアントの名前、営業担当者、業界、コンバージョン率、さまざまな段階などを含めることもできます。

ステップ 2:予測フィールドを計算する

2.1.予想収益:

• セル F2 を選択し、次の数式を挿入します。
• 数式を下にドラッグして、残りのセルに適用します。

式:

この式は、取引額と期待収益が得られる確率を乗算します。

2.2.加重累積収益:

• セル G2 を選択し、次の数式を挿入します。
• 数式を下にドラッグして、残りのセルに適用します。

式:

この数式により、各行が以前のすべての値を合計し、累積合計が作成されます。

3.2.推定終了日カテゴリ:

• セル H2 を選択し、次の数式を挿入します。
• 数式を下にドラッグして、残りのセルに適用します。

式:

=IF(E2<=TODAY()+30, "Short-Term", IF(E2<=TODAY()+90, "Medium-Term", "Long-Term"))

この数式は、予想完了日が次の範囲内にあるかどうかを確認します。

• 30 日間>> 短期
• 31～90 日>> 中期
• 90 日以上>> 長期

4.2.勝敗ステータスの計算:

• セル I2 を選択し、次の数式を挿入します。
• 数式を下にドラッグして、残りのセルに適用します。

式:

=IF(C2="Closed Won", "Won", IF(C2="Closed Lost", "Lost", "Open"))

この式は販売ステージをチェックして勝敗ステータスを確認します。

• 取引が成立した場合は「落札」
• 取引が成立しなくなった場合は「負け」
• その他のステータスは「オープン」になります。

ステップ 3. ピボット テーブルを挿入して売上収益を予測する

月ごとの収益を予測するには、ピボット テーブルを使用できます。

• データ範囲を選択します。
• 挿入 に移動します。 タブ>> ピボットテーブルを選択します .
• 新しいワークシートを選択します。>> [OK] をクリックします。

ピボット テーブル フィールド リスト内 :

• 完了予定日をドラッグします。 行内 セクション
• 予想収益をドラッグします 価値観で セクション
• 勝敗ステータスをドラッグします コラム内 セクション
• 販売ステージをドラッグします フィルタ内 セクション

ステップ 4:予測を視覚化する

• ピボット テーブル データを選択します。
• ピボットテーブル分析に移動します。 タブ>> ピボットグラフを選択します .

出力:

収益分配:

• 予想される総収益: $30,568
• 紛失: $9,688
• 開く: 10,480 ドル
• 勝利: 10,400 ドル

重要な分析情報:

• 2 月のウォン収益が最も高くなっています (10,400 ドル)。
• 3 月と 4 月には未解決の取引が多数あり、将来の成約を示唆しています。
• 6 月と 7 月は収益が低く、パイプラインのギャップを示しています。
• このグラフは、損失取引、オープン取引、成立取引を分けています。

ステップ 5:傾向を分析し、調整を行う

販売パイプラインの予測の精度を分析し、主要な指標を追跡し、実際のデータの洞察に基づいて調整できます。

追跡すべき重要な分析情報:

• パイプラインのボトルネック :予想完了日と勝敗ステータスを分析して、動きの遅い取引を特定します。 60 日を超えて取引が成立している場合は、減速を示している可能性があります。
• コンバージョン率の傾向 :時間の経過とともにオープンから成約に移行した取引の割合をモニタリングします。さまざまな販売段階での成約率を比較し、弱点を特定する
• 収益予測の精度 :予想収益と成立した取引からの実際の収益を比較します。実際の収益が常に下回っている場合は、確率の見積もりを調整する必要がある可能性があります。

予測の精度を向上させるには:

• 過去の勝敗データに基づいてコンバージョン率を定期的に更新し、収益予測を調整できます。
• 終了日カテゴリ (短期、中期、長期) を使用して、さまざまな時間枠での傾向を理解できます。
• 勝敗率を注意深く監視して販売戦略の有効性を評価し、それに応じてパイプラインを調整する

結論

上記の手順に従うことで、Excel で機能的な販売パイプライン予測モデルを構築し、パイプライン データ、コンバージョン確率、および取引額に基づいて将来の売上を見積もることができます。このモデルは、将来の価値を予測し、パイプラインのパフォーマンスを特定して販売戦略を最適化するのに役立ちます。定期的な更新と改良により、販売主導の組織における意思決定のための強力なツールとなります。特定の要件に基づいて、モデルにさらにカスタマイズを適用できます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!