Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Breakeven Analysis with Excel Using Goal Seek Method, Lecture notes of MS Microsoft Excel skills

The first thing we're going to do in Excel is set up a table for entering price, demand, unit cost, fixed cost, variable cost, total cost, revenue, and profit.

Typology: Lecture notes

2021/2022

Uploaded on 09/12/2022

pauleen
pauleen 🇬🇧

3.5

(8)

211 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Breakeven Analysis with Excel Using Goal Seek Method
In this tutorial, we are going to use the goal seek method with Excel to solve breakeven
analysis.
Our objective is to use Excel to calculate the breakeven point using the goal seek
method. The first thing we’re going to do in Excel is set up a table for entering price,
demand, unit cost, fixed cost, variable cost, total cost, revenue, and profit.
Below is a table for doing the breakeven analysis using Goal Seek. The table will
include price, the demand, the unit cost, the fixed cost, the revenue, the variable cost,
total cost, and profit.
pf3
pf4
pf5

Partial preview of the text

Download Breakeven Analysis with Excel Using Goal Seek Method and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!

Breakeven Analysis with Excel Using Goal Seek Method

In this tutorial, we are going to use the goal seek method with Excel to solve breakeven analysis.

Our objective is to use Excel to calculate the breakeven point using the goal seek method. The first thing we’re going to do in Excel is set up a table for entering price, demand, unit cost, fixed cost, variable cost, total cost, revenue, and profit.

Below is a table for doing the breakeven analysis using Goal Seek. The table will include price, the demand, the unit cost, the fixed cost, the revenue, the variable cost, total cost, and profit.

To begin, you will need to enter the following formulas in your table (shown below):

  1. Enter the formula for variable cost (demand times unit cost) in cell B8 by typing =B4*B5 in the cell and clicking the checkmark icon.
  2. Enter the formula for total cost (fixed cost plus variable cost) in cell B9 by typing =B6+B8 in the cell and clicking the checkmark icon.
  3. Enter the formula for revenue (demand times price) in cell B7 by typing =B4*B in the cell and clicking the checkmark icon.
  4. Enter the formula for profit (revenue minus total cost) in cell B10 by typing =B7- B9 in the cell and clicking the checkmark icon.

Your table should now look like this:

Next select the profit cell (B10), then click on the Data tab and click What-If Analysis as shown below:

From the What-If Analysis dropdown menu, select Goal Seek.

A pop-up window will appear, as shown here:

Set cell should already be set to your cell for profits (B10).

Now type 0 next to To value and enter $B$4 next to By changing cell. Then click OK twice.