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. This is probably not unlike your agency.

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," and "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.

So, over time, we’ll be sharing some of the very resources and tools that we have developed to build, grow, and manage our agency on an everyday basis.

The Ultimate Profit Margin Calculator

Seriously, the name might be hard to get out, but the function of this calculator is very simple and straight-forward.

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, which 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 do my employees’ wages affect my profit margin per client?
• Are the right employees working on the right clients?
• How much does each of my services make?
• Who is my top salesperson?
• How much of a drag is overhead for my bottom line?
• What is the net profit of my business?
• What kind of margins am I making on each of my clients and my overall business model?

And, at the very least, you can use the calculator as an easy journal for your clients that will keep track of who’s managing what account and their notes.

While we use the term "ultimate" in describing the calculator, we're sure there are things we haven't included or that are not an ideal fit for every type of agency. We've left some things off because it really doesn’t fit our business model; there are certainly other things we simply missed.

But, for the most part, 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 we're always tweaking the calculator.

If you’re ready to use the calculator, grab it here: Ultimate Profit Margin Calculator.

It’s hosted on Google Drive to make it easy for collaboration between you and your team.

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, you'll collect data about your overhead for things like office rent, and software, 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 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 rates 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 and 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 the first row to view the notes.

PPC Clients

The PPC Clients sheet supports the following types of fees:

• Percentage of total spend
• Flat-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 flat-rate management fee of $10,000 and also receive a 2% commission for $1 million in client revenue ($20,000), then 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 simply quote the client a fee, and then 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 quote 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.

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; 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 four 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 ongoing 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, though —or any sheet for that matter — as the calculator will not function correctly if you do.

Final thoughts

Well, that’s it. I truly hope this calculator will help you and/or your agency become more efficient. At Lemonade Stand, we’re firm believers that you can’t manage what you can’t measure. It's my goal that this tool will help you measure the individual impact of each one of of your clients, as well as the overall growth of your business.

Please feel free to share your thoughts, feedback, and suggestions in the comments below.