The key is setting up your structure properly in the beginning.An Excel add-in interacts with objects in Excel by using the Office JavaScript API, which includes two JavaScript object models:Įxcel JavaScript API: These are the application-specific APIs for Excel. Excel will create an additional tab with a recap of the solution:Īnd that’s Solver! It can be used for very complex calculations. Next, click on Answer under Reports and click OK. Note that the Total Labor for A is 2,996, which is 4 less than the Max Labor, while for B it is 3,200, which hit the limit. Click on Solve, and Excel will generate the answer:Įxcel has determined that the most profitable use of these production lines would be 107 for A and 100 for B. You can now see your Objective, the cells with the variables and the constraints. Here is an example of the entry of two of them:Ĭlick Add after the first 3, but click on OK after the last one. We accomplish the entry of these four constraints by clicking on the Add button and entering the appropriate information. For our example, we have four constraints:Ĭell B5 must be an integer – we don’t want to produce any partial productsĬell B8 – Total Labor for Product A – cannot exceed 3,000, which is in cell B4.Ĭell C8 also cannot exceed 3,200, which is in cell C4. Our parameters so far should look like this: We are then asked what cells will be changed to accomplish this? That would be cells $B$5:$C$5, the Qty/Day cells. We then have 3 choices: do we want a Max, Min, or Value Of: and fill in the blank with a value? Our choice will be Max, since we want to maximize our profits (and who wouldn’t!). In the Set Objective area, we want to enter cell $B$12, which is the Max Profit cell. When we click on the Solver button we get this pop-up: We want Excel to tell us what the right quantity is for each line to give us the maximum overall profit. You can see how the selling price, profit and total labor are calculated: I’ve included in Column D the formulas for Column C (Column B’s formulas are similar). Our goal is to use both production lines in a quantity that generates the most profit for our manufacturing plant.
Each also has a Max Labor that indicates the most labor that can be used by that line in a day. Each one produces an item with a material and labor cost. Here is our scenario: We have two product lines. Excel will install the Solver option in your Data tab on the ribbon: Make sure Solver is checked and click OK. If you don’t have the Solver add-in installed go to File=>Options=>Add-Ins and at the bottom click Go next to Manage Excel Add-ins:
Excel’s Solver provides a more elaborate structure for those more complex circumstances. In a previous post we looked at Goal Seek to see how Excel can be used to come up with a solution based on a simple set of criteria. Text version (click on the images for a better view): (Note: Video version above, text version below) Here is how you can use the Solver add-in in Excel to determine the best solutions for your calculations….