中国輸入ビジネスで売っチャイナ

EXCELで出来るネットショップの効率化、中国の習慣やどうでもいい話を記事にしています。

消費税増税時対策。EXCEL関数使って売価を一括更新する際に1,980円とか値頃感のイイ数字にしたい件

   

20150519-1

売価を決める時って原価計算して売価を決めると思いますが、
こないだの消費税が8%に増税した時、結構困りませんでした?

楽天ショップなんかでも今まで980円だった商品を単純に

980円÷1.05×1.08=1,008円

という中途半端な数字にして更新しているショップさんが非常に多かったです。

何千何万と商品がある訳ですから、商品一個一個手作業で
999円や1,980円などの値頃感のイイ数字計算しなおすなんて
苦行の他なりません。

実は関数を使って原価計算をして、なおかつ値頃感のイイ数字にする方法があるんです!

 

そもそもEXCELで原価計算はどうやる?

そもそもEXCELで原価計算をどうやるのか?
まずはここからご説明をします。

「そんなもん知っとるわ!」

と言う方は軽くスルーして、次項へお願いします。

 

例えば弊社サイトで仕入れを行っている場合を例に説明をさせて頂きます。

商品の仕入値が700円

商品重量が200g

だったとします。

 

毎回5kg以上で注文を入れるようにしていると仮定すると
送料は5kg=3,850円になります。

 

この場合のEXCELの原価計算式は

=700+3850/5000*200

になります。

EXCELで
 /(スラッシュ)は ÷
 *(アスタリスク)は ×
になります。

700 は仕入値

3850が5kgの送料なので、5kg=5000g で割って、
200gの商品なので200gを掛けると原価が出ます。

 

売価の決め方

次は粗利をどれだけ取るか及び、
楽天等のモールの手数料、消費税、送料込みにした場合等で変わってきますが、
今回は

粗利率50%
モール手数料 15%
消費税 8%
送料込み 上乗せ600円

にて計算してみます。

計算式は
=((700+3850/5000*200)+600)/(1-0.5)*1.15*1.08

になります。

(700+3850/5000*200) が原価計算

+600) が送料

/(1-0.5) が粗利率

*1.15 がモール手数料の15%

*1.08 が消費税になります。

これを計算すると、売価は 3611.736円 となります。

こうやって計算した数字をEXCELのセルの書式設定で小数点以下を
表示しないようにしてUPしているショップさんがほとんどだと思われます。

3611.736円 を関数を使って値頃感のイイ数字にしてみましょう。

 

四捨五入ならROUND関数

売価で出た数字を四捨五入してから値頃感のいい数字にしたい場合の方法を説明します。

先程計算した、3611.736円 付近の値頃感のイイ数字とすると、

3,680円

もしくは

3,699円

こんな所だと思います。

3611.736円 を四捨五入して値頃感のイイ数字にするにはROUND関数を使います。

=ROUND(3611.736,-2)

 

先程の売価の計算式と合わせるとこうなります。

=ROUND(((700+3850/5000*200)+600)/(1-0.5)*1.15*1.08,-2)

この様になります。

,-2) これは10の桁で四捨五入すると言う指示を出しています。

実際にEXCELに入力するとわかりますが、 3600 と表示され四捨五入されているのがわかります。
20150519-2

 

ちなみに

,-1) で1の桁

,-2) で10の桁

,-3) で100の桁

,1) で少数第1位

,2) で少数第2位

となります。

で、この計算式の最後に +80 もしくは +99 しておけば
値頃感のイイ数字の出来上がりです。

計算式はこうなります。(下2桁80円の場合)
=ROUND(((700+3850/5000*200)+600)/(1-0.5)*1.15*1.08,-2)+80

 

「切り上げ」もしくは「切り捨て」の時は「ROUNDUP」「ROUNDDOWN」関数で

「四捨五入では無く、切り上げや切り捨てで計算したいよ~」

そんな事もあるかと思います。

そんな時は「ROUNDUP関数」で切り上げ、「RONDDOWN関数」で切り捨てになります。

実際にやってみましょう。

 

切り上げのROUNDUP関数の場合はこうなります。20150519-3

 

 

切り捨てのROUNDDOWN関数の場合はこうなります。
20150519-4

先程のROUND関数にUPもしくはDOWNを付けるだけなので簡単です。

,-2)の指定もROUND関数と全く同じです。

 

実際にCSVで一括で売価を出してみよう

EXCELの関数と計算式がわかった所で、実際に弊社で無料提供している一括更新データを使って
売価を一括で出してみましょう。

 

まず、仕入れ価格と重量の入ったデータを準備します。

 

卸売価格の一列後ろに売価を入れたいので、G列を選択して一列挿入します。
列挿入のショートカットは 右クリック I もしくは Alt+I+C です。
20150519-6

 

 

一列増やしたら G2セル に先程の計算式をコピペします。

 

 

計算式は =ROUND(((700+3850/5000*200)+600)/(1-0.5)*1.15*1.08,-2)+80 です。

700 が仕入値
200 が重量

なので、ここを該当セルに変更します。

 

2行目の商品は
仕入値である 700 が F2セル の金額なので、700 を F2 に変更
重量である 200 が I2セル の数値なので、 200 を I2 に変更

 

入力を終えたら、G2セル から データがある一番したの行まで、
このファイルで言うと G27セル まで選択して、Ctrl+D を押します。

 

 

 

すると一番下の行まで、一気に計算式が入ります。

 

 

ROUND関数を使う事により、一括で値頃感のいい値段にする事が出来ました。

初めての方は計算があっているか、計算機で一番上の行と一番下の行の価格に
誤りが無いか確認する事をオススメ致します。

 

消費税が10%に増税になったら

ここまで説明すれば、書かなくともわかるかも知れませんが、
消費税が10%に増税になった際はどの様な計算式にすれば良いのかと言うと、

=ROUND(元の価格のセル/1.08*1.10,-2)+80

/1.08*1.10 で8%の消費税を10%に変更しています。

切り捨てや切り上げの際は、ROUND関数ROUNDDOWN関数ROUNDUP関数に変更して使って下さい。

ハセガワは今何位?


 - Excelを使ってネットショップ効率化 , , , , ,

アドセンスPC

アドセンスPC

  関連記事

20150516-1
Excelを使って数千数万商品CSVの中から、在庫商品を秒殺で抜き出す魔法の関数(INDEX関数とMATCH関数のコラボ)

大量の商品CSVのデータの中から、 商品を絞り込みたい時ってネットショップを運営 …

20130413-1
CSVを使って商品一括更新で効率アップ

皆様こんにちは。長谷川です。 北朝鮮のミサイル問題のTVでの報道もめっきり少なく …

鬼タイピング
Altで加速するスタイリッシュワーキングスタイル!!

  ネットショップをフルブーストで運営するにあなたにオススメしたいショ …

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>