Chat with us, powered by LiveChat This assignment requires you to use a mixed integer program to solve a network design problem. Refer to the ‘Hands on Excel’ discussion on pag | EssayAbode

This assignment requires you to use a mixed integer program to solve a network design problem. Refer to the ‘Hands on Excel’ discussion on pag


This assignment requires you to use a mixed integer program to solve a network design problem.

  1. Refer to the "Hands on Excel" discussion on pages 53–56 of the textbook.
  2. Download the MIP 9 City Example Excel file from the assignment resources and open it in Excel.
  3. Ensure that Solver has been enabled on your PC. This may require  you to turn Solver on in the "Add-Ins" area of Excel. For instructions  on how to do this, you can go to the Office Support page and do a search  for "Excel Solver Add-in."
  4. Develop an answer to question 6 on page 61. In addition, solve the model with four locations.
  5. Develop a 2- to 3-page document that includes:      
    • Quantitative answers to the questions raised in the text.
    • Explanations of what these solutions mean in business terms.
    • An explanation of the managerial implications to your solutions.
    • Answers to the following questions:        
      • Consider the change in total distance as you move from two,  to three, to four locations. How significant an improvement does the  firm achieve in each step?
      • What change to your results do you see in parts C and D of question 6?
      • What service level impacts do you foresee as you move from two, to three, to four locations?

As you complete your assignment, be sure your paper meets the following guidelines:

  • Written communication: Written communication is free of errors that detract from the overall message.
  • APA formatting: All resources and citations should be formatted according to current APA style and formatting guidelines.
  • Length: 2–3 typed, double-spaced pages.
  • Font size and type: 10-point Arial


In this chapter we want to expand on the models from  Chapter 3 , “Locating Facilities Using a Distance-Based Approach,” by adding some sophistication to modeling service levels. We will also dig deeper into the important concept of sensitivity analysis and learn why running many different iterations of your model will ultimately bring the most value to your project. Finally, this chapter will introduce the notion of infeasible models and efficiently finding their root causes.

What Does Service Level Mean?

Picture yourself standing in the kitchen after a long week of work or classes. You are starving and rifling through your delivery menus from a few of your favorite pizza parlors. Pat’s Pizzeria has always had your favorite deep dish, but there is only one location all the way across town and it always takes at least 60 minutes for your order to arrive. You then come across the most recent menu left at your doorstep for a new place called Primo Pies. You have heard good things about them and know they have three locations in town, with one right down the street. You quickly pick up the phone and place your order for a deep-dish pizza from Primo Pies. Within 30 minutes you are on the couch enjoying your pizza and watching your favorite movie. Pure bliss!

The rationale you just used to determine which restaurant to order pizza from is very similar to the decisions consumers make in almost all product markets. Think back to how Al’s Athletics got their start in the industry. Locating themselves in a market where the other big sporting-goods stores couldn’t provide an acceptable service level led to their success in the industry. For all retailers, this means locating stores close to customers or locating warehouses that ship online orders close enough to get next-day service (or be willing to pay for air freight).

The reasons customers want stores close is similar to why businesses want to be able to service these stores from nearby locations. Al valued being close to customers in two senses. He wanted his stores in convenient locations for his customers, but he also wanted his warehouses close to his stores. Shoppers at Al’s may not care how far away the warehouses are. But Al knows that the closer his warehouses are, the faster he can replenish the stock at stores and ensure that shoppers have the product that they want when they want it.

Supply Chain Design Service Levels

The term “service level” can mean many different things in supply chain modeling. If we think back to the pizza example, we can relate the most important measures for network design.

Let’s assume that the pizza parlors in town understand that many of their customers go through the same decision-making process you went through. If they are thinking of where to open their first or a new restaurant, they will likely consider one of the following two definitions:

· 1. Minimize the average distance—We have seen this definition before. This is the same one used within our previous practical center of gravity solutions. In this case, for a given number of restaurants, you want to minimize the distance and thus increase the ability to get pizza to as many hungry people as fast as possible so that they don’t go elsewhere.

· 2. Maximize the percentage of customers within a certain distance—There is a rationale that says customers don’t care if the pizza gets there in 12 minutes or 18 minutes. That is, demand won’t change within a range of times. But if you cross some threshold, maybe 30 minutes, demand will drop off dramatically. So, when locating facilities, we care more about being within 30 minutes of the most potentially hungry people rather than minimizing the overall distance to the customers.

These are the two definitions used to measure service level for companies completing strategic network design studies across all industries and geographies. For example, if a chemical company or consumer products company wants to be within one day of their customers, they will both need to consider their facility locations in terms of one or both of the above service-level definitions.

