「Excel で損益計算書（P/L）を作ろうとしたら、数式が縦横に入り組み、前提の売上成長率を 1 つ変えただけで数値が壊れた」——そんな経験はないでしょうか。結論から言えば、壊れない P/L モデルの鍵は“数式の書き方”ではなく“セル設計”にあります。 理由はシンプルで、財務モデルの品質はセル 1 つの関数ではなく、前提・計算・出力をどう分離したかでほぼ決まるからです。たとえば前提値を数式の中に直接打ち込むと、後で条件が変わったとき修正漏れが起き、静かに間違った利益が出力されます。本記事では、現役コンサル（具体企業名は伏せる）が実務で組む、3 期分の P/L を 1 シートで仕上げるセル設計と関数の使い方を、そのまま流用できる形で整理します。

前提｜なぜ「3 期分」を 1 シートで作るのか

事業計画の P/L は単年ではなく複数年で作るのが定石です。金融機関は短期の返済能力を、投資家は中長期の成長性を見るため、少なくとも 3 年、できれば 5 年分を並べると説得力が増します（事業計画書の P/L の作り方（bulkup））。実務では 1 期目は月次、2 期目以降は年次 で作ると、初年度の資金繰りの解像度と、中期のトレンドの見やすさを両立できます。

そして 3 期分を横に並べる最大の効用は、前年比の変化が一目で追えることです。売上・費用・利益を年ごとに横並びにすると、どの費用がどれだけ膨らんだかがすぐ分かり、中期経営計画の検討やコスト構造のレビューに直結します。

P/L モデルの 3 ブロック構造

壊れないモデルは、シートを役割で 3 つに分けます。この分離こそがコンサル品質の土台です。

ブロック役割中身色分け
A. 前提条件入力する顧客数・単価・成長率・原価率・税率🔵 青字
B. 計算（P/L 本体）数式で連動売上・原価・粗利・販管費・営業利益⚫ 黒字
C. 参照・集計他シート引用KPI・グラフ用データ🟢 緑字

この 3 分離と色分け（青＝直接入力・黒＝計算式・緑＝他シート参照）は、財務モデリングの標準作法として広く推奨されています（財務計画（財務モデル）の作り方（start-link））。色を見るだけで「ここは触っていいセル／触ってはいけないセル」が判別でき、他人が引き継いでも事故が起きません。

ステップで組む｜前提から営業利益まで

STEP 01

A ブロックに前提条件を集約する

シート上部（1〜10 行目）に、顧客数・月額単価・成長率・原価率・人件費・固定費・税率といった「後で動かしたくなる数値」をすべて青字で並べます。数式の中に直接書きたくなった数字は、例外なくここへ追い出すのが原則です。

STEP 02

売上を「単価 × 数量」で構造化する

売上は結果ではなく分解して作ります。売上 = 顧客数 × 月額単価 が基本形で、既存顧客と新規顧客を分けて計算し合算すると精度が上がります。成長率は前提セルを絶対参照（$B$3）で引き、翌期は =前期売上*(1+$B$3) と横にコピーするだけで 3 期分が伸びます。

STEP 03

原価・販管費を前提参照で積む

売上原価は =売上*$原価率 のように率で連動させます。固定費や人件費は前提ブロックの月次額を参照し、採用計画による段階的増加は SUMIFS や SUMPRODUCT で「入社月が当月以前の社員だけ」を累積加算する設計にすると、期の途中の増員も正しく反映されます。

STEP 04

粗利・営業利益を差し引きで出す

粗利 = 売上 − 売上原価、営業利益 = 粗利 − 販管費 を単純な引き算で組みます。ここは前提を持たず、上のブロックの結果を引くだけにするのが鉄則。利益セルに前提値が混ざると、どこで数字が作られたか追えなくなります。

使う関数はこれだけ｜P/L モデルの主力 5 関数

P/L モデルは難しい関数を必要としません。実際に多用するのは次の 5 つに集約されます。

関数用途使いどころ
SUM範囲合計販管費の小計・費用合計
SUMIFS条件付き集計部門別・科目別に試算表から集計
IF条件分岐黒字／赤字判定・閾値処理
XLOOKUP縦横参照勘定科目コードから名称・金額を引く
絶対参照 $参照固定前提セルをコピーしてもずらさない

試算表から損益計算書を組み立てるモデルでは、SUMIFS と VLOOKUP／XLOOKUP が主役になると解説されています（Excel で学ぶ財務諸表作成術（GLOBIS 学び放題））。凝った配列数式より、この基本 5 関数を絶対参照で正しく組むことのほうが、はるかに保守しやすいモデルを生みます。

3 期比較を見やすくする仕上げ

数字が組み上がったら、レビューに耐える見た目に整えます。

  • 前年比・成長率の行を追加——各科目の下に =当期/前期-1 を置き、%表示にする。伸び／縮みが一目で伝わります
  • 表示単位を「千円」か「百万円」に統一——桁が大きいと誤読を招きます。単位の揃え方は 数値の単位を揃える方法 を参照してください
  • 入力セルだけ薄い背景色——青字に加えてセル塗りを付けると、触っていい場所がさらに明確になります

まとめ

3 期分の P/L モデルを Excel で組む核心は、前提・計算・参照の 3 分離と、基本 5 関数を絶対参照で正しく使うことの 2 点に尽きます。凝った関数は要りません。

  • 3 ブロック構造——前提（青）・計算（黒）・参照（緑）を役割で分ける
  • 前提の集約——数式に生の数字を書かず、前提ブロックの 1 セルに置いて参照する
  • 売上の分解——「単価 × 数量」で構造化し、成長率で 3 期に伸ばす
  • 主力 5 関数——SUM・SUMIFS・IF・XLOOKUP・絶対参照で保守可能に組む
  • 3 期比較の仕上げ——前年比・表示単位・入力色で、レビューに耐える見た目にする

まず、次に作る P/L で「前提にすべき数値」をシート上部に 10 行書き出してみてください。その 10 行を分離した瞬間から、あなたのモデルは壊れない資料に変わります。次は Excel で CF（キャッシュフロー）モデルを作る で、P/L から現金の流れへつなげる設計に進みましょう。

関連記事：

参考文献