#What is the data analysis tool in excel how to
Now not only do we know how to use the goal seek function, but we also know that with a fixed costs of is thirty-eight thousand four hundred dollars, a unit price of five, and a unit cost of three seventy-eight, we need to sell a quantity of thirty-one thousand four hundred seventy-five water bottles to reach breakeven. 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 It is a very powerful data analysis tool and almost all big and small businesses use Excel in their day to day functioning.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. Click the Data tab in the main Excel interface, and the. 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. After installing the tool, the button to use it is found in the same location as the Solver tool. How to perform Data Analysis in Excel video transcript: In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. If you would like an alert when the latest video debuts, you can subscribe to our YouTube Channel.