There are two other measures of service level that are important to the business, but they are not directly relevant to network design. We are discussing them here to help you understand why these are not inputs to an accurate network design model:

· 1. Fill Rate—This is a measure of the percentage of orders that are filled from inventory. In the case of our pizza places, the fill rate would measure the percentage of time a customer calls up looking for a sausage pizza but they don’t have any sausage on hand. This will obviously hurt sales and it is clearly important for a firm to maximize the fill rate. However, the location of the pizza parlor has nothing to do with whether they have enough of all the key ingredients when customers want them.

· 2. Late Orders—This is a measure of how late the shipment is. To measure this factor, we look at when a customer orders a product, when the firm promises a delivery, and whether that delivery actually happens on time. In the case of our pizza parlor, if a customer places an order and they are promised delivery within 20 minutes (we originally assume it will take ten minutes to cook), but their pizza doesn’t show up until 40 minutes later (because in actuality it took us 30 minutes to cook), this results in a late order that is not the fault of location of the pizza parlor.

In summary, the best way to think about network design is that it gives you the opportunity to meet your service promises. If you want to open up pizza parlors with a 30-minute delivery promise, network design is the best way to put your parlors in the right locations. If you can’t keep enough sausage on hand or if you take 30 minutes to cook a pizza, you can’t blame the location of the facility. Other more tactical processes, like training of employees, defining reorder policies, or managing supplier service levels, will have to be altered to improve these measures in the long run.

Consumer Products Case Study: Chen’s Cosmetics

Let’s now begin service-level analysis of a major consumer products company in China. Chen’s Cosmetics specializes in supplying affordable quality cosmetics to stores across China.

Chun Chen grew up in Beijing near one of the most popular fashion-modeling agencies in China. She envied the gorgeous models leaving the studio each day looking nearly perfect thanks to the cosmetics their makeup artists had so artfully applied before they had their pictures taken. Chun soon became a makeup artist herself and found that the best cosmetics were quite costly and could never be afforded by most people. So she decided to work together with her brother, a talented chemist, to produce a line of professional quality cosmetics for affordable prices to be sold all over China.

After years of trials, formulations, and production out of a single manufacturing facility, Chun Chen, now CEO of Chen’s Cosmetics, finds herself with more demand than the existing plant can produce. Chun decides she will add two production facilities in locations across China and wants to ensure that these additional locations offer the best service to Chen’s existing customer base (distributor’s warehouse locations across China).

Chun and her supply chain team first started their analysis by reviewing the service level they currently offered from their single production facility in Guangzhou, China. Before they could do this, however, they had to formally define how they would measure service level.

Service levels within network design are most commonly measured by transit time or distance. Gathering data on transit times for all existing and potential lanes is not always an easy task for companies, however. This data can be quite variable, and requests from carriers to provide detailed data in this area are often denied or unreliable. Network design analysts commonly use a distance equivalent to transit time to avoid the frustrations with transit-time data. As we continue to review samples of these concepts, however, bear in mind that any assumptions we will make regarding speeds of vehicles and driver service hours are all estimates for the purposes of understanding the concept and are not based on definitive research on our part or any public standards and guidelines.

If we think about our previous example determining where to locate pizza restaurants within a 30-minute delivery range, it’s safe to assume that a modeler would not look to determine the time it takes to get to each and every house within the city. Instead, she makes an assumption about how fast a delivery vehicle will travel on average (including required stops for traffic signals, congestion, and so on) and how much time it takes to make the pizza (say, 15 minutes in this case), and then determines the max mileage that can be covered with that speed in 15 minutes (the remaining time to get to the house).

For instance, if a delivery vehicle can travel at 20 mph on average, we would assume that our objective should be centered around demand within 5 miles:

·  20 mph ⋆ .25 hour (15 minutes for delivery) = 5 miles traveled

Chun Chen and her team made similar assumptions in their model when deciding on additional plant locations. Due to the tight operating hours of the distributors’ warehouses requiring trucks to travel during peak periods of city traffic, in conjunction with the requirement for trucks to travel on a significant number of underdeveloped roads as they approach distributor warehouses, the team knew that the number of kilometers that could be covered within a day would be limited. Using averages from their own shipment data as well as public information about average speeds and traffic patterns on both major roadways and less-traveled local access roads, the team determined that delivery trucks would travel at an average speed of only 50kmph. Combining this information with an assumption that drivers can only work a maximum of eight hours per day, we are able to calculate our equivalent one-day transit distance:

·  50kmph ⋆ 8 hours ~ 400 kilometers traveled in one day

