Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

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?

What is the significance of an absolute cell reference in Excel?

Carlton construction is supplying building materials for a new mall construction project in Kansas. Their contract calls for a total of 250,000 tons of material to be delivered over a three-week period. Carlton's supply depot has access to three modes of transportation: a trucking fleet, railway delivery, and air cargo transport. Their contract calls for 120,000 tons delivered by the end of week one, 80% of the total delivered by the end of week two, and the entire amount delivered by the end of week three. Contracts in place with the transportation companies call for at least 45% of the total delivered be delivered by trucking, at least 40% of the total delivered be delivered by railway, and up to 15% of the total delivered be delivered by air cargo. Unfortunately, competing demands limit the availability of each mode of transportation each of the three weeks to the following levels (all in thousands of tons): The following is the LP model for this logistics problem. What values would you enter in the Risk Solver Platform (RSP) task pane for the cells in this Excel spreadsheet implementation of this problem? Objective Cell: Variables Cells: Constraints Cells:

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 Risk Solver Platform (RSP) task pane for the following cells for this Excel spreadsheet implementation of this problem? Objective Cell: Variables Cells: Constraints Cells:

A company is planning production for the next 4 quarters. They want to minimize the cost of production. The production cost, demand and production capacity vary from quarter to quarter. The maximum amount of inventory which can be held is 100 units and management wants to keep at least 50 units on hand. Quarterly inventory holding cost is 4% of the cost of production. There are currently 50 units in inventory. The company wants to produce at no less than one half of its maximum capacity in any quarter. What formulas are required for cells D3, D6, D8, D15, D17 and D18 in the Excel spreadsheet implementation of the formulation?

Numeric constants should be

In the Risk Solver Platform (RSP) dialog box simple upper and lower bounds for decision variables are specified by

You have been given the following linear programming model and Excel spreadsheet to solve this problem. What numbers should be entered into cells B5:C5 and B8:C10 to implement this model?

A hospital needs to determine how many nurses to hire to cover a 24 hour period. The nurses must work 8 consecutive hours but can start work at the start of 6 different shifts. They are paid different wages depending on when they start their shifts. The number of nurses required per 4-hour time period and their wages are shown in the following table. What are the key formulas for this Excel spreadsheet implementation of the following formulation?