Using Goal Seek to Find the Breakeven Point

Homework BE1
Profit = Revenue – Fixed Expenses – Variable Expense
Breakeven Point (BEP) is when Revenue = Expense which is the same as Profit = 0
Before the BEP, the company is losing money (Revenue < Expense).
After the BEP, the company is making money (Revenue > Expense).
Let us work on determining the BEP in units and dollars using GRAPHS and GOAL-SEEK.
Let us assume you have a store that sells TUNA SANDWICHES.
Open a new workbook. On sheet1 – create something like this:
BE1 put your name here

# of units
needed to
breakeven CCCCC <<<< goal seek will determine this

Fixed Cost DDDDD Fixed Cost BBBBB Variable Costs Per Sandwich
Variable Cost per Unit EEEEE Rent 1,000.00 AAAAA
Variable Cost FFFFF Utilities 200.00 bread 0.12
Total Cost GGGGG Helpers 1,200.00 tuna 0.24
Insurance 300.00 wrapper 0.03
Selling Price
per Unit 2.50 napkin 0.01
Revenue HHHHH

Profit JJJJJ We want Profit to be equal Zero
Breakeven
Point in
Dollars KKKKK Profit = Revenue minus Expense

You can be creative and make it look different. As you can see, the store has several fixed costs per month. Each sandwich is the same and has 4 components.
You can use alt-Enter to have a literal appear as two rows in one cell.
You need to enter Excel Formula for
AAAAA – sum of the variable costs
BBBBB – sum of the fixed costs
DDDDD – you can just point to cell BBBBB (example: =E5)
EEEEE – you can just point to cell AAAAA (example: =H5)
FFFFF - # of units times variable cost
GGGGG – add up the fixed cost and the variable cost
HHHH - # of units times selling price per unit JJJJJ – profit is revenue minus expense
KKKKK - # of units times selling price – or – point to HHHHH

Using GOAL-SEEK, determine the value of CCCCC (# of units) where Profit equals 0.
Remember to take screen-shot before running Goal-Seek and paste it into a Word Doc.
My answer is # of units = 1,285.71