Now to break even our net profit needs to equal zero, so Set Cell net profit (C13) To Value of zero by changing the quantity.īecause we have all these formulas set up and contingent on each other, the calculation will be performed instantly. Instead of adjusting the quantity up and down and seeing how it affects our net profit, go to the Data tab, under Data Tools click What if Analysis, and select Goal Seek. We want to reach the break-even point.And Net profit equals all three of these cells put together.Variable Costs equal quantity multiplied by a negative unit cost.Revenue equals Quantity multiplied by Unit Price.Our unit cost, or how much it’s going to cost us to make each water bottle is three dollars seventy-eight cents.Īnd we know we can charge five dollars per water bottle. To start, let’s say we are producing water bottles at the fixed cost of thirty-eight thousand four hundred dollars, meaning no matter how many we produce, we are paying this cost. And we’ll even expand on this understanding to learn how to arrive at several different numbers with the Data Table tool in our next Quick Tip video. We’re going to learn how to save time by quickly arriving at a specific number through the What if Analysis Goal Seek tool in Excel. Have you ever found yourself in excel spending too much time changing one cell in a grouping of formulas just to arrive at a specific result in another cell?įor example: Trying to see how much quantity we need to sell in order to break even, or reach a net profit of zero. How to perform Data Analysis in Excel video transcript: If you would like an alert when the latest video debuts, you can subscribe to our YouTube Channel.

How to get data analysis on excel lab