Data Analysis in Excel

One of the primary functions of Microsoft Excel is to record and analyze data. This quick tip will give you a foundation of the different applications and uses for these functions to help you understand your data and make the best decisions for your business.

Download the support worksheet here. Below are equations used in this video:

• (C9) Revenue cell: "=C2*C5"
• (C10) Fixed costs: "=C6"
• (C11) Variable costs: "=C2*(C7)"
• (C13) Net profit: "=C9+C10+C11"

If you would like an alert when the latest video debuts, you can subscribe to our YouTube Channel.

How to perform Data Analysis in Excel video transcript:

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?

For example: Trying to see how much quantity we need to sell in order to break even, or reach a net profit of zero.

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. 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.

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.

Our unit cost, or how much it’s going to cost us to make each water bottle is three dollars seventy-eight cents.

And we know we can charge five dollars per water bottle.

Let’s set up our formulas:

• Revenue equals Quantity multiplied by Unit Price
• Fixed Costs equal negative fixed costs
• Variable Costs equal quantity multiplied by a negative unit cost
• And Net profit equals all three of these cells put together
• We want to reach the break-even point. OR get our net profit of zero

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.

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.

Because we have all these formulas set up and contingent on each other, the calculation will be performed instantly. Click OK.

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. Wow, that’s a lot of water!