Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

The "Objective Value of" option in the Analytic Solver Platform task pane may be used to

Exhibit 3.2 The following questions are based on this problem and accompanying Excel windows. The Byte computer company produces two models of computers, Plain and Fancy. It wants to plan how many computers to produce next month to maximize profits. Producing these computers requires wiring, assembly and inspection time. Each computer produces a certain level of profits but faces only a limited demand. There are also a limited number of wiring, assembly and inspection hours available in each month. The data for this problem is summarized in the following table. -Refer to Exhibit 3.2. What formula should be entered in cell D8 in the accompanying Excel spreadsheet to compute the amount of wiring used?

Scaling problems

Decision variables are sometimes referred to as problem parameters. ​

A financial planner wants to design a portfolio of investments for a client. The client has $400,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. ​ ​ ​ What values would you enter in the Analytic Solver Platform (ASP) task pane for the following cells for this Excel spreadsheet implementation of this problem? Objective Cell: Variables Cells: Constraints Cells:

How many decision variables are there in a transportation problem which has 5 supply points and 4 demand points?

Which type of spreadsheet cell represents the decision variables in an LP model?

A financial planner wants to design a portfolio of investments for a client. The client has $400,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. ​ ​ ​ ​ ​ What formulas are required for the following cells in the Excel spreadsheet implementation of the formulation? B7 D7 F7 H7

Bounds on the decision variables are known as nonnegativity constraints.​

You have been given the following linear programming model and Excel spreadsheet to solve this problem. What cell references would you enter in the Analytic Solver Platform (ASP) task pane for the following? Objective Cell: Variables Cells: Constraints Cells: ​

Exhibit 3.3 The following questions are based on this problem and accompanying Excel windows. Jack's distillery blends scotches for local bars and saloons. One of his customers has requested a special blend of scotch targeted as a bar scotch. The customer wants the blend to involve two scotch products, call them A and B. Product A is a higher quality scotch while product B is a cheaper brand. The customer wants to make the claim the blend is closer to high quality than the alternative. The customer wants 50 1500 ml bottles of the blend. Each bottle must contain at least 48% of Product A and at least 500 ml of B. The customer also specified that the blend have an alcohol content of at least 85%. Product A contains 95% alcohol while product B contains 78%. The blend is sold for $12.50 per bottle. Product A costs $7 per liter and product B costs $3 per liter. The company wants to determine the blend that will meet the customer's requirements and maximize profit. -Refer to Exhibit 3.3. Which cells should be changing cells in this problem?

Exhibit 3.2 The following questions are based on this problem and accompanying Excel windows. The Byte computer company produces two models of computers, Plain and Fancy. It wants to plan how many computers to produce next month to maximize profits. Producing these computers requires wiring, assembly and inspection time. Each computer produces a certain level of profits but faces only a limited demand. There are also a limited number of wiring, assembly and inspection hours available in each month. The data for this problem is summarized in the following table. -Refer to Exhibit 3.2. Which of the following statements will represent the constraint for just assembly hours?

A grain store has six types of grain, each varying in cost, quality, and nutritional content. Periodically, excess inventory of these grains are consolidated into two local products, Feed-M-All and Supreme-Feed. Feed-M-All sells for $6.50 for a 10-pound bag while Supreme-Feed sells for $8.50 for a 10-pound bag. These feeds are advertised as having the following nutritional content: The component grains have the following content characteristics: Targets for Feed-M-All are a cost of $ 4.35 per 10-pound bag, a quality rating of 2.25, along with the minimum percentages of protein and fat, and the maximum percentage of carbohydrates. Similar targets are set for Supreme-Feed with cost set at $ 4.60 and quality at 2.45. There must be at least a 70%-30% mix among these two local feeds. Formulate an LP model for this product mix problem.