So far in this course, we've used models to do what if analysis in a variety of business scenarios. What if we share housing costs in San Francisco with another person? How does that affect my budget? What happens if we spent much more on marketing a new product? How might that affect the growth of my customer base? For the most part, we've used linear functions to calculate the outcomes of changes in our decisions. I've referred to outcomes or objectives of a model as generated by objective functions, to minimize costs, for example, or to maximize profit. Linear programming finds the right combination of decisions for achieving the very best possible outcome. In this lecture and demonstration, we look at the types of problems addressed by linear programs and implement them in a spreadsheet model. I'll demonstrate for you the ways in which constraints are included in models. For example, the cash we have available for a marketing budget. Finally, we'll use the Excel tool Solver to let the power of your personal computer calculate the very best possible set of decisions to make given a specific model. Let's look at an example. Do you remember those two fellows I mentioned in our first module, who invented the personal computer electronic spreadsheet? They were Dan Bricklin and Bob Frankston. The second product that they brought to market after VisiCalc was called TK Solver. You can find a somewhat simplified version of it here in Excel, under Data > Solver. This spreadsheet was organized as you see it here in order to use the model with the Solver tool. Solver is another what if analysis tool that helps you identify the best possible set of decisions to make in a modeled business scenario. In this case, the scenario is based on a linear function. This model asks the question, what's the best use of our limited resources this month? What should we make with those resources, given a set of options? This is a classic example of the use of linear programming, that is, optimizing resource allocation. Here's the components of the model. In blue, from cells C4 to E4 are the three production decisions we are making. We can make any number of the three types of speakers that we have in inventory, basic, midrange and high end. Below, in the logic of the model, you see the three categories of resources that are needed for each of the speaker types. So each speaker requires a cabinet and a different mix of components. To simplify our demo here, I've just referenced those components as a relative number from 1.5 for the basic speaker to 4 for the high-end speaker. In the green cell, you'll see the output of the model expressed as a calculation of total profit. Here's our old friend, the SUMPRODUCT function. In this case, it's multiplying two ranges or arrays. Profit per unit are located in cells C13 through E13, units produced refers to the decision variables in blue from C4 to E4. Notice that the sum product objective function, as written here, is a linear function. In this model, our objective is to maximize profit. In other models, we might want to instead minimize expenses or possibly to hit a specific target, let's say a profit margin for a particular product line. Our model needs one more element. I mentioned that this is an example of a Resource Allocation model. That suggested there are limited resources in this situation and that we're choosing how to use them. In this section of the model, I've laid out those limits, here, in the range from H8 to H10. At this time, we have 120 cabinets in inventory, we have 200 various electrical components. And our employees who assemble speakers collectively have 650 hours available to work during this period we are planning for. In this case, we assume that everyone has been trained to assemble all three types of speakers. In this area, in F8, I put another SUMPRODUCT function. In this function, I'm multiplying the number of resources needed in this range C8 through E8, times the number of units we're making of each speaker type. Those are our decision variables up here in C4 through E4. I'm doing the same for labor in cell F9 and the same for components in cell F10. Now at this point, I could manually try what if analysis to see how high I can get the total profit number. That's the number in green here in H4, so let's try just making more of everything. I can, for example, instead of making 25 basic units, let's make it 50. In the same fashion, let's do 50 for the midrange speaker and produce 50 high end speakers as well. So, the good news is we've gotten our total profit number up much higher to 10,250. But here's the problem, I've exceeded the number of cabinets that I have available in inventory based on this production schedule. In addition, I've exceeded the number of components and the hours of labor I have available. Solver will help us with that problem, staying within our resource constraints. And also will help us with the manual tedium of trying out all possible production decisions, using what if trials to find the most profitable mix given our limited resources. So let's use solver to identify the optimal production decision. First, I'll highlight cell H4, which is where the objective function is located. Next, I'll choose Data > Solver. Notice that H4 is the objective and the radio button indicates that we're trying to maximize the number in that cell. In the next field on the Solver form, we indicate where Solver should adjust values to achieve that objective. Those are the blue decision variables located in C4 through E4. Next, we need to implement our resource constraints, and you see them here. For example, F10 is the cell in which we calculated the total number of components needed, given a particular set of production decisions. That number, according to this constraint, must be less than or equal to H10, that is to say, the number of components we have in inventory. Similarly, F8 shows a calculation of the number of cabinets we need for this set of production runs. And meanwhile, H8 shows the number of cabinets available in inventory. F8 must be less than or equal to the number of cabinets we have in inventory. And finally, F9 showing the total number of hours of labor used for this production schedule, must be less than or equal to the number of hours we have available in cell H9. Click on Make Unconstrained Variables Non-Negative. That means that we can't make negative amounts of any given speaker type. Finally, under the Select a Solving Method, choose the simplex linear programming option. Now we're ready and you can click Solve. Solver has found a solution, and I can keep that Solver solution in my spreadsheet by clicking OK. Solver has now tried every possible combination of numbers in cells C4 through E4. Our production decisions and found the highest possible profit number in cell H4, given the resource constraints that we've laid out in the range F8 through H10. So our optimal decision suggests that we should build no high end speakers at all, even though they have the highest profit per unit. The problem is they require the most resources in time and in components. So this is simple linear programming model shown in a resource allocation example. Other types of problems addressed by linear programs include scheduling facilities use, creating optimal routes for delivery trucks, allocating a fixed marketing budget among various media initiatives, and so on.