売上が増えても利益が残らない会社へ｜粗利ダッシュボードをExcelで作る方法

「今月は売上が伸びた」と報告されているのに、月末の利益を見ると前年や前月より少ない。こうした状況で、売上のグラフだけを眺めても原因はわかりません。

確認したいのは、売上が増えた商品や取引先が、粗利益も増やしているのかという点です。今回は、中小企業がExcelで始められる粗利ダッシュボードの作り方と、外注した方がよい条件を整理します。

売上が増えても、粗利は減ることがある

次の数字は説明用の架空データです。

指標：売上高 ／ 前月：1,000万円 ／ 当月：1,200万円

指標：売上原価 ／ 前月：800万円 ／ 当月：1,044万円

指標：売上総利益（粗利） ／ 前月：200万円 ／ 当月：156万円

指標：粗利率 ／ 前月：20.0% ／ 当月：13.0%

売上高は20%増えていますが、粗利額は44万円減り、粗利率は7.0ポイント低下しています。単に売上が伸びたかどうかではなく、増えた売上の中身を確かめなければ、改善策を選べません。

基本式は「粗利＝売上高－売上原価」「粗利率＝粗利÷売上高×100」です。中小企業基盤整備機構の経営指標でも、粗利率と営業利益率は別の指標として扱われています。粗利が減ったのか、販管費が増えたのかを切り分けることが出発点です。

ダッシュボードに表示したいのは、4つの数字と内訳

最初から複雑な分析画面を作る必要はありません。私なら、経営者が一目で確認する部分を「売上高・粗利額・粗利率・前月差」の4つに絞ります。その下に商品別・得意先別・月別の内訳を配置します。

図1：架空データで作成した粗利ダッシュボードの設計イメージ。実際の会計ソフトや顧客システムの画面ではありません。

図の架空例では、商品Aは売上600万円で粗利60万円、粗利率10%です。商品Bも粗利60万円ですが、売上は400万円。売上順位だけで評価すると、利益率の違いが見えなくなります。

ただし、粗利率が低い商品を機械的に販売停止にするのは早計です。販売数量、粗利額、固定費への貢献、取引先との関係など、判断に必要な条件を合わせて確認します。

Excelで作る手順は「データの形」を決めるところから

1．売上と原価を同じ単位で並べる

まず、売上明細を「1行＝1取引明細」にそろえます。必要な列は、取引日、伝票明細ID、商品コード、得意先、数量、税抜売上高、対応する売上原価です。必要に応じて部門や販路も追加します。

ここで重要なのは、当月の仕入額をそのまま当月の売上原価とみなさないことです。在庫を持つ業態では、期首在庫・当期仕入・期末在庫の関係を考慮する必要があります。請求日と売上計上日が違う場合も、同じ期間で比較できるように整理します。

2．明細ごとの粗利を計算する

Excelのデータをテーブル化し、列名を「売上高」「売上原価」「粗利益」とした場合、粗利益列には次の式を入れられます。

=[@売上高]-[@売上原価]

明細の参考粗利率を表示するなら、ゼロ除算を避けて次のようにします。

=IF([@売上高]=0,"",[@粗利益]/[@売上高])

ただし、全社・商品別の粗利率は明細率の単純平均ではありません。集計した粗利益の合計を、売上高の合計で割って算出します。返品、値引き、キャンセルをどのような符号・期間で記録するかも事前に統一してください。

3．ピボットテーブルで比較する

Excelのピボットテーブルで、行に「年月→商品」、値に「売上高の合計」「売上原価の合計」「粗利益の合計」を置きます。必要なら得意先別も別表で作ります。集計した粗利額÷集計売上高で粗利率を求め、ピボットグラフなどで前月と比較します。

月次CSVの列構成が一定なら、Power Queryを利用して複数ファイルを結合し、更新しやすい仕組みにできます。元データを追加しただけで数字が必ず反映されるわけではなく、接続・更新手順を設計し、更新後の確認も必要です。

粗利の悪化を見つけたら、何を確認するのか

粗利率が下がった理由は一つとは限りません。たとえば商品Aの粗利が減っていれば、販売単価の値引き、仕入単価の上昇、返品・廃棄、売上構成の変化を順に調べます。担当者を責めるためではなく、どの条件を変えれば改善するかを見つけるための分析です。

もう一つ大切なのが、会計の数字との照合です。明細から計算した粗利と、月次の損益計算書の粗利が一致しないなら、棚卸調整、原価の配賦、締め日の違い、未確定原価などを確認します。一致していない数値を「確定した利益」として扱わない設計が必要です。

粗利は最終利益でも現金残高でもありません。家賃・人件費・広告費など、売上原価に含めない費用が増えれば営業利益は減りますし、入金の遅れや在庫増加は資金繰りへ影響します。「粗利ダッシュボードで見える範囲」を明確にして使うことが重要です。

Excelで十分な会社と、別の仕組みが向く会社

Excelだけで始めやすい条件は、扱うデータ量が小さく、原価の持ち方が決まっており、主な更新担当者が限られていることです。まずは商品別の粗利額と粗利率が正しく出ることを確認しましょう。

一方、売上・仕入・在庫が別々のシステムにあり、複数担当者が頻繁に更新する場合は、データの突合や権限管理が課題になります。Power Query、Pythonによる定型処理、データベースを使った集計などを、件数と運用方法に応じて選びます。画面を作るより先に、原価と売上を対応付けるルールを決めることが大切です。

制作を外注する場合、費用を左右するのは見た目以上に、入力ファイルの種類、原価の計算方法、在庫・返品の扱い、更新頻度、照合テストの範囲です。仕様が確定した小規模・単機能のExcel集計と、複数システム連携を含む粗利管理では、必要な作業が異なるため個別に見積もる方が適切です。

まず手元に準備したいもの

最初に必要なのは、機密情報を伏せた売上明細のサンプル、原価がわかる資料、現在使っている月次損益計算書の項目、そして「商品別なのか、得意先別なのか」という分析目的です。これだけでも、Excelで対応できるか、データの整備から必要かを検討できます。

数字をきれいに並べることが目的ではありません。売上の増減を見て終わる管理から、粗利が変わった理由を追える管理へ変えること。そのための最小限の画面から始めるのが、無理のない方法です。

私も、Excel集計や業務データの整理・自動化について、ココナラ内のメッセージで購入前のご相談を受け付けています。既存資料を匿名化したうえで、必要な機能と対応範囲を整理するところからご相談いただけます。