売上管理テンプレート+分析
売上管理の基礎
売上管理とは何をしたら良いのでしょう？まず、会社の年間業績を損益計算書にまとめます。その中で収益、費用、利益の3つの要素を記載します。
収益ー費用＝利益
この金額が分かるようにエクセルで管理を行えばよいのです。
では一般的にどのような項目が管理されているか見ていきましょう。
管理に必要な項目例一覧
| No | 取引を行った際に重複しない番号を振ります。 |
| 販売日 | 取引を行った日付です。 |
| 取引先名 | 取引を行った相手です。 |
| 商品番号 | 取引した商品の固有番号です。バーコードの番号のように商品ごとに重複しない固有の番号を振ります。 |
| 商品名 | 商品番号に対応した商品名です。 |
| 販売価格 | 商品の価格です。割引を行う場合もありますので、商品ごとに一定ではない場合もあります。 |
| 個数 | 商品を何個売ったかの個数です。 |
| 原価 | 商品の仕入れ値です。 |
| 売上 | 商品の販売価格×個数です。 |
| 粗利 | 売上ー原価の値です。 |
| その他 | 消費税など、管理に必要な場合は追加します。 |
Noや商品番号は必ず重複しない固有の番号を使用します。のちに集計を正しく行うために必要です。
売上管理の土台が整ったら、KPIの設定も忘れずに行いましょう。KPIの設定については「データドリブンな店舗運営を実現する実践メソッド」もございます。合わせてご覧ください。
マスターデータを作成する
同じ相手に同じものを売るときなど、商品名や取引先は何度も同じものを使用します。その都度手入力を行うのは無駄も多く、入力者によって全角・半角・略称などの違いが出てしまいます。
そのため、マスターを用意し、マスターに記載された項目を選択するというような手法を取ります。
例にある項目程度でしたらマスターは2つになります。
取引先マスター
取引先番号、取引先名、郵便番号、住所、電話番号など
項目を入力したらテーブルの形式にしておきます。
「先頭行をテーブルの見出しとして使用する」というチェック項目があるので、こちらをチェックしてテーブルを作成します。
テーブルを作成するとリボンに「テーブルデザイン」が表示されるようになります。「テーブル3」のようなデフォルト名ではどのテーブルなのか分かりにくくなりがちなので、テーブル名を変更しておきましょう。
商品マスター
商品番号、商品名、原価、販売価格など
仕入れたものを販売する場合には仕入れ先名、仕入れ先住所なども追加します。
取引先マスターと同様にテーブルにし、テーブル名を変更しておきます。
売上管理シート
それでは取引先マスターと商品マスターを使用して売上管理シートを作成しましょう。
それぞれの項目をセットしていきます。
取引先番号や商品番号のような重複しない番号は取引先マスターや商品マスターから値を取得し、リスト選択が出来るように設定します。
「入力規則」を選択します。
Excelを使った売上管理シート作成方法
「入力値の種類」で「リスト」を選択し、「元の値」に取引先マスターの取引先番号など、使用したい範囲を選択します。実際にエクセルで売上管理シートを作成する場合には知っておいた方が集計が早くなるような便利な関数がたくさんあります。
先ほど作成した売上管理シートから集計をしてみましょう。
便利な機能：SUMIF
SUMIFは条件に合った値のみ集計出来る関数です。
取引先ごとの粗利や顧客ごとの売上高など、一部の集計を行いたいときに使用します。
=SUMIF（範囲、検索条件、合計範囲）
- 範囲には、条件になる範囲を選択します。
- 検索条件は、範囲で指定した範囲の中でどの値になればよいかの値を設定します。
- 合計範囲は、金額などを合計する範囲を選択します。
便利な機能：VLookup
VLookupは表の中から指定した値を取り出してくる関数です。しかし、文章だけでは分かりにくいですね。
売上管理シートで取引先番号を取引先マスターからリストで選択できるようにしましたね。
VLookupでは取引先番号の横に取引先名を入れるという使い方ができます。
=VLookup（検索値、範囲、列番号、検索の型）
- 検索値には、取引先番号を設定します。
- 範囲には、取引先マスター（テーブル）を指定します。テーブル名を事前に設定しておけばそのテーブルを指定するだけです。
- 列番号には、範囲（取引先マスター）の中で何列目か、です。
- 検索の型には、２種類あります。
- 「近似一致」のTRUE（1）
- 「完全一致」のFALSE（0）
今回は取引先番号と完全に一致する値が欲しいのでFALSEにします。
これで取引先番号を選択するだけで、自動的に取引先名を表示してくれるようになります。入力の手間や入力ミスを防ぐことができます。
便利な機能：ピボットテーブル
SUMIFの解説の時に、取引先ごとの利益を計算しました。
作成する際に取引先番号を取引先マスターからコピーペーストしたり、取引先名をVLookupでもう一度取得したりしています。
集計するたびにコピーペーストして表を作り直して、とするのは手間がかかります。そんな時にピボットテーブルが活躍します。
まずは画像をご覧ください。
慣れたらこんな表が30秒で出来てしまいます。
データの元になる表にカーソルがある状態で「挿入」→「ピボットテーブル」を選択します。
テーブルの範囲も自動で選択してくれます。ほとんど手を付けることはありません。このまま「OK」を押します。
右側に「ピボットテーブルのフィールド」が表示されます。ここを設定していくと左側に表が自動で作成されます。
先ほど作成していたピボットテーブルはこのように設定しています。
- 列に販売日
- 行に商品名
- 値に売上
上のフィールドから下の列などにドラッグで移動させます。間違えた場合やフィールドを変更したい場合は下から上のフィールドにドラッグして戻します。
今度は取引先ごとに集計したピボットテーブルを作成しました。商品ごとでは列にあった販売日を行にしてみると、また違った印象になります。日付ごとの集計を行にすると日付ごとでも月ごとでも見ることができます。
ピボットテーブルのメリットは自動で集計表を作成してくれることですが、全角・半角、略称などの違いも全て別の項目として扱われるデメリットがあります。このデメリットを発生させないためにも、マスターテーブルを作成しておくことは不可欠になります。
EXCELは、以下のサイトで配布されています。
EXCELダウンロード：/sample/sales.xlsx
