## Most Probably An Embarassingly Easy Problem

For the discussion of math. Duh.

Moderators: gmalivuk, Moderators General, Prelates

### Most Probably An Embarassingly Easy Problem

I can do adding up and taking away, but when it comes to "proper maths", I'll freely admit to being useless. So of course, I was the perfect choice to be put in charge of financial reports. ¬_¬

My problem is, I need a formula to accurately calculate how much more money we need to make, than we actually made, in order to make our profit target (or, less pessimistically, the variance between what it was, and what it should've been. It can of course be BETTER than the target). Currently I do this the way my boss used to - by trial and error: editing a copy of the spreadsheet to increase the amount of revenue until the profit percentage hits the target. But this is time consuming (sometimes it takes up to thirty seconds ¬_¬) and hardly professional or scientific. I'm sure I'm overlooking something blatantly obvious, and will soon be embarrassed to learn what it is...

Here's an example using mock figures.

Sales: 5,870.32
Spend: 2,386.56
Actual %: 40.65
Target %: 30
Variance: 10.65%

Using trial and error, we're about 2,500 out (that'd make it 30.04%, which is near enough for the boss but not for me! :p). I can't seem to work out a formula that will get me a number close to 2,500. I've tried most things a man with my brainpower could reasonably be expected to try... Any help would be much appreciated.
KernowDragon

Posts: 10
Joined: Mon Jan 10, 2011 3:54 pm UTC

### Re: Most Probably An Embarassingly Easy Problem

Given two quantities, sales and spending, we want to calculate what percent of your sales is being seen as actual income.

This is given as:
Percent Income = Spending / Sales

You want to find how much more sales you need to make in order to hit a certain percent income. In other words, you want:
Target Percent = Spending / (Sales + New Sales)

Derivation:
Spoiler:
Multiply both sides by (Sales + New Sales):
(Target Percent)(Sales + New Sales) = Spending

Distribute terms:
(Target Percent) * Sales + (Target Percent) * (New Sales) = Spending

Subtract (Target Spending) * Sales from both sides:
(Target Percent) * (New Sales) = Spending - (Target Percent) * Sales

Divide by (Target Percent) on both sides:
New Sales = Spending / (Target Percent) - Sales

For spending = 2386.56, sales = 5870.32, target = 30% we have:
New Sales = Spending / (Target Percent) - Sales
New Sales = 2386.56 / 0.3 - 5870.32 = 2048.88

This becomes a MILLION times easier if you use variables:
Spoiler:
x = Spending
y = Sales
dy = New Sales
z = Percent Income
w = Target Percent

Have currently: z = x / y
Want to satisfy: w = x / (y + dy)

w = x / (y + dy)
w * (y + dy) = x
w * y + w * dy = x
w * dy = x - w * y
dy = x / w - y
New Sales = Spending / (Target Percent) - Sales
http://en.wikipedia.org/wiki/DSV_Alvin#Sinking wrote:Researchers found a cheese sandwich which exhibited no visible signs of decomposition, and was in fact eaten.
Sagekilla

Posts: 385
Joined: Fri Aug 21, 2009 1:02 am UTC
Location: Long Island, NY

### Re: Most Probably An Embarassingly Easy Problem

We can definitely set up an equation that relates sales, spending, and profit. Your 40.65% appears to actually be the percentage of revenue you spent, rather than the percentage which is profit, although these figures are complementary (so with 40.65% spent, you should have 59.35% profit).

That equation should look like this: target% = 100*spending/sales
But you want to know the level of sales you need, so we solve the equation for that, ending up with: target_sales = 100*spending/target%
That would tell you the total sales you need, so if you want to know how far off you were, just subtract how much you actually made: (100*spending/target%)-actual_sales
If you use the target percentage as a decimal (.3) rather than a percent (30) you can get rid of that 100 in the equation, leaving you with: (spending/target)-actual_sales.

