Pivot Tables: A Secret Weapon for Food Business Profitability
(Listen on Apple or Spotify. Full transcript below.)
I'm obsessed with pivot tables. And after years of wanting to share this powerful tool with you, the timing is finally perfect.
Why is Now the perfect time to talk about pivot tables?
To create a pivot table, you have to have what I call “clean data”. Up until recently, getting “clean” data out of QuickBooks was literally impossible. And cleaning up what you could get, was like trying to solve a Rubik's cube blindfolded. You'd have to spend hours adjusting the exported data before you could even start analyzing it. Who has time for that? (no founder I know!)
But now? QuickBooks' new Modern Report View (along with great reporting in platforms like Shopify and Toast) has changed everything. You can now get powerful insights in minutes instead of hours.
A Real World Example of Why I love Pivot Tables
Recently, a founder felt like one of their customer relationships wasn’t profitable. We pulled that customer data into a pivot table and could see something fascinating - despite significant chargebacks and fees (and a gut feeling the relationship wasn’t making money), they had a 52.4% gross profit margin. That's the a cashflow positive relationship worth keeping!
pivot tables for food business profitability
In this week's podcast episode, I break down exactly how pivot tables work and share visual examples of how impactful pivot tables are for your food business profitability.
Pivot tables enable you see things like:
Which customers are truly your most profitable (spoiler: it's not always your biggest)
How seasonal patterns affect your sales
Whether your promotional strategies are actually working
If your distribution partnerships make financial sense
And so much more!
Plus, I've got something special in the works - a hands-on workshop where you can master these skills yourself (more details coming soon!).
Ready to see the exciting things that Pivot Tables have to offer?
Watch the latest episode here.
And if you're thinking "but I'm not a numbers person!" - don't worry. If you can click and drag, you can create powerful insights with pivot tables. I promise.
___________________________________________________________________________________________________________________________
Full Episode Transcript
This episode of the good food CFO podcast is sponsored by settle.
You're listening to the good food CFO podcast. I'm your host Sarah Delevan. And with us as always is our producer Chelsea Stier. Hey, Chels. Hey, Sarah. Hey, listeners, welcome back to a new episode. Listen, in case you missed us talking about this recently, we have a really big goal here on the podcast. And that goal.
Is to reach 1, 000, 000 food founders. And the way that we're going to do that is with your help. So if you're listening at home, in the car, working out, whatever you're doing, right, you can help us by a sharing this episode with anybody, you know, who might be interested in listening, rating the podcast, wherever you listen and leaving us a review.
We love reviews. And if you're watching on YouTube. which I highly recommend you do for this episode in particular, but more on that later. Make sure that you click that subscribe button down below and turn on notifications so that you can keep up with everything that we're doing here on YouTube. So Sarah, like I said, this episode in particular is one that listeners are going to want to watch on YouTube.
Yeah, today we are talking all things They are a highly underutilized tool for good reason. Until now, we're going to talk about that. We're also going to talk about what they are and the types of valuable insights that they provide and some of the big business decisions that founders have made and can make.
in using them. Yeah. And speaking about big business decisions, I actually brought a new story that I thought would be a really good intro to this episode because it's about Stephen Caldwell, who is the founder of, now it's called just Stuffed Tots, but was originally called Roasty Stuffed Spuds. And A pretty big decision that he made and I don't know if he used a pivot table or not and making that decision I can't say that but he did make a really really big decision and he made it based off of the numbers That's right Chelsea Stephen says that after looking at the numbers is when he woke up and said one morning we are out Yeah, and we're going to share this, of course, in the show notes, like we always do, a link to this video.
Um, this video comes from the Food Institute. It's part of their F. I. Spotlight Series, uh, where they talk to founders from all over the food business. But, yeah, he started off with, As I called it, Roasty Stuffed Spuds was their name originally, and one thing to note here is that if you're familiar with Roasty, this is a stuffed kind of hash brown essentially, and normally they're like big in a pan, so you've got like a lot of potatoes down and then a lot of cheese, and the more potatoes, they're delicious, um, but he actually got a patent on sort of a handheld size version of this, and so Yeah.
With very little competition, he was off to the races and he actually had a Whole Foods buyer say to him, like, you're going to be huge. This is going to be huge. And so by mid 2023, I think, um, they were in like 2000, between 2000 and 2200 stores nationally. But just like with all founders. Over the past four years, right?
COVID had a huge impact on their business. It started out with their freezer trucks in their distribution, right? Really the cost of that going way up. But then the other thing that he noticed post COVID was that these national retail stores that they had been in We're now doubling their promo fees, doubling their marketing ask.
Their deductions were going up and up and up and up and up. I think at some point in the story, he calls it like the fees were just crazy. And as you said, he looked at those numbers and realized, yeah, this isn't where we want to be. It's time to get out. Yeah, he gives really solid advice to other brands and founders.
He says, if you're considering making the decision to pull out of retail, look at the history of where you have been since you've launched, right? What have the asks been? What have the costs been? And of course, look at your velocity. He goes on to say. If your velocity is going up and your marketing spend is not that's good, but if your velocities are going up and your costs are going up as well, or in his case, going up even more right out pacing the growth in velocity, then get out.
And he very clearly says, and this is a quote, don't throw some more money at it to drive sales. It is a no win situation. And he goes on to say. That if your finance people don't see a window of opportunity for this to truly work, trust them. And I have to say, Steven, I agree with you here. Yeah. And we actually recently shared Sarah, the story of big spoon roasters who also pulled out of big grocery, right?
They, they made that decision to focus on DTC, their DTC business and the relationships that they built with independent retailers. But in this case. Stephen and his team have decided that after pulling out of retail, they're going to focus on convenience stores and food service. Yeah. Another reason that I love that you brought this story to the podcast is because it helps us to illustrate the fact that there's no one path, right?
We always talk about Bobby Yacht. There's a lot of, you know, Bobby Yacht energy in this story, as well as looking at the numbers to guide what building a business on your own terms looks like. I think we. No, I know that we always want your terms to have the highest margins and the most profitability and the most financial sustainability.
Right. But that can look different for, for different businesses. And, you know, he talks a bit about the things he didn't like about. Big retail as well, very similarly to Big Spoon Roasters, but he has, as you're going to share, I bet he had a little bit of experience working with convenience stores and he could see how his product was a, was a good fit and could grow there.
And then also how food service could be a really good option for his product. So don't think that when you pull out a big retail, there's one path and that's D to C or independent stores. There's a lot of opportunities in food. Yeah, and I, I know that we've talked about food service as a channel before that I think like goes so untapped, right?
And, and can be a huge win for a lot of people, but you're right. He did have some experience with the convenience store route. They actually had been building their relationship with the distributors for Circle K before they pulled out of retail. And so they really leaned into that. After and they actually changed the size of their product to like even smaller kind of bite size on the go type food.
Um, I, I think this is something that, and I'm not 100 percent sure, but it sounded like from the story that this is something when we're talking about food service that was in conjunction with the convenience stores right in the, in the like hot food windows and. He really felt like this was a huge opportunity.
He could see the success that they were already having with that relationship with Circle K. And he cites in the video, like there are 152, 000 convenience stores across the country. So why would they not? Consider this as a huge opportunity. I think too, something to point out here is if you're a good food founder and you're listening to this and you're saying to yourself, well, it's a potato.
It's a stuffed potato. It's potato and cheese. Maybe this is not a good food brand. We've talked also on this podcast about the trend and convenience stores and how better. Quality foods are showing up more and more in convenience stores because sometimes you you simply have to stop there You're on a road trip.
You're getting gas. You're just whatever the situation is. We all find ourselves in convenience stores and I think the the Management, the ownership of convenience stores are starting to recognize and have been recognizing that there is a bigger market for better quality, better for you, um, products there.
So I think, you know, it continues to be an untapped market for good food brands. And the last thing I want to say here is that, you know, you don't have to be considering a massive channel change or like a big strategy change in your business to get value from. Pivot tables, which is what we're going to be talking about today.
Right? It's like, no matter where you are in your business, if you've got sales, that means you've got sales data and you can use that data in a pivot table to better understand your customers and improve your margins and your cashflow. Period. So this is a very exciting thing, whether you are thinking about massive big change or you just want to do better where you are and continue to have consistent positive growth, pivot tables are going to help you.
You know, no matter what. Yeah. All right. Sarah, should we get into it? I think so. I'm ready. Good food founders. Let's talk about cashflow, the metric that truly determines your ability to grow your business. We all know that figuring out your true costs, your margins, and projecting cashflow isn't always as straightforward as it could be.
When your data is scattered across systems, it is easy to lose track and leave money on the table. That's where Settle comes in. Settle is the only platform built for CPG brands that combines inventory, payments and procurement into one unified system. And they offer integrated financing for your invoices to help extend your cash runway.
Settle helps you make smarter decisions and keeps your business on track to grow sustainably. Head over to settle.com/good food to learn how brands like carnivore snacks use settle to manage their cash flow and growth. Okay, Sarah, so you teased us in the beginning of this episode that we're gonna be able to make some really big and small decisions.
Through pivot tables, but I gotta take us back to the beginning here and just start with the basics of can you just explain what is a pivot table? Of course. Yes. I think that is an excellent place to start. A pivot table is In the simplest terms, it's a spreadsheet, okay? It's the structure that it is in, but it's not your typical spreadsheet.
It is a powerful tool that helps you to calculate, summarize, and analyze data. It lets you see things like patterns and trends. It allows you to compare, you know, data. You know, month to month, quarter by quarter, things, things of that nature. So essentially, it takes what I would consider to be like, Plain data, right, that you might export, you know, from QuickBooks or Shopify, something like that.
And it turns it into a powerful storytelling tool. Is that helpful? Definitely helpful. So can you maybe shed some light on, I know you already said, like, write your sales data month over month, quarter over quarter, annually, but what are some of the other things that you can. Compare or look at within a pivot table.
Yeah. I'm going to give you like a list of things. Cause I want to, there's so much that you can do with the pivot table. I want to give like a good amount of examples here. So let's talk about comparing. What can you compare? You can compare sales data, right? So like sales in units or sales in revenue numbers, right.
Revenue dollars, for example. So you can look at that monthly. You can look at that quarterly, you can look at that annually, right? So to dig a little bit deeper, you can say, okay, you know, how many units do I sell per month of product a, or how many units do I sell per month to customer a, right? And, and are there any trends here?
Like things of that nature, right? So you can compare data from one time period to another. You can also compare things like sales by location. So if a customer has more than one location, you can see how do velocities compare, uh, location to location. Right? And you can imagine that that will start to inform activities or actions, right, within your business.
Or at least, at the very least, help you to start asking some questions about, yeah. Why are sales maybe different? Or why are they so high in one location versus another? You can also compare sales by customer, right? So on a customer level, you can look at, like I said, customer A versus customer B. Like, how do they compare to one another in terms of units, in terms of revenue?
This could be an individual store. This could be an individual person. This could be a distributor, literally whomever you are selling to. You can compare data across, you know, all of your different Customers. And the last thing I'll kind of share here in terms of what you can compare is sales by product.
So you can look at, maybe you have five products that you sell. You can see, okay, overall for the past year, how do my product sales compare in terms of units? Across all of my products you can compare how annual sales in terms of revenue dollars Look, you can spread it out and look at let's say the last 12 months and say okay How do sales compare to one another across 12 months?
Am I you know, what do I see there? Is there like a high month? For, for one product versus another product is one selling consistently all of the time. And another one is sort of like having peaks and valleys. So you're, you're starting to see that story, right? When you're just simply comparing data. Yeah.
And it sounds like sometimes you might have something that you're really looking to figure out or see, but sometimes it's just good to run them and see what do you see, right? Yeah. And I think sometimes when you don't know what you're, what you're. You're just running it to see like, yeah, what is there for me to learn here?
You might initially just compare monthly numbers versus quarterly numbers versus annual numbers. When you start to get familiar with what a pivot table looks like, then I think you can step into recognizing patterns, right? So patterns for specific customers or patterns for specific channels, right?
Maybe this customer. is ordering, you know, twice as many products per month than this one. Why is it location? Is it customer base? You know, of that particular store, for example, maybe you recognize patterns in terms of what people are buying. Maybe you've got a bulk option and a smaller unit option. Are there patterns in who's purchasing, you know, the different products or how people are purchasing products.
You can also. You know, get a sense of like one of the examples that I like is if people who live in like very snowy, like wintry climates, they might buy smaller units in the summertime. And then they might bulk up and either by like a bunch of units, maybe in November as they're getting ready for snow and delayed deliveries in their area, right?
Or they might buy, you know, a larger volume, like package of your product. Those are the types of like patterns or trends, if you will, that you can, that you can see in your data to better prepare, better promote, create better offerings, right? There's like really kind of cool things that you can do once you have this data.
And then the last thing I'll share here, just as like, what can you see is when we talk about trends. So I kind of like patterns and trends are very similar. And we just talked about that, that sort of, you know, pattern or trend in how people buy and when they buy. So trends might also look like on an individual customer basis.
Um, the example that I jotted down was like a retail store or a cafe that sells. Like hot beverages and cold beverages, right? Um, or ice cream and like cold desserts versus sort of like pastries and like, you know, sort of more doughy desserts. Is there a seasonal trend here? Is there a point in time where we see the offerings and like what people are actually buying starting to change?
And can we make changes internally to help, right? To encourage? Maybe there's a drop off. Of your hot beverage sales and your doughier pastries, right? And you're recognizing that drop off and then like a month later, there's a spike in the cold beverages and the cold desserts that you're offering. Well, perhaps if you make those cold offerings available.
earlier, you won't experience a dip in overall sales, right? So there's these interesting trends that I think a lot of founders like might have their pulse on or think they have their pulse on. But when you look at the data, oftentimes we're really surprised at what we find. Yeah. I mean, you're taking me back and not that we necessarily used pivot tables, right, but there was a lot of info similar to what you're describing.
In our POS system at Starbucks that we would analyze day over day, hour over hour, half hour over half hour in terms of how many customers were coming in. Like, do we really need to be open until midnight or is it. You know, should we be closing at 10 PM or maybe we closed at 10 PM, but we have so many customers up until that last half hour that we actually need to think about staying open later.
And then same thing, like exactly what you were talking about with the hot and cold beverages, right? Like I could look at individual product sales. Of like this type of latte or this type of cold coffee or whatever, and really analyze. Yeah, like you said, do we have enough product on hand when we need it to sell these things?
And so I, I definitely can see where utilizing something like a pivot table would give you. The information, not just for, as we said, those really big decisions, but even the small ones. That's so great. And another example that you just like brought to my mind is, you know, if you're running a promo, cause I know Starbucks, they do, you know, this is a holiday promo.
And then there's like, you know, they're often promoting like a particular latte or flavor or drink certain times of the year, seasonally in CPG, especially if you're in big retail and you're running promos. Pivot tables can help you to see how did our sales compare during a promo time versus a non promo time or how did our sales perform when we ran, you know, a 20 percent off promo versus a, you know, buy one, get one promo, right?
Oftentimes people think, well, a bigger, a bigger, better promo is going to yield better results. It might not, right? And, and your pivot table data and the story that it can show you will, will showcase that for you. And listen, if you can get away with 20 percent off rather than a BOGO, go 20 percent off, right?
Cause it's more margin for you. So there's also sort of like these efforts that we're, that we're taking these tactics that we're employing to drive trial or drive velocity. We can correlate those activities with sales numbers and, and look. over time to see, you know, are they having an impact? How big of an impact are they having?
The other thing that you reminded me of, Chelsea, is that you didn't have pivot tables at, at Starbucks. Behind the scenes pivot tables were happening. That software, right? Yeah. It basically is taking the data, the raw data, and it's organizing it and it's presenting it to you in a way that you can utilize it.
And that is exactly what a pivot table is doing. And so if we think about, you know, Shopify has a lot of data for folks who are selling D to C on Shopify, they present like charts and graphs and comparisons and stuff. I think it can oftentimes be overwhelming, but, but if you know what you're looking for and how to read it, like it's super helpful.
It they're basically. You know, doing that for you, but then they've also got a ton of reports and you can customize reports in there. So if you want to see something specific or you want to see it for a specific timeframe, you can go and create your own pivot table with that data, which we'll talk about in some more detail.
Folks who use toast, which is a really common in store POS system. It's super popular for restaurants and things, right? They also utilize sort of behind the scenes pivot tables to give you data like. Daily sales to give you customers by our customer by day, things of that nature. So you can utilize that data to make those same types of decisions in terms of.
You know, hey, our store opens at nine, but we really don't start seeing customers until 11. You know, maybe we can save some money on labor, um, and have our team start a little bit later, which they might love, you know, based on the data, or maybe our hours are a little bit different, you know, on certain days of the week, but there are a lot of.
Tools and systems that don't have sort of the behind the scene pivot tables, if you will, and the ability to present you with beautiful charts and graphs that are meaningful, like QuickBooks, for example, they do have what they're calling a pivot table feature, but it is really weak, and it doesn't allow you to really get a lot of data and really get your hands on the information.
So in that case, there's tons of valuable. stuff in there. And a pivot table is how you can pull it out and then utilize it. Yeah. So, Sarah, I know that on the podcast before we've talked about blended margin, product margin, and like the ways that you can very easily figure that out. But I also know that recently you had someone in office hours that wanted to be a little bit more nuanced in the way that they were looking at their margins.
And That, that's why this episode actually exists because you brought up, we're gonna do a pivot table. Yeah. So you bring up a great point and thank you for reminding me about this because it's one of the like most exciting ways to use a pivot table is to identify the profitability of your customers, to identify the profitability of a sales channel, right?
So on your PNL. It's very easy to see your blended margin. That's your gross profit margin. It's right there for you, right? And it's easy to see or to obtain your product margins, right? So you see that in our perfect pricing calculator. That's exactly why that tool exists, but it becomes much more difficult to identify that customer.
Gross profit margin and the channel gross profit margin, because there is no singular place where that data is just laid out for you. Right. So yes, in a recent office hours, you know, there was a particular person and he, we dug into a lot of things and I don't remember what he said, but I was like, oh.
Pivot table. And he was like, what is that? And I was like, let me show you. And it just so happened that he was in there solo that day. The other like attendees weren't able to attend. We had time together. And so we, we dove into how do you create a pivot table? And I showed him how you can see. Or in calculate customer profitability and how you can see and calculate sales channel profitability.
And he was just like, this is exactly what I need. This is such powerful information. And it is every time I do that specific work with a founder that they are just like, how did I not know about pivot tables before? This is the most amazing thing. And yeah, after hearing the, you know, fourth, fifth founder.
Respond that way, right? Him being the most recent one. I was like, okay, I literally wrote down on my paper. I was like pivot table episode. Like we just have to do it. And here's the thing that I'm going to say, I have wanted to do this episode for a very long time and I have Preached and talked about the, the power of pivot tables for a very long time, but up until very recently, it's actually been quite hard for people to take raw data, particularly from QuickBooks, which has a ton of valuable information in it.
Especially if you are invoicing from that system, it has been really hard to get that information and get it into a format. That you can then easily create a pivot table from. And so we're going to talk about things like, you know, clean data. Clean data, whenever I say that, just means information that's organized in a way that you can easily turn into a pivot table.
And for years, the, the information we get from QuickBooks, the information we get from different platforms has not been clean. And so there would be. One to four, you know, even more hours of work that would go into organizing and cleaning up data that you had just exported from somewhere before you ever get to the point of making a pivot table.
And it's purely unrealistic to say to a founder, there's really beneficial information at the end of this four hour task that, Oh, by the way, you've never done before. You're not really confident in and it's extremely nuanced. Yeah, that seems like a lot. Yeah. That seems like a lot. So I say we start from the beginning, and let me just ask you, where do pivot tables exist?
Where can I, like, create one? Yeah, so as I mentioned earlier, they're a spreadsheet based tool, right? So if you use spreadsheets in Excel, you can create a pivot table there. If you do your spreadsheets in Google Sheets, you can make a pivot table there. Full disclosure, I never use numbers. I have no idea if you can make a pivot table there.
Reach out, tell us if you can. But I actually, despite being like a super like fan of Excel and like, that's my OG, I literally, you know, studied, you know, how to utilize Excel in college. And it's so powerful. I actually prefer Google sheets whenever I'm making a pivot table because they have made the process.
So user friendly, whenever we export data now, like with a client or with an office hours, you know, attendee, we take it from Excel and put it right into Google sheets and we build our pivot tables there. And I will say some folks, depending on what. QuickBooks subscription level you have and depending on what report you're running, there is an option to export directly to Google Sheets, which is exciting.
It's not broadly available right now, but kind of take a peek if you're interested in doing this work because they might have it there. But to answer your question again, uh, they exist in spreadsheets and the easiest, and I think most convenient way to create a pivot table is with a Google Sheet. So Sarah, I know that you said you've been wanting to do this episode for a really long time.
So, why did we wait until season 13 to do it? That is a fair question. So, as I mentioned, uh, I think a moment or two ago, there has been a lack of what I call a clean, clean data, right, um, up until this point. And to ask a founder to spend multiple hours cleaning up data, and again, it's like a very, it can be a very nuanced process because, you know, To even get to the point where a pivot table is possible, right?
Like it was just unrealistic. I couldn't imagine having an impactful podcast episode where I was like, here's this great thing that's going to take you five hours at a minimum to do. And Oh, by the way, you have to do it every single time you want to create a pivot table, right? Like I just hear people turning, like turning us off, tuning out and being like, that woman is crazy.
But what has changed is that data has gotten So when you think about Toast, when you think about Shopify, those are two platforms we had just recently talked about. The data that comes out of those platforms when you run reports is a clean data set. And for years, I think since obviously since like the beginning of QuickBooks reports, they have not had clean data and now they do.
And I think I shared about this, you know, when they made the change, basically they have what's called modern reporting view. And I didn't talk about pivot tables as soon as the change happened because similar to exporting to Google sheets, not everyone had this feature and this feature didn't exist on every single report.
And it's possible that someone listening to this episode who goes to their QuickBooks account and runs, you know, a report might not see this view, but the vast majority of founders and subscription levels now do have what's called modern report view in QuickBooks. And what that does is it. Creates instantly a clean data set that you can export and then right away, turn into a pivot table.
This is saving hours of work for me, uh, for founders who want to create pivot tables, right? And it's making this, this ability to see the story. of what is happening with sales, right? To see the story of what's happening with chargebacks and distribution fees, to see the story of how profitable are my customers, how profitable are the different sales channels that I'm in.
So easy. That is why now is the time to talk about pivot tables here on the podcast. So I think we should probably start. looking at some of this data. What do you think? I think that's a great idea. And so this is also a great time to remind anybody who's listening to, I mean, you could definitely stay here, but join us over on YouTube so that you can see everything that we're going to be showing you and talking about for the rest of the episode.
Yeah. So Chelsea, on the screen right now, I'm actually showing the view in QuickBooks. This is a report view, and I'm going to do a little shout out to my favorite QuickBooks report. Whenever I'm running a pivot table, I always seem to go to the sales by product and service detail. This is A report that you can customize and like seemingly put any information from QuickBooks in and so it is my go to and I think a great place to start for anybody who wants to create pivot tables.
So I'm going to show you the difference between classic view, which is typically what the default view is whenever you look at a report in QuickBooks versus modern view and just talk a little bit about that. Like the two big differences here. So the report itself, you can see it on the screen now, if you're watching on YouTube, the report itself, isn't going to look super different, right?
You've got information grouped here by product. Um, and then you've got things like, you know, section headers and totals for different sections. When you switch to modern view, the report doesn't look super different. I mean, there's a little bit of shading, you know, at the top of a new section, but you can still see that it's sorted by product that you've got the totals for different products.
Right. And so information is grouped together in both. The classic view and what they call the modern view, the difference and the value in the modern view is that you actually have the opportunity to ungroup the data. And I want to show you what that looks like, because this is like the key to quick pivot table creation.
So here now is an ungrouped report. It's literally as easy as clicking to, you know, two clicks. And you have converted your grouped report where you've got headings, you know, and, and like, It's like totals and different information groups together to just a stream like raw data. Exactly. And when you're building a pivot table, blanks cause a ton of issues.
So you would export the classic view. Grouped data and then have to, you know, modify columns and modify headers and get rid of empty spaces or fill empty spaces in, you know, with information. And it was quite frankly a pain in the butt. Yeah, I'm now understanding the 4 to 5 hour pre work of a pivot table.
Yeah, so now you can just take this information in the ungrouped version. There are no gaps, there are no spaces and you can export it and it's ready to go. Nice. So, Sarah, we, we have our clean data set now. You, you know, you've run that report and We have it here inside our, you know, Excel sheet or Google sheet, but I don't necessarily see like, as I'm looking at this right now, right?
Like, I can't tell what the story is here. Um, so maybe you could show us some examples. Yeah. I'm so excited to show you examples, but one thing I want to note before we jump into the sample pivot tables is any column that you see here in your data set, you can look at in a pivot table. So I'm just showing you a couple examples and there's like really almost an endless number of combinations and ways that you can look at information about your business.
So let's start super simple, right? Let's look at customer unit sales by month. Running down the left side of the pivot table, we have our customers. Then across, so like left to right, we've got January through December, numbered like one through 12 to represent the months. And then we can see the unit sales by month per customer.
So I'm picking on an example here for those folks looking at YouTube, you know, Donna Jean, we can see that they had pretty stellar unit sales, January, February, March. Every month is above 129 units and then we start to see a pretty significant decline and then we get into single digit sales. This is powerful information for a founder, for a sales team, right?
What is going on with that customer? Have we touched them recently, right? So you can start to see, A, the story and then hopefully your wheels are turning like, okay, this could be helpful for a myriad of reasons, right? You can also see, like I talked about, you know, earlier in the episode, Um, this is a, a, a company that has, what is that, five locations.
So you can see monthly sales by location. We can see that, okay, it looks like they started with three locations and then the next month they started selling in all five. And how do sales compare location to location? The Pasadena location is by far having, having the largest number of units, right? So this is just a way to start to like, See, and we're just like barely scratching the surface of the data and like the story that you can tell let's go a level deeper So we looked at customer level like units by month now we've added product So we get another layer of detail that can tell us.
Okay. These are the products we're selling to a particular customer Customer number one, they buy one product. Customer number two, they're buying, what's that? Three, six, eight. They're buying eight different products. And we can analyze what is our highest selling product for that customer, right? Did they stop buying something?
Is there a seasonal trend in the way that they're buying? What is the story within a particular customer about the products that they're buying and when they buy them? But you don't have to just stop at unit sales. As I said, anything in the data set can be pulled into a pivot table. So now on the screen, we're still looking at customer.
We're still looking at product sales by customer, but in addition to total units sold in the year, we can also pull in revenue information. And when you have unit sales next to your revenue totals for any timeframe. This is a foundational like building block for really valuable, really in depth analysis that you can do for your business.
Yeah. And I love that you were able to pull all three of these tables together from, as you said, the same set of data. Yeah. So I'm wondering. How can we take this even further, like how can we really start to understand profitability or, um, you know, either like through a customer or maybe by a product and start to really build that story out?
Chelsea, I'm so glad you asked. So now on the screen I have a pretty Like small, pretty simple pivot table. So here we have a big data set, but we essentially filtered it down. So we're looking at sales, product sales to one customer and one customer only. Okay. And so what we're able to do is we have the total revenue.
You know, for the time frame, and then we also have total units of that product for the time frame, and I'm going to work some internet magic here and make some data appear. We can add information alongside our pivot table. So all of this COGS, right, per product, total. Cogs profit dollars and profit margin can be calculated based on the found foundational data in your pivot table.
So just, I'm going to try to run through this really quickly, right? You should know your cogs. For a unit of your product and you can literally type it in here. I'm just going to change this to 5. Like you can change that. It's very easy. So you can add the information alongside your pivot table, literally right next to it.
If you know your cogs, you can then calculate your total cost of goods sold, right? So my cost of goods times, the number of units I sold, that's my total cogs for that product for that customer. Well, if you have revenue. And you have COGS, you can calculate profit. So now we can calculate profit dollars. We can just look at our total revenue minus our total cost of goods sold, and we can see profit.
For those who are just listening, we're seeing from 3, 881 of Revenue, we have 641. 52 in profit dollars. Now let me change that back to its original number because I feel like I'm, I'm cheating people. The real number was 1, 108, but, but what we're seeing then, right, when we have profit dollars and we have revenue, we can calculate our profit margin.
As you can see, if you're watching on YouTube, there are several. Products here that have a 28. 5 percent profit margin, a 37 percent profit margin. There are quite a few that have a gross profit margin above 60%. This information helped this founder determine whether or not they should continue working with this retailer because the overall.
Gross profit margin for this customer was 45%. Now they have decisions to make. Do you want to pull that all together or maybe you just change the price, increase the margin on certain products. So this becomes a more profitable customer. The point here is Chelsea, that you can see the profitability on a product level and on a customer level, and then you can make decisions.
That will help you create a more profitable relationship and a more profitable business. It gets even better than this. If I might just hop to the next table. So the next example I have for you guys is for a gifting site. Now if you guys are familiar or maybe you've sold on a gifting site before, they, They sometimes offer discounts or sometimes like service fees and things right associated with selling on that website.
So in this case, we had two products sold on the website. And in addition to seeing the number of units sold for the timeframe and the revenue generated in that timeframe, we also can see discounts and service fees and they're represented here as a negative number. So there's actually three products sold here.
If we look at this, I'm just going to total this up here. So there's 7, 296 in sales, and we can see that there were 2, 026 in discounts and service fees. So a grand total, sort of like net revenue here was 5, 270. I will share that this client thought that this was an unprofitable relationship because of the discounts and because of the service fees.
But what we found Was that the profitability of this particular customer was 52. 4%. So profitable and had a margin that was above that sort of 50 percent sort of like middle ground. Right. So they decided, you know what, we'll continue to work with them. This, this actually works for us. Yeah. And they, and if they would have just gone on their gut feeling, wow.
Yeah. Lost revenue and lost cashflow, right? Because when we have 52 percent that's cashflow positive. So okay. Now I want to make things like really exciting for people. So if you are selling to a distributor or you have a partner of any kind that does, you know, chargebacks that wants, you know, like payment term discounts, maybe they require advertising.
Right. Anything, right, we're, we're thinking about like the K he's in the UNF eyes of the world, but there's also other regional, you know, distributors that will have similar fees. This is where pivot tables become super valuable. So what I'm showing on the screen right now is actually just a summary of everything that we can see from the data set for this pivot table.
And so for those who aren't watching on YouTube, we have four. Different sales channels here. So direct wholesale, distributor sales, food service, and retail. That means that we can calculate the profitability of each of those sales channels. Because it's in the data set, right? That's exciting. We also can see that we have data for expired or destroyed product costs, um, postage, freight, and shipping.
Discounts to customers, term discounts, right? Variable trade spend, like partner fees, OI, so off invoice and manager, sorry, manufacturer chargebacks, right? Scans, retail advertising. This company. Keeps good data. They put valuable information into QuickBooks in a way that allows them to have all of that information in their data set.
They can export the clean data and then they can see. So this is like a high level summary. So this is like valuable, but again, just kind of scratching the surface. We can see our customers, then we can see all of the revenue and. all of the discounts, chargebacks, et cetera, for that customer. And then we can see what I'll call net revenue, right?
Yeah. So you have your, your high, you know, your, your top line revenue, and then all of your expenses that come out after it. But this is before your cost of goods sold are taking into consideration, right? So customer number one, we're going to call them Ace. They don't have any chargebacks. They just have nice sales of 11, 000 for this time frame.
We like that. Yeah, we like that. If you come down a little bit further, you can see Kahey is here on this list. And I'm going to call out that the, the sales here, the revenue number is 37, 590. You can also see that there's a number of negative amounts here for the various different types of chargebacks.
And what the pivot table shows us very easily without ever having to get a calculator is that from that 37, 590, sorry, 90 worth of revenue, we actually get to have. 20, 948. So what you're saying here is that you're basically cutting that revenue almost in half before you've ever even looked at your cogs.
Exactly. Yes. So I'm going to show you, like, again, this is sort of scratching the surface. This gives us a sense of. I'm sort of calling it net revenue again because cogs aren't here, right? So what is my net revenue? I could look at a sales report and go, woohoo. We sold 37, 000, almost 38, 000 to Katie. That's amazing.
But what are we actually getting to keep 20, 948? Well, okay. Well then what about our cogs? Like what do we have after that? That's like leftover. This is a different data set that I'm going to show you on the screen now, but like, take a look at this. Like this is nerd town, like this makes me so happy, but this is really easy to do.
This is a similar data set, right? And what we've done was we've laid out the adjustments, the credit memos, the discounts, the chargebacks from left to right on the screen instead of, um, top to bottom. And this is just so I could show you. Both of these charts at the same time. So up top, we have a customer.
Let's look at UNFI in this data set and we can see what the total was for adjustments, credit memos, chargebacks, et cetera. We can also see the total revenue, right? That top line revenue. And if we scroll all the way to the right, we can see what that net income is, right? So in this case, we had 112. We're going to round up and call it 113, 000 in sales.
And we ended up with 77, 000. 638. Okay. In net revenue. Again, that's before cost of goods sold. So similar to what we did in a previous pivot table, we brought in the data again, right? For each of the customers, we've got the total revenue amount. And we also summarized our cogs. Now, again, this customer, this client keeps really good cogs information per product and per customer.
So we were able to have a data set. And literally just incorporate it into, you know, our sales data set and, and just very easily do the COGS calculation without having to sort of manually add anything to the side. But we did want to take this a step further and say, okay. For example, UNFI, we can see 113, 000 in revenue.
Our cost of goods sold for the product alone was 12, 767. So our product COGS were 11%. This is a really unique product, so the numbers look, they look really good here. These, you know, these are real numbers. And so the margin for this customer for UNFI before credits and chargebacks is calculated at 88. 75%.
And then we can look up to this chart up here where we had all of our discounts and chargebacks calculated and we can see that it's 83. 5 percent profit margin after COGS and chargebacks for UNFI. And again, like a lot of people You know, say how expensive it is to work with distributors. If you've got the right kind of product, you know what I mean?
This is an example where this is a positive working relationship. And I think, I think that that really speaks to also, like we talk about all the time, right? You can't just say. Oh, unify is bad or good, or there's, it's really about what does it look like in your data and your numbers and to be able to pull them together in such a way that it's so clear.
Yeah. To see that story. Like, how valuable is that? Yeah. And so, I mean, I hope those listening, like, are starting to get what you can do with pivot tables. The kind of information that you, that can be difficult. To obtain and to ascertain from the tools you're likely using in your business. And when you're small, like you don't have a ton of money to invest in systems and things that are going to create this for you.
Like your, like your Starbucks tools, right? Chelsea. Yes. Shopify will give you some information. Yes. Toast, you know, and other tools are great at giving you information. But if you are utilizing QuickBooks, if you are. You know, selling wholesale, selling distribution, and you don't have a tool that does it for you.
Pivot tables, they're quick and easy to make now, and they allow you to look at your data any way you want. Yeah. And provide just an absolute ton of value. Okay, Sarah, so pulling back a little bit here, if I am listening to this episode and I'm like, Oh my God, I want to make pivot tables, but I'm still like, I don't know what I'm doing.
Yeah. Where can people go and like practice this more or learn more about creating pivot tables? I'm so glad you asked because we have been. I'm talking about this behind the scenes and we really want to make a pivot table course. And so I don't have all of the details at this moment about how that course is going to happen, but I will share what I'm thinking about is a live workshop for a small group of founders and doing that once or twice.
Because As a pivot table nerd, a pivot table expert, if you will, it's easy for me in teaching without a live audience to like skip over some important steps, right? So I really feel like if we can get maybe two small groups of folks together and learn how to create pivot tables together, just from the very basics.
And then we can sort of ramp up from there. A, I would enjoy that because I love, I love, you know, seeing and working directly with founders, but then we can also create an on demand. You know, course that can be available on our website, um, for folks to utilize any time, you know, when they hear this episode and they want to start diving in.
So I would say, make sure that you are on our mailing list because that's where we will provide updates about. The forthcoming course, I've talked to a couple of founders who I've, you know, had these conversations with one on one. They're all interested in being a part of a live workshop, which is really exciting.
But in the newsletter, we'll post the dates and then we'll just kind of keep everybody aware of like, you know, how we're going to go about teaching and supporting everybody in building pivot tables and really making them their own and, and creating the most value for their businesses. I love that. And of course, if you are still trying to, you know, get even more support, right?
Maybe you've gone through the course and it's in the future now, right? Um, and you're still like, ah, I really want to pull this piece of data in or how would I, you know, organize it in this way? You can always also come to office hours, Sarah, I know that that's one of the, I mean, and that's how this episode even came to be, right?
It was. Yeah. And working through that with the founder in office hours. So definitely visit our office hours page on the website under coaching to get more information how to join us there as well. Awesome. Well, thank you for letting me talk all about pivot tables today and the value that they bring.
And I hope to see many of you in our course in the future. Looking for more content like this, subscribe to our YouTube channel. You'll see weekly podcast episodes as well as other content related to the show. Just visit youtube. com forward slash at the good food CFO. Thank you for joining us here today.
If you enjoyed this episode or found it helpful or inspiring in any way, please share it with your founder friends on social and rate and review the podcast wherever you listen. It's the number one way to help good food founders find the show. We'll be back with a brand new episode next week.
HAVE YOU RATED AND REVIEWED THE GOOD FOOD CFO PODCAST YET?
IT’S THE NUMBER ONE THING THAT YOU CAN DO TO HELP OTHERS IN THE FOOD INDUSTRY FIND THE SHOW!
And you can do it in just a few easy steps:
Click here to find the podcast on iTunes
If you listen on Apple hit “Follow” so you never miss an episode!
Scroll to the bottom of the page, Click “write a review” and share what you love about the show!
THANK YOU for helping to support & promote The Good Food CFO!