Claude for Excelで塾の損益分岐の生徒数を出す手順 — 講師給の段差と退塾の試算

塾の教室で「何人いれば黒字か」を出すとき、固定費を生徒1人あたりの粗利で割る一本の式だけを使うと、実際より1〜2人少ない数字が出ます。講師給が生徒の数ではなく、クラスの数に合わせて増えるからです。

この記事では、月謝・家賃・講師給から教室ごとの損益分岐を求め、退塾が増えた場合の損益を試算する表を、Claude for Excelと組む手順を書きます。前提の入力、試算表の式、作例の数字、3教室の比較、退塾の試算、検算の順に進めます。数字はすべて作例で、実在の塾のものではありません。

損益分岐の生徒数は講師給の段差で一本線の答えからずれる

損益分岐の生徒数は、売上と費用が等しくなる生徒数です。塾の費用は性質が3つに分かれます。

2番目が肝です。講師給を「生徒1人あたり」に均して一本の式で割ると、定員8人のクラスに9人目が入った瞬間に新しいクラスの講師給が丸ごと乗る現実が消えます。そのぶん損益分岐は低く出ます。

表にすると、損益は生徒数が増えるごとに右肩上がりになりますが、クラスが増える生徒数のところで一度だけ下に落ちます。この落ち込みの位置を押さえておくと、教室長が「あと何人で黒字」と言うときの数字に、クラス編成の条件を添えられます。

なお、この記事の式は集団授業が前提です。個別指導は講師給が生徒のコマ数に比例するため、費用の分類が変わります。その場合は2番目の費用を「生徒に比例する費用」へ移して同じ表を使います。

教室ごとの前提を入力シートに分ける

最初に、数字を入れる前提シートを作ります。教室ごとに列を分け、式はこのシートを参照させます。作例のA校は次の値です。

固定費の合計は550,000円です。ここで決めておく点が2つあります。

1つ目は月謝の値です。兄弟割引や特待で月謝が下がっているなら、定価ではなく生徒全体の平均の実収入を使います。月謝を定価で入れると、損益分岐が実際より低く出ます。

2つ目は講師給の中身です。授業のコマ単価に、準備時間や交通費、研修の手当を含めるかを決めます。含めないと決めたなら、それらを固定費の「その他」に入れます。どちらに置くかは教室で揃えます。置き場所が教室ごとに違うと、3教室を並べた比較が意味を失います。

実績の数字を既存の月次収支シートから写すなら、Claudeに「どのセルの値を使ったか」を答えさせます。Claude for Excelは、ブックについての質問にセル単位の出典を付けて答え、その出典をクリックすると該当セルに移動できます。前提シートの各値が元の収支表のどの行から来たか、確かめながら入れられます。

生徒数を1人ずつ増やす試算表をClaude for Excelに組ませる

試算表は、生徒数を1人から120人まで1行ずつ並べ、行ごとに損益を計算する形にします。一本の式で答えを出さず、全部の行を見える形にするのが目的です。

Claudeへの依頼は、列と式を具体的に書きます。

「前提」シートの値を使って、新しいシート「試算」を作ってください。
A列に生徒数を1から120まで入れます。
B列 クラス数 = 生徒数÷定員を切り上げた値
C列 売上 = 生徒数×月謝
D列 講師給 = クラス数×1クラスの講師給
E列 固定費 = 家賃+教室長の給与+その他の固定費
F列 損益 = 売上−講師給−固定費
前提の値は、セルを直接参照してください。数字を式に埋め込まないでください。

「セルを直接参照して」と頼むのは、前提を変えたときに試算が追随するようにするためです。Claude for Excelは、数式の関係を保ったまま値を更新する用途を想定した機能を持っています。前提シートの月謝を変えれば、下流の列がすべて再計算される形になります。

Claudeが組んだ式は、そのまま信じず目で確かめます。たとえばクラス数の列は、次の形になっているはずです。

