How to find a ratio with a negative number

[cliff.clayman]

I am trying to find a ratio in which one of the numbers is negative. My example is as follows: My revenue is up 40% and my Expenses are down -6.5%. What is that ratio? I cannot figure out how to do this in Excel.

[ben_hensel]

... you mean *(1- 6.5%) or something like that?

[Richard Buttrey]

A ratio is another way of expressing a % but it's not clear what statistic you are trying to demonstrate.

Here's a small example of several resulting %s based on your % changes to revenue and expenses.
What are you wanting to show?

Capture.JPG

[cliff.clayman]

I think I want Change in Margin as % of Margin, but I'd like to see all the calculations please.

[Richard Buttrey]

Hi

See attached

[shg]

I think the meaningful interpretation is that productivity (revenue per expense dollar) is up about 50%:

=(1 + 40%) / (1 - 6.5%) - 1 ~ 49.7%

[cliff.clayman]

I am looking to obtain a ratio, not a percentage.

[ben_hensel]

OK!

What is the actual math you're trying to accomplish? Don't worry about excel syntax, just give us the equation you need to calculate.

[cliff.clayman]

Okay, here are some numbers to use for the calculation. Revenue FY17 - $10,000. Revenue FY18 - $14,000. Expenses FY17 - $1000. Expenses FY18 $930. I want to see the change in Revenue to Expenses expressed as a ratio Year over Year.

[ben_hensel]

No, I'm not asking for "what numbers to use in the calculation", I'm asking what is the calculation itself. Give us that equation, and we can translate it into Excel syntax for you.

[cliff.clayman]

(FY18 Revenue / FY17 Revenue-1)*100 = x
(FY18 Expenses / FY17 Expenses-1)*100 = y
x/y

[ben_hensel]

Then it's plugging references into the cells.

So for example:
A1 = FY17 Revenue
A2 = FY18 Revenue

B1 = FY17 Expenses
B2 = FY18 Expenses

Then your formula is:

Please Login or Register  to view this content.

(The *100 in the numerator/denominator comes out in the wash).

Now this delivers something crazy like -571.4% (or -5.71, or whatever, based on what you've got the cell formatted to display).

[cliff.clayman]

The issue is that the ratio should be positive as decreasing expenses and increasing revenue are both positive changes.

[ben_hensel]

So I think what you need is:

Please Login or Register  to view this content.

That will work out as +50.5% change ; it's 140% / 93% -1 = 150.5% -1 = 50%
Increasing Revenue (140% => 40% up) divided by decreasing expenses (93% => 7% down) works out to a total of 150%. Then subtract one to get the change, rather than, like, the coefficient.


Note this is pretty much the same direction that shg first suggested.

[cliff.clayman]

I'm still looking for a ratio and not a percentage change.

[ben_hensel]

...are you looking for something like this?

Please Login or Register  to view this content.

Note: that right up there? It's is a bad idea.

[MrShorty]

Or are you looking for the 150% (1.50) result: =1.40/0.935? =(A2/A1)/(B2/B1) =A2/A1*B1/B2?

[cliff.clayman]

I'm not even sure it's possible to do what I am trying to do at this point. I'm trying to get the ratio of spend to revenue. So, for every $100 I make, it cost $5. That is a 20:1 ratio. I make $20 for every $1 I spend. I'm trying to figure out if I am doing better this year versus last year. There will be 4 different situations. 1. Revenue is Up and Expenses are Down. 2. Revenue is Up and Expenses are Up. 3. Revenue is Down and Expenses are Down. 4. Revenue is Down and Expenses are Up. I'm trying to get a calculation that works for all those examples.

[shg]

	A	B	C	D	E
1		Revenue	Cost	Rev/Cost
2	CY2016	$ 100.00	$ 5.00	20.0	C2: =A2/B2
3	CY2017	$ 140.00	$ 4.68	29.9

[cliff.clayman]

I don't understand what that table gives me....

[shg]

In the first line, you had $100 in revenue for $5 cost; that's a 20:1 ratio (your example, post #18).

In the second line, revenue increased by 40% and cost decreased by 6.5% (your example, post #1). The new ratio is 29.9.

Which is a 49.7 percent year-over-year increase (post #6).

[protonLeah]

...I'm still looking for a ratio...
wrt #16, you can custom format the divisions cells as fractions.