Tutorial 10 Using Solver for Complex Problems

• - Formulate a problem
• -What if
• - Trail and error
• - Goal seeks
• - Use solver
• - Answer report

• Open a new workbook
• Rename sheet one to title Sheet, sheet 2 to Orders
• Group worksheet and A1 type Appliance Mart Superstore, enlarge to 14-point red. Deselect worksheets

This is a what-if analysis wondering how many fridges's to order and what restrictions might be placed

Create the following worksheet

Complete the formulas

• profit per unit = unit retail price - unit wholesale price

• b7 = =b6-b5

• total cost = unit wholesale cost*quantity order b13 =b5*b11
• total profit = profit per unit *quantity ordered b14 =b7*b11
• total cubic feet = cubic feet per unit*quantity order b15 =b8*b11
• format the cells to currency no decimals

Perform a what if = 6 units ordered, = 20 units ordered

We are now given the restrictions of $ 15,000 and 1000 cubic feet of space

Seeking a solution by Trial and Error

• - we can come close to these numbers by putting numbers in B11 until it almost reaches the values
• - try 10, try 15, try 17 and almost there with lots of cubic feet left over
• a new scenario = price dropped to 850 and order limit 18000

Excel has a solution to this

• Automates the trial and error process - you enter the results and excel determines the amount
• Using goal seek

• set up template with labels formulas and values
• click tools - goal seek - displays goal seek dialog box
• click the SET cell box -click cell where you want to display the result - cell that contains the formula
• click the TO value box - type the value you want as the result
• click the BY changing cell box - click the cell that excel can change to produce the result
• click OK

On worksheet

• click tools - goal seek
• set cell B13
• to value - type 18000
• by changing - b11
• click OK
• excel finds the # 21.17647
• we can change to 21

• Repeat goal seek using 1000 square feet
• Save the file

Retrieve file GoldStar and save as Appliance Order

• click orders tab
• add a column for total orders
• e4 - enter all appliances
• select E10:E11 and E13:E15 - ctrl key - click auto sum and see results
• select d13:d15 - format painter - click e13

• - Has three items to order
• - Has customer orders and now has limit of $50000 and 1300 cu ft

• play around
• b11 50 over budget and space
• b11 40
• b11 39
• need to maximize space and profit
• play around with values

 

• Create a worksheet that contains labels etc. and formulas for the problem you want to solve
• Click tools - solver - to display solver dialog box
• SET target cell - contains cell reference for the target cell - the end number - the cell you want to maximize, minimize or set to a certain number
• BY changing cells - list the cells that excel can change to arrive at the solution
• ADD - to add constraints that limit the changes solver can make to the values in the cells
• Click the solve button to generate a solution
• Click OK to return to worksheet

• Constraints - 50000 spending limit and 1300 cubic feet
• Click tools - solver - drag box to upper right corner so you can see cells
• Set cell - we want to get the most profit possible -so e14 equal to max.
• By changing cells box - cells excel can change b11:d11
• Click subject to constraints - click add button

• total order = E13 - down arrow button and choose <= option constraint = 50000
• click add to add another constraint
• cubic feet = E15- down arrow button <= option -constraint=1300
• click OK
• click the solve button
• EXCEL =Solver has converged to the current solution All constraints are satisfied.
• observe the results on the worksheet and notice the quantity ordered is negative stoves and microwaves
• to remove these values from the worksheet click - restore to original values - then OK

• We have other constraints that we must order at least the customer order amount
• Add more constraints

• click tools - solver - add
• b11 >= b10
• add c11>=c10
• add d11>=d10
• OK - solve button - OK

• The answer is better and more realistic but we have part fridges and we would like even numbers

• click tools - solver - add button to add more constraints
• drag pointer to outline B11:d11 - click integer and OK
• solve
• and OK

• The answer is okay now with the constraints that we placed on it but we would like a report that would show the entire constraints etc.
• Can either return B11: D11 to original numbers or just run solver again
• Tools - solver- solve
• When results box appear click ANSWER and keep solver solution
• OK
• A new sheet tab appears with the answer report - click on tab and observe

• Not binding = that these constraints were not limiting factors in the solution
• Binding = that the final value in the set cells was equal to the constraint value

Add a header to the answer report

• click print preview - setup button - header/footer tab
• click custom header - delete header in center section - click right section and type YOUR NAME space click date button (4th button) space filename button ( 6th button)
• OK - OK - Print

Print the order tab for your own reference