Calculate and forecast growth rate using Excel

- [Brad] Hey, and welcome to another episode of weekly marketing tips. I'm Brad Batesole, and this week, I wanna discuss how to calculate and report on your growth rate. If you've spent any time developing marketing reports, you're likely familiar with discussing month over month growth. This metric shows the change between two months as a percentage. And you might talk about month over month growth as it pertains to active users, buyers, leads, or maybe website visitors. So, here's a spreadsheet that I've built in Excel with some metrics in it. And I've calculated month over month growth by taking the difference between this month and last month's value and then dividing by last month's value. I can double-click in this cell to show you that formula. Now, this is great, but it doesn't give you a good sense of how this growth rate is trending over time. It's a very narrow look. And it's not exciting to report that, say, August had a 7% month over month growth rate if your overall growth rate is better than that. So, what if we wanted to discuss our growth rate over the past eight months? To do that, you'd wanna calculate your compound monthly growth rate. Now, it doesn't have to be over the past eight months. It can be over whatever period you'd like. Now, if you're looking at it over a 12-month period, then it simply becomes your compound annual growth rate. Now, let me show you how this looks. In this example, our compound monthly growth rate is 11% for the duration from January 2018 to August 2018. And this is even though our monthly growth rate has fluctuated from as high as 15% to as low as 7%. The formula that we use here is last month over first month to the power of one divided by the number of months in the period minus one. To show you that, I can also click in the cell. You'll see that I'm dividing the last month over the first month, and I've put that to the power of one divided by the seven months that transpired and all of that minus one. That, then, represents 11%. So now, let's say that you wanna answer the question of how many users we'll have by, say, August of next year. Here's how we can figure that out. To forecast that time period, we'll take the very last month of data, in this case, August of 2018, and we'll multiply that by one plus our compound monthly growth rate, all to the power of the number of months, in this case, it's 12. So, let me drop in the formula here. It's going to be August 2018 data times our compound monthly growth rate plus one to the power of 12 months. And here, we can see that we'll have 10,760 users by this time next year if we maintain our current growth rate. Now, pay very careful attention to the numbers that you use when you do this. If you have small numbers, your model will show huge growth. Going from 10 to 50 is significant, but that growth rate will not be sustainable if you were trying to go from 1,000 to 5,000. Also, if you have very high fluctuations in your growth rate, it would be a mistake to model your growth in this manner. Your compound monthly growth rate is great to show consistent growth, but you have to have moderate fluctuations for this metric to really be meaningful. Now, this is just one way to look at your growth model, but I find it provides a nice narrative, and it helps tell a better story than month over month growth alone. Thanks for checking in this week. As always, I'd love to hear from you. So connect with me on LinkedIn or reach out to me on Twitter via @bradbatesole, and let me know what you think about this week's episode. I'll see ya next week.