I have the following formula for working out the profit margin: (Revenue - Cost) / Revenue
I am now required to work backwards starting from Profit Margin.
I have a column for Revenue change % where I'd like to work out what percentage is required to be applied to Revenue in order to arrive at Profit Margin of my choosing.
How would I go about doing this? I have been using the Goal Seek function, which works perfectly, except that it's very time consuming as I'm working with thousands of items :(
Your help would be greatly appreciated
Answer
You do not want Goal Seek not only due to the work, but because this is just algebra resulting in a formula to enter that will simply give you the answer directly.
Say the current data of Revenue and Cost are in columns A and B. Current profit Margin ("Margin" from here on out) is in column C as a reference, calculated as you do now. (As you do it now is not "precise" algebraicly, as "1 - Cost/Revenue" is more so, BUT you formula is easy to look at and tell what is going on... and you are Excel-ing, not Algebra-ing, right?) Use column D then for a new target Margin that you will enter (even if it just to type 20% once and Fill down) and the formula you enter will go in column E.
That formula will show the dollar (or whatever currency) rise required in the Revenue, with Cost remaining constant, for the percentage rise in Margin you entered in column D for that row.
Algebraicly, start with the Margin = 1 - Cost/Revenue equation. Use Ri$e for the dollar rise needed to meet the Margin target. It will be some amount added to Revenue so the equation becomes Margin = 1 - Cost/(Revenue + Ri$e).
Move the last bit to the left side (add it to both causing it to disappear from the right so it looks like you just moved it), and then move Margin to the right side. Now do a similar swap, but multiplying and dividing where what you just did was adding/subtracting. Multiply each side by (Revenue + Ri$e) so it disappears from the left side and appears on the right side and similarly divide by (1 - Margin) to similarly move that term. You get this:
Cost/(1 - Margin) = (Revenue + Ri$e)
Dividing Ri$e by Revenue gives the % increase in Revenue needed and now is the time to shift from the absolute dollar rise to the % Rise. So divide each term by Revenue. You get:
Cost / {Revenue * (1 - Margin)} = Revenue/Revenue + Ri$e/Revenue = 1 + Ri$e/Revenue = 1 + %Rise
Subtract the 1 from each side and reverse the sides to get:
%Rise = Cost / {Revenue * (1 - Margin)} - 1
(Remember, "Margin" is the desired margin you entered, not the current margin.)
Revenue is in A1, Cost is in B1, desired Margin is in D1. Put those things in:
%Rise = B1 / {A1 * (1 - D1)} - 1
Turning that into an Excel formula, you get the following to enter in E1:
= B1 / ( A1 * ( 1 - D1 )) - 1
(Of course, column's C, D, and E should be formatted as percentages to display sensibly to you but be decimals to Excel for use in this formula.)
If Revenue was $1,000, Cost was $900, and the desired new Margin is 20%, you find the formula returns 12.5%. (12.5% of $1,000 is $125 dollars so the new margin is $1,000 + $125 = $1,125. Divide $900 by that and subtract it from 1 to see the desired Margin of 20% I used as the example).
That is the percentage rise you need to see in Revenue to produce the Margin entered in column D.
No comments:
Post a Comment