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 spend 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 the model as generated by objective functions to minimize costs for example or to maximize profits. 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 beset 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 Brickland 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 in 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 modelled 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 C-4 to E-4 are the three production decisions we're making. We can make any number of the three types of speakers that we have in inventory, basic, mid-range, and high end. Below, in the logic of the model, you see the three categories of resources that are need 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 sum product function. In this case it's multiplying two ranges or a raise. Profit per unit are located in cells C13 through E13. Units produces refers to the decision variables in blue from C4 to E4. Notice that the SUMPRODUCT objective function, as written here, is a linear function. In this model, our objective is to maximize profit. In other models, me 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 suggests that there are limited resources in this situation and that we are choosing how to use them. In this section of the model, I've laid out those limits. Here in the range from H8 through 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 assumed 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 are 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 lets make it 50. In the same fashion let's do 50 for the mid range 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 are 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 show 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 okay. 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 we've laid out in the range F8 through H10. So our optimal decision suggests that we should be 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 a simple, linear programming model shown in a resource allocation example. Other types of problems addressed by linear programs include: scheduling facility's use, creating optimal routes for delivery trucks, allocating a fixed marketing budget among various media initiatives, and so on.