Therefore, Chun’s team may now easily extrapolate this to their desired two-day service level as a distance band of 800km from plants outbound to distributor warehouse locations. Modeling their “As Is” state (or baseline) shows Chun that she is currently able to service only 18% of her demand within two days of service (800km).

In addition, on average Chen’s Cosmetics products travel a whopping 1,603km before reaching the customers (see  Figure 4.1 ). Therefore, Chun realizes that these new manufacturing locations not only will alleviate their capacity problems but also should have a significant impact on their ability to offer more competitive service levels.

The supply chain team decides to run models using each of the two popular network design service-level objectives discussed previously and will then analyze the results with the company’s executives. This strategy represents the beauty of network design modeling. You don’t have to pick just one potential objective or set of data. You can easily try several different optimization runs (also known as scenarios) and then compare the results. Sometimes, you will be pleasantly surprised by what you find.

The two scenario results, shown in  Figures 4.3  and  4.4 , use the objective of minimizing average distance (Objective 1) and maximizing the demand within 800km (Objective 2). But, the analysis showed Chun and the executive team that there is little difference in the resultant 2-day service level Chen’s could provide by implementing the results from one objective versus the other. That is, one solution covers 80% of the demand in two days and the other covers 78%. However, the solution that is optimized for average distance is about 10% better on this metric (588 versus 654). Chun and her team decide to add their two additional plants (Tianjin and Nanjing) found with Objective 1 (minimizing the average distance). They have proven that offering the best service to their highest-demand customers has little to no effect on their ability to maximize service to markets within 800km, and therefore these locations are ideal for their expansion.

Based on this study, it would have been easy for Chun to assume that there was no need for the alternative service-level analysis based on the very similar results produced by each objective. But let’s now look forward five years. The new plant locations were a huge success and Chen’s Cosmetics’ market share grew so much that the company now has interest from markets in 38 European countries as well. Chen’s Cosmetics has enough production capacity to handle this growth but obviously cannot afford to fly smaller amounts of product directly from their plant to each of the approximately 500 customer distribution centers in Europe that have already promised their business. The question for the company now becomes “Where should they locate three regional warehouses across Europe?” These will store and deconsolidate Chen’s Cosmetics products flown or shipped in from the China plants before distribution to all European distributor locations.

Consumer Products Case Study: Chen’s Cosmetics European Warehouse Selections

Chen’s has elected to hire a third-party logistics (3PL) company to handle all the transportation of product inbound and outbound from their regional European distribution centers. Chen’s team has worked with the 3PL and jointly identified 48 warehouse location options from which they are to select the optimal 3 they would like to use.

Chen’s team must now conduct a modeling exercise very similar to the one performed five years ago to locate their additional plants in China. Their initial network map, depicted in  Figure 4.5 , shows their 500 customer locations plotted with their 48 potential warehouse locations.

Figure 4.5 Chen’s European Market

The team initially creates a model with the objective of maximizing service level by minimizing the distance weighted by demand, as had been so successful in their previous study in China. In this case, however, information provided from the 3PL tells them that their delivery vehicles are able to travel faster than the estimate that Chen’s team made for their deliveries in China. The service that the 3PL offers Chen’s Cosmetics includes their ability to cover 500km in one day of transit. Therefore, maximizing the two-day service level within this model is now equivalent to a distance of 1,000km.

As seen in the results shown in  Figure 4.6 , this study selects locations in Paris, Rome, and Kremencug (in the Ukraine). These locations will offer only 65% of their new markets serviced within two days (1,000km). This doesn’t seem like an ideal solution to the team, because the company is just getting their feet wet in these completely new market areas. Chun quickly reminds them, however, that there are other methods of optimizing their service level—and that although they were committing to only three warehouses continent-wide, they wanted to make sure they could offer good service to as many markets as possible. This can only help them grow sales even faster during their first few years in the market. She wants to make sure that they test their European service strategy fully before making a decision.

Figure 4.6 Chen’s European Market “Objective 1” Scenario Results

The team then shifts their model strategy slightly to look at the selection of locations based on maximizing the demand they can service within two days, and produces the results shown in  Figures 4.7  and  4.8 .

The team is astonished to find that they can increase their service within two days from 65% to 84% of their demand with this new recommended structure of warehouses in Paris, Belgrade, and Voronezh. Just by quickly adjusting and running another version of their model, they are able to find a solution that allows them to serve an additional 19% of customer demand at a competitive service level. Chun pitches this solution to the executive team as not only optimal for the current customer base but also as a way to put them in a better position to grow their business with these clients in the near future. Chen’s is now ready to make their entrance into the European cosmetics market!

