A while ago, a friend who is in sales asked me a question. Say a company makes revenue predictions based on selling a certain number of units at a certain price. They end up selling fewer units at a lower price, so they miss their revenue forecast by some amount. How much of that amount can be attributed to the lower price, and how much can be attributed to the lower sales number?
To put concrete numbers to the question, a company planned on selling 500 widgets for $1000 each, for a total of $500,000, but they only sold 450 for $800 each for $360,000. My friend wanted to know what percentage of the $140,000 revenue shortfall was due to the reduced price and what percentage to the reduced number of units sold. On its surface, this question seems fairly straightforward: each percentage should be a portion of the missing $140,000. But looking at percentage when both positive and negative numbers are in play is risky.
I should probably say here that I have taken a grand total of one course in economics or business in my life, and there may well be an MBA-approved formula for making such calculations. But I’m not going to look it up. Instead, I want to see where a naive mathematical approach takes me.
My friend said one way he thought through the question was to look at alternate scenarios: if the company had hit the sales number, but at the lower price, it would have made $400,000, and if it had sold the smaller number of units for the target price, it would have made $450,000. In the first scenario, they came in $100,000 short, or 71 percent of $140,000, and in the second, they came in $50,000 short, 36 percent of the missing money. It doesn’t take a lot of high-powered math to see there’s a problem: the two shortfalls add up to 107 percent of the missing revenue.
My friend thought it might make sense to attribute 2/3 of the miss to the lower price and 1/3 to the lower number of widgets sold because the shortfall in the first scenario was twice as big as the shortfall in the second scenario.
I don’t think that’s a terrible idea. If a screaming executive is demanding that you give them two numbers to explain why revenue is down (my understanding of the business world is mostly based on Better off Ted), I think you could do worse than attributing 2/3 to a low price and 1/3 to a low number of sales. But this is a risky maneuver. What if the lower price had ended up driving more sales?
In that case, the company might have sold 550 units for $800 each, for a total of $440,000 in revenue. They still missed their projected sales, this time by $60,000. What happens when we try to break that down? If they had sold 550 units for the “right” price of $1,000, they would have made $550,000, and if they had sold the right number of units for $800, they would have made $400,000. Blindly applying the same reasoning as before, the lower price accounted for $100,000 of the $60,000 revenue miss, and the change in the number of sales accounted for $-50,000. Did the lower price cause 167 percent of the revenue shortage? Did the higher number of sales cause negative 83 percent? With one small change, the approach runs into serious trouble.
This is the peril of looking at percentages when numbers might be negative. Jordan Ellenberg has a great example of this in his book How Not to Be Wrong, excerpted at Slate. The U.S. had a net increase of 18,000 jobs in June 2011. The state of Wisconsin had a net increase of 9,500 jobs during that month. Politicians proclaimed that “over 50 percent of U.S. job growth in June came from our state.” But what about Minnesota, which added 13,000 jobs? Or the other states that also had positive job growth? The problem was that some states gained jobs and some lost them. As Ellenberg writes, “Put negative numbers in the mix, and percentages get wonky.”
Getting back to my friend’s original widget problem, I thought it was better to think about it with FOIL. With apologies to the excellent guidelines in Nix the Tricks, which urges educators not to use tricks and mnemonics that can become a crutch or mask conceptual problems in math, FOIL was seared into my brain at a young age, and it will always be how I think about multiplying polynomials.
For those who had trick-nixing teachers, FOIL is a mnemonic for applying the distributive law to two expressions that both have two parts. For example, take (2x+1)×(x+3). To find their product, multiply the First terms (2x2), the Outside terms (6x), the Inside terms (x), and the Last terms and add them all together to get 2x2+7x+3.
To put my friend’s problem into the same form, we can think of each term as the target number minus the shortfall. The company wanted to sell 500 widgets, but it only sold 450. It wanted the price per widget to be $1000, but it was only $800. So their revenue was (500-50)×(1000-200). In this form, we can see exactly what happened. (500-50)(1000-200)=500,000-100,000-50,000+10,000. The last term is what threw off the naive deficit calculation we did at the beginning. The combination of lower price and lower sales meant each widget they failed to sell didn’t hurt their revenue numbers quite as much as it would have if they had been missing out on full-priced sales.
In my other example, they sold 500+50 units for $1000-$200 each, for a total of 500,00-100,000+50,000-10,000 in revenue. The last term indicates that the lower price meant that each extra sale helped a little less than anticipated.
I’m not suggesting FOIL as the answer to every problem involving negative numbers and percentages—I don’t see how it applies to Wisconsin’s job growth in June 2011, for example—but in the case of my friend’s revenue analysis, I think it might add something to the discussion. It’s not as snappy as one number that sums up all the company’s problems, but it also doesn’t risk attributing 167 percent of a revenue shortfall to cost and negative 83 percent to the number of widgets sold.