Excel Solver for Business Decisions
Today in Operations Management we did a case study using the Excel Solver "Add-In." In this example the company sells three products (A, B, and C). An Income Statement calculates the profitability of each product and the goal is to "maximize" profit for the entire company given the constraints on Labor Costs, Materials, and Sales.
Students were required to enter all of the information from the case into the spreadsheet and then configure the Solver parameters in order to come up with a solution. The "Target Cell" is of course, profit. The "Changing Cells" are the "optimum" number of units of Product A, B, and C that will be produced as determined by the Solver. Constraints include how much of each resource is available as well as how many of each product can be sold at best.
Once everything was entered, all that needed to be done was to click the "Solve" button and let Excel find an answer. The model assumes the solution will be "non-negative." Logically, it is not possible to produce "minus" amounts of something. If a product is not deemed profitable given the constraints, then "0" units will be produced.
Running Solver with the original settings gives a "profit" of $21,367 and 600, 40, and 43 units of A, B, and C produced as shown:
After verifying that everything was working correctly, students were then required to achieve a profit of at least one-million dollars. This could only be realized by greatly increasing the amount of resources available and the number of products A, B, and C produced. All of the other amounts on the spreadsheet were "fair game" including the cost of materials and labor as well as the amounts used for each product. The only limitation was that students were not allowed to increase the selling price of each product. This would make it “too easy” in that one could set the price so high as to make a million dollars quite quickly.
Most students realized profits of at least one-million dollars after five to seven iterations. Three of my students, Brad Springer, Jeff McClure, and Reid McDowell decided to see how far they could take the simulation. Each one passed the "quadrillion" dollar mark but only by producing enough of A, B, and C for "everyone in the known universe." It was an interesting exercise in testing Excel's capabilities but as I told them "they better have one terrific salesforce"! At one point we thought we had caused the simulation to "crash" but Excel recovered once we entered more realistic numbers into the spreadsheet. Overall, the exercise went well and students learned how Solver can be applied to common business decisions.
Students were required to enter all of the information from the case into the spreadsheet and then configure the Solver parameters in order to come up with a solution. The "Target Cell" is of course, profit. The "Changing Cells" are the "optimum" number of units of Product A, B, and C that will be produced as determined by the Solver. Constraints include how much of each resource is available as well as how many of each product can be sold at best.
Once everything was entered, all that needed to be done was to click the "Solve" button and let Excel find an answer. The model assumes the solution will be "non-negative." Logically, it is not possible to produce "minus" amounts of something. If a product is not deemed profitable given the constraints, then "0" units will be produced.
Running Solver with the original settings gives a "profit" of $21,367 and 600, 40, and 43 units of A, B, and C produced as shown:
After verifying that everything was working correctly, students were then required to achieve a profit of at least one-million dollars. This could only be realized by greatly increasing the amount of resources available and the number of products A, B, and C produced. All of the other amounts on the spreadsheet were "fair game" including the cost of materials and labor as well as the amounts used for each product. The only limitation was that students were not allowed to increase the selling price of each product. This would make it “too easy” in that one could set the price so high as to make a million dollars quite quickly.
Most students realized profits of at least one-million dollars after five to seven iterations. Three of my students, Brad Springer, Jeff McClure, and Reid McDowell decided to see how far they could take the simulation. Each one passed the "quadrillion" dollar mark but only by producing enough of A, B, and C for "everyone in the known universe." It was an interesting exercise in testing Excel's capabilities but as I told them "they better have one terrific salesforce"! At one point we thought we had caused the simulation to "crash" but Excel recovered once we entered more realistic numbers into the spreadsheet. Overall, the exercise went well and students learned how Solver can be applied to common business decisions.
Comments