We can now create an Excel graph of the Actual Sales vs. If we now hit the Solve button, we get the following result: Solver has optimized the Decision Variables to minimize the objective function as follows: Here is a close-up of the Solver Objective, Decision Variables, and Constraints: Here is the completed Solver dialogue box: Our sales equation appears to be smooth and non-linear: These functions have graphs that are curved (nonlinear), but have no breaks (smooth) Examples of smooth nonlinear functions in Excel are: The GRG Nonlinear method is used when the equation producing the objective is not linear but is smooth (continuous). Selection of Solving Method: GRG Nonlinear There are none for this curve-fitting operation. The Decision Variables are therefore Cells B3 to B5. We are changing A1, A2, and B1 (cells B3 to B5) to minimize our Objective, Cell G13. We are trying to minimize Cell G13, the sum of the square of differences between the actual and predicted sales. Once again, here is the data table for Solver inputs: This is the target cell that we are either trying to maximize, minimize, or achieve a certain value.Ģ) Minimize or Maximize the Target, or attempt to achieve a certain value in the Objective cell.ģ) Decision Variables – A set of variables that will be changed by the Excel Solver in order to optimize the target cell.Ĥ) Constraints – These are the limitations that the problem subjects the Solver to during its calculations The Solver dialogue box has the following 4 parameters that need to be set: The following blank Solver dialogue box comes up: Options / Add-Ins / Manage / Excel Add-Ins / Go / Solver Add-In), you can access the Solver in Excel 2010 by: Data / Solver. Once the Solver has been installed as an add-in (To add-in Solver: File / In other words, we are trying to find A1, A2, and B1 that will minimize the number in cell G13. We are trying to find the settings for the Decision Variables that will minimize the sum of the squares of the differences. The square of each difference is taken and then all squares are summed up. We now take the difference between the actual number of sales and the number of sales predicted by our model with our arbitrary settings for the Decision Variables. We have arbitrarily set our Decision Variables for: In Solver language, these solves that we are changing are called Decision Variables. We are listing the variable that we are solving for (A1, A2, and B1) in cells B3 to B5. One example of this formula in action is explained for Cell E16. Here we have created an Excel model based upon our model of: This table shows the arrangement of data and the calculations.
We need to arrange the data in a form that can be input into the Excel Solver as follows: We can use the Excel Solver to solve for A1, A2, and B1. Sales = A1 + A2 * (Number of Ads Running) B1 A formula for such a curve would have the general form: In this case it appears to a graph the has a diminishing y value for an increasing x value. The first step is to eyeball the data and estimate what general type of curve this graph probably is. We would like to create an equation from this data that allows us to predict the sales based upon the number of ads currently running.
Here is an Excel scatter plot of that data: the number of ads that were running at different times. The purpose of this equation is to be able to predict the number of sales based upon the number of ads that will be run.Ī marketing manager has collected this following data on the company’s sales vs.
In this problem we are going to show how to use the Excel Solver to calculate an equation which most closely describes the relationship between sales and number of ads being run. Solver then calculates all needed variables which produce the equation which most closely fits the data points. This information is in the form of the general equation that defines the curve, such as a0 + a1*x + a2*x2 = c or a*ln(xb) = c. One very important caveat must be added: the user must first determine the general type of the curve and input that information into Solver at the start. The Excel Solver will find the equation of the linear or nonlinear curve which most closely fits a set of data points. Its curve-fitting capabilities make it an excellent tool to perform nonlinear regression. Nonlinear Regression in Excel How To Do Nonlinear Regression in ExcelĮxcel Solver is one of the best and easiest curve-fitting devices in the world, if you know how to use it.