その会社は、小さなファブレス企業です。自社企画製品を海外の工場で作ってもらい、日本の商社などにコンテナ単位で製品を販売しています。まだまだ紙を媒体とする業務が多く、SUM関数以外の関数を使ったことがなく、いろいろと手間がかかっています。Excelを活用することで、もっと効率よく原価表などを作れるようにし、一覧表作成だけでなく、今後の行動などを考えるための道具としてExcelを利用できるようにします。
Excelで輸入品原価を自動計算しデータ化
まずは、仕入原価に相当する輸入品原価データを作るしくみから作り始めることにします。
冒頭の画像にあるように、青字の部分に値を入力すると、黒字の部分に計算結果が表示されるようにします。上段の「為替レート」は当社の想定する為替レートです。「コンテナサイズ」、「海上運賃」などの項目は、取引中の日本国内フォワーダーの現在の条件を入力します。また、海外工場から見積もりを得て、下段にある「製品」、コンテナサイズごとの「条件」に従った単価とコンテナ積載数を入力します。それらの入力だけで、想定為替レートによる日本円建ての製品単価が自動計算されて表示されるようにします。
Excel数式と関数の内容
製品金額/コンテナ
「製品金額/コンテナ」は、「製品金額/個」に「個数/コンテナ」を掛けた値になるので、画像例「G11」セルには「=E11*F11」という式が入力されています。項目名にある「AｘＢ」は項目名の上のアルファベットを示しています。見る人が式の内容をすぐに分かるように表示しています。
海上運賃/コンテナ
貿易条件が「CFR」の場合、売手の見積金額に海上運賃も含まれるので海上運賃の欄には何も表示されません。条件が「FOB」の場合だけ、上部に入力された海上運賃を参照して、コンテナサイズに従って該当の値が転記されるようにします。
式：=IF($C11=”FOB”,INDEX($G$4:$K$5,MATCH($D11,$G$4:$G$5,0),2),””)
- IF関数
- =IF($C11=”FOB”,〇〇〇,””)
・・・もし「C11」セルの値がFOBという文字であれば、〇〇〇を計算し、そうでなければ、””（空白）にする・・・という意味
- =IF($C11=”FOB”,〇〇〇,””)
- INDEX関数
- INDEX($G$4:$K$5,◇◇◇,2)
G4からK5の範囲で、2列目（「海上運賃」項目）の、◇◇◇と一致する値を表示する・・・という意味
- INDEX($G$4:$K$5,◇◇◇,2)
- MATCH関数
- MATCH($D11,$G$4:$G$5,0)
G4からG5の範囲で、D11（コンテナサイズ）と完全に一致する値が何行目にあるか表示する・・・という意味
- MATCH($D11,$G$4:$G$5,0)
保険料/コンテナ
INDEX関数とMATCH関数については上記「海上運賃/コンテナ」同様です。INDEX関数の第3引数を「3」としているので、3列目の値を抜き出すという点だけが異なります。
式：=INDEX($G$4:$K$5,MATCH($D11,$G$4:$G$5,0),3)*SUM($G11:$H11)
輸入諸経費/コンテナ
上記「保険料/コンテナ」と同様です。INDEX関数の第3引数が「4」となっています。
式：=INDEX($G$4:$K$5,MATCH($D11,$G$4:$G$5,0),4)
国内運賃/コンテナ
上記「輸入諸経費/コンテナ」と同様です。INDEX関数の第3引数が「5」となっています。
式：=INDEX($G$4:$K$5,MATCH($D11,$G$4:$G$5,0),5)
原価/コンテナ
項目欄に「原価/コンテナ （C+D+E+F+G）」とあるように、「製品金額」、「海上運賃」、「保険料」、「輸入諸費用」、「国内運賃」の総計を日本円で表示しています。
式：=SUM($G11:$I11)*$A$4+SUM($J11:$K11)
米ドル建てになっている「製品金額」、「海上運賃」、「保険料」の合計金額に、「為替レート」($A$4)を掛けて日本円建てにし、それらと、日本円建て表示の「輸入諸費用」、「国内運賃」を加えて原価合計を表示するようにしています。
原価/個
上記「原価/コンテナ」を「個数/コンテナ」で割って、製品一個当たりの原価を計算表示します。
以上で出来上がった数式を各行にコピーペーストすれば、各工場から提示される見積金額（米ドル建て）を、諸経費を含めて製品ごとに日本円でいくらになるかの一覧をすぐに参照できるようになります。
Excel輸入品原価データの利用
フィルターで必要な項目だけ表示
原価表下段の表項目「工場」をカレントセルとして、ホームタブにある「並べ替えとフィルター」＞「フィルター」をクリックすると、下段の項目名それぞれに下矢印ボックスが現れ、フィルターをかけられるようになります。
例えば、項目名「製品」の下矢印ボックスをクリックし、「Alfa」だけにチェックマークをいれてOKすると・・
フィルターによって、製品Alfaだけの表示となります。
続けて、項目名「条件」で、「CFR」だけにチェックマークを入れてOKすると・・
フィルターで、製品Alfa、かつ、CFR条件だけの一覧を表示することができます。
閲覧するだけでなく、表示された内容を別のワークシートに値をコピーペーストするなどして、必要とされる比較表などを作成することができます。
為替レート変動をシミュレーション
作例では、為替レートを130円として制作したのですが、為替レートの値を変えれば、変動した場合の原価計算値をすぐに参照できます。
レート130円の場合・・
レート132円の場合・・
必要な値だけを、他のシートにコピペして、レート変動に伴う原価の変化を一覧にして比較対照することができます。同様にして、海上運賃の変動などによる原価比較を参照できます。
小企業のExcel活用
以上、小企業のExcel活用の輸入品原価データの例でした。私自身が、ファブレス企業に勤務した際にも、説明したようなしくみをExcelで制作して、海外輸入仕入の管理や、営業計画作成と商談準備などを助けるしくみとしていました。私は中小企業の半生ですが、企業の規模に関わらず、個人のExcelリテラシーを広げることで、今の業務を、分かりやすく、より効率的にすることができますし、単に効率的にするというだけでなく、作成したExcelのデータをシミュレートするなどで、今後の行動や姿を数値として確認して考える道具を用意することができるというのは、誰にとっても望ましい姿になってくれることと思います。
Excelをもっと利用したい、Excelでこんなことができるか、そして、もっとITリテラシーを高めたいなどのご要望があれば、クラウド上でプロモート致します。いつでもご連絡下さい。
