Excelで精度の高い販売パイプライン予測モデルを作る方法｜ステップバイステップ完全ガイド

画像：Editor / Midjourney

販売パイプライン予測とは、営業ファネル内の案件状況をもとに将来の売上を予測する、ビジネスにおいて極めて重要なプロセスです。このモデルを活用することで、今後の売上を予測・分析・追跡でき、営業活動の重点を明確にできます。本記事では、Excelで販売パイプライン予測モデルを構築する手順を、ステップごとにわかりやすく解説します。

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

予測モデルを構築するには、まず構造化された営業データを整えることが不可欠です。販売パイプラインには、少なくとも以下の項目を含めましょう。

• 案件ID：各案件を一意に識別する番号。
• 案件金額：その案件から見込まれる売上（推定値）。
• 営業ステージ：案件の進捗状況（例：リード獲得、提案送付、交渉中、成約、失注）。
• 成約確率：過去の実績データに基づく、案件成立の可能性（%）。
• 成約予定日：案件が成立すると予想される日付。

計算フィールドとして追加すべき項目：

• 期待収益（加重売上）：成約確率と案件金額を掛け合わせて算出した予測売上。
• 累計加重収益：期間ごとの加重売上を累計した数値。
• 成約予定日の区分：タイムフレーム別に案件を分類したカテゴリ。
• 勝敗ステータス：案件が受注か失注か、あるいは進行中かを示す項目。

さらに、顧客名、担当営業、業種、コンバージョン率、各ステージの詳細などを追加すれば、より多角的な分析が可能になります。

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

2.1. 期待収益の計算

• セルF2を選択し、以下の数式を入力します。
• 数式を下方向にドラッグして、残りのセルにも適用します。

=D2*E2

この数式は「案件金額 × 成約確率」で期待収益を求めるものです。

2.2. 累計加重収益の計算

• セルG2を選択し、以下の数式を入力します。
• 数式を下方向にドラッグして適用します。

=SUM($F$2:F2)

この数式により、各行でそれ以前のすべての値が合計され、累計値が自動的に作成されます。

2.3. 成約予定日の区分（短期・中期・長期）

• セルH2を選択し、以下の数式を入力します。
• 数式を下方向にドラッグして適用します。

=IF(E2<=TODAY()+30, "短期", IF(E2<=TODAY()+90, "中期", "長期"))

この数式は、成約予定日が以下のどの範囲に該当するかを判定します。

• 30日以内 → 短期
• 31〜90日 → 中期
• 90日超 → 長期

2.4. 勝敗ステータスの判定

• セルI2を選択し、以下の数式を入力します。
• 数式を下方向にドラッグして適用します。

=IF(C2="成約", "受注", IF(C2="失注", "失注", "進行中"))

この数式は営業ステージを確認し、勝敗ステータスを自動判定します。

• ステージが「成約」なら「受注」。
• ステージが「失注」なら「失注」。
• それ以外は「進行中」と表示されます。

ステップ3：ピボットテーブルで売上予測を作成する

月別の売上見込みを集計するには、ピボットテーブルが便利です。

• データ範囲を選択します。
• リボンの挿入タブ → ピボットテーブル を選択します。
• 新しいワークシート を選択 → OK をクリックします。

ピボットテーブルのフィールドでは、以下のように配置します。

• 行：成約予定日
• 値：期待収益
• 列：勝敗ステータス
• フィルター：営業ステージ

ステップ4：予測結果を可視化する

• ピボットテーブルのデータを選択します。
• ピボットテーブル分析タブ → ピボットグラフ を選択します。

出力結果：

収益の内訳：

• 期待収益の合計：$30,568
• 失注：$9,688
• 進行中：$10,480
• 受注：$10,400

読み取れるポイント：

• 2月の受注収益が最も高い（$10,400）。
• 3月と4月は進行中の案件が多く、今後の成約が期待できる。
• 6月と7月は売上が低く、パイプラインの空洞化（案件不足）が懸念される。
• グラフ上で失注・進行中・受注の案件が色分けされて表示される。

ステップ5：トレンドを分析し、予測を改善する

予測モデルは作って終わりではありません。予測精度を検証し、主要指標を継続的に追跡しながら、実際のデータに基づいて調整することが重要です。

追跡すべき主要ポイント：

• パイプラインのボトルネック：成約予定日と勝敗ステータスを分析し、停滞している案件を特定します。60日以上進行中のままになっている案件は、営業プロセスの遅れを示している可能性があります。
• コンバージョン率の推移：進行中の案件が「成約」に至る割合を時系列でモニタリングします。ステージごとの受注率を比較すれば、弱点となっている工程が見えてきます。
• 予測精度の検証：期待収益と実際の受注額を比較します。実際の売上が継続的に予測を下回る場合は、成約確率の設定を見直す必要があります。

予測精度を高めるためのコツ：

• 過去の受注・失注データをもとに、コンバージョン率を定期的に更新しましょう。
• 成約予定日の区分（短期・中期・長期）を活用すれば、時間軸ごとのトレンド把握が容易になります。
• 受注率と失注率のバランスを常に監視し、営業戦略の有効性を評価しながらパイプラインを最適化しましょう。

まとめ

以上の手順に従えば、Excelだけでパイプラインデータ・成約確率・案件金額に基づいた実用的な販売パイプライン予測モデルを構築できます。このモデルを活用すれば、将来の売上予測はもちろん、パイプライン全体の健全性の把握や営業戦略の最適化にも役立ちます。定期的な更新とブラッシュアップを重ねれば、営業主導の組織における意思決定を支える強力なツールになるでしょう。自社のニーズに合わせて、さらにカスタマイズしてみてください。