The spreadsheet must be prepared with data and formulas for certain calculations before Solver can be used. Excel QM can be used to simplify this process (see Appendix F). We will briefly describe the steps, and further discussion and suggestions will be provided when the Flair Furniture example is presented. Here is a summary of the steps to prepare the spreadsheet:

1. Enter the problem data. The problem data consist of the coefficients of the objective function and the constraints, plus the RHS value for each of the constraints. It is best to organize this in a logical and meaningful way. The coefficients will be used when writing formulas in steps 3 and 4, and the RHS will be entered into Solver.

2. Designate specific cells for the values of the decision variables. Later, these cell addresses will be input into Solver.

3. Write a formula to calculate the value of the objective function, using the coefficients for the objective function (from step 1) that you have entered and the cells containing the values of the decision variables (from step 2). Later, this cell address will be input into Solver.

4. Write a formula to calculate the value of the LHS of each constraint, using the coefficients for the constraints (from step 1) that you have entered and the cells containing the values of the decision variables (from step 2). Later, these cell addresses and the cell addresses for the corresponding RHS values will be input into Solver.

These four steps must be completed in some way with all linear programs in Excel. Additional information may be put into the spreadsheet for clarification purposes. Let’s illustrate these with an example.

1. Program 7.2A provides the input data for the Flair Furniture example. You should enter the numbers in the cells shown. The words can be any description that you choose. The “$\le$” symbols are entered for reference only. They are not specifically used by Excel Solver.

2. The cells designated for the values of the variables are B4 for T (tables) and C4 for C (chairs). These cell addresses will be entered into Solver in the By Changing Variables Cells box. Solver will change the values in these cells to find the optimal solution. It is sometimes helpful to enter a 1 in each of these to help identify any obvious errors when the formulas for the objective and the constraints are written.

3. A formula is written in Excel for the objective function (D5), and this is displayed in Program 7.2B. The Sumproduct function is very helpful, although there are other ways to write this. This cell address will be entered into Solver in the Set Objective box.

4. The hours used in carpentry (the LHS of the carpentry constraint) are calculated with the formula in cell D8, while cell D9 calculates the hours used in painting, as illustrated in Program 7.2B. These cells and the cells containing the RHS values will be used when the constraints are entered into Solver.

   

   Figure 7.2A Full Alternative Text

   

   Figure 7.2B Full Alternative Text

The problem is now ready for the use of Solver. However, even if the optimal solution is not found, this spreadsheet has benefits. You can enter different values for T and C into cells B4 and C4 just to see how the resource utilization (LHS) and profit change.

To begin using Solver, go to the Data tab in Excel 2016 and click Solver, as shown in Program 7.2D. If Solver does not appear on the Data tab, see Appendix F for instructions on how to activate this add-in. Once you click Solver, the Solver Parameters dialog box opens, as shown in Program 7.2E, and the following inputs should be entered, although the order is not important:

1. In the Set Objective box, enter the cell address for the total profit (D5).

2. In the By Changing Variable Cells box, enter the cell addresses for the variable values (B4:C4). Solver will allow the values in these cells to change while searching for the best value in the Set Objective cell reference.

3. Click Max for a maximization problem and Min for a minimization problem.

4. Check the box for Make Unconstrained Variables Non-Negative, since the variables T and C must be greater than or equal to zero.

5. Click the Select Solving Method button and select Simplex LP from the menu that appears.

6. Click Add to add the constraints. When you do this, the dialog box shown in Program 7.2F appears.

7. In the Cell Reference box, enter the cell references for the LHS values (D8:D9). Click the button to open the drop-down menu to select $<=\text{,}$ which is for$\le$constraints. Then enter the cell references for the RHS values (F8:F9). Since these are all less-than-or-equal-to constraints, they can all be entered at one time by specifying the ranges. If there were other types of constraints, such as $\mathrm{ \ge }$ constraints, you could click Add after entering these first constraints, and the Add Constraint dialog box would allow you to enter additional constraints. When preparing the spreadsheet for Solver, it is easier if all the$\le$constraints are together and all the$\mathrm{ \ge }$constraints are together. After entering all the constraints, click OK. The Add Constraint dialog box closes, and the Solver Parameters dialog box reopens.

8. Click Solve on the Solver Parameters dialog box, and the solution is found. The Solver Results dialog box opens and indicates that a solution was found, as shown in Program 7.2G. In situations where there is no feasible solution, this box will indicate this. Additional information may be obtained from the Reports section of this box, as will be seen later. Program 7.2H shows the results of the spreadsheet with the optimal solution.

   

   

   Figure 7.2G Full Alternative Text

   

   Figure 7.2H Full Alternative Text