When done, close browser tab or window.
Start video.
Narrator: "Within Microsoft Excel, there's actually a number of functions that you can add in to the program. One of these add-ins is something referred to as Solver."
The video opens to display a Microsoft Excel spreadsheet with two tables, one with the title ABC Company and the other with the title Contribution Format Income Statement. The video describes the steps to use the solver tool in Microsoft Excel. The Excel file has two spreadsheets named Solver and CVP Analysis. The narrator has selected the spreadsheet CVP Analysis to display the two tables. Table 1 is displayed as follows:
| Data Inputs: | |
| Unit sales | 40,000 |
| Selling price per unit | $ 10.50 |
| Variable expense per unit | $ 5.50 |
| Total fixed expenses | $ 180,000 |
Table 2 is displayed as follows:
| Total | |
|---|---|
| Sales | $ 420,000 |
| Variable expenses | 220,000, single underlined |
| Contribution margin | 200,000 |
| Fixed expenses | 180,000, single underlined |
| Net operating income | $ 20,000, double underlined |
The Excel sheet displays the tabs for File, Home, Insert, Page Layout, Formulas, Data, Review, View, Developer, and Help, among others, on the top row of the screen. The narrator has selected the Data tab, which displays a second row of tabs that include Get and Transform Data, Queries and Connections, Data Types, Sort and Filter, Data Tools, Forecast, et cetera.
Narrator: "So, before we get going with this tutorial, I want to show you how to add an add-in, in case you've never done it before. The first step is to go to File within the Excel file. Then, in the bottom left-hand corner, you're going to find the button for Options. Go ahead and click that. Then, you see your Excel options here. And then, down the left-hand side, close to the bottom is something called Add-Ins. You'd click there. You're going to see all the add-ins that are available here. The ones we're worried about are the ones that come built into Excel, so the Excel add-ins. So, go down to the bottom where it says Manage Excel Add-ins and click Go. And you're typically going to see four options here - Analysis ToolPak, Analysis ToolPak VBA, Euro Currency Tools, and Solver Add-in. You might have different variations of this, but the key is to make sure that the Solver add-in has a check in the box next to it. Mine already has, because I've already installed it. So make sure that this has a check in it, and click on OK. To get to Solver, you're actually going to go to the Data ribbon and click on the far right-hand corner. And in the Analyze section, you're going to see Solver. Okay?"
The narrator clicks on the File tab to display the home page where, on the left side, the tabs to open folders, save, print, share, export, or publish files, Account, Feedback, and options are listed. The narrator clicks on the Options tab to open the Excel Options window that lists tabs for General, Formulas, Data, Proofing, Add-ins, et cetera on the left side, with more options listed on the right side, to customize the Excel using each tab selection. Clicking on Add-ins displays the list of Add-in tools inserted to work in Microsoft Excel files. The window has the Manage button with a drop-down list on the bottom left with a Go button next to it, and OK and Cancel buttons on the bottom right corner. Once the manage option is selected for Excel Add-ins from the drop-down list, the narrator clicks on the button Go. This displays a small pop-up window listing the Add-ins available that includes Analysis ToolPak, Analysis ToolPak VBA, Euro Currency Tools, and Solver Add-in. Analysis ToolPak and Solver Add-in options are selected and the OK button next to it is clicked to close the window. The Solver tool can be located under the Data ribbon, in the Analyze section located in the right corner above the spreadsheet.
Narrator: "So, again, in your problem, you're going to have a tab that shows you a step-by-step of how Solver works. And while it looks kind of complicated, it really is almost as simple as Goal Seek."
The narrator now clicks the Solver spreadsheet to display the following textual information: Solver: Solver is a tool within Microsoft Excel that is similar to "Goal Seek" as it performs a "what-if analysis" that enables you to learn what input values would be needed to achieve a desired goal or outcome while also implementing designated constraints or limits on the values. Solver itself is a Microsoft Excel add-in, therefore before you can perform the function, you need to make sure the add-in is enabled. To do that follow the below steps:
Once you've ensured that Solver has been enabled you should proceed as follows:
Narrator: "So, Solver is a lot like Goal Seek. It performs a what-if analysis that enables you to learn what input values would be needed to achieve a desired goal or outcome while also implementing designated constraints or limits on the values. So, said another way, it turns Excel into a decision-support system and is a little bit more sophisticated than Goal Seek itself. So, with this tutorial, we're going to walk through ABC Company and a very simple format of their contribution format income statement. So, here we have the number of units they've sold, their selling price per unit, their variable expense per unit, and their total fixed expenses. If we carry it down here, we have our contribution format income statement. We have sales, which are just a function of the unit sales times the selling price per unit, and our variable expenses, which are just the unit sales times the variable expense per unit, to get to our contribution margin, which is just our sales minus our variable expenses. Then, we have our fixed expenses, brought directly down, and then, finally, our net operating income, or just the contribution profit we have here. Okay?"
The narrator displays the tables again by clicking the CVP Analysis spreadsheet. The narrator selects the data in cells of the table as he mentions them. The narrator demonstrates that the Sales in table 2 Contribution Format Income Statement is the value derived from the Unit sales multiplied by the Selling price per unit in table 1 ABC Company. Similarly, the Variable expenses in table 2 are the value derived from the Unit Sales multiplied by the Variable expense per unit in table 1. The contribution margin in table 2 is the value derived from subtracting the Variable expenses from Sales in the same table. The fixed expenses in table 2 are the same as the total fixed expenses in table 1. The net operating income in table 2 is the value derived from subtracting the Fixed expenses from the Contribution margin in the same table.
Narrator: "So, right now, this company has a net operating income of 20,000. Well, we can look at Solver to find out what unit sales would be needed to break even. Or, said another way, how do we make sure that we're not losing any money? So, how can we get this operating income down to zero with just unit sales, to make sure that that's our goal, that we don't lose money? So, to do this, go to the Data ribbon, as I mentioned before. Go over to the Analyze section, then click on Solver. So, you're going to see this Solver Parameters window come up. It's going to work a lot like Goal Seek. We're basically going to tell it to set a certain value to a certain value by changing a different cell."
To use the Solver tool, the narrator clicks on the Data ribbon, Analyze section, and then Solver. A prompt window named Solver Parameters appears on the screen having options to Set Objective, To select the maximum, minimum, or value of, By changing Variable Cells, Subject to the constraints, Select a solving method, along with buttons to Add, Change, Delete, Reset all, and Load or Save the options. The buttons for Help, Solve, and Close is located at the bottom of the window.
Narrator: "So, here, our set objective - what do we want to change? Well, here, we want to change our net operating income. So, you can see, it doesn't absolutely reference to C-15 when I click on that cell. So, a net operating income of $20,000 right now. We want to change that cell to the value of zero. So, we want to turn the net operating income down to zero. Here, you can change it to the max, saying, "Using certain constraints, get me to the biggest I can, or the smallest I can." But, right now, we want to set the objective of the net operating income to zero by changing variable cells. We want to change here the cells and units. So, C-4. And that's all we have to really do here. If you have certain constraints - for example, let's say that we can't sell more than 35,000, we could add a constraint here, saying that this cell has to be less than or equal to 35,000. So, maybe that's something that we're adding in there. You click Add to add a constraint, then click OK when you're done. Okay? So, now we have a constraint added in there. We want to make sure that we leave this box checked to make unconstrained variables non-negative. And then we're going to leave this at the GRG Nonlinear. Then, we're going to go ahead and click Solve. The difference between Goal Seek, is you're going to have actually a window that pops up that says, "Solver could not find a feasible solution." That's because of the parameter that we added. So we're going to go ahead and click OK here. And you can see that brought our unit sales down to the constraint that we added in. However, with that, we're at negative 5,000."
The Set Objective option is set to the cell number bearing the Net operating income in table 2. Next, the value is set to zero. By changing variable cells option is set to the cell number bearing the Unit Sales in table 1. The narrator demonstrates steps to add a constraint by clicking on the Add button. This pops up a prompt window where the cell value for which the constraint has to be added is provided, followed by a drop-down list to select the function such as less than or equal to is set. This is followed by adding the constraint value, such as 35000 in this case. The narrator clicks on the Add button and then the OK button. A warning window pops up with the message Cell Reference box is empty or contents are not valid. The narrator clicks on the OK button to close the window. This takes us back to the Solver Parameters window where the constraint is added. The option Make Unconstrained Variables Non-Negative is selected, and solving method is set to GRG nonlinear. Once all options are set, the Solve button is clicked which changes the values in the tables. Table 1 is displayed as follows:
| Data Inputs: | |
| Unit sales | 35,000 |
| Selling price per unit | $ 10.50 |
| Variable expense per unit | $ 5.50 |
| Total fixed expenses | $ 180,000 |
Table 2 is displayed as follows:
| Total | |
|---|---|
| Sales | $ 367,500 |
| Variable expenses | 192,500, single underlined |
| Contribution margin | 175,000 |
| Fixed expenses | 180,000, single underlined |
| Net operating income | $ (5,000), double underlined |
A Solver Results window is displayed which displays the message Solver could not find a feasible solution. Solver cannot find a point for which all Constraints are satisfied. There are other options displayed in the window such as Keep Solver Solution and Restore Original Values, Return to Solver Parameters Dialog, Outline Reports, et cetera. The narrator clicks on the OK button to close the window.
Narrator: "So, let's go back into Solver. Leave everything the same as we had it before. Our objective is operating income to zero, by changing the sales. But now let's delete this constraint. And now we're going to click Solve. Okay? Solver found a solution. So, we click OK here. You'll see that Solver found a solution. "All constraints and optimality conditions are satisfied." So you can restore the original values to where they were before, or keep the Solver solution that it found. So, we click on OK here. We can now see that our unit sales of 36,000 are how we achieve a net operating income of zero - otherwise known as a break-even. We could change this in multiple ways to modify it to have any target that we want or set any constraints that we want. Again, Solver is just a more sophisticated form of Goal Seek. It helps us when we have constraints such as machine- or man-hours. Thank you for watching this tutorial."
The narrator clicks on the Solver tab again to display the Solver Parameters window with options as described earlier. The constraint that was added is deleted by clicking on the Delete button. The Solve button is clicked which displays the Solver Results window displaying the message Solver found a solution. All constraints and optimality conditions are satisfied. Other options visible in the window are as described earlier. When the OK button is clicked, the table data is updated. Table 1 is displayed as follows:
| Data Inputs: | |
| Unit sales | 36,000 |
| Selling price per unit | $ 10.50 |
| Variable expense per unit | $ 5.50 |
| Total fixed expenses | $ 180,000 |
Table 2 is displayed as follows:
| Total | |
|---|---|
| Sales | $ 378,000 |
| Variable expenses | 198,000, single underlined |
| Contribution margin | 180,000 |
| Fixed expenses | 180,000, single underlined |
| Net operating income | $ hyphen, double underlined |
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.