So for the example numbers you posted, you need (2386.56/.3)-5870.32 = 2084.88, or almost \$2100. This doesn't match the \$2500 you came up with... maybe I've misunderstood what you're asking? Regardless, hopefully this helps point you in the right direction.
No, even in theory, you cannot build a rocket more massive than the visible universe.
Meteoric

Posts: 205
Joined: Wed Nov 23, 2011 4:43 am UTC

### Re: Most Probably An Embarassingly Easy Problem

Thanks a lot for that. Glad it wasn't a simple one. :p
Edit: That was in reply to Sagekilla.

Meteroic: That seems more inline with what I was expecting as the answer. But as you noted, doesn't seem to work for some reason. Adding 2,840.88 to the revenue gives me 35.37%

You're correct in the assumption that when I said profit I meant revenue spent. The target for that is 30%, which means 70% profit. 30% profit would see us bankrupt pretty damn quick.
KernowDragon

Posts: 10
Joined: Mon Jan 10, 2011 3:54 pm UTC

### Re: Most Probably An Embarassingly Easy Problem

Meteoric wrote:We can definitely set up an equation that relates sales, spending, and profit. Your 40.65% appears to actually be the percentage of revenue you spent, rather than the percentage which is profit, although these figures are complementary (so with 40.65% spent, you should have 59.35% profit).

That equation should look like this: target% = 100*spending/sales
But you want to know the level of sales you need, so we solve the equation for that, ending up with: target_sales = 100*spending/target%
That would tell you the total sales you need, so if you want to know how far off you were, just subtract how much you actually made: (100*spending/target%)-actual_sales
If you use the target percentage as a decimal (.3) rather than a percent (30) you can get rid of that 100 in the equation, leaving you with: (spending/target)-actual_sales.

So for the example numbers you posted, you need (2386.56/.3)-5870.32 = 2084.88, or almost \$2100. This doesn't match the \$2500 you came up with... maybe I've misunderstood what you're asking? Regardless, hopefully this helps point you in the right direction.

Your formula works fine if you don't fall into the same trap that I originally did (it's Spending/0.7, not 0.3). Thanks muchly.

Edit: Hmm, nope, that's not quite right either actually... I'll work on it, though. Just in case anyone else reads this and is willing to have a shot before I figure it out, here's a (hopefully) better overview of the situation:

I have the total net sales figure, and the total expenditure. I have a target for the percentage of the revenue we're allowed to spend - 30% (giving us a target of 70% profit). I'm trying to work out a formula to tell me the difference between the revenue we actually made, and the revenue we needed to make. Currently I approximate it by trial and error, adding arbitrary figures to the total sales amount until it hits 30%.

So... if:
x is Total Sales
y is Total Spend
z is Extra Sales Needed (the amount that Sales would have to increase by, to make Spend 30% of Sales).
p is Current Percentage
t is Target Percentage (30%)

Currently I have y/x=p. Obvious.
I then estimate z, until (x+z)/y=t

So basically I need a formula to work out what z is, without estimating it all the time.

Hopefully that makes more sense. It might even make sense to me when I wake up tomorrow...
Last edited by KernowDragon on Tue Jan 31, 2012 6:11 am UTC, edited 5 times in total.
KernowDragon

Posts: 10
Joined: Mon Jan 10, 2011 3:54 pm UTC

### Re: Most Probably An Embarassingly Easy Problem

If it's in a spreadsheet, surely you can look at the formula that the spreadsheet is using and just rearrange that thing? (Assuming of course, the spreadsheet formula is correctly giving you what you want.)

Dopefish

Posts: 753
Joined: Sun Sep 20, 2009 5:46 am UTC
Location: The Well of Wishes

### Re: Most Probably An Embarassingly Easy Problem

KernowDragon wrote:Here's an example using mock figures.

Sales: 5,870.32
Spend: 2,386.56
Actual %: 40.65
Target %: 30
Variance: 10.65%

