 There are four basic steps of building a decision model in Excel that you want to use, solve or define an optimal solution. You need to identify the objective and we just call the objective function. What is the goal? You need to define the decision variables. What are the things you can control that will determine whether or not you meet your objective? You need to identify all the constraints, the things that keep you from doing what you need to do. And then you need to write the objective function and the constraints as mathematical expressions that you can code into Excel. This is the chapter 13, problem 5, and we're talking about the lamb developer that wants to build condominiums. They've got a 40.625 acre site. The restrictions, the zoning restrictions, only allow eight units per acre. That's the density control that's common in zoning. And they can build three types of condominiums, a one, two or three-bedroom unit with cost of $450,000, $600,000 and $750,000 respectively. They tell us that the profits, the units generate a 10% profit. So that tells me that if I build a $450,000 unit, I will generate 10% or 45,000 profits. So it's just saying that the more money I spend on units, the more profit I'm going to make. They say they have a budget of $180 million, and their experience tells them that they need to have a certain mixture of one, two and three bedrooms, 15%, one bedroom, 25%, two bedroom, and 25%, three bedroom. So that's what we're given in the problem. We're asked to develop a linear optimization model, and we want to come up with a optimal solution. There's some keywords here if you're paying attention, and I hope you are when it comes to exam time. This is a linear optimization problem. It's not an integer optimization problem. And that's a key difference that I'll show you in a few minutes. And the last part of the question is, in essence, if we can increase the budget, what does that do for our profits? That's the way I interpret that. So let's look at a setup. Go to another sheet here. There's our problem again. And the way I looked at this, we have to identify, first of all, the objective function. I start with that. I know the text says, starts with your decision variables, but mine works a little bit different. I want to know what it is that I'm trying to maximize or minimize, which you're always trying to do in a linear optimization problem. In this case, the thing that jumps out at me is I want to maximize my profit. And how do I do that? Well, profit, as I said earlier, is 10% of the cost. So I can think of the profit as 10% of the number of the one-bedrooms, type 1s, times their cost of 450, plus the number of type 2s, the two-bedrooms, times their cost of 600,000, and the number of type 3 times their cost of 750,000. So my profit is 10% of the cost, and I want to maximize that profit. So the way I would approach it, I'm going to let x1 be my first decision variable, and that's the number of the type 1, the one-bedrooms, x2, the two-bedrooms, x3, the number of three-bedrooms. So if we state our objective function as a mathematical expression, I would say I want to maximize profit, and that's equal to 10% of the cost, and that's the number of type 1s times 450, plus the number of type 2s times 600, plus the number of type 3s times 750,000. So that's pretty logical. This is profit, and that is our objective function. Now I want to decide on my decision variables. Well, the things that I can control are the number of each type of units. So that, those are our decision variables, and those are what we will vary in the Solver Solution, and I'll show you that on the next tab. Finally we need to come up with the constraints, you know, what limitations are imposed on this developer? Well, the first thing, we can't spend more than a budget, and we can state that constraint mathematically as the total cost is equal to the number of type 1s times 450, 2s times 600, 3s times 750,000. That total cost has got to be less than or equal to the budget of $180 million. That's the budget stated in the problem. Second restriction is the zoning restriction. We've got 40 acres, and we can have a maximum of 8 units per acre. That means the total number of units is equal to 8 times 40.625 acres, and for my mind it works better if I rearrange that, and I say that the total number of units divided by 8 has got to be less than that 40.625 acres. Okay, the next constraint is that the one-bedrooms, the type 1s must be 15% of the total, and showing that as a mathematical expression, x1, the number of one-bedrooms divided by the total number of condo units, x1 plus x2 plus x3, has got to be greater than or equal to 0.15 or 15%. Similarly 25% of the units must be two-bedroom, type 2 or x2, and mathematically that's x2 divided by the total number of units, x1 plus x2 plus x3 greater than 0.25. The final restriction that I get out of reading this is that 25% of the three-bedrooms, it must be three-bedrooms, so that's x3 divided by the total, it's got to be greater than 0.25. So those are our constraints. One thing that you need to know when you're building a model is that the more constraints you add, the more difficult it becomes to maximize a profit or minimize a cost. That's what your objective function is. In essence, if I added a sixth constraint here, more than likely my objective function, the maximized profit, is going to be lower than if I run it with just these five constraints, and that becomes important when we talk about integers in just a second. So here's my setup. Now let's actually put that into an Excel model, and I try to be as simple as I can but make it obvious to someone that's looking at my model what I did. And I said over here in this first column, this is my objective function, this is the first part of the spreadsheet, my three decision variables, x1, x2, and x3, I've got the number of each of those types there, and then I created the total, total of course of those three numbers. I've got the cost of each unit, which we were given 450,000, 600,000, 750,000. Total cost is just the number of units times the unit cost, and then I sum those up to get a total cost, and the profit is just 10% of this cost. Total profit is the sum of the profits on each of the units, and of course mathematically 10% of the sum of these is equal to 10% of that total, it works either way, so you can set it up either way. I just put one in here just so my model would be fleshed out, and I want to make sure. One thing I want to remind you of when you're doing Excel, I like to have my spreadsheet set up that it will automatically calculate, so whenever I change something, everything downstream changes, and the reason to my mind set is I don't want to change something, and then think that my bottom line is correcting or adjusting. It won't do that if you're set to manual recalculation, and the way you control that go to, I think it's formulas, there it is, calculations, options, and I click on automatic. By default, Excel seems to want to set it to manual so that you have to manually recalculate, and like I said, I don't like to do that, so I'll always double check to make sure it's set to automatically recalculate when any cell has changed. So now we've got the model set up, I put down here my constraints, and the reason I do this, I think I've set it in one other example I gave you all, was that when you start adjusting or playing around with a model, you may want to go back and adjust some of these constraints. We may want to change the budget, and I can see right there, you can see I was playing around that. Our budget is $180 million, oops, put another zero on that, so that's our budget $180 million, and our constraints are 15%, 25%, 25%, total acres, now for this problem that's not going to change, 40.625, and the zoning restrictions won't change, but I just get in the habit of doing that, because in other situations you might be able to go back and play around with these various decision variables, or variables that impact the decision variables, and come up with a different solution by having those constraints change.