フランチャイズ加盟店のロイヤリティ計算をエクセルで行う方法と原価管理の限界

結論から：ロイヤリティ計算表は「売上集計→算出→納付額突合」の3層で組む

ロイヤリティ管理は「売上集計」「ロイヤリティ算出」「本部納付額突合」の3表に分けて連動させると、エクセルでも実務は十分に回せる。ただし算出基準のズレは仕組みだけでは防げない。

フランチャイズ加盟店のオーナーから受ける相談で最も多いのが「毎月のロイヤリティ計算はエクセルでできているが、本部からの請求額と自分の計算がずれることがある」という声です。原因の多くは、1枚のシートに売上・ロイヤリティ・原価をすべて詰め込み、どの数字がどの計算の元になっているかが追えなくなっていることにあります。実務で崩れにくい構成は、目的の違う3つの表を分けて連動させる形です。以下、売上集計表→ロイヤリティ算出表→本部納付額突合表の順に、実際に手を動かせる粒度で組み方を説明します。

なお、フランチャイズ契約は本部ごとにロイヤリティの算出方式（売上高比例・粗利分配・定額など）が異なります。この記事では最も一般的な「売上高に一定率を掛ける」方式を軸に、エクセルでの実装と限界を解説します。契約書に記載された自社の算出方式を必ず確認したうえで読み進めてください。

売上高ロイヤリティ方式をエクセルで組む方法

売上高ロイヤリティは「日次売上を月次で合算し、契約上のロイヤリティ率を掛ける」だけの単純な計算だが、集計の基準となる売上高の定義を揃えないと本部との金額差異につながる。

具体的な設例で見てみます。ある月の店舗の売上高（POSレジ計上額、値引き・返品調整前）が500万円、契約上のロイヤリティ率が6%だったとします。この場合のロイヤリティ額は「500万円×6%＝30万円」です。エクセル上では、日次売上シートにPOSの締めデータを転記し、SUM関数で月次売上高を出し、別セルでロイヤリティ率を掛ける2ステップで組めば、月末の手計算は不要になります。ロイヤリティ率をセル参照にしておけば、契約更新で率が変わった際も1か所の修正で全計算に反映されます。

ここで実務上つまずきやすいのが「どの売上高を基準にするか」です。POSレジ計上額をそのまま使うのか、値引き・返品を差し引いた実収額を使うのかは契約書ごとに定義が異なります。ある加盟店オーナーから実際に受けた相談では、値引き前の総額でロイヤリティを計算していたところ、本部の算出基準は「値引き後の実収額」だったため、半年分の差額を一括で調整する事態になっていました。エクセルの数式自体は正しくても、基準となる売上高の定義がずれていると、計算結果全体が本部の請求額と食い違います。契約書の該当条項をコメント欄に転記しておき、毎回同じ基準で計算していることをシート上で確認できるようにしておくことが、この種のトラブルを防ぐ最も実務的な対策です。

原価管理とロイヤリティの整合をどう取るか

ロイヤリティは売上高に対して発生する一方、原価は仕入や人件費に対して発生するため、両者を同じ表で見ないと「ロイヤリティ納付後に本当に利益が残っているか」が見えなくなる。

先ほどの設例を使って、原価管理まで含めた収支を見てみます。月間売上高500万円、原価率30%（原価150万円）の店舗の場合、粗利は「500万円−150万円＝350万円」です。ここからロイヤリティ30万円を差し引くと「350万円−30万円＝320万円」が、人件費・家賃・水道光熱費などの固定費を賄う前の残額になります。エクセルでは、売上集計表・原価集計表・ロイヤリティ算出表の3表を1枚のサマリーシートにSUMIFSやセル参照でつなぎ、この一連の流れを自動計算できるようにしておくと、月次の着地見込みを早い段階で把握できます。

多くの加盟店で見られるのは、ロイヤリティと原価をそれぞれ別のシートで管理していて、両者を突き合わせる作業を月末にまとめて手作業でやっている状態です。これだと、原価が想定より膨らんでいる月にロイヤリティ負担も重なって初めて赤字に気づく、という後追いの経営になりがちです。サマリーシートで両者を常に連動させておけば、原価率が悪化した時点でロイヤリティ込みの利益インパクトを即座に確認でき、対策を打つタイミングを早められます。

複数店舗・値引き時のロイヤリティ計算のズレ

店舗を複数運営している場合、ロイヤリティは店舗ごとに個別計算するのが原則であり、売上を合算してから一括計算すると本部への申告額がずれる。

たとえば店舗Aの月間売上高が420万円、店舗Bの月間売上高が280万円、ロイヤリティ率がともに5%だったとします。店舗ごとに計算すると「店舗A：420万円×5%＝21万円」「店舗B：280万円×5%＝14万円」で合計35万円です。ここで売上を先に合算してから計算すると「(420万円＋280万円)＝700万円、700万円×5%＝35万円」となり、この設例では偶然一致しますが、店舗ごとにロイヤリティ率が異なる契約や、店舗ごとに上限・下限（キャップ）が設定されている契約では、合算計算だと店舗別の上限管理が効かなくなり、実際の納付額と乖離します。

もう一つ注意が必要なのが、値引きキャンペーンを実施した月です。ある店舗で、値引き前の総売上高（POS計上額）が420万円、値引き額が20万円、値引き後の実収額が400万円だったとします。ロイヤリティの算出基準が「値引き前の総売上高」であれば「420万円×5%＝21万円」、「値引き後の実収額」であれば「400万円×5%＝20万円」となり、月あたり1万円の差が生じます。1万円と聞くと小さく見えますが、年間では12万円、複数店舗になればさらに膨らみます。エクセルでは、値引き額を独立した列として記録し、どちらの基準で計算しているかをシート上に明記しておくことが、この種の集計ミスを防ぐ最初の一歩になります。

本部納付額の突合とエクセル運用の限界

エクセルでのロイヤリティ・原価管理は1〜2店舗であれば十分に運用できるが、店舗数が増えるほど算出基準の属人化とファイルの分裂リスクが高まる。

本部納付額突合表は、自社で計算したロイヤリティ額と、本部から届く請求書・明細の金額を並べて差異を出す仕組みにしておくと、金額のズレを早期に発見できます。列構成としては「対象月・自社算出額・本部請求額・差異・差異理由」を持たせ、差異が出た月は理由を必ず記録しておくと、次に同じ論点が出たときに過去の経緯を追跡できます。ここまで実装できれば、1店舗運営であればエクセルでもかなりの精度で運用が回ります。

ただし店舗数が増えると、シートのコピーが増えて数式の参照範囲がずれたり、算出基準の設定を知っているのが特定の担当者だけになったりする属人化のリスクが同時に高まっていきます。特に複数店舗でロイヤリティ率や算出基準が微妙に異なる契約を抱えている場合、どのシートがどの契約に対応しているかの管理コストがエクセルの限界を超え始めます。自社のロイヤリティ・原価データがどこまで可視化できているか、どの店舗の収支から手をつけるべきか自分たちだけでは判断しづらい場合は、初月無料の経営AI診断（通常30万円相当）で現状のデータ構造を整理し、店舗別の採算管理の改善提案までご一緒することもできます。

まとめ

フランチャイズ加盟店のロイヤリティ計算は、売上集計表・ロイヤリティ算出表・本部納付額突合表の3表構成で組めば、エクセルだけでも実務レベルの精度に持っていけます。ただし算出基準となる売上高の定義（値引き前か後か）を契約書と揃えること、複数店舗では合算せず店舗ごとに計算することの2点を押さえていないと、本部との金額差異という形で問題が表面化します。まずは自店の売上集計表を1枚組んでみて、そこからロイヤリティ算出・原価管理・本部納付額突合の3点に広げていくのが現実的な進め方です。自社の採算管理をどう可視化すればいいか迷ったら、初月無料の経営AI診断で一緒に整理することもできます。

関連記事

• 飲食店の原価管理をエクセルで行う方法 原価率計算とロス管理・棚卸の実装 — 関連: 業種別の原価管理設計
• 製造業の原価計算をエクセルで行う方法と材料費・労務費・経費の配賦の考え方 — 関連: 原価配賦の実装知
• 工事原価管理をエクセルで行う限界と実行予算・実績対比の実務知 — 関連: 原価と収支突合の設計
• 卸売業の与信・売掛管理をエクセルで行う方法 限度設定とエイジング表の作り方 — 関連: 取引先別の金額突合設計
• 中小企業のAI導入 費用相場と内訳 初期費用から運用コストまで実例で解説 — 関連: AI導入費用の考え方（異クラスタ）