Mathematical Formulation

Let’s return to our mathematical problem formulation we first defined in  Chapter 3 . Now that we have introduced a new model objective (maximizing demand within a distance band), let’s see how we adjust this formulation to get us to our new modeling goal. The constraints remain the same. So we will just focus on the new objective function. To maximize the amount of demand with a certain distance (or time), we can write this:

Maximize Σi ∈ I Σj ∈ J ( dist i , j > HighServiceDist ? 0 : 1 ) d j Y i , j 

In this new equation, the term disti,j > HighServiceDist? simply is a test condition that asks whether the distance between the two points is greater than the service parameter. In the previous case, 800km was the service parameter in China. The next part of the expression simply tells the model what to do. If the statement is true, this expression takes on a value of 0. If it is false, it takes on a value of 1. So points that are more than 800km get a 0 and do not help our objective function. This then guides the objective to look for as many combinations as possible in which the demand is within the 800km.

In the next section, we will also introduce a constraint that forces the average customer demand to be within a certain distance (or time) of the servicing facility. This constraint tells the solver engine that even if a customer cannot be assigned to a facility within the HighServiceDist restriction, you would still prefer that it be assigned to a facility that is reasonably close. Note that in the previous formulation, we are providing no guidance for how to assign customers outside the HighServiceDist, so, it will make a random selection.

Σi ∈ I Σj ∈ J dist i , j d j Y i , j < AvgServiceDist  Σ j ∈ J d j

The AvgServiceDist constant represents the largest average customer demand assignment distance that will be tolerated. If this constraint is tight for a given solution, you know that the typical unit of demand travels exactly this distance along the final leg of its journey. It would be reasonable to choose an AvgServiceDist value that is reasonably close to your HighServiceDist optimization target. That is, you might try to maximize the amount of customer demand that is serviced within 800km, while insisting that the average servicing distance is no larger than 1,000km.

If we provide the full formulation, it would look like this:

Maximize  Σi∈IΣj∈J(disti,j>HighServiceDist?0:1)djYi,j

Subject To:

Σi ∈ I Σj ∈ J dist i , j d j Y i , j < AvgServiceDist  Σ j ∈ J d j

Σi ∈ I Y i , j = 1 ; ∀ j ∈ J

Σi ∈ I X i = P

Yi, j ≤ Xi; ∀iI, ∀jJ

Yi, j ∈ {0,1}; ∀iI, ∀jJ

Xi ∈ {0,1}; ∀iI

Note that the AvgServiceDist constraint, while discouraging the solver from selecting lengthy demand assignments, doesn’t strictly prohibit such selections. Our MIP engine might still assign a few stray demand points to unreasonably distant servicing facilities, while nevertheless maintaining a reasonable average servicing distance across the supply chain as a whole. Indeed, for demand points that are fairly small, these “outlier” selections will tend to crop up with some frequency. Because small demand points don’t contribute much to the left-hand side of our AvgServiceDist constraint, the solver has little incentive to provide them with reasonable service.

To address this problem, it is common to apply constraints that limit the maximum servicing distance. Whereas the average service distance can be modeled with a single constant applied to a single constraint, the maximum service distance constraint requires a single constant applied over many constraints. Luckily, these constraints are all very similar, and thus can be written out concisely in mathematical notation:

Yi,j ≤ (disti,j > MaximumDist?0:1); ∀iI, ∀jJ

This family of constraints simply says that customer j cannot be assigned to facility i if the distance from i to j is larger than the MaximumDist constant value. As you might expect, we need to be more relaxed with our selection of MaximumDist than we are with our selection of AvgServiceDist. (Indeed, if MaximumDist is smaller than AvgServiceDist, then the AvgServiceDist constraint will be redundant. Can you see why?) A reasonable selection of values for our three constants might be HighServiceDist = 800km, AvgServiceDist = 1,000km, and MaximumDist = 1,200km.

In addition, a sophisticated modeler might want to minimize the average servicing distance, while treating the quantity of demand met with high service as a constraint. For example, suppose that the user determines, by solving the model we’ve been working with, that it is possible to assign 75% of demand to a facility within 800km, while maintaining an average servicing distance that is no greater than 1,000km. By flipping the constraint and the objective, he or she can instead minimize the average servicing distance, while insisting that 75% of demand be met with a high-service shipping lane.

