Make better decisions about allocating your marketing budget by product to quicken your path to profitability.

Updated April 2020:
Given the immense challenges many small business owners are currently facing, we wanted to help by moving our Cost-Volume-Profit Analysis out from behind the paywall. You can download it here, and below you'll find the complete instructions on how to complete it. Best of luck to everyone.

Cost-Volume-Profit Analysis

Download our FREE Cost-Volume-Profit Analysis worksheet. See how you can become more profitable earlier in the month by allocating your marketing budget differently.

Get Started

Send download link to:

I confirm that I have read and agree to the Privacy Policy.

We promise we won't spam you. We'll only contact you to see if you need help completing your cost-volume-profit analysis worksheet and/or to send you relevant content.

Hello there, fellow small business owners, solopreneurs, and side hustlers! Thanks for downloading our Cost-Volume-Profit Analysis (CVP) worksheet. The worksheet will help you to make better decisions about allocating your marketing budget by product.

First, the details.

  1. The Download Includes:
    The Cost-Volume-Profit Analysis Worksheet, which is a Microsoft Excel file requiring Microsoft Excel 2007 or later
  2. Detailed instructions

The Time to Complete the CVP is:

  • 1 – 4 hours, depending on how many products or services you have and how easy it is for you to gather your fixed and variable costs.

If you work in Google Sheets, a Google Chrome Extension, or Zoho Docs, links for these applications are included in the detailed instructions that were provided with your download.

We're not going to take the time here to go through the Disclaimer and Terms of Use. You can read those in the detailed instructions that were provided with your worksheet. That should make the lawyers happy enough.

And now, on to the good stuff!

OVERVIEW

A Cost-Volume-Profit Analysis or CVP will help you to allocate your marketing budget and show you how to become more profitable earlier in the month.

I know what you’re thinking: “Ugh, math. I started my business to follow my passion, not to spend hours doing mental gymnastics in Excel spreadsheets.”

Don’t worry. The spreadsheet is set up so that most of the math is done for you. While math isn’t always our friend, it does come in handy when it comes to marketing.

These instructions are divided into four sections:

  1. Introduction and Definitions
  2. Items You’ll Need and Examples
  3. How to Complete Your CVP
  4. What It All Means

We'll start with why the CVP is such a valuable marketing tool and define some terms. Then, we'll send you on a treasure hunt to find the data you’ll need and show you some examples. Next, we'll walk you through how to input the data. Finally, we'll wrap it all up by showing you how to best allocate your marketing budget by product.

But first… a word of warning. We're sure you’ve heard the quote, “Nothing worth having comes easily.” The CVP falls into that category. We promise you, though, that while this exercise can take a bit of time, it will be well worth the effort. Given the high failure rate of newly launched ventures, it’s clear that very few of your competitors take the time to do a CVP. Making this effort will give you a competitive advantage and a significant leg up. It will probably also save you a lot of pain and heartache.

Section 1: Introduction and Definitions

What is a Cost-Volume-Profit Analysis and why is it such a useful tool for marketers?

The Cost-Volume-Profit Analysis shows how changes in sales volume, the selling price of products, and the cost paid to produce those products will affect profits. It contains two critical data points that a business owner can use to determine how best to allocate the marketing budget to maximize revenue: Contribution margin and the break-even point.

What is the Contribution Margin?
Before we can define contribution margin, we need to define costs. Business owners incur two types of costs: Fixed and variable.

Fixed costs are expenses that do not change regardless of the number of units produced – they remain the same whether you make 1 or 100 units. For example, if your company prints custom mugs with images supplied by your customers, you need a mug printer whether you receive 1 or 100 orders per month. The monthly payment on the mug printer is a fixed cost. Other examples of fixed costs are rent, utilities, and advertising.

Variable costs are expenses that are incurred only when you produce a product. For the mug printing business, the costs of the mug and ink are incurred only when a mug is printed. (Note that our mug printing business owner would more than likely incur the cost of a case of mugs and a can of ink ahead of the order for one custom mug, but this is a marketing exercise, not an accounting exercise.) Other examples of variable costs are raw materials, labor directly associated with producing a product, shipping materials, and payment processing fees to charge the customer’s credit card.

Let’s assume that the variable cost of printing and selling a 12oz mug is $3.40. That includes the mug, ink, PayPal processing fees, a shipping box, and a portion of the hourly rate of the person operating the mug printer. We’ll sell the printed mug for $12.00.

To calculate the contribution margin, we subtract the variable cost of $3.40 from the selling price of $12.00 to get $8.60. That’s it! That’s the contribution margin of the 12oz mug. Each 12oz mug “contributes” $8.60 to covering the fixed costs.

Contribution Margin = Sale Price – Variable Cost
$8.60 = $12.00 – $3.40

What is the Break-Even Point?
“More math???”, you cry? Yep. But again, we’ll do it for you. The break-even point is the number of units that need to be sold to cover our fixed costs.

Let’s say our mug printing business has fixed costs (rent, a loan payment on the mug printer, the website, phone service, Google Adwords, etc.) of $1,146.00 per month. To calculate the break-even point, we divide the fixed costs of $1,146.00 by the contribution margin of $8.60 and… Voila! The number of mugs that must be sold each month to break-even is 134.

Break-Even Point = Fixed Costs/Contribution Margin
134 = $1,146.00/$8.60

Now we know what portion of the sale of a 12oz mug covers our fixed costs AND we know how many units we need to sell each month to cover those costs.

Now it’s time to gather some info and enter some data.

Section 2:
What You’ll Need to Complete Your Cost-Volume-Profit Analysis

Time to start the treasure hunt! Here are the four items you’ll need to gather:

  1. The names and selling prices of your products
  2. The total of your monthly fixed costs
  3. Historical product sales data
  4. The total variable cost for each product

Now, we tend to say “products” rather than the more inclusive “products or services.” That’s just because it’s faster, not because we have anything against services. This worksheet will work for you if you sell products and/or services.

We suspect (1) names and selling prices will be easy to find. You probably don’t even need to look them up.

Your (2) fixed costs can be found in a few places – your accounting software whether you use QuickBooks, Xero, FreshBooks, Wave, or an Excel spreadsheet, or your tax return depending on the legal structure of your business.

Your (3) historical product sales data is the number of each product sold for a specific period and it is needed to calculate your “Product Sales Mix.”

Here we are, springing a new term on you late in the game. Sorry about that. But this one is easy. Product sales mix is the percentage each product represents of your total sales. The easiest way to think about product sales mix is this way: “Out of every 100 products I sell, X are Product A, X are Product B, X are Product C”, and so on.

Historical product sales data can also be found in your accounting software. You can use sales data from the past year or you can average the past several years if your sales have grown rapidly or have been inconsistent year-over-year. If you’re just starting out, you’ll need to come up with an estimate based on the research you’ve done.

To calculate your product sales mix, divide the number of units sold of each product by the total number of units sold of all products. For example, let’s say that last year you sold 60 of Product A, 20 of Product B, and 20 of Product C for a total of 60%, 20%, and 20%, respectively.

ProductUnits SoldProduct Sales Mix
Product A6060.00%
Product B2020.00%
Product C2020.00%
TOTAL100100.00%

Product Sales Mix = Units Sold Product A/Units Sold All Products
60.00% = 60/100

Product Sales Mix = Units Sold Product B/Units Sold All Products
20.00% = 20/100

Product Sales Mix = Units Sold Product C/Units Sold All Products
20.00% = 20/100

See? We told you that would be easy.

Now comes what might be the hard part. The (4) variable cost for each product might not be as easy to grab quickly. In fact, if you’re not already tracking variable costs by product you’ll need to do that before you can complete this worksheet.

Your accounting software tracks the items you buy to produce an item for sale as “Cost of Goods Sold” (COGS). None of the above-mentioned software tracks these costs by product. There are third-party extensions that you can buy, but not many people want to spend the dough.

How do you break down your COGS into variable costs by product? You divide the cost you pay for the case or the can or the cartridge by the number of units you’re able to produce from it.

Using the 12oz mug as an example, our COGS break out into a variable cost of $3.40 per mug as follows:

ItemCost per/Units perCost per Unit Sold
Case of 12oz Mugs$18.00/case12$1.50
12oz Can of Black Ink$6.00/can48$.13
Direct Labor$20/hour30$.67
Shipping Carton$7.00/case50$.14
Bubble Bag$94.00/case500$.19
Address Label$30.00/case600$.06
Ink to Print Label$10.00/cartridge200$.05
PayPal Fees$.66
TOTAL$3.40

Cost per Unit Sold = Cost per/Units per
$1.50 = $18.00/12

Depending on the number of products you sell, calculating your variable costs by product is the most time-consuming part of preparing your CVP. Once you have all variable costs, you’re ready to enter data into the worksheet.

Section 3: Entering Data

When you open the spreadsheet, you’ll see the following cells are highlighted in yellow and contain maroon text:

  • “Fixed Costs” (C7)
  • “Product Name” (B15-24)
  • “Sell Price” (C15-24)
  • “Variable Cost” (D15-24)
  • “Product Sales Mix” (E15-24)

These are the cells into which you’ll enter data. All other cells are locked so you can’t make a mistake entering data into a cell that has a formula.

  1. First, enter your “Fixed Costs” in (C7). In our example, we’ll use $1,146.00 per month. (Ignore the negative ($1,146.00) in “Break-Even/Profit/(Loss)” in (C12) that pops up. That will resolve as other data is entered.)
  2. In the “Product Name” column (B15-24), replace “Product A”, “Product B” and so on with the names of your products. In our mug printing business example, we'll replace “Product A” with “12oz Mug.”
  3. In the “Sell Price” column (C15-24), enter the price at which you sell your products. We'll add “$12.00” for the 12oz mug.
  4. In the “Variable Cost” column (D15-24), enter the variable costs for each product that you so painstakingly calculated in the last section. We'll enter “$3.40” for the 12oz mug.
  5. Finally, in the “Product Sales Mix” column (E15-24), enter the percentage that each product represents of your total sales. For now, we'll enter 100% for the 12oz mug, since that’s the only product we sell.

Note that the numbers in the “Contribution Margin” (F15-24), “Contribution Ratio” (G15-24), and “Break-Even Units” (H15-24) columns have been calculated automatically, as have the numbers in the “Break-Even Revenue” (C9), “Break-Even Variable Costs” (C10), and “Break-Even Contribution Margin” (C11) as soon as the “Product Sales Mix” percentages were entered.

So, what is this data telling us?

  1. In the “Contribution Margin” column (F15-24), we have $8.60. Remember from earlier in our conversation that means $8.60 of every $12.00 is available to contribute to the fixed costs.
  2. The “Contribution Ratio” (G15-24) is the “Contribution Margin” expressed as a percentage. It means that almost $.72 of every dollar is available to contribute to the fixed costs.
  3. The “Break-Even Units” (H15-24) is the number of units that must be sold every month to cover our fixed costs. In our example, if we only sell 12oz mugs, we must sell 134 each month to break even.
  4. In the top section, the “Break-Even Revenue” (C9) is the “Break-Even Units” (H15-24) expressed as a dollar amount. Some people prefer to know how many units they need to sell and others prefer to know what revenue they must achieve, so we've included both.
  5. The “Break-Even Variable Costs” (C10) is the amount of money you’ll need to spend on variable costs each month to produce the units required to break even. Our mug printing business needs to shell out $455.60 every month in variable costs just to break-even. (Hint, hint, hint: Remember this number when it comes to managing your cash flow.)
  6. In a perfect world, the “Break-Even Contribution Margin” (C11) would equal the “Fixed Costs” (C7) and the “Break-Even/Profit/(Loss)” (C12) line would be $0, not $6.40. But costs and units don’t always divide evenly into one another, so the “Break-Even Units” (H15-24) for each product has been rounded up to the nearest whole number.

This is the point at which a lot of small business owners and side hustlers see the break-even number of units and think, “Hmmm. No wonder I’m not making any money.” If this is you, don't worry. It happens to a lot of folks. Take this opportunity to learn and make adjustments.

Back to data entry. Most businesses don’t sell just one product, though. So, let’s expand our example and add a 15oz mug and an iPhone case. Remember that the break-even units for just the 12oz mug was 134.

Our 15oz mug sells for $15.00 and has a variable cost of $3.80. The iPhone case sells for $9.00 and has a variable cost of $4.40.

Reviewing historical sales data, we discover that our mug printing business sold 60 12oz mugs, 20 15oz mugs, and 20 iPhone cases last month for a product sales mix of 60%, 20%, 20%. In other words, for every three 12oz mugs sold, one 15oz mug and one iPhone case are sold.

The total “Product Sales Mix” (E25) must equal 100%. If it does not equal 100%, the background color and the text will be red. If this happens, correct your percentages so they equal 100%.

You can see now that our break-even units has changed from 134 12oz mugs to:

  • 83 12oz mugs
  • 28 15oz mugs
  • 28 iPhone cases

for a total of 139 units. With this product sales mix, we need to sell five more units each month to break-even than if we were just selling 12oz mugs.

You can sort the data by column. For example, if you’d like to sort “Contribution Ratio” from highest to lowest, click the arrow and choose “Descending.” You can also see the data in graphical form in Section 2: Data Visualization.

But let’s take a closer look at the “Contribution Ratio” column. The 12oz mug and the 15oz mug are contributing almost 72% and 75%, respectively, to the fixed costs.

Remember, that means almost $.72 and $.75 of every dollar in sales generated goes toward covering the fixed costs. The iPhone case only contributes $.51 of every dollar toward the fixed costs.

The higher the contribution ratio, the more “productive” a product is.

What if we could sell more 15oz mugs? We’d probably have to sell fewer units to break-even each month AND we’d probably break-even sooner.

By stalking our competitors online and talking with mug suppliers at trade shows, we’ve found that the industry standard sales ratio for 12oz mugs to 15oz mugs is 2:1. But our ratio is 3:1. What would we need to do to get our ratio to 2:1?

We only have one mug printer, so printing more 15oz mugs means we’ll need to print fewer of one of our other products. (Remember, our product sales mix must equal 100%.) Because the contribution margin of the iPhone cases isn’t as high as the mugs, we decide that the best course of action is to sell fewer iPhone cases.

To get the 12oz mug to 15oz mug ratio to 2:1, we’ll see what happens if we can increase the sales mix of the 15oz mug to 30% and reduce the iPhone case to 10%.

Now, the total number of break-even units is 129 vs 139. If we sell 10 units a day, that means we’ll break-even one full day sooner than we would if we had kept the “Product Sales Mix” at 60/20/20.

Assuming a 20-day work month (we know, we know – what small business owner or side hustler works only 20 days a month) but assuming a 20-day work month, that means we're breaking even on day 13 rather than day 14.

That’s seven days a month we're working for profit. Yay! Profit!

Break-Even Work Days = Break-Even Units/Units Sold Per Day
13 = 129/10

With this new product mix:

  • we’re breaking even sooner
  • we’re profitable for more days each month
  • we need to sell fewer units
  • there’s less wear and tear on the equipment
  • we pay fewer processing fees
  • there are fewer shipping cartons to buy
  • we lower direct labor costs
  • we experience less stress

We could go on and on, but we think you see the benefits.

Finally, a note about line 25, “Weighted Average and Totals”. "Average Sell Price” and “Variable Cost” are the average price for which we sell an item and the average variable cost we pay to produce an item. If we sell more than one product, and the “Sell Price”, “Variable Cost”, and “Product Sales Mix” are not the same for each product, there might be times when we need to use a weighted average.

In other words, we can’t just take the sell prices of $12.00, $15.00, and $9.00, add them together and divide by three. We need to “weight” the average based on the product sales mix.

Section 4:
Allocating the Marketing Budget

So, you ask, how will all of this information help you to allocate your marketing budget? Well, you now know that in our example the mugs contribute a larger percentage to the fixed costs each month than the iPhone case. And, you know that by changing the product sales mix our hypothetical business owner will break-even sooner and need to sell fewer products.

The first step is to set a goal:
In our example, our goal will be to increase the sale of 15oz mugs by 10% (or 11 mugs) per month.

Since you probably found this worksheet in a search related to allocating your marketing budget, we’ll assume you already have a marketing budget in place and that you are spending at least some of it on generating sales (vs branding) each month. To allocate by product, adjust the amount you spend on each product according to the changes you’ve made in your product sales mix.

If you’re rotating products in monthly campaigns, adjust the rotation so the concentration is higher for the new product mix. For example, if our mug printer wants to be bold, she could stop advertising the iPhone cases completely for a bit and put that money into advertising the 15oz mugs.

The ideal allocation is the one that gets you the highest number of sales for the least amount of money. Obvious, right? Yet not so easy to determine. Test your new allocation over the course of several months and track the results to see how it does. If it doesn’t do what you need it to do, change the allocation. Test and track, test and track, test and track!

The important thing is to make your decision based on data, not on a guess and a whim.

Whew! There’s a lot to unpack here. If it takes several days or even a couple of weeks for you to feel like you have a handle on the CVP, that’s ok. It contains a lot of information. But it will help you to allocate your marketing budget and quicken your path to profitability.

If you haven’t already, download the Cost-Volume-Profit Analysis worksheet to get started. If you have questions about completing your CVP or any other aspect of your marketing strategy, feel free to contact us.

Cost-Volume-Profit Analysis

Download our FREE Cost-Volume-Profit Analysis worksheet. See how you can become more profitable earlier in the month by allocating your marketing budget differently.

Get Started

Send download link to:

I confirm that I have read and agree to the Privacy Policy.

We promise we won't spam you. We'll only contact you to see if you need help completing your cost-volume-profit analysis worksheet and/or to send you relevant content.

Leave a Reply