Using excel solver for solution.

A B D E F G H O P R S T 1 K L N Allocating Components to Assemblies Bikes.com is a web-based company that sells bicycles on the internet. Its distinctive feature is that it allows customers to customize the design when they order and then to receive quick delivery. Bikes.com gives customers choices for frame size (34, 36, 38), suspension (standard or heavy-duty), and gear speeds (5, 10, 15). As a result, customers can order one of 18 possible combinations (3 x2x3). The company shorthand refers to frame size as Option A (A1 is the 34-inch model, A2 is the 36-inch model, and A3 is the 38-inch model). Similarly, 1 the standard suspension is option B1, and the heavy-duty suspension is B2. The gear speeds are C1 (5), C2 (10), and C3 (15). 2 Rather than stock 18 different types of bicycles, Bikes.com holds inventories of the major components and then assembles the bikes once a customer order comes in. Orders are taken Mondays through Wednesdays, assemblies are done on Thursdays, and shipments go out on Fridays. Thus, at the close of business on Wednesday, Bikes.com has an inventory of components and a list of orders, and its task is to match components with orders to meet as much demand as possible. The tables below describe customer orders for this week and the inventory status at the end of 3 Wednesday. 4 5 Model 6 A 1 1 2 2 2 2 3 3 3 3 3 3 7 B 1 1 8 C 2 3 1 2 3 2 1 9 Orders 5 1 5 Compon A1 A2 3 C2 C3 10 ent Inventor 12 20 30 20 25 16 20 11 ly 1 2 3 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 1 1 1 1 2 2 2 2 2 2 2 2 7 B 1 1 1 2 2 2 1 1 1 1 1 2 2 2 1 2 3 1 2 3 1 1 3 2 3 4 5 0 5 1 7 0 4 8 1 2 0 6 4 0 B1 B2 C1 18 12 13 14 a. What is the maximum number of customer orders that can be satisfied this week? 15 16 b. Suppose that the profitability varies by model type, as shown in the table below. What is the maximum profit that can be achieved from this week's orders? 17 18 Model 1 2 3 3 4 5 6 7 8 9 10 11 12 13 14 15 19 Profit 45 55 70 65 75 90 57 67 77 92 50 60 75 20 17 18 16 70 47 72 80 95 A B D E F G H O P R S T 1 K L N Allocating Components to Assemblies Bikes.com is a web-based company that sells bicycles on the internet. Its distinctive feature is that it allows customers to customize the design when they order and then to receive quick delivery. Bikes.com gives customers choices for frame size (34, 36, 38), suspension (standard or heavy-duty), and gear speeds (5, 10, 15). As a result, customers can order one of 18 possible combinations (3 x2x3). The company shorthand refers to frame size as Option A (A1 is the 34-inch model, A2 is the 36-inch model, and A3 is the 38-inch model). Similarly, 1 the standard suspension is option B1, and the heavy-duty suspension is B2. The gear speeds are C1 (5), C2 (10), and C3 (15). 2 Rather than stock 18 different types of bicycles, Bikes.com holds inventories of the major components and then assembles the bikes once a customer order comes in. Orders are taken Mondays through Wednesdays, assemblies are done on Thursdays, and shipments go out on Fridays. Thus, at the close of business on Wednesday, Bikes.com has an inventory of components and a list of orders, and its task is to match components with orders to meet as much demand as possible. The tables below describe customer orders for this week and the inventory status at the end of 3 Wednesday. 4 5 Model 6 A 1 1 2 2 2 2 3 3 3 3 3 3 7 B 1 1 8 C 2 3 1 2 3 2 1 9 Orders 5 1 5 Compon A1 A2 3 C2 C3 10 ent Inventor 12 20 30 20 25 16 20 11 ly 1 2 3 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 1 1 1 1 2 2 2 2 2 2 2 2 7 B 1 1 1 2 2 2 1 1 1 1 1 2 2 2 1 2 3 1 2 3 1 1 3 2 3 4 5 0 5 1 7 0 4 8 1 2 0 6 4 0 B1 B2 C1 18 12 13 14 a. What is the maximum number of customer orders that can be satisfied this week? 15 16 b. Suppose that the profitability varies by model type, as shown in the table below. What is the maximum profit that can be achieved from this week's orders? 17 18 Model 1 2 3 3 4 5 6 7 8 9 10 11 12 13 14 15 19 Profit 45 55 70 65 75 90 57 67 77 92 50 60 75 20 17 18 16 70 47 72 80 95