(Solved): BestChip (BC) is a large nationwide corporation that produces low-fat snack products for an expandin ...

BestChip (BC) is a large nationwide corporation that produces low-fat snack products for an expanding market (pun intended). Basically, BC takes materials (corn, wheat, and potatoes) and turns them into two types of snacks: chips (regular and green onion) and party mix (one variety). BC is expanding into the western United States and is considering sites for locating production facilities. BC currently has eight candidate sites. Table 1 shows the sites’ purchase prices and the purchase and shipping cost per ton of each material to each site. The purchase cost represents the yearly amortized cost of opening and operating the site (exclusive of shipping costs). Each site may produce as many as 20,000 tons of product per year. BC has six major customers, and all demand is shipped by truck from the plant to the customer warehouse. The shipping cost depends on the tonnage and distance and comes to $0.15 per ton-mile. The customers, their location, and their yearly demand in tons for each product are listed in Table 2. You must meet demand. The makeup of the products does not depend on the production plant. Table 3 gives the product ingredient mix data. The company requires that we consolidate our business, so we cannot locate plants in more than two states. For this analysis, ignore the differences in property and income tax rates between the states (this is usually critical, but it gets us far afield of the key issue of math programming). In addition, many critical factors actually determine locations; for example, the method of financing the site purchase will also be a major factor in the decision—but we will ignore that also.Your job is to determine how we should expand into the west and develop alternatives. Questions you should answer include: • What sites should be selected? How should the customers be served? • If gasoline gets more expensive and our trucking costs change, then how is the recommendation affected? • If rail freight costs for material shipping increase, then how is the recommendation affected? Please consider other sensitivity-analysis issues that you feel might be important for management’s decision-making process. Table 1. Site Information and Material Shipping Cost Site Location Purchase Cost ($/year) Material Shipping Cost ($/ton) Corn Wheat Potato Yuma, AZ 125000 10 5 16 Fresno, CA 130000 12 8 11 Tucson, AZ 140000 9 10 15 Pomona, CA 160000 11 7 14 Santa Fe, NM 150000 8 14 10 Flagstaff, AZ 170000 10 12 11 Las Vegas, NV 155000 13 12 9 St. George, UT 115000 14 15 8 Table 2. Demand Information Company Location Demand Regular Green Onion Party Mix Jones Salt Lake City 1,300 900 1,700 YZCO Albuquerque 1,400 1,100 1,700 Square Q Phoenix 1,200 800 1,800 AJ Stores San Diego 1,900 1,200 2,200 Sun Quest Los Angeles 1,900 1,400 2,300 Harm's Path Tucson 1,500 1,000 1,400 Table 3. Product-Ingredient Mix Company Ingredient Corn Wheat Potato Regular Chips 70 20 10 Green Onion Chips 30 15 55 Party Mix 20 50 30 NEED LINGO/EXCEL SOLVER SOLUTION.