理查德·蔡斯：运作管理——供应链管理的视角（精要版第3版）Appendix_A.pptVIP

Step 1 – Define Changing Cells A convenient starting point is to identify cells to be used for the decision variables in the problem These are H and C , the number of hockey sticks and number of chess sets to produce Excel refers to these cells as changing cells in Solver We have designated B4 as the location for the number of hockey sticks to produce and C4 for the number of chess sets Note that we have set these cells equal to 2 initially We could set these cells to anything, but it is good to use some value other than zero to help verify that our calculations are correct Step 1 – Define Changing Cells Changing Cells – designate B2 as “H” and B3 as “C” Step 2 – Calculate Total Profit This is the objective function Calculated by multiplying profit associated with each product by the number of units produced We have placed the profits in cells B5 and D5 so the profit is calculated by the following equation: B2*B5 + B3*D5, which is calculated in cell F5 Solver refers to this as the Target Cell, and it corresponds to the objective function for a problem Step 2 – Calculate Total Profit Objective Function – “=B2*B5+B3*D5” Step 3 – Resource Usage (Constraints) Our resources are machine centers A, B, and C We have set up three rows (7, 8, and 9), one for each resource constraint For machine center A, 4 hours of processing time are used for each hockey stick produced and 6 hours for each chess set For a solution, the total amount of the machine center A resource used is calculated in F7 (=B7*B2 + D7*B3) We indicate in cell G7 that we want this to be less than the 120-hour capacity of machine center A, in H7 Resource usage for machine centers B and C are set up in the exact same manner in rows 10 and 11 Step 3 – Resource Usage (Constraints) Constraints – Row 7 – Machine Center A Row 8 – Machine Center B Row 9 – Machine Center C Step 4 – Set Up Solver Set Target Cell: is the location where the value that we want to optimize is calculated This is the profit calculated in F5