Spreadsheet: 845934d1697218793-excel-solver-not-solving-problem-keeps-spitting-out-an-error-problem_1-2-
Sheet: Product Portfolio Problem

Problem Description |  | Problem question: How many cartons of each product should GD order at the start of the week to maximise its profit?
The product portfolio problem asks which products a firm should be making. If there are contracts that obligate the firm to enter certain markets, then the question is which products to make in quantities beyond the required minimum. Consider Grocery Distributors (GD), a company that distributes 15 different vegetables to grocery stores. GD’s vegetables come in standard cardboard cartons that each take up 1.4 cubic feet in the warehouse. The company replenishes its supply of frozen foods at the start of each week and rarely has any inventory remaining at week’s end. An entire week’s supply of frozen vegetables arrives each Monday morning at the warehouse, which can hold up to 20,000 cubic feet of product. In addition, GD’s supplier extends a line of credit amounting to $33,000. That is, [NAME] is permitted to purchase up to $33,000 worth of product each Monday. [NAME] can predict sales for each of the 15 products for the coming week. This forecast is expressed in terms of a minimum and maximum level of sales. The minimum quantity is based on a contractual agreement that GD has made with a few retail grocery chains; the maximum quantity represents an estimate of the sales potential in the upcoming week. The unit cost and unit selling price for each product are known. The given data are compiled in the table. The table columns [NAME] respectively to the name of the product, the unit cost, the unit selling price, the minimum quantity required and the estimated maximum quantity possible to sell. How many cartons of each product should GD order at the start of the week to maximise its profit? |  | Step 1: Problem Data
 |  | Product | Cost | Price | Minimum | Maximum |  | Price minus Cost
 |  | Whipped Potatoes (WP) | 2.15 | 2.27 | 300 | 1500
 |  | [NAME] (CC) | 2.2 | 2.48 | 400 | 1000
 |  | [NAME] (BP) | 2.4 | 2.7 | 250 | 900
 |  | Artichokes (AR) | 4.8 | 5.2 | 0 | 150
 |  | Carrots (CR) | 2.6 | 2.92 | 300 | 1200
 |  | Succotash (SU) | 2.3 | 2.48 | 200 | 800
 |  | [NAME] (OK) | 2.35 | 2.2 | 150 | 600
 |  | Cauliflower (CL) | 2.85 | 3.13 | 100 | 300
 |  | Green Peas (GP) | 2.25 | 2.48 | 750 | 3500
 |  | Spinach (SP) | 2.1 | 2.27 | 400 | 2000
 |  | [NAME] (LB) | 2.8 | 3.13 | 500 | 1800
 |  | [NAME] (BS) | 3 | 3.18 | 100 | 500
 |  | [NAME] (GB) | 2.6 | 2.92 | 500 | 2500
 |  | Squash (SQ) | 2.5 | 2.7 | 100 | 500
 |  | Broccoli (BR) | 2.9 | 3.13 | 400 | 2500
 |  | Step 2: Decision Variables | Product | No. of cartons to order | Profit from each product | Space for each Product | Credit for each product
 |  |  | Whipped Potatoes (WP) | 300
 |  |  | [NAME] (CC) | 1000
 |  |  | [NAME] (BP) | 900
 |  |  | Artichokes (AR) | 150
 |  |  | Carrots (CR) | 1200
 |  |  | Succotash (SU) | 700
 |  |  | [NAME] (OK) | 150
 |  |  | Cauliflower (CL) | 300
 |  |  | Green Peas (GP) | 3500
 |  |  | Spinach (SP) | 400
 |  |  | [NAME] (LB) | 1800
 |  |  | [NAME] (BS) | 100
 |  |  | [NAME] (GB) | 2500
 |  |  | Squash (SQ) | 500
 |  |  | Broccoli (BR) | 2500
 |  | Step 3 Objective Function: | Maximise Profit
 |  | Step 4: Constraints
 |  | Space |  | <= | 20000
 |  | Credit |  | <= | $33,000