How to do break-even analysis in Excel?
This method will guide you to create a break-even chart easily. Prepare a sales table as below screenshot shown. In our case, we assume the sold units, cost per unit, and fixed costs are fixed, and we need to make the break-even analysis by unit price. Finish the table as below shown: So far, we have finished the source data of break-even chart we will create later. Now a line chart is created.
Please right click the chart, and select Select Data from the context menu. In the Select Data Source dialog box, please: Now in the break-even chart, you will see the break-even point occurs when the price equals to 36 as below screenshot shown: Similarly, you can also create a break-even chart to analyze the break-even point by sold units as below screenshot shown: Do break-even analysis with chart in Excel.
For example, when you finish your break-even analysis in Excel, you may want to make your presentation with this break-even analysis.
You can visit the English version of this link. Please enter the email address for your account. A verification code will be sent to you. Once you have received the verification code, you will be able to choose a new password for your account. Please enter the email address associated with your User account. Your username will be emailed to the email address on file. Forum Get forum support.
User Testimonials Customers say. How to do break-even analysis in Excel? Do break-even analysis with chart in Excel Tip: Combine and Consolidate Multiple Sheets and Workbooks. You are guest Sign Up? The comment will be refreshed after To post as a guest, your comment is unpublished.
How do I use this same formula when the variable cost differs based on quantity sold? Click on "Download" again if prompted to do so in the next window. Locate the template in your downloaded files. Note that it is saved under a file name that includes a string of letters and numbers such as "TSI" for the chart-only Excel template. Click on the file icon or launch Excel to open the template. Locate the rows and columns of cells at the top of the template, below the title and instructions. Type the requested data in the unshaded, empty cells next to their descriptions, such as the price you plan to charge for one unit of your product or service in the cell to the right of the cell labeled "Price per unit;" the number of units in the cell to the right of the cell labeled "Number of units sold;" and your fixed and variable costs in their respective cells.
Keep an eye on the results in the shaded boxes and the bars or lines of your graph, which should change. Look to reduce your costs if the price you need to charge to cover all expenses is higher than the typical price for similar goods or services. Plan to reduce variable costs such as advertising, or look for less expensive ways to deliver your product to your consumer, if you need to lower your break-even price.
Click the "File" menu at the top of your template, then choose "Save as" from the options to save your work. Select the drive and folder you want to save your work in by using the pull-down menu at the top of the "Save as" dialog box that appears after you choose "Save as. Choose "Excel workbook" or "Excel file" in the bottom pull-down menu and click "Save. Change your entered values as necessary and watch the figures in the shaded cells as well as the lines or bars in your graph change.
Save your work again once you have entered your final figures by clicking the "File" menu and then clicking "Save. Type your desired title, such as "Break-Even Chart for XYZ Widgets," in place of the template title and highlight and delete any instruction fields.
Save your work again according to the instructions in Step 7. John DeMerceau is an American expatriate entrepreneur, marketing analyst and Web developer. Skip to main content. Print the chart or send it by Email as need be.