Excel Challenge 50: Break-Even Analysis Using Data Tables

Welcome to the 50th GoSkills Excel Challenge! 🎉 This month’s challenge is all about break-even analysis.

Imagine you own a coffee shop and need to find the perfect balance between pricing and sales volume to cover costs and turn a profit.

Your mission? Identify the break-even point using a data table. Along the way, use conditional formatting to highlight profitable and unprofitable scenarios at a glance.

Here’s how to get started:

1. Watch the challenge video for an overview of the scenario.
2. Review the instructions below to understand the tasks — and grab some hints!
3. Download the Excel worksheet and start crafting your solution.

Need some tips? Join our Slack community to exchange ideas, ask questions, and connect with fellow learners.

The challenge 🥷

The challenge file contains two worksheets:

• Challenge, which outlines all the questions you need to answer.
• Data, where you’ll perform your break-even analysis.

Now, let’s go through the tasks:

1. Calculate monthly profit

Using the provided data on:

• Fixed monthly costs
• Variable cost and selling price per coffee cup
• Monthly coffee cup sales

Calculate the monthly profit in cell B7 using an appropriate formula.

2. List possible coffee prices and sales volumes

To analyze different scenarios, create two data ranges:

• Row 11: List possible selling prices from $4 to $8 in $0.25 increments.
• Column A: List sales volume options from 800 to 1,200 cups, increasing by 25 cups each step.

You could enter these values manually, but there’s a faster way… Can you figure it out? (Hint: The Series function might help!)

3. Build a data table to analyze break-even points

Create a two-variable data table to calculate the monthly profit for each price and sales combination. This will allow you to identify the break-even point — where profit equals zero.

4. Highlight break-even and profit/loss scenarios

Apply conditional formatting to make the results easy to interpret:

• Green for profit
• Red for loss

Blank-colored cells indicate the break-even point.

This formatting will help you visually identify which price and sales combinations lead to profitability and which ones fall short.

5. Test your break-even analysis tool

Want to explore different business scenarios with your new break-even analysis tool? Once your data table is set up, try answering these key questions:

1. What is the break-even point? At what price and sales volume does profit reach zero?
2. If we raise the price to $7 per cup but sales drop to 900 cups, will the business still be profitable?
3. If we invest in marketing to sell coffee at $8 per cup, how many cups must we sell to make $1,000 in profit?

The hints 🤩

How can you quickly fill in all possible price and sales volume combinations? It’s time to refresh your knowledge of data tables in Excel! Part of the What-If Analysis toolkit, data tables let you test multiple values at once and see how changes to one or two variables affect the results.

The solution ✅

Did you complete all the tasks and pinpoint your coffee shop’s break-even point? Let’s see how your solution stacks up!

Have a different approach? Excel offers endless ways to solve a problem, and we’d love to see your method! Share your solution in our Slack community, where you can connect with fellow Excel enthusiasts.

Keep the momentum going! Explore previous Excel challenges (now 50 and counting!) or dive into the Resource Hub — packed with tutorials, tips, and tricks to sharpen your skills.

Want to master more Excel tools? Check out the GoSkills Excel course library to level up your expertise in key features like PivotTables, What-If Analysis, and more!