如何将目标寻求转换为公式?

如何将目标寻求转换为公式?

我有以下计算利润率的公式:(收入 - 成本)/收入

我现在需要从利润率开始逆向工作。

我有一个“收入变化百分比”列,我想计算出需要将多少百分比应用于收入才能达到我选择的利润率。

我该怎么做呢?我一直在使用“目标搜索”功能,效果很好,只是它非常耗时,因为我要处理数千个项目 :(

非常感谢您的帮助

答案1

您不想进行目标求解,不仅是因为工作量太大,还因为这只是代数运算,只需输入一个公式即可直接得到答案。

假设收入和成本的当前数据在 A 列和 B 列中。当前利润率(从现在开始称为“利润率”)在 C 列中作为参考,按照您现在的方式计算。(您现在这样做在代数上并不“精确”,因为“1 - 成本/收入”更精确,但您的公式很容易看,也很容易知道发生了什么……而且您是在用 Excel,而不是代数,对吧?)然后使用 D 列输入您将输入的新目标利润率(即使只是输入一次 20% 并向下填充),您输入的公式将进入 E 列。

该公式将显示在成本保持不变的情况下,收入所需的美元(或任何货币)增长,以获得您在该行的 D 列中输入的利润率百分比增长。

从代数上讲,从Margin = 1 - Cost/Revenue等式开始。使用 Ri$e 表示达到利润目标所需的美元增长。它将是收入中增加的一定金额,因此等式变为Margin = 1 - Cost/(Revenue + Ri$e)

将最后一位移到左侧(将其添加到两侧,使其从右侧消失,因此看起来就像您刚刚移动了它),然后将利润率移到右侧。现在进行类似的交换,但进行乘法和除法,而您刚才所做的是加法/减法。将每侧乘以(收入 + Ri$e),使其从左侧消失并出现在右侧,然后同样除以(1 - 利润率),以同样的方式移动该项。您将得到:

成本/(1 - 利润)=(收入 + Ri$e)

将 Ri$e 除以收入可得出所需收入的百分比增长,现在是时候从绝对美元增长转向百分比增长了。因此,将每一项除以收入。您将得到:

成本 / {收入 * (1 - 利润)} = 收入/收入 + Ri$e/收入 = 1 + Ri$e/收入 = 1 + %增长

从每边减去 1,然后反转两边可得到:

%上涨 = 成本 / {收入 * (1 - 利润)} - 1

(请记住,“保证金”是您输入的期望保证金,而不是当前保证金。)

收入在 A1 中,成本在 B1 中,期望利润在 D1 中。将以下内容输入:

%上升 = B1 / {A1 * (1 - D1)} - 1

将其转换为 Excel 公式,您将得到以下内容并在 E1 中输入:

= B1 / ( A1 * ( 1 - D1 )) - 1

(当然,C、D 和 E 列应格式化为百分比,以便您能够合理地显示,但对于 Excel 来说,在此公式中应为小数。)

如果收入为 1,000 美元,成本为 900 美元,而期望的新利润率为 20%,则公式返回 12.5%。(1,000 美元的 12.5% 为 125 美元,因此新利润率为 1,000 美元 + 125 美元 = 1,125 美元。用 900 美元除以该数字,再从 1 中减去,即可得到我用作示例的 20% 的期望利润率)。

这就是您需要在收入中看到的百分比增长,以产生在 D 列中输入的利润率。

相关内容