Using trial and error, we're about 2,500 out (that'd make it 30.04%, which is near enough for the boss but not for me! :p). I can't seem to work out a formula that will get me a number close to 2,500. I've tried most things a man with my brainpower could reasonably be expected to try... Any help would be much appreciated.

It seems to me that the 2,500 figure is incorrect - have you made a typo at some point converting these to mock figures?

If revenue is 5870.32 and spend is 2386.56, then the amount spent is 100 * 2386.56/5870.32 = 40.65% of the amount raised
If revenue is 2500 higher at 8370.32, and the spend is unchanged, then the amount spent is 100 * 2386.56/8370.32 = 28.51% of the amount raised

i.e. if you were adding 2500 to the revenue and getting out an 'answer' of 30.04%, either you've made a mistake or there is something else going on in the spreadsheet (are there further costs associated with bringing in more sales revenue?).

I agree with the other posters that:
Total sales needed = (100 * total spend/target percentage)
So, extra sales needed = (100 * total spend/target percentage) - current sales
Or to use your notation, z = (100y/t)-x (note that z will go negative if you beat your target, i.e. p < t)

For the example case, z = (100 * 2386.56 / 30) - 5870.32 = 2084.88

If we work backwards to check that answer, the total sales revnue you would be getting is 5870.32 + 2084.88 = 7955.2
If revenue is 7955.2 and spend is 2386.56, then the amount spent is 100 * 2386.56/7955.2 = 30% of the amount raised
Now I am become Geoff, the destroyer of worlds

ElWanderer

Posts: 259
Joined: Mon Dec 12, 2011 5:05 pm UTC

### Re: Most Probably An Embarassingly Easy Problem

I am assuming that the reason people are coming up with different answers here is because we aren't entirely sure how your mock figures are meant to interact with each other. (Variance? variance of what? during what time period?) So I am a suggesting an answer which is simply a more 'professional' way of doing what you've already been doing.

From your original post, what I'm guessing is going on is that you have an excel spreadsheet with the numbers and formulae in it. What you're then doing is to change a cell using trial and error until your 'actual' cell is close to or equal to your 'target' cell.
Instead of using trial and error, you should use the 'solver' add on in excel. This basically makes the computer do the trial and error for you and will generate a number that will make the cells as close as possible to being equal. (assuming you got the formulae correct in the spreadsheet).

Also, don't worry, professionalism doesn't mean that you have to 'know' the answer by working it out by hand. It basically means that you need to know roughly where the answer should be and know how you're getting it so that you can replicate the method for anyone who asks.
liveboy21

Posts: 117
Joined: Thu Dec 01, 2011 9:33 am UTC

### Re: Most Probably a Embarassingly Easy Problem

Target = Target_Spend / Target_Income
Observed = Observed_Spend / Observed_Income
Actual = Actual_Spend / Actual_Income
Delta = Target-Observed (what you call Variance, but your choice of term is horrible, don't use the term Variance)

Let's assume Actual_Income is what we Observed. What does our Actual_Spend have to be in order to hit our Target?

Then:
30% = Actual_Spend / 5870.32
30% * 5870.32 = Actual_Spend
Actual_Spend = 1761.096.

Next, suppose we want to fix our Actual_Spend to be our Observed value. Then we have:
30% = 2,386.56 / Actual_Income
30% * Actual_Income = 2,386.56
Actual_Income = 2,386.56 / 30%
Actual_Income = 7955.20

If we subtract our Observed_Income, we get:
2084.88
more income on the same expenditure.

Now, if you want to answer a different question, like "how much longer do I need to run to hit a 30% expenditure target", the answer is "your numbers don't say".
One of the painful things about our time is that those who feel certainty are stupid, and those with any imagination and understanding are filled with doubt and indecision - BR

Last edited by JHVH on Fri Oct 23, 4004 BCE 6:17 pm, edited 6 times in total.

Yakk
Poster with most posts but no title.

Posts: 10212
Joined: Sat Jan 27, 2007 7:27 pm UTC
Location: E pur si muove