その会社は、小さなファブレス企業です。自社企画製品を海外の工場で作ってもらい、日本の商社などにコンテナ単位で製品を販売しています。まだまだ紙を媒体とする業務が多く、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の運用データから請求明細を組立てる方法
運送会社では、配車業務にExcelを利用するところがあります。その配車表から、請求明細をExcelで組立てる例です。
Excelで作る検索データベース
検索値と一致するものを抽出するというだけでなく、検索値を含むデータを抽出する方法です。
Excelをもっと利用したい、自分の仕事のためにExcelを活用したい、そして、もっとITリテラシーを高めたいなどのご要望があれば、業務の目的に合わせて、過去の実例を紹介したり、プロモート致します。いつでもご連絡下さい。日本国内外、クラウド上で後押しします。
