こんにちは!扶養内作家おはぎです。
みなさん仕入れた在庫の単価管理どうしていますか?
ハンドメイド作家を仕事とする上で材料の仕入は必ずありますが、表を作ってまで管理する必要があるのか疑問ですよね。
こちらの記事でも紹介した通り、12月の一大イベント棚卸では仕入れた材料の単価を使用し、仕訳帳へ棚卸額を計上します。
棚卸後に慌ててレシートを出してきて単価を計算…なんて方も多いのではないでしょうか。
今回はエクセルでの在庫管理表の作り方を紹介します!
棚卸表としても活用できますし、1度作ってしまえば翌年からは少しの加筆・手直しですむので、ぜひ一緒に作って見てくださいね。
この記事では、Excelでの在庫管理表の作り方をメインに、
在庫管理表の必要性と棚卸表にもなる実際の使い方を紹介します!
在庫管理表をつくる目的
ハンドメイド作家でいう所の在庫管理表とは、材料や完成品の仕入時期・種類・単価などを管理できる表の事です。仕入帳のようなものですね^^
作り方解説の前にそもそもなんで在庫管理表を作るのか、その目的やメリットについてご説明しようかと思います!
そんなことは分かっているという方はへ^^
棚卸に使用する単価を把握する為
在庫管理表を作る一番の目的は、棚卸の金額計算に使用する単価を簡単に確認できるようにする為です。
↑こちらの記事でも紹介している通り、棚卸では最終仕入原価法という方法で金額を計算します。
この方法を使用するためには1年で1番最後に仕入れた時の単価を材料ごとに把握しておかなければなりません。
棚卸の時に慌ててレシートや納品書を取り出して単価を計算する方も多いのではないでしょうか。
のちほど紹介するエクセルでの在庫管理表では、材料名・規格・仕入時期・単価などを入力できるように作り、一番最後に仕入れた際の単価が一目でわかるようになります!
1年間の材料の仕入れ数を把握する為
仕入ってみなさん計画的にできていますか?
やばい!材料が切れそう…急いで発注しないと!
おはぎも個人事業主になってすぐは、行き当たりばったりの仕入を行っていました。
在庫管理表を使うようになり、1年間でどのくらいの頻度で仕入れを行っているのか材料ごとに把握できるようになってから、翌年の仕入数も前年を参考に調整できるようになりました。
また単価を仕入時期に分け管理することで、意外と材料の価格も仕入時期により異なることに気づけました。
輸入材料を取り扱う方ですと、円安円高で単価がかなり変わってきますよね。
昔のままの価格設定だと、今の原価に対して低すぎることもしばしば。
在庫管理表を使用することで仕入数・価格設定など見直すよい機会となります。
棚卸表にもなるExcel在庫管理表の作り方
エクセルを新規作成
必要項目(商品名・規格・単価など)を入力
2行目に項目を入力していきましょう。項目例を重要度つきで紹介します!
自身の運営に必要のない項目は表が完成後削除して使ってくださいね。
①材料名(重要度:★★★)
②色・柄(重要度:★★☆)
基本的には色柄異なるのであれば分けて管理しましょう。
③購入先・④棚番(重要度:★☆☆)
購入先や収納場所となる棚番を管理したい方は作成してください。
おはぎは購入先は管理していますが、収納場所はそんなに分かれていないので棚番列は作っていません。
⑤規格(重要度:★★★)
必須の項目です。その材料の単価を割り出すときにg(重さ)で計算しているのか、cm(長さ)なのか、個(個数)なのかを入力しておきます。規格を間違えて計算すると単価は大きく変わりますので、注意しましょう。
⑥期首の数・単価(重要度:★★★)
期首=前年の12月に行った棚卸時の数・単価の列です。
え?これ必要?と思う方も多いかと思いますが、とっても重要な項目となります!
期末で最終仕入単価を出す際に、昨年の仕入分の在庫はあるが今年新たに仕入は行わなかった材料は、前年の棚卸時の単価を使用します。数・単価の並びにも意味がありますので、この並びで入力しましょう。
今期のシートを作成する際に前年の期末棚卸数と最終仕入単価をコピーペーストしてつかいます。
1行目に期首などと入力しておくと分かりやすいです
⑦1月の数・金額・単価(重要度:★★★)
2月~12月は関数を入力後コピペするのでひとまず、1月分をつくっておきます。
仕入があれば該当月の該当材料のセルに金額と数を入力し、単価を計算します。1~12月すべて分けなくてもいいのですが、仕訳票の仕入金額と合致させてチェックしたいので、12ヶ月で分けております。
⑧仕入数合計(重要度:★★☆)
1年間でその材料に対してどのくらいの数を仕入したのか確認できる列です。隣に棚卸数を入力することになりますので、棚卸後仕入した数に対してどのくらい材料が残ったのか確認できます。翌年の仕入の基準にもなりますので作っておくことをおすすめしますが、簡潔な表にしたい方や、必要ない方は削除してもいい項目です。
⑨棚卸数(重要度:★★★)
棚卸時に使用する列です。棚卸した数を入力していきます。
⑩最終仕入単価(重要度:★★★)
最後に仕入した際の単価が表示される列です。関数を入力し、⑦の月々の仕入をしっかりと入力していれば自動で最終仕入時の単価が表示されるようになります。
⑪期末棚卸金額(重要度:★★★)
⑥と同様に1行目にはわかりやすいよう期末などと入力しておきましょう。⑨で入力した棚卸数×⑩最終仕入単価の金額を関数で入力する列です。この棚卸金額の総額を決算の棚卸資産の仕訳に使用することとなります。
⑫期棚卸金額(重要度:★★☆)
⑥と同様に1行目にはわかりやすいよう期首などと入力しておきましょう。⑥で入力した期首棚卸数×単価の金額を関数で入力する列です。この棚卸金額の総額を決算の棚卸資産の仕訳に使用することとなります。
合計を計算するための行を用意する
自身の材料数に合わせて、余裕のある行数を確保したうえで、下の行に合計行をつくる。
おはぎの場合だいたい材料100種類は必須なので200行下に合計の行をつくります。
もちろん材料が増えるたびに行挿入で足していってもいいのですが、関数が壊れたり、入力漏れがあったりすると後々面倒くさいので、ある程度材料が増えても大丈夫なように作っておいた方が安心です。
ウィンドウ枠の固定を行う
作業をしやすいようにウィンドウ枠の固定をしておきましょう。
H3セル(1月数の3行目のセル)を選択し、表示タブのウィンドウ枠の固定をクリック。
これで下のセルを入力していても1,2行目の項目がずっと表示されるようになります。また右側のセルを入力する際もA~Hの列が表示されたままとなります。
期首・期末棚卸額の関数、1月金額の関数を入力する
期首・期末棚卸額の関数を入力
期首期末のそれぞれ棚卸額の下3行目に=数*単価を入力する。期末の数=棚卸数であり、単価は最終仕入単価を使用します。
期末のこの関数は文字通りifエラー=もしエラーが出たら””で囲った値を表示してねという関数で、数や単価の入力が無いセルがあってもエラーが表示されず空欄のまま表示されます。
下の合計行まで関数をコピーします。関数を入力した単価3行目セルの右下+にカーソルを合わせてダブルクリックすると一番下に作成した合計列までコピーできます。
1月単価の関数を入力
I3/H3というのが金額÷数です。
入力出来たら合計行までコピーします。
書式設定を行う
単価や金額などの書式設定を行います。コピー前に設定しておくと楽なのでこのタイミングで行いましょう!
設定したいセル・列などを選択しキーボード「Ctrl+1」を押すとセルの書式設定がでてきます。
右クリックから書式設定を開いていた方も多いかと思いますが便利かつ簡単なショートカットキーなのでぜひこの機会に覚えておきましょう!
「Ctrl+1」で選択したセルの書式設定
棚卸額・金額
- 「Ctrl」キーを押しながら棚卸額・金額の列を選択し、「Ctrl+1」でセルの書式設定を開く
- 表示形式 → 分類:会計を選ぶ
- 記号:¥を選ぶ
お金についての列ですので、会計を選択します。
他にも 通貨 などでもいいのですが、¥0の表示が会計の方が見やすいので、会計をおすすめしています。
もちろん通貨でも問題ないのでお好みでお選びください。
単価
- 「Ctrl」キーを押しながら単価・最終仕入単価の列を選択し、「Ctrl+1」でセルの書式設定を開く
- 表示形式の分類:ユーザー定義を選択
- 種類に”@”0.00を入力(↓下で解説)
単価表記はお好みで設定していただいて大丈夫です。おはぎは単価と一目でわかるように数字の前に「@」をつけています。この数字の前に@を表示させるためにユーザー定義という項目から設定をしていますので、@必要ない方は数値に設定しましょう。
また、小数点をどこまで管理するかによっても変わってきます。小数第一位まででいいという方や少数いらない方は「”@”0.0」「”@”0」と入力してください。
セルの幅や文字の大きさなど調整する
次回より列のコピーを行いたいので、ひとまずこのタイミングでセルの幅や文字の大きさ・配置など見た目部分の調整を行います。これに関してはしなくてもいい部分であり、好みになります。
参考までにおはぎが調整したことを紹介しますね。
- セル幅を整える
- セルの結合や文字を選択範囲内で中央に設定
- 見やすくするために行の背景色をしましまになるようにする(条件付き書式)
1月(数・単価・金額)を12ヶ月分コピーする
- 数・単価・金額の列を選択する
- 隣の列を選択し、右クリック
- コピーしたいセルの挿入し、12月分まで列をつくる
- 1行目「1」の数字を「12」まで変更していく
最終仕入単価・仕入数合計の関数を入力する
最終仕入単価
最終仕入単価列の3行目(AT3のセル)に関数を入力します。
IFERRORは上でも使ったエラーが出た時に””内の値を表示するもの
LOOKUPが最後に表示されている値をAT3セルに表示させるという関数です。期首単価~12月単価までを範囲指定します。
これも一番下までコピーしておきます。
仕入数合計
1月~12月の数をすべて足す計算式を入れ、下までコピーします。
グループ化する
2か所グループ化を行います。
グループ化することにより「-」「+」ボタンでグループ化した列をまとめて折りたたむことができるようになります。
棚卸表として活用したいときには折りたたむなど、活用できますので設定しておきましょう。
1月~12月
- 1月~12月の列を選択する
- グループ化をクリック
期首~仕入数合計
棚卸表として活用する際にF列(期首数)~AR列(仕入数合計)は不要になるのでグループ化し、非表示にできるようにしておきます。
合計行を整える
1~10ステップで一通り表が完成しました!最後に合計行にSUM関数を入れていきます。
期末棚卸高と期首棚卸高は必須です。合計行を選択し、オートSUMをクリックするとAU列3行目~199行目の合計関数が入力できます。
他にもおはぎは1~12月の金額列のみSUM関数で合計を出していますが、これは無くても良いので使いやすいように作成してください。
表の完成!罫線などで見やすくしよう。
最後に表に罫線を入れて見やすくしましょう!
不要な項目(棚番など)は削除します。
入力例
青い枠で囲ったところが自分で入力して使用するセルです!
- 基本情報:材料名・規格など
- 期首 数・単価:シート作成時に前期棚卸表の棚卸数・最終仕入単価よりコピペしておく
- 1月~12月:仕入時に数と金額を入力(送料含と規格に注意)
- 棚卸数:棚卸で数えた数を入力
しっかりと青枠以外の関数が作動しているか確認して完成です!
お疲れ様でした!!これで来年からは棚卸もらくらくですよ♪
棚卸表しての使い方
完成した表は棚卸時に2パターンの使い方があります。
- 印刷しない・表に数を直接入力して使う
- 印刷する・紙に数を手書きしてから表へ入力する
タブレットなどで表に直接入力できると一番いいのですが、紙に印刷していろいろメモしながら棚卸したい人も多いのではないかと思いますので紙に印刷する方法についてご説明します!
期首数~仕入数を非表示にする
グループ化した期首数~仕入数合計を「-」ボタンで非表示にします。
印刷範囲の指定
- A~AS(材料名~棚卸数)の棚卸に必要な材料セルを選択する
- ページレイアウト
- 印刷範囲の設定をクリック
ページ設定をする
ページレイアウトタブの印刷タイトルの下にあるななめ矢印をクリックで印刷に関するページ設定ができます。自分で管理しやすいように設定してくださいね^^
おはぎが設定しているのはこのあたり↓
- 余白左側をファイリング用に広めに設定
- ページ中央水平のみチェック
- ヘッダーフッダーの設定
- タイトル行(1・2行目)の設定
印刷
まとめ
Excelで作る在庫管理表の作り方と棚卸表としての活用方法を説明しました!
Excel操作や関数が苦手な方でも関数コピペで作れるようにしたので、まずはアレンジをせずそのまま作ってみてくださいね^^
完成後にそれぞれの運営に合わせた仕様にアレンジしてお使いください