When we created Lemonade Stand, we envisioned an agency that helped companies around the globe build their digital brand, grow their online business, and manage their effectiveness. We would offer our services to those businesses, using the years of experience we’ve gained and the tools we’ve created.
Recently, we began to think about all the different types of companies and industries that we’ve served over the past few years that have helped us get closer to that mission. However, if we’re to be honest with ourselves, there is one industry and community that we have yet to make any impact at all in — our very own, the internet marketing community.
And if our vision is to really help other companies “Build. Grow. Manage.” then why shouldn’t those same values extend to other internet marketing agencies who could benefit from our experience or resources? After all, the contributions of those in the industry who so graciously share their knowledge is what has helped us grow our agency to this point, anyway.
So, over time, we’ll be sharing some of the very resources and tools that we have developed and use to build, grow, and manage our own agency on an every day basis.
Starting with…
Lemonade Stand’s Ultimate Profit Margin Calculator for Internet Marketing Agencies
Wow, that was a mouthful.
Seriously, the name might be hard to get out, but the function of this calculator is very simple and straight to the point. Use it to calculate your profit, loss, and margin for each SEO, PPC, or one-time client your agency serves as well as the overall P&L for your business using all clients and services combined.
This calculator will help answer the following questions every internet marketing agency has:
- Which of my clients nets the most profit for my business?
- Which clients are the biggest drain of my resources?
- Will I make enough money if I quote a business a certain amount?
- Where is all my gross revenue going?
- How much does my employees’ wages affect my profit margin per client?
- Are the right employees working on the right clients?
- How much do each of my services make?
- Who is my top salesman?
- How much is overhead killing my bottom line?
- What is the net profit of my business?
- What kind of margin am I making on each of my clients and my overall business model?
- And more…
And, at the very least, you can use the calculator as an easy way to journal your clients, track who’s managing what account, and other tidbits.
We called this calculator “Ultimate” but I’m sure there are things I’ve probably missed or things that don’t work for every type of agency. Some of those things, I have decided to leave off because it really doesn’t fit our business model here at Lemonade Stand. However, if you charge your clients in a fairly standard way, this calculator should be beneficial to you.
Feel free to share your feedback about what might be missing as I’m always tweaking the calculator and may update it with more features at a later time.
If you’re ready to use the calculator, click the button below. It’s on Google Drive to make it easy for collaboration between you and your team.
Get the Ultimate Profit Margin Calculator Here
If you want to learn more about how the calculator works and how to use it, here’s a breakdown of each sheet included within the tool…
Get Started
The Get Started sheet contains basic instructions on how to use the tool, a key to each cell’s color and its purpose, and two important data input sections. Within these two sections, we’ll collect data about your overhead for things like office rent, software, etc and you’ll also be able to set the average wage levels of your workers.
If you are a single-person agency or have pretty even average wages among your employees, you can simply set the “Standard” rate. If you are a larger agency that has different levels of wages for employees, you can use this section to set those levels for calculations within the Recurring Clients sheet.
The reason we used wage levels within the Recurring Clients sheet instead of individual rates is because average wages fluctuate from month-to-month and this allows us to calculate wage/overhead increases/decreases (like insurance hikes) over multiple clients all at the same time.
Recurring Clients
If you perform regular, month-to-month work for your clients with services like SEO, this sheet will become your best friend. It will allow you to calculate each of your client’s average monthly profit while also discovering which of your clients drain you each month and which ones you should and can afford to give more love to.
Unlike the next two sheets, the Recurring Clients sheet is a monthly average of revenue and expenses. This is because, Recurring Clients will almost always pay the same monthly while specific hard costs and hours you spend working on the client’s account often fluctuate. So, when putting in your data in the blue cells, just use average costs, hours, etc. Also, because this sheet is based on averages, it will calculate differently on The Bottom Line sheet and you’ll only be able to see current month & year P&L.
To begin using the sheet, simply fill in as many blue cells within a row as you can. Each will be used to calculate the P&L section in the middle of the sheet (white cells that change green or red) and on The Bottom Line sheet — which I’ll talk about more later.
If you need more information about what each cell does, simply mouse over the white cells in row 1 to view the notes.
PPC Clients
The PPC Clients sheet supports the following types of fees:
- Percentage of total spend
- Flate-rate fee
- Percentage of client revenue (Commission)
The sheet will also calculate if you earn any combination of the three. For example, if you are paid a flate-rate management fee of $10,000 plus you receive a 2% commission for $1 million in client revenue ($20,000), the sheet will automatically calculate a gross revenue of $30,000.
Most often, your client’s spend will vary month-to-month and so will your fee, but in some instances, you may have arranged a set spend and fee with your client each month. For those situations, you can select “On-Going” instead of assigning a month and year to the account. If you select “On-Going” you’ll be unable calculate P&L in The Bottom Line for a custom date range, but the P&L is included in the current month & year’s numbers. Essentially, the “On-Going” option just makes clients within the PPC Clients sheet function like Recurring Clients.
One-Time Projects
Getting paid on one-time projects can be extremely simple… you quote the client a fee, the client pays you up-front.
Unfortunately, that easy billing structure very rarely seems to happen and it can be quite complicated to calculate how many payments you’ll be receiving, how much revenue you can expect, how much you are owed, and how it translates into actual net profit. This sheet will help with all that.
You’ll need to start with the total expected gross revenue from the project — usually, this is the number you quoted your client. If you are billing your client hourly and have yet to give them a final quote, you’ll need to put in the total gross revenue AFTER all hours are completed as the calculator doesn’t yet include a running billable hours function… yet.
There are two blue cell sections within this sheet separated by an orange line. The section on the left is for the “actual” hours and expenses you’ve already incurred and the section on the right is for the overall “projected” hours and expenses. This will allow our sheet to give you an estimated net profit and margin and to also calculate the Outstanding P&L in The Bottom Line.
The Bottom Line
After inputting all that data in the other 4 sheets, The Bottom Line will present your agency’s overall P&L in two easy ways.
The top half of the sheet will be the current month and year’s profit, loss, and margin. This will include all active Recurring Clients, all on-going PPC Clients, all PPC Clients that returned a profit or loss during the current month, and all one-time projects that returned a profit or loss during the current month.
The bottom half of the sheet will be similar to the top half except it will allow you to set a custom date range that calculates the profit, loss, and margin during any given period. The limitations with setting a custom date range is that it will not include any Recurring Clients or on-going PPC Clients P&L. This is because both are used for the purpose of calculating the current month’s profit only and not profit for any one specific time frame.
Data
This sheet is simply used for calculating P&L in The Bottom Line and you can ignore it completely unless you’re a spreadsheet nerd and want to go nuts with up to 25 years of raw, month-to-month data. Please don’t delete this sheet, or any sheet for that matter, as the calculator will not function correctly.
Final Thoughts
Well, that’s it. I truly hope this calculator will contribute to you or your agency becoming even just a tad bit more efficient. We’re firm believers, here at Lemonade Stand, in “You can’t manage what you can’t measure,” and it’s my goal that this tool will help you measure the individual impact of each of your clients and the overall growth of your book of business.