Linear Programming (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 1 of 18 possible combinations (3 x 2 x 3). 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). Simimilary, the standard suspension is B1 and the heavy duty suspension is B2. The gear speeds are C1 (5), C2 (10), C3 (15).

Rather than stock 18 different types of bicycles, Bikes.com holds inventories of the 8 major components and then assembles the bikes once the customer orders come in. Orders are taken Mondays through Wednesdays, assemblies are done on Thursdays and shipments go out on Fridays. Thus, at the close of business 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 chart below shows customer orders for this week and the inventory status at end of Wednesday. 

Questions: (Please use excel solver and also provide a “concise” algebraic formulation / narration) 

1.      What is the maximum number of orders that can be satisfied this week?

2.      Suppose the profitability varies by model type as shown in the chart, what is the maximum profit that can be achieved by this week’s orders?

3.      In the solution to part (b), what percentage of potential profit is satisfied?