 Hello, and welcome to this session in which we would look at the what if analysis in Excel, I would say the powerful what if analysis in Excel specifically illustrating the gold seek feature. This topic is covered in introduction to data analytics and accounting or simply a data analytics course, auditing you can use this as well as managerial accounting. As always, I would like to remind you to connect with me only then if you haven't done so, YouTube is where you would need to subscribe. I have 1,700 plus accounting, auditing, finance and tax lectures as well as Excel tutorial. This is a list of all the courses that I cover. If you like my recording, please like them, share them, subscribe to the channel. If it helps you, it means it might help other people. So share the wealth, connect with me on Instagram and on my website farhatlectures.com. You will find additional resources to supplement your CPA exam, your CMA exam, your enrolled agent exam, as well as all your accounting course. So to illustrate this concept, I'm going to be using this toy which is my son's favorite toy. His name is Roy and let's assume I can manufacture Roy and sell it to retailers for $10 a piece. Right now I have orders of 3,000 units that I can sell. My variable cost is $5.75. Variable cost means to manufacture this unit. I need the plastic pieces, the screws, this black piece here. So I need to buy them and altogether they cost me $5.75. In addition to that, I need machinery in a place to operate which cannot be a fixed cost, a cost of $140,000 if I went into this investment. So the question is how many units? Let's assume I want to know how many units I need to sell to breakeven. And hopefully you guys remember this concept from the breakeven analysis. If not, remember the formula we have the selling price minus variable cost equal to the contribution margin minus the fixed cost equal to the net profit or net income. So we're going to be using this formula. We have the selling price, we have the variable cost, we could figure out the contribution margin, we have the fixed cost. Anyway, you don't have to worry about this just in case you remember in accounting, if not, go to my cost and managerial accounting course. So first let's compute my variable cost. So my variable cost is how many units I sell times the variable cost per unit. So here I'm assuming 3000 unit and it's going to be $5.75 variable cost. Now my fixed cost is 140. So those are my cost, my variable and my fixed. Now I can find my total cost. Well, my total cost is my variable plus my fixed. Those are my total cost B5 plus B6. That's my total cost. And my revenue is how many units I can sell times the selling price. Those are the formulas and my profit or net income or net loss is my sales revenue minus my total cost, which is a negative 127,250. So obviously I should not go into this business because there is no way with selling 3000 unit I can make any profit. Actually, I am at a loss. Now what I want to find out is how many units do I need to sell to breakeven. So simply put, I'm looking for the breakeven point. In other words, the breakeven point, when is my net income equal to zero? That's your breakeven point. And let's assume I cannot change anything from my cost. So my fixed cost is fixed, my variable cost is variable. What I can change is my unit. So what do I do? How do I run this analysis to find out how many units I need to sell? Well, I can go to this feature in Excel called data, what if analysis and goal seek and click on goal seek. It's going to tell me which cell do you want to set. So what answer are you looking for? What I'm looking for this cell, cell B9 already highlighted. So notice I already highlighted cell B10 and I want the value in this to be zero. Zero means I make no profit, I make no profit and I have no loss. By changing which cell? Well, I can only control the units that I can sell. If I increase my units, how many units do I need to increase to make sure my net income equal to zero? So I click on, by changing cell B3 and I click on OK. And it's going to perform the computation. And it seems I need to sell 32,941 units. Let's make it 42 because I cannot sell 41.18. So it seems I need to sell approximately 34,000, 32,942,000 units in order to break even. Now I can run the math for you through this form, through the calculation, like I can run the math here, but I want, you can prove it for yourself if you want to. Now let's assume I cannot sell those 32,000 units. So what I can do is I will try to change, let's assume I can sell only 25,000 units. If I can sell only 25,000 units, I am at a loss of 33,750. Now what can I do? Well, maybe I can buy different pieces rather than my supplier right now is shipping the product, shipping the pieces at $5.75. Maybe I will try to find another supplier at a cheaper rate. So at what rate do I need to do so? At what price can I function and have a no profit and no loss? I'm going to go back to data, what if analysis, goal seek. Again, I'm going to set my goal to zero, my B9 to zero because I just want to see at what point I break even by changing the sell, by changing my variable cost. So let's assume, let's assume if I can find a supplier, if I can only sell 25,000 units, what supplier I can find that will give me no profit and no loss. I need someone that's willing to sell me the variable units, the variable cost at $4 and 40 cents. Well, let's assume I can't find anyone. Now maybe I can do something about my fixed cost. If I can sell 25,000 units, can I cut out, reduce some of my fixed cost? I will do the same thing. I go to the goal seek, I go to goal seek, set my income to zero and changing the fixed cost. So how much do I need to reduce my fixed cost? I would need to reduce my fixed cost to 106,250. Now again, the last scenario I'm going to be doing is, can I raise my selling price from $10 or something else to break even? Well, I can do what if goal seek, set my profit to zero by changing my selling price. Well, if I can charge $11.35, selling 25,000 units, I can break even. Now bear in mind, let me go back to 25,000 units. Now bear in mind, it doesn't have to be break even. I can set the value, for example, if I want to make $55,000 in profit by changing, then I can change something else. I can change my selling price, I can change my variable cost, I'm sorry, variable cost per unit, I can change anything. Okay, if I want to make 55,000 in profit, how much do I need to get my variable cost down to? It's going to be down to $2.20. I will get this to 55,000, so on and so forth. So this is how it works in the goal seek. So notice, you can play with this and use simple scenario first, something that you could relate to, then you could use this in your own work to use this powerful tool to help you make the session. As always, I would like to remind you to like this recording and visit my website, farhatlectures.com, especially if you're an accounting student looking for additional resources to pass your certification, pass your accounting courses. Education is a lifetime investment. Make sure you invest in your education wisely, because it's going to pay dividend for years. Good luck and stay safe, especially these those coronavirus days.