B2: =ROUNDUP(A2/前提!$B$6,0)
D2: =B2*前提!$B$7
F2: =C2-D2-E2

損益分岐の生徒数は、F列が初めて0以上になる行です。ただし前の節の段差があるので、「黒字になる最小の生徒数」と「そこから先がずっと黒字の最小の生徒数」は一致しません。2つ出します。

G2: =MIN(F2:$F$121)
黒字の最小の生徒数:      =MINIFS(A2:A121,F2:F121,">=0")
ずっと黒字の最小の生徒数: =MINIFS(A2:A121,G2:G121,">=0")

G列は、その行以降の損益の最小値です。これが0以上なら、その生徒数から先に赤字の行が出ません。

A校の例で見る段差 — 46人でも47人でもなく50人が安定ライン

A校の数字を試算表に入れると、生徒数ごとの損益は次のようになります。

47人で初めて黒字になります。ところが49人目が入ると、7クラス目の講師給48,000円が乗り、損益は再びマイナスに戻ります。黒字が安定するのは50人からです。

一本線の答えは、固定費550,000円を1人あたりの粗利12,000円で割った値です。粗利12,000円は、月謝18,000円から、1人あたりの講師給6,000円(48,000円÷定員8人)を引いて出します。550,000÷12,000は約45.8で、切り上げて46人です。実際に黒字になるのは47人からで、さらに49人の谷があります。

もちろん実際の塾は学年別・科目別にクラスを組むので、生徒がどのクラスにも自由に入れるわけではありません。この表は、教室全体の人数だけで見たときの目安です。学年別に細かく見たい場合は、学年ごとに同じ表を作り、固定費を生徒数の比などで按分して並べます。

3教室を並べると線形の答えとのずれ方が違う

教室ごとに前提が違うと、段差の効き方も変わります。作例のB校とC校を並べます。

試算表に通した結果は次のとおりです。

C校は、一本線で計算すると「51人で黒字」と出ます。ところが現在52人でも6,000円の赤字です。51人のときも52人のときも7クラスで運営していて、講師給が53人目まで変わらないためです。あと1人入れば+15,000円になります。固定費の重い教室ほど、一本線の答えと実際の差が、現場の感覚とずれやすくなります。

B校は定員が10人と大きいので、クラスの段差の間隔が広く、現在の38人がちょうど4クラスぎりぎりです。損益は黒字ですが、余裕は13,600円しかありません。次の節の退塾の試算が効いてくるのは、こういう教室です。

退塾が増えたとき、黒字がどこで崩れるか

退塾の試算には2つの見方があります。

クラス数を変えない見方なら、1か月ぶんの式は1行で済みます。

退塾に耐えられる人数 =INT(現在の損益÷月謝)

A校は86,000円÷18,000円で4.7となり、4人までなら黒字を保てます。5人辞めた月は−4,000円です。54人に対して4人は約7%にあたるので、退塾率が7%を超えるかどうかが目安になります。B校は13,600円が月謝16,000円に届かないので、1人辞めただけで−2,400円になります。

クラスを統合する見方なら、A校は退塾が6人になって48人になった時点で6クラスにまとめられれば、損益は+26,000円に戻ります。ここでの条件は、学年と曜日が合う生徒を1クラスに詰められることです。49人のときはどうしても7クラス必要で、前の節の谷にそのまま当たります。

退塾の想定は、Claudeに頼むと複数の人数を並べた表にまとめてくれます。

「試算」シートの現在の生徒数(前提の現在値)を起点に、退塾が1人から10人までの場合の表を新しく作ってください。
列は、退塾の人数、残る生徒数、クラス数を変えない場合の損益、クラスを統合できる場合の損益です。
統合できる場合のクラス数は、残る生徒数÷定員を切り上げた値にしてください。
各行が黒字か赤字かが分かるように、赤字の行に条件付き書式で色を付けてください。

条件付き書式の設定も、Claude for Excelが扱う操作に含まれます。並べ替えやフィルター、ピボットテーブルの編集、データ検証のドロップダウンも、直接頼める操作です。

