Now, we’ll exhibit how we can find out the selling price of a product at the break-even point using the Goal Seek feature of Excel. Previously, we have shown how we can create a break-even chart in Excel.
BREAK EVEN GRAPH SERIES
From the list, select Series “BEP” Y Error Bars and do the same for the Vertical Error Bar.Īfter a bit of furnishing and formatting, the break-even chart looks like the below one.Ĭalculating Selling Price at BEP (Break-Even Point) Using Goal Seek Tool in Excel.Thenceforth, click on the drop-down arrow beside Error Bar Options.In the Error Amount region, select Percentage and set it as 100%.Then, select Minus in the Direction section and No Cap in the End Style section.Presently, advance to the Error Bar Options tab icon.Suddenly, it opens the Format Error Bars task pane.
Then, click on the arrowhead at the right of Error Bars.Initially, click on the plus-shaped Chart Element icon.Now, we’ll relate the axis values to the BEP. Look, how nicely our break-even point accommodated itself in the chart. Additionally, set the marker Size at 8.Here, select the Built-in option and choose the circle marker in the Type list.Then, go to the Marker options like before.Similarly, open the Format Data Series task pane for the newly created series.You can see the newly added series in the dialog box also. Secondly, go to the Break Even worksheet and give the references of cells C10 and C11 in the Series X values and Series Y values box respectively.Firstly, write BEP in the Series name box.Suddenly, the Edit Series input box appears. Then, click on the Add button in the Select Data Source dialog box.From the context menu, Choose the Select Data… option.First and foremost, right-click anywhere inside the plot area.In this step, our task is to anticipate the position of the break-even point in the chart. Step 05: Determine Break-Even Point in the Chart In the Line section, set the Dash type as Long Dash Dot.From the context menu, select Format Data Series….
Firstly, right-click on the line of fixed cost.Our chart looks like the following now: neat and clean.Ĭurrently, we’ll distinguish between the line of fixed cost and variable cost. Later, expand the Fill section and choose No fill from the available options.ĭo the same for other series as well.After that, expand the Marker options and select None.At first, click on the Fill & Line icon.Here, just click on the Format Data Series… option.Now, right-click on the marker on the line of any series just like in the image below.Īs a result, the context menu will appear before us.Immediately, you can see a chart in the sheet that looks like the following. After that, choose Scatter with Smooth Lines and Markers type plot.Then, proceed to the Insert tab and click on the Insert Scatter (X, Y) or Bubble Chartdrop-down icon.Firstly, select the whole table without the Profit column only.It’s the most important part of this article.
BREAK EVEN GRAPH HOW TO
Now, come to the main topic, how to make a break-even chart in Excel. That’s how we got cost and revenue for various numbers of units of the product.
0 kommentar(er)