Suppose the result of this second model is a solution that has an average servicing distance of 950km, while also meeting 75% of demand with a servicing facility within 800km. This solution will have an interesting property, in as much as it will have successfully optimized two different goals. That is to say, it might be possible for a solution to meet more than 75% of demand with high service, but only by sacrificing the average servicing distance and allowing it to go higher than 950km (indeed, higher than 1,000km). Moreover, it might also be possible for a solution to achieve an average servicing distance lower than 950km, but only by allowing more than 25% of demand to be met through a low-service distance.

If a solution has this multiple-goal property, under which one cannot improve one objective without degrading the other, it can be called Pareto optimal (named after an Italian economist). One can always stumble into a Pareto optimal solution by using this “two solve” process, which involves using the objective result of one solve as the constraint on the second. However, there are more general, and more powerful, techniques for surveying the entire range of Pareto optimal choices. We shall discuss these methods in more detail in  Chapter 11 , “Multi-Objective Optimization.”

Here is an overview of the two complementary models that can be used to create a solution that is Pareto optimal with respect to average servicing distance and total demand met with high service.

First Model

Maximize  Σi∈IΣj∈J(disti,j>HighServiceDist?0:1)djYi,j

Subject to:

Σi ∈ I Σj ∈ J dist i , j d j Y i , j < AvgServiceDist  Σ j ∈ J d j

Yi, j ≤ (disti, j > MaximumDist?0:1); ∀iI, ∀jJ

Σi ∈ I Y i , j = 1 ; ∀ j ∈ J

Σi ∈ I X i = P

Yi, j ≤ Xi; ∀iI, ∀jJ

Yi, j ∈ {0,1}; ∀iI, ∀jJ

Xi ∈ {0,1}; ∀iI

Second Model

Minimize  Σi∈IΣj∈Jdisti,jdjYi,j

Subject To:

Σi ∈ I Σj ∈ J ( dist i , j > HighServiceDist ? 0 : 1 ) d j Y i , j ≥ HighServiceDemand

Yi, j ≤ (disti, j > MaximumDist?0:1); ∀iI, ∀jJ

Σi ∈ I Y i , j = 1 ; ∀ j ∈ J

Σi ∈ I X i = P

Yi, j ≤ Xi; ∀iI, ∀jJ

Yi, j ∈ {0,1}; ∀iI, ∀jJ

Xi ∈ {0,1}; ∀iI

For both of these models, we have incorporated the MaximumDist family of constraints to ensure that no assignment exceeds a reasonable limit. The second model incorporates a new constraint using the HighServiceDemand constant. This value for this constraint can be determined from the result of the first model. For example, if the first model discovers a solution that meets 75 million units of demand with high service, then the second model can set HighServiceDemand to be 75 million.

Service-Level Constraints

Although the decision for Chen’s Cosmetics was simply based on proximity to customer demand alone, many studies will want to consider service level as just a part of their entire network optimization. In this case, service level is no longer the overall solver objective but a constraint within the larger model.

As previously discussed, our first service-level objective asks the model to locate plants as close to as much demand as possible. A solution to this model can result in some customer locations being as close as 20km from the servicing plant while others are as far away as 4,000km. A modeler on Chen’s team thinks it will be best for business if the solution ensures that all customers can be serviced within one-day transit or 400km. After quick analysis of their data, it is clear that this constraint will cause infeasibility in their model. In the table he created, as well as just a cursory glance at our map of the network including all potential locations shown in  Figure 4.9 , we can easily see that there are customer locations that are located more than 400km from any potential plant. Therefore, there is no possible way the solver can adhere to this new constraint.

Figure 4.9 Customer Locations Farther Than 400km from Any Plant

Knowing this, the modeler now decides to alter the constraint. He realizes that the best that Chen’s can ensure with their current set of potential plant locations is a maximum of three-day transit to all customers. In essence, he must now tell the solver that the plants selected must be in locations where all distributors are no more than 1,200km away. After this constraint is applied, however, the model quickly tells him his optimization run resulted in no feasible solution. He is slightly puzzled by this and must now begin to logically analyze the effect that adding this constraint has had on the model.

Infeasibility in modeling can be quite common. As we continue through this book, we will continue to introduce you to common methods of constraining model output in order to produce real and implementable solutions. Constraints within modeling are useful and necessary when applied well, but you will also find that the opportunities for creating unattainable and/or conflicting constraints can be limitless and must be considered with each additional constraint you apply.  Chapter 12 <

Related Tags

Academic APA Assignment Business Capstone College Conclusion Course Day Discussion Double Spaced Essay English Finance General Graduate History Information Justify Literature Management Market Masters Math Minimum MLA Nursing Organizational Outline Pages Paper Presentation Questions Questionnaire Reference Response Response School Subject Slides Sources Student Support Times New Roman Title Topics Word Write Writing