Figure 6-17 shows a workbook set up to solve the example problem dis- cussed in the EasyRefresher. You take three steps to set up a workbook for solver: provide guesses of the variables that optimize your objective function, supply the objective function, and then supply the con- straint functions. As long as you understand the concepts of optimization modeling, as described in the preceding EasyRefresher, this process is simple. To use Excel’s Solver, first build a workbook that describes your optimization-modeling problem, including its objective function and any constraints, and then tell Solver to look for an optimal solution. Or, you can provide the objective function and the con- straint equations to Excel and have it solve the problem, which is the solution technique described in the paragraphs that follow. You can solve this equation in a variety of ways, including graphically, iteratively, or using a technique like simplex algebra. You are subject to the following constraints: You want to maximize your profits, which can be described using the following objective function:īut you can’t develop unlimited numbers of building lots or build unlimited numbers of houses. With the information provided in the preceding paragraphs of this EasyRefresherTM, I’ve described your fictional optimization-modeling problem.
Again, the greater than or equal to symbol is represented by the >= operator. Or, restated, this formula says that the houses variable must be greater than or equal to 10.
This formula says that you want to build at least 5 houses. The minimum-number-of-houses policy constraint can be expressed as follows: The greater than or equal to symbol is represented by the >= operator. Or, restated, this for- mula says that the lots variable must be greater than or equal to 10. This formula says that you want to develop at least 10 building lots. For example, the working capital constraint can be expressed as follows: These two constraints can also be expressed as equations. The fact that you have a single bulldozer available for only 3,000 hours each year also limits the number of lots and houses you can annually sell because every lot requires 80 hours of bulldozing and every house requires 200 hours of bulldozing. Your working capital of $1,200,000 limits the number of lots and houses you can annually sell because every lot requires a $50,000 cash investment and every house requires a $25,000 cash investment. To continue with the fictional case of residential development, suppose that you have two principal limiting fac- tors: working capital and bulldozer capacity. Of course, any objective function is limited by certain constraints. Your princi- pal financial objective is to maximize your profits, and this objective can be expressed as an objective function, or equation, that you want to maximize: Suppose that you make $20,000 on each home you build and $15,000 on each building lot you develop and then sell. You create and sell two products: building lots and houses. Suppose, for example, that you’re a residential real estate developer and contractor. EasyRefresher: How Optimization Modeling Works While this abstract definition sounds complicated, at least at the conceptual level, optimization modeling makes common sense once you provide a concrete example. With an optimization-modeling problem, you want to optimize an objective function but at the same time recognize that there are constraints, or limits. Excel’s Solver tool lets you solve optimization-modeling problems, also commonly known as linear programming programs.
0 kommentar(er)
