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):
- 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.
- 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.
- Enter the formula for revenue (demand times price) in cell B7 by typing =B4*B in the cell and clicking the checkmark icon.
- 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.
