Join Curt Frye for an in-depth discussion in this video Changing parameters by hand, part of Solving Optimization and Scheduling Problems in Excel.
- After you create a working Solver model, you can save your work, make a backup copy of your workbook and use that backup to change your inputs and to summary formulas by hand to see how those changes affect your results. In this movie, I will show you one possible way to do that. The file that I'm using is called Nonlinear_Backup, to reinforce the fact that you should be working with a backup copy of a file, you can find this in the Chapter07 folder of the Exercise Files Collection.
This is the Nonlinear Mixing Problem solution that you can find in Chapter03. I have my input materials here, cost and octane and the number of gallons available. I have the target number of gallons and the inputs for each of the Input Octanes which are listed here. Let's play around with this, change our parameters a little bit and then rerun the solution. We'll be focusing on Total Cost, for this first one, and what I'll do is change the cost of the Octane95 per gallon to $2.75.
I'll click in cell D5 and type 2.75 and press tab or enter. The Total Cost changed but let's rerun the model to see if there's any change in the mixtures. Our Total Cost at the moment is $203,150.00. To rerun the model, I'll click the DATA tab on the ribbon. This is a nonlinear problem so that means I need to check that my Solving Method is Nonlinear. It's been changed since the last time I ran this model so instead of Simplex LP, I'll click GRG Nonlinear and click Solve.
Okay, I got a solution so I'll click OK and it looks like nothing changed. That's not really a mystery, the reason that nothing changed is because the Input Octanes did not change. All that changed was the final cost. Now, let's change one of the values for the Input Octanes. In particular, I'm gonna go to cell B10 and indicate that the Input Octane is actually 84 so in cell B10 I'll type 84 and then press tab.
I've definitely changed the characteristics of a mixture and I'll click Solver on the DATA tab, make sure I have GRG Nonlinear, it's still there, click Solve. I got a result so I'll click OK to clear the Solver Results dialogue box and we can see that the solution has definitely changed. My Total Cost has gone down and the reason for that is that I'm using more of the cheapest octane input which is now 84 and you can see that I've still produced all of my target gallons and my target octanes are on target as well.
That demonstrates how you might change the parameters of a worksheet by hand to get a new result in Solver.
- Finding target values using Goal Seek
- Organizing worksheets
- Finding a solution using Solver
- Solving linear and nonlinear mixture problems
- Solving transportation problems
- Solving resource scheduling or sports scheduling problems
- Analyzing Solver results