事業別損益の共通費配賦をClaude for Excelで試す手順 — 継続・撤退の判断材料に
売上・変動費・直接固定費を事業別に置き、共通費を売上高・人員数・床面積の3基準で配ってClaude for Excelに比べさせる手順。限界利益と貢献利益で撤退の影響を見る検算まで、数値例つきで扱います。
事業別損益で共通費を配る基準を変えると、同じ会社の同じ数字から、黒字の事業が赤字に見えたり、その逆になったりします。Claude for Excelは、基準を切り替えられる損益シートを数式で組み、基準ごとの結果を並べるところまで進められます。ただし、どの基準が妥当か、撤退したら共通費がどれだけ減るかは人が決める前提です。この記事では、その線引きを手順にしました。
使うのは架空の3事業(A・B・C)です。特定の会計ソフトには依存せず、事業別の売上・原価・共通費がExcelのブックに入っていれば同じ流れで進められます。予算との差異を月次で見る作業は月次予実対比レポートの差異分析が主題で、ここで扱うのはその先の「事業ごとの採算をどう見るか」です。
配賦基準を変えると事業の黒字・赤字はどう入れ替わるか
先に架空の数字で結論を示します。単位は千円で、共通費(本社費)の総額は36,000です。
| A事業 | B事業 | C事業 | 合計 | |
|---|---|---|---|---|
| 売上高 | A事業120,000 | B事業80,000 | C事業40,000 | 合計240,000 |
| 変動費 | A事業66,000 | B事業52,000 | C事業30,000 | 合計148,000 |
| 限界利益 | A事業54,000 | B事業28,000 | C事業10,000 | 合計92,000 |
| 直接固定費 | A事業24,000 | B事業12,000 | C事業6,000 | 合計42,000 |
| 貢献利益 | A事業30,000 | B事業16,000 | C事業4,000 | 合計50,000 |
限界利益は売上高から変動費を引いた額、貢献利益はそこから事業が直接負担する固定費を引いた額です。ここまでは配賦の基準に左右されません。共通費を配ったあとの営業利益は、基準によって次のように変わります。
| 配賦基準 | A事業の営業利益 | B事業の営業利益 | C事業の営業利益 | 合計 |
|---|---|---|---|---|
| 売上高比 | A事業の営業利益12,000 | B事業の営業利益4,000 | C事業の営業利益-2,000 | 合計14,000 |
| 人員数比(20人・30人・10人) | A事業の営業利益18,000 | B事業の営業利益-2,000 | C事業の営業利益-2,000 | 合計14,000 |
| 床面積比(300・100・100㎡) | A事業の営業利益8,400 | B事業の営業利益8,800 | C事業の営業利益-3,200 | 合計14,000 |
合計は3基準とも14,000で動きません。動くのは事業間の配り方だけです。B事業は、売上高比なら4,000の黒字、人員数比なら2,000の赤字、床面積比なら8,800の黒字になります。人員数が多い事業ほど共通費を多く負担する基準なので、B事業の見え方がここまで振れます。
配賦後の数字を読むときの前提
- 配賦基準を変えても、全社の営業利益は変わらない
- 変わるのは、共通費をどの事業が負担したように見えるか
- 継続・撤退の判断には、配賦前の限界利益と貢献利益を並べて使う
Claude for Excelに任せる範囲と、人が決める範囲
Claude for Excelは、ExcelにClaudeを持ち込むアドインです。開いているブックについての質問にはセル単位の出典が付き、数式の関係を保ったまま前提を変えられます。既存のテンプレートへの入力や、自然言語の説明からのモデル作成にも対応します。並べ替え、フィルター、ピボットテーブルの編集、条件付き書式、入力規則のドロップダウン作成といったExcel本来の操作も、そのまま依頼できます。
事業別損益に当てはめると、任せやすいのは次の作業です。
- 入力シートの数字から、限界利益・貢献利益・営業利益を数式で組む
- 配賦基準をドロップダウンで切り替えられるようにする
- 基準ごとの結果を1つの表に並べる
- 合計が一致するかを確かめる検算行を足す
一方、次の判断はブックの中に答えがありません。
- どの配賦基準が自社の実態に合うか
- 共通費のうち、事業をやめれば実際に減る額はいくらか
- 撤退や継続の最終判断
利用できるのはPro、Max、Team、Enterpriseの各プランです。対応するExcelは、Excel on the web、Microsoft 365契約のWindows版(ビルド16.0.13127.20296以降)、Mac版(バージョン16.46以降、ビルド21011600以降)です。Excel 2016・2019の永続ライセンスやボリュームライセンス版、iPad、Androidでは動きません。
使えない操作もあります。データテーブル(What-If分析のデータテーブル)と、マクロ・VBAの操作には対応していません。感度分析をデータテーブルで組んでいるブックでは、同じ結果を通常の数式で組み直す必要があります。この記事の手順は、最初から通常の数式だけで組む形にしてあります。
手順1: 入力シートに事業別の数字を置く
数式が短くなるかどうかは、入力シートの形で決まります。事業を列、項目を行にして、次のように置きます(単位は千円)。
入力シート
A列 B列 C列 D列
1 項目 A事業 B事業 C事業
2 売上高 120000 80000 40000
3 変動費 66000 52000 30000
4 直接固定費 24000 12000 6000
5 人員数 20 30 10
6 床面積 300 100 100
8 共通費総額 36000配賦基準に使う項目(人員数、床面積)も同じ表に並べておくと、基準を増やすときに行を足すだけで済みます。変動費と固定費の分け方は会社ごとに違うため、原価の勘定科目をどちらに振るかは、依頼の前に人が決めて入力しておきます。
作業は、必ず信頼できるコピーで始めます。公式のベストプラクティスも、大きな編集を頼む前に信頼できるコピーのブックで始めること、変更を確定する前に必ず内容を見直すことを挙げています。
手順2: 配賦基準を切り替えられる損益シートを組ませる
次に、損益シートを依頼します。基準の切り替え方、シート名、計算式の意味まで書くと、やり直しが減ります。
「配賦基準」シートのB1に、売上高・人員数・床面積から選べる
ドロップダウンを作ってください。
次に「事業別損益」シートを作り、B〜D列に各事業、E列に合計を置いて、
行ごとに次を数式で組んでください。
2 売上高 / 3 変動費 / 4 限界利益(売上高-変動費)
5 直接固定費 / 6 貢献利益(限界利益-直接固定費)
7 配賦共通費 / 8 営業利益(貢献利益-配賦共通費)
9 限界利益率 / 10 営業利益率
配賦共通費は、入力シートの共通費総額を、B1で選んだ項目の
事業別の値の比で配ってください。値の直書きは使わないでください。配賦共通費の行は、例えば次のような式になります(公式サンプルではなく、この手順のための例です)。
B7: =入力!$B$8
*INDEX(入力!B$2:B$6, MATCH(配賦基準!$B$1, 入力!$A$2:$A$6, 0))
/SUM(INDEX(入力!$B$2:$D$6, MATCH(配賦基準!$B$1, 入力!$A$2:$A$6, 0), 0))
B8: =B6-B7MATCH でB1の選択項目が入力シートの何行目かを探し、INDEX でその行の事業別の値を取り出しています。分母は同じ行の合計です。数式で組ませておけば、B1を切り替えるだけで3基準の結果が再計算され、共通費総額や事業別の数字を更新しても自動で追随します。値だけを書き込ませると、基準を変えるたびに依頼し直しです。
公式は、前提を変えても数式の関係が保たれ、下流のセルが再計算されると説明しています。基準の切り替えはこの性質をそのまま使う作業です。
手順3: 3つの基準の結果を1枚に並べる
ドロップダウンを切り替えながら3回見る方法は、会議で説明しにくくなります。基準ごとの営業利益を並べた比較表を、別シートに作らせます。
「基準比較」シートを作り、売上高比・人員数比・床面積比の
3基準について、事業別の配賦共通費と営業利益を並べてください。
配賦前の貢献利益を最上段に置き、基準ごとの営業利益が
貢献利益からいくら減ったかも示してください。
ドロップダウンの値を変えず、各基準の式を個別に組んでください。この依頼で、ドロップダウンの値を変えない指定が効きます。比較表がドロップダウンの選択に連動すると、表示中の基準しか合っていない表になり、比べたい数字が揃わなくなるからです。
比較表ができたら、最初の節の2つめの表と一致するかを見ます。数式で組んだ結果が手計算と合わなければ、参照範囲のずれを疑います。
基準ごとの見え方の違い
売上高比
売上の大きい事業ほど多く負担します。負担の大きさが事業の稼ぐ力に比例する基準です。
人員数比
人が多い事業ほど多く負担します。人件費や管理業務に共通費が使われる会社で使われます。
床面積比
場所を広く使う事業ほど多く負担します。賃料や設備費が共通費の中心のときに合います。
基準の選び方に決まった正解はなく、共通費の中身が何に引かれて発生しているかで変わります。本社の家賃が中心なら床面積、情報システム部の費用なら利用者数や端末数といった具合に、共通費を費目に分けて費目ごとに基準を持たせる組み方もあります。その場合は、費目の一覧と基準をClaudeに渡し、入力シートに費目の行を足して式を広げます。
手順4: 継続・撤退は配賦前の数字と回避可能額で見る
配賦後の営業利益が赤字の事業を、そのまま撤退候補にすると判断を誤ることがあります。共通費は事業をやめても自動的には減らないためです。撤退の影響は、その事業が稼いでいた貢献利益と、共通費のうち実際に減る額で決まります。
C事業を例にします。C事業は売上高比でも人員数比でも床面積比でも赤字ですが、貢献利益は4,000のプラスです。
| シナリオ | 全社の営業利益 | 計算 |
|---|---|---|
| 現状(3事業とも継続) | 全社の営業利益14,000 | 計算50,000-36,000 |
| C事業を撤退・共通費は減らない | 全社の営業利益10,000 | 計算14,000-4,000 |
| C事業を撤退・共通費が3,000減る | 全社の営業利益13,000 | 計算10,000+3,000 |
どちらの撤退案でも、全社の営業利益は現状より下がります。撤退のほうが全社にとって有利になるのは、C事業の撤退で減る共通費がC事業の貢献利益4,000を上回るときです。この4,000が、判断の分かれ目になる数字です。
逆の誤りもあります。人員数比の営業利益はB事業が-2,000ですが、B事業の貢献利益は16,000です。共通費が減らない前提でB事業をやめると、全社の営業利益は14,000から-2,000になります。配賦後の数字だけでは、B事業が全社の利益を支えていることが見えません。
依頼の文は、次のように書けます。
「撤退検討」シートを作り、事業をプルダウンで選べるようにしてください。
選んだ事業を撤退した場合の全社営業利益を、次の式で出してください。
現状の全社営業利益 - 選んだ事業の貢献利益 + 撤退で減る共通費
撤退で減る共通費は入力セルにして、初期値は0にしてください。
撤退で全社が有利になる「減る共通費」の損益分岐額も表示してください。損益分岐額は、選んだ事業の貢献利益と同じ数字になります。式が複雑でないぶん、表示した値が貢献利益と一致するかを見れば、シートの組み間違いに気づけます。
貢献利益がプラスなのに営業利益が赤字になる事業は、撤退の前に打ち手があります。値上げした場合の粗利の動きは値上げシミュレーションを離脱率つきで組む手順、人員配置を変えた場合の人件費は要員計画の人件費シミュレーションで、同じ入力シートの型を使って比べられます。
手順5: 検算を入れてから会議に出す
公式は、監査上重要な計算を検証なしに任せる使い方や、レビューなしの最終成果物としての利用を推奨していません。事業別損益は撤退の議論に使う資料ですから、次の突合を入れます。
- 3基準とも、営業利益の合計が14,000(貢献利益合計50,000から共通費36,000を引いた額)になっているか
- 配賦共通費の合計が、入力シートの共通費総額と一致するか
- 事業別の売上高・変動費の合計が、試算表や損益計算書の合計と一致するか
- 配賦比率の合計が100%になっているか
- 撤退検討シートの損益分岐額が、選んだ事業の貢献利益と一致するか
検算行も、数式で組ませておきます。
「事業別損益」シートの最下行に検算行を追加してください。
- 配賦共通費の合計 - 入力シートの共通費総額
- 営業利益の合計 - (貢献利益の合計 - 共通費総額)
どちらも0になるべきで、0でなければ赤字で表示してください。この検算は、基準を切り替えても常に0になります。0にならないときは、配賦の式で分母の範囲がずれているか、事業の列を1つ足し忘れたといった組み間違いです。
Claudeは、既存データを上書きする前に警告を出します。確認画面は流さず、何が書き換わるかを目で見てから承認します。
毎期の更新に備えて指示を固定する
配賦の基準や表示の単位は、毎回書くと抜けが出ます。アドインのSettingsにあるInstructions欄に、恒常的な指示を置けます。
- 金額は千円単位、千円区切りで表示する
- 配賦は必ず数式で行い、値の直書きはしない
- 撤退で減る共通費など、根拠が不明な数字は推測で入れず、空欄にして確認を求めるこのInstructionsはExcelにだけ適用され、PowerPointやWordの設定とは別です。
手順そのものを再利用したい場合は、Skillsが選択肢になります。Claudeの設定で有効にしたSkillsは、Claude for M365の各アドインで使えます。サイドバーで / を入力すると、そのアプリで使えるSkillsから選べます。ただしSkillsとコネクタは、Claudeアカウントで直接サインインしている場合の機能です。Amazon Bedrock、Google Cloud Vertex AI、Azure AI Foundry、LLMゲートウェイ経由の接続では使えない場合があり、接続方式ごとの状況は第三者プラットフォーム向けの機能比較で見られます。
会話の記録は、使い方によっては消えます。チャット履歴は端末のブラウザー内に保存され、利用者・組織・Officeアプリごとに直近50件までです。ブラウザープロファイルの保存容量が尽きたときも古い会話から消えるため、検算の依頼文や指示は会話に残る前提にせず、Instructionsやブック内のメモシートに置いておくと再現できます。
使えないこと・注意すること
- データテーブル、マクロ、VBAの操作には対応していません。事業別損益をVBAで組んでいる会社は、その部分を数式に置き換えるか、人が実行します
- 外部から受け取ったファイルには、Claudeを操作する隠れた指示が含まれる可能性があります。公式は、信頼できるスプレッドシートでだけ使うよう警告しています。子会社や取引先から届いた事業別の数字は、信頼できる形に作り直してから貼り付けます
- 入力と出力は、受領または生成から30日以内にバックエンドで削除されます。組織で独自のデータ保持設定をしていても、Claude for Excelはそれを引き継ぎません。Enterprise監査ログの対象にもなりません
- 事業別の採算は、撤退や人員の議論に直結する機密性の高い情報です。機密性の高い、または規制対象のデータを含むモデルは、適切な管理なしに使うことが推奨されていません
社外に出せない損益データを扱う部門は、情報システム部門と、これらの取り扱いを先に確かめておくと安全です。
隣接する作業との使い分け
予算と実績の差を科目別に見る作業は、月次予実対比レポートの差異分析が扱っています。こちらは時間軸の比較で、この記事の事業軸の配賦とは見る方向が違います。同じブックで両方を組む場合は、事業コードと科目コードを別々のキーとして入力シートに持たせます。
モデル全体を組む作業はClaudeで財務モデルを作成する手順が近く、財務の資料をどこまで任せるかの線引きはClaudeに決算資料・税務書類の作成をどこまで任せてよいかにまとめています。
まとめ
共通費の配賦は、基準を変えるだけで事業の黒字・赤字が入れ替わる計算です。Claude for Excelに基準切り替え式の損益シートと比較表を数式で組ませ、継続・撤退の議論には配賦前の貢献利益と撤退で減る共通費の額を使います。後者は人が根拠を添えて入力セルに書く数字で、判断の中心はそこに残ります。