売上は販売管理、仕入は会計、工数は別の表にあると、顧客別・案件別の粗利を出すたびに複数ファイルを結合する必要があります。名称表記だけで結ぶと、同名や変更でずれます。
共通の顧客コード・案件コードと対象期間を使い、売上、直接費、工数原価、配賦費を別明細としてまとめます。
先に結論
各データに顧客・案件コードがあり、原価と配賦ルールが確定していれば自動集計できます。未設定コード、案件外費用、締め後修正を別に確認し、売上と原価の計上期間をそろえます。
粗利を構成するデータ
何を原価へ含めるかを先に決めます。
- 売上・値引き・返品
- 仕入・外注費・材料費
- 社員工数と適用単価
- 交通費・経費など直接費
- 共通費の配賦
- 顧客・案件・部門・対象期間
明細を残したまま集計する
最終的な粗利だけを保存せず、どの売上・原価明細から作ったか追えるようにします。修正が入った場合も対象明細を差し替えて再集計できます。
売上計上月と仕入計上月がずれる案件では、月次粗利だけで判断すると誤解します。案件累計と月次の両方を表示します。
- 1各明細へコードを付ける
顧客・案件・部門・期間を共通キーへそろえます。
- 2未配賦・不明コードを確認する
どの案件にも属さない原価を別一覧へ出します。
- 3月次と累計を集計する
売上、原価、粗利、粗利率を案件・顧客別に表示します。
Excelで集計するか、会計・原価管理へ寄せるか
複数システムのCSVをまとめる補助としてPower Queryは有効です。配賦、仕掛、進行基準など会計判断が複雑な場合は、確定した会計ルールを専門家と確認し、Excelは計算・表示を担います。
| 方法 | 向いている場面 | 確認したい点 |
|---|---|---|
| Power Query | 売上・仕入・工数を共通コードで結合 | 期間とコードの品質を確認 |
| データモデル・ピボット | 多角的な集計 | 明細と集計の関係を保つ |
| 原価管理システム | 複雑な配賦・承認・会計連携 | Excelとの役割を限定 |
アンケート集計で確認しておきたいこと
まず主要10案件だけで、売上と直接費が同じコードで結べるか確認します。
- 顧客名・案件名だけで結ばない
- 未配賦原価を0に見せない
- 月次と累計を分ける
- 締め後修正の反映日を記録する
アンケート集計を相談するときに伝えること
売上、仕入、工数、経費の各データと、正しい粗利が分かる案件を用意します。
- 各データのCSV・Excel
- 顧客・案件マスター
- 原価に含める項目
- 配賦と計上期間のルール
- 必要な顧客別・案件別レポート
アンケート集計をプロに任せる目安
- データ元が複数システムに分かれる
- コード欠損・名称表記ゆれが多い
- 工数原価・共通費配賦がある
- 経営会議用の定例レポートまで作りたい
画面操作の自動化で確認しておきたいこと
粗利率が出ても、未配賦原価が残っていれば正しいとは限りません。
- 売上月と原価月を無条件に同じとみなす
- 名称だけで結ぶ
- 案件未設定を除外して粗利を高く見せる
- 現在単価で過去工数を再計算する
アンケート集計で確認しておきたいこと
案件コードがない経費はどうしますか？
未配賦として残し、担当者が割当または共通費処理を確定します。
月をまたぐ案件はどう見ますか？
月次と案件累計の両方を出し、計上時期の差を分けます。
粗利計算の会計ルールも決めてもらえますか？
税務・会計判断は社内責任者や会計専門家が確定し、そのルールをExcelへ実装します。