数字を信じる前に確かめる箇所

損益分岐の数字は、教室を閉じるか、講師を減らすか、月謝を上げるかといった判断に使われます。Claudeに組ませた表は、次の点を確かめてから使います。

• 試算表の1行を電卓で追う: 47人の行で、売上846,000円、講師給288,000円、固定費550,000円、損益+8,000円になっているか。1行が合っていれば、式の構造は合っています
• クラス数の切り上げを確かめる: 定員の倍数ちょうど(48人)でクラス数が増えていないか、49人で増えているか
• 月謝と講師給の置き場所が教室で揃っているか: 前提シートの全教室で、同じ項目に同じ種類の費用が入っているか
• 前提を1つ変えて動きを見る: 月謝を1,000円上げたら損益分岐が下がる向きに動くか

Claude for Excelの公式ページは、監査に使う計算や最終的な顧客向け成果物に、人の確認なしで使うことを勧めていません。損益分岐は経営判断の材料にとどまるので、数字は教室長か経理の目を通します。Claudeは書き込む前に既存データの上書きを警告しますが、元のブックの複製を作ってから頼むほうが安全です。

感度分析の表を作るときの注意点もあります。非対応の機能として、データテーブルとマクロ・VBAが挙げられています。月謝や定員を変えた場合の表は、データテーブルの機能に頼らず、前の節のように行ごとに式を書く形にします。

生徒の氏名や成績は、このブックに入れません。損益分岐の表に必要なのは人数と金額だけです。集計済みの数字だけを持つブックなら、生徒個人の情報を渡さずに済みます。外部から受け取った見積書や他社のひな形を同じブックに貼ると、そこに仕込まれた指示にClaudeが従ってしまう危険があるため、自分の教室の数字だけで組んだブックを使います。

月謝や講師給の定義でつまずきやすい所

数字が合わないときは、多くが前提の定義にあります。

• 月謝に講習費を入れた: 夏期・冬期講習は月によって偏ります。月次の損益分岐からは外し、講習の期間は別に見ます。講習期間のコマ数と講師の割当は、夏期講習の時間割と講師割当の記事で組み方を扱っています
• 講師給をコマ単価で入れたが、実際は固定給の講師がいる: 固定給の講師は固定費に入れ、時給講師だけを1クラスの講師給に入れます
• 学年別にクラスの定員が違う: 小学生クラスは定員が少なく、受験学年は大きい場合があります。教室の平均で計算せず、学年ごとに前提を分けます
• 消費税の扱いが月謝と費用でずれた: 月謝を税込にしたなら、家賃や外注費も税込で揃えます
• 人件費率の見方: 教室ごとの人件費率の変化を要因に分けて見るなら、人時生産性と人件費率の分解の手順が応用できます

試算の結果は、月が変わるたびに更新します。毎月の実績との差を理由つきで残す方法は、月次変動分析の理由メモにまとめています。損益分岐の生徒数が毎月動くのは、月謝の平均や講師給のコマ数が動くためで、その理由の記録が次の月の前提になります。

退塾が増える兆候を、出欠や成績や面談メモから拾う方法は、退塾防止の兆候をExcelで洗う手順の記事にあります。本記事の試算で「あと何人の退塾まで耐えられるか」が分かれば、兆候を追う優先度を教室ごとに決められます。

まとめ

塾の損益分岐は、講師給をクラス単位の段差として入れると、一本線で割った答えより1〜2人多く出ます。作例では、A校が46人→47人、C校が51人→53人でした。さらに、クラスが増える生徒数の直後で一度赤字に戻る谷があるので、「黒字になる最小の人数」と「ずっと黒字の最小の人数」を分けて持ちます。

退塾への耐性は、現在の損益を月謝で割った人数が目安です。余裕が月謝1人分に満たない教室は、1人の退塾で赤字に転じます。Claude for Excelには試算表を組ませ、1行は電卓で追い、判断は人が引き取ります。