How to Calculate ROI in Marketing [Free Excel Templates]
It’s a brand new month, everyone! It’s a time for new beginnings. New email campaigns, blog posts, and social media strategies are on the horizon.
But, aren’t we forgetting something? With last month’s activities done, we need to pause for a second to examine what worked and what didn’t. You can’t do that just by saying last month’s activities were “great” — because “great” doesn’t pay the bills.
If you can prove that your marketing activities actually made an impact on your business, you’ll have many more opportunities at your disposal. More budget to play with, potential hires to bring on board, and more ideas to put in action. Sounds like every marketer’s dream, right?
So how do you go about first, finding the right metrics to use, and second, proving your marketing’s ROI to your boss? Why, with a monthly marketing report template, of course. Download our completely revamped PowerPoint and Excel templates by clicking here, Keep reading to learn how you can calculate your ROI in marketing through Excel, and use them in real life.
For example, if you’ve been running a $800 campaign for three months, and you’re seeing a average sales growth of 5% per month and $10000 sales growth from the previous month, then your ROI calculation can assess that trend: (10000 – (.05*10000) – 800)/800
Additionally, if you’re a HubSpot customer, you can access an ROI calculator programmed with the same formula in our CRM. But let’s go ahead and break down how it’s done in Excel for you with and without an existing sales trend.
How to Calculate ROI in Excel
Inputting formulas into Excel is a quick and easy way to solve any equation, and calculating return on investment can be done in a matter of seconds. For an equation without a pre-existing sales trend, the simple ROI equation can be plugged in with ease in Excel as follows: (Sales Growth – Marketing Cost)/Marketing Cost
When you have the amount of sales growth collected and the amount it cost to market collected, plug in the variables into their own rows or columns. When completed, select a free cell to type in the formula using your data.
With these steps completed, press “ENTER” and you’ll have your ROI calculated. If the formula doesn’t automatically calculate it as a percentage, make sure the outcome is recorded as such by selecting the Home Tab > Number > Percentage.
How to Calculate ROI in Marketing
Figuring out your return on investment with more factors — or more realistic outcomes — doesn’t have to be a tricky feat.
For example, over a 12-month campaign, your team may identify an existing sales trend in organic growth and can use it to determine a more precise return on investment. Plug in this variable along with the original factors and input them using this ROI equation: (Sales Growth – Average Organic Sales Growth – Marketing Cost)/Marketing Cost
To compute the percentage, press “ENTER” and you’ll have a more precise ROI to base your marketing decisions on for the future.
Now that we’ve reviewed a couple of examples, let’s walk through how to use your data and how a HubSpot resource can make your marketing reporting a piece of cake.
Which Metrics Should You Track?
First things first — which metrics should you be paying attention to in the first place? Not all metrics were created equal, and if you’re going to capture your boss’ attention with this PowerPoint deck, you’re going to need to tell your story efficiently through data. Click through our marketing metrics template and get tips on how you can put together your marketing report deck.
How to Create Your Own Marketing Metrics Report
So now you know what metrics you need, but what about customizing the PowerPoint slides to fit your business? Follow these four steps so you’ll have engaging, accurate, and concise slides to show to your boss.
2. Open up Excel, and fill in the metrics you want to track.
Equations and graphs will automatically populate based on the metrics you add.
3. Copy the graphs and paste them into PowerPoint.
4. Customize your graphs and charts to fit your company’s theme.
After crunching a bunch of numbers, you probably need a little creative break anyway. Click on individual elements in the graph to customize them. If you really want to get fancy with your presentation, check out these five simple PowerPoint tips.
5. Add relevant examples to back up your graphs.
If you truly want to explain your marketing team’s success from the past month, don’t forget to include a slide here or there that touches on specific examples. For example, if you have a blog post that drove a crazy number of leads in a month, you could include that example slide with a few bullet-point takeaways after the “Leads Generated by Marketing” slide. These specific examples give color to your presentation and justify the time spent on future projects.
ROI Excel Templates
Excel is one of the best tools to use when tracking and calculating ROI regularly.
In the above collection of marketing reporting templates, you’ll get to fill in the following contents (and more) to suit your company:
In this template, you can calculate your marketing reach. For example, you can calculate the reach of your blog, email, or social media.
Then, the graphs below will help turn your data into a visual graph.
With this template, you can calculate your visits. This will include visits via several sources and channels including paid traffic, direct traffic, and social media referrals.
Then, the graph below will create a visualization of your data.
In this template, you can input how many leads are generated with your marketing campaigns.
In the graph below, you can visualize your leads depending on the sources.
In this template, you can calculate your customers via traffic source. In the below graph, you can visualize your customers.
5. Conversion Rates
In this template, you can calculate conversion rates by inputting your information.
Keep Better Track of Your Marketing in Excel
Excel is a tool meant to help you in your business, and it’s an organized way to track your team’s success. Make sure to use it to your advantage, and who knows, after a few months of this you may have a bigger budget or a brand new marketer on board.
Editor’s note: This post was originally published in September 2013 and has been updated for comprehensiveness.