How to calculate contribution margin for fun and profit

In my last post I gave you a brief introduction to managerial accounting, showed you how to calculate contribution margin per unit, and told you why it is important. In this post I’m going to show you how I calculated our contribution margin per unit for every product we sell and how we used that analysis to change the course of our company.

Our question

We had several questions related to profitability. But, for this post, our goal is to figure out how to calculate contribution margin per unit for our online tree business and have a look at what that tells us.

How to calculate contribution margin: what costs go where?

The most difficult part of this whole project was taking a bunch of receipts and making decisions about how costs should be assigned to each tree so that the contribution margin we calculated would be valid.

For example, let’s say we paid $1,000 to ship a load of trees from our nursery to our cold storage facility. This cost is clearly an avoidable and incremental cost (if we shipped zero trees, the cost of shipping would be zero). Therefore, this cost must be included in our contribution margin calculation.

The question is how much of that $1,000 cost should be assigned to any particular tree? My first thought was to take $1,000 divided by the number of trees in the load but that didn’t seem right. Some trees are so small that you can fit five of them in your hand but there are also four foot tall trees. Surely the cost of shipping the smallest and the largest trees shouldn’t be the same.

I tried a couple of ideas and the one that worked best was to calculate how many of each kind of tree would fit in all the boxes we would theoretically put on a pallet. So for the smallest trees we might be able to fit 8,100 trees per pallet. And only 400 trees per pallet for the largest trees. Then I calculated how many pallets fit in the trailer and used that number to figure out the shipping cost per pallet. Then I used shipping cost per pallet to figure out the shipping cost per tree. Does that make sense? Big trees cost more to ship than small trees, right?

Anyway, I went through the process of trying to fairly allocate all the incremental and avoidable costs for each tree and for each order.

Some things were easy. Like credit card transaction processing fees. And some things were difficult such as calculating how much it cost us to pick and pack each order in our warehouse.

How I did the actual calculations

I used the pandas data analysis python library to do all the math. Pandas is amazing for this kind of work. I started with a bunch of CSV files that I either hand made in a spreadsheet or that I dumped directly from our database. I imported those CSVs in pandas, did the necessary data transformations and joins to get the data into the correct shape, did my calculations, and exported the results as CSVs. From there I sorted, filtered, pivoted, and graphed the output in a spreadsheet to my heart’s content.

As an aside, it would be torture to try and do all these calculations in a spreadsheet. I ran the pandas scripts hundreds of times as I tweaked the calculations to increase their accuracy. This is the part of this project that only a programmer could do.

I used pandas to calculate the contribution margin for each order and for each product in that order.

Once I got that data into my spreadsheet, I could answer any number of questions about contribution margin. For example, what is the contribution margin by:

• species
• age
• age and species
• customer segment
• province
• customer

What did we find?

We were surprised by many of the results. We were all operating on a set of shared assumptions about how our business functioned and where we made our profit. But without any prior analysis, we were wrong as often as we were right.

Let’s look at two examples of the surprises.

Older trees are more profitable than younger trees

At the time, we were selling 1-4 year old trees. And the consensus was that older trees were more profitable than younger trees. White spruce was priced something like this:

• 1 year old – $1.99
• 2 year old – $3.99
• 3 year old – $6.99
• 4 year old – $9.99

$9.99 is clearly better than $1.99 so we intentionally grew the older trees and promoted them aggressively. Boy, were we wrong. The contribution margin for 1 and 2 year old white spruce was healthy and almost equal. Three year olds were about break-even and we lost about $15 on every 4 year old white spruce we sold!

Here’s what happened. We grow one year olds in a greenhouse and we pay a fixed price for each tree. Then we ship them to our cold storage warehouse for the winter. Then we ship them back to the nursery in the spring where the plant them outside. The trees that survive the summer are harvested in the fall and the cycle repeats. But ever time you harvest the trees you lose some, and the older the trees, the bigger the loss. It goes something like this:

• 1 -> 2 year old = 90% survival
• 2 -> 3 year old = 65% survival
• 3 -> 4 year old = 50% survival

So if you start with 1,000 one year old trees, you end up with 900 two year olds. Then you transplant them and end up with 585 three year olds. And then you transplant them and end up with a pathetic 292 four year olds.

So we have 292 four year old white spruce to sell at $9.99 each. But what did it cost us to produce those 292 trees? Well, we need to assign the cost of all the lost trees among the way to the 292 trees that survived, plus all the shipping, storage, growing, handling, and other fees all along the way (including the trees that died). When I added all those numbers together we were losing $15/tree!

But it was even worse than that. We give our customers a guarantee that our trees will put on new growth in the first summer or we’ll replace them for free. Four year old white spruce had a survival rate well below 50%. So when you take our guarantee into account, we lost even more money on four year old white spruce.

The math both surprised and shocked us. The forcing function on the cost of older trees is the recover rate (which no one had realized). The recovery rate varies from year to year and species to species. But after I did some additional calculations, I determined that we had lost money on every four year old tree we’d ever sold! And we hovered around break-even on our three year old stock.

We discontinued our four year old stock and severely curtailed our three year old stock to species we could sell profitably (now that we had hard numbers to guide us).

Fruit and berries are our future

We thought that fruits and berries were a significant source of contribution margin for us because they were high priced and we felt like we spent a lot of time talking to customers about them. But the contribution margin from fruits and berries was a few percent of our total contribution margin. Even though we spent lots of time on the phone helping customers buy them, the volume wasn’t that great. And our total cost on fruits and berries was much higher than our average trees.

We saw fruits and berries as the future of our business but our contribution margin analysis showed that they weren’t our core business. It further showed that we would have trouble turning fruits and berries into a core business; doubling fruit and berry sales would barely move the needle. We scrapped that plan pretty quickly.

Other discoveries

• our volume discounts were too generous. When we gave customers our largest volume discount we found many of those orders had large negative contribution margins
• there were huge differences in contribution margin ratios for different species even though they might sell for similar prices
• we assumed that small orders had low profitability but we our analysis showed that small orders were quite profitable as a group

Our takeaways

Our assumptions about how our business made money were shockingly inaccurate. It wasn’t that our people didn’t care or weren’t smart, it’s just impossible to take all the important factors into account and do all that math in your head. With only a few weeks of my time, we were able to replace our assumption and intuition-based management style with a data-driven management style.

We made many changes to our business based on our contribution margin analysis and our profit soared as a result. Having contribution margin data also allowed us to make other changes that I’ll describe in my next post.

Takeaways for a 10x programmer

This is the perfect kind of project for a 10x programmer. It’s unlikely any non-programmer is going to decide to learn python and pandas and learn how to calculate contribution margin on his or her own. Yet, without some kind of scripted analysis or software that can do the heavy lifting for you, these calculations are all but impossible. By crossing over into another functional area for just a few weeks, you might be able to change the course of your company. Can you think of an activity you could do with a higher return on investment?

If your company isn’t doing a contribution margin analysis, you might want to consider doing one. Don’t be intimidated by the scale of the challenge. Find someone to work on it with you and just go for it. And don’t worry if your analysis isn’t perfect–they never are. Do as well as you can with the time and data you have available. Just be honest with people about the limitations of your methods. It’s not a big deal as long as people understand the limitations of your analysis and use the data appropriately.

Further reading

The Strategy and Tactics of Pricing: a Guide to Profitable Decision Making (Thomas T. Nagle and Reed K. Holden) is THE book to read on the topic of pricing. Get your boss to buy you the most recent edition of the book. Read it first and then get your boss to read it. Neither of you will regret it. The authors explain the hows and whys of everything I did here better than I can. And they cover so much more than how to calculate contribution margin. The whole book is excellent.

Wrapping up

I hope I’ve convinced you of the power of contribution margin analysis and the important role you can play in your company. You just have to shift your mindset and view your whole company as fair game for optimization.