エクセルの関数について、難しい利益の計算も含みます。

エクセルのA列に、販売価格を設定するとします。
B列に卸値、C列に送料、利益を４００円に近くするとします。
手数料が14％と４０円とします。卸値には、消費税が加算されるとします。
なので、A＝（B*1.08+C)*1.14+400となり。販売価格が設定されます。
例えば、卸値を５００円、送料を３００円とします。
A=(500*1.08+300)*1.14+400+40
A=1357
利益を４００円にするために、販売価格の設定が１３５７円という答えが出ます。
しかし、実際は、この販売価格１３５７円に14％の手数料がさらにかかります。
なので、販売価格*手数料-卸値＝４００としたいです。
なので、実際の最初の４００円の利益は可変的なもので、上がっても、または利益を取りすぎないために、下がってもよいとします。

今回の例で、実際にいくらの利益が出るかというと　1357*(1-0.14)-卸値500*1.08＝1167-540＝540
最初の例では、５４０円の利益が出ています。
ですが、１４０円多いので、１４０円マイナスしたいです。
例えば、卸値が、１００００円、送料が７００円だとします。
すると、(10000*1.08+700)*1.14+400+40＝１３３５０円
１３３５０円という販売価格が出ます。
この価格に手数料を加算し卸値と送料を引くと
13350*0.86-700－10800＝-19円になります。
ということは３８１円の赤字になります。
このプラスになったり、マイナスになるものを限りなく４００に近づけたいのですが、
どのような計算式が成り立ちますでしょうか。

エクセルの卸値は、ランダムに１００円から２００００円の金額が１００００行記載されているとします。送料は、５００円から８００円とします。
この１００円から３０００円の金額体は、４００円の利益、３０００円から６０００円は、６００円の利益、６０００円より上は７００円の利益設定のようにして、限りなく４００円の利益に近づけたいのですが、どのような関数が成り立ちますでしょうか。

No.1 回答者： EFA15EL 回答日時： 2016/11/25 13:34

なるほど、仕入れの最初の手数料は仕入れに係るものだというわけですね。

そこをごっちゃにするから計算を間違うのでしょう。

再度項目を整理します。
理想販売価格=A
仕入れ価格（税抜）=B
仕入れ送料=C
仕入れ手数料率=D（14%）
仕入れ手数料（固定）=40
目標利益=E
販売手数料率=F（14%）
販売手数料（固定）=40

理想販売価格=仕入れコスト+目標利益＋販売手数料（料率＋固定）
ですから、

仕入れコスト=(B*1.08+C)*(100%+D)+40
目標利益=E
販売手数料=A*F+40

つまり、

A=(B*1.08+C)*(100%+D)+40+E+A*F+40
A*(1-F)=(B*1.08+C)*(100%+D)+E+80
A=((B*1.08+C)*(100%+D)+E+80)/(1-F)

となります。

この回答へのお礼

ありがとうございます。Eを求めるには、どうすればよろしいでしょうか。

お礼日時：2016/11/25 15:20

No.2 回答者： GooUserラック 回答日時： 2016/11/25 13:44

ゴチャゴチャしていますので合っているか確認です。

「販売価格=(卸値*消費税+送料+利益)*(1+手数料)」という事ですよね？
これを変形して代入になると思います。いかがでしょうか？

この回答へのお礼

ご回答ありがとうございます。これに、代入して出た販売価格で販売しても、代入して出た販売価格に、１４パーセントの手数料が引かれるということです。ですので、利益を可変的にしないと、求められないので、どのようにすればよいか困っています。

お礼日時：2016/11/25 15:24

No.3 回答者： 銀鱗 回答日時： 2016/11/25 14:32

送料と手数料は別な気がするんだけど、送料に手数料を上乗せしていいのかなあ。

さらに14％の手数料が二重取りになっているのはどうしたものか…。

と言うように、当初の質問のように
対象がebayであることや、取引額の3.9% + 40円がPaypal支払手数料という事についても記述しないと、意味が分からない質問になってしまいますよ。
（実際、質問文では手数料が14％の二重取りになっているような記述になっています）

・・・
そんなわけで自分は「ゴールシーク」を前回の質問で答えたのですが、伝わりませんでしたか。
…しっかり考えていらっしゃると思ったので伝わると信じていたのですが…

ちなみにebayでは出品手数料と落札手数料は別に計算されるので、質問の計算では正しい結果にならないことがあります。
（14％の手数料は、出品手数料6％、落札手数料８％とするべきと思います）

この回答へのお礼

出品手数料は、特に考えないとします。ゴールシーク関数が良いことがわかったのですが、設定と使い方がわかりません。

お礼日時：2016/11/25 15:13

No.4 ベストアンサー 回答者： 銀鱗 回答日時： 2016/11/25 15:33

ゴールシークの使い方が分からないのですか…。

A1セルに落札価格、B1セルに原価×消費税、C1セルに落札時の利益、D1セルに手数料の利率が入力されているとします。
E1セルに利益を計算する式が入力されているとします。
　＝A1-(B1+C1)*D1
など。

E1セルに対してゴールシークを行います。
「データ」→「What IF分析」→「ゴールシーク」
を選択し、
　「数式入力セル」に、最終的な数式の入っているセルであるE1セルを指定。
　「目標値」に400円の利益を得たいのであれば、400を入力。
　「変化させるセル」には予定している落札価格が入力されているセルを指定。（C1セルなど）
すると、C1セルの値を変化させて計算を繰り返し行い、E1セルの【計算結果】が400に一番近くなる値をC1セルに返してくれます。

もうホント、こんだけ。
凄く簡単。

・・・
　A2セルに　4
　B2セルは　5
　C2セルに　＝A2+B2
として、ゴールシークで
　「数式入力セル」に C2
　「目標値」に10
　「変化させるセル」にB2
を指定すると、
5と入力されていたB2セルの値が6に変わるんです。
なお、ゴールシークを使う時、B2セルが空欄でも問題ありません。

この回答へのお礼

一つのセルに対して答えを出せたのですが、２５００行あるものをコピペしてもきちんとした答えが出せず、ゴールシーク関数は、複数のセルには適用できませんでした。

お礼日時：2016/11/25 16:49

No.5 回答者： GooUserラック 回答日時： 2016/11/25 15:42

他の方の回答を見ると以下の式で計算可能だと思います。

手数料を可変する必要はありません。
販売価格=((卸値*消費税+送料)*(1+手数料①)+40+利益)*(1+手数料②)
=((500*1.08+300)*(1+0.14)+40+400)*(1+0.14)
=(840*1.14+40+400)*1.14
=(957.6+40+400)*1.14
=1397.6*1.14
=1593.264
≒1593