Posted: January 23rd, 2016

Use Excel Solver or Lindo to solve the model you formulated. Copy and paste your spreadsheet and the Answer report in its entirety from Excel OR your Lindo input and output.

Giapetto’s Woodcarving, Inc. manufactures two types of toys: soldiers and trains. Each soldier sold generate $3 in profit while each train generates $2 in profit. The manufacture of wooden soldiers and trains requires two types of skilled labor: carpentry and finishing. A soldier requires 2 hours of finishing labor and 1 hour of carpentry labor. A train requires 1 hour of finishing labor and 1 hour of carpentry. Each week, Giapetto can obtain all the needed raw materials but only 100 finishing hours and 80 carpentry hours. Demand for trains is unlimited, but at most 40 soldiers are bought each week.  Help Giapetto to maximize his profit.

1. a) Formulate the linear programming model for the problem.
2. b) Use the Graphical method to find the optimal solution. Show all steps.
3. c) Use Excel Solver or Lindo to solve the model you formulated. Copy and paste your spreadsheet and the Answer report in its entirety from Excel OR your Lindo input and output.
4. d) Generate the range sensitivity analysis as part of the output (will use next week).

Questions 2

My diet requires that all the food I eat come from one of the four “basic food groups” (chocolate cake, ice cream, soda, and cheesecake).  At present, the following four foods are available for consumption: brownies, chocolate ice cream, cola, and pineapple cheesecake.  Each brownie cost $0.50, each scoop of ice cream costs $0.20, each bottle of cola cost $0.30, and each piece of pineapple cheesecake costs $0.80.  Each day, I must ingest at least 500 calories, 6 oz. of chocolate, 10 oz of sugar, and 8 oz of fat.  The nutritional content per unit of each food is shown in the table.  Formulate a linear programming model that can be used to satisfy my daily nutritional requirements at minimum cost.

1. a) Formulate the linear programming model for the problem.
2. b) Use Excel Solver or Lindo to solve the model you formulated. Copy and paste your spreadsheet and the Answer report in its entirety from Excel OR your Lindo input and output.

Question 3

Semicond is a small electronics company that manufactures tape recorders and radios.  The per-unit labor costs, raw materials, and selling price of each product are given in Table 1.  On December 1, 1997, Semicond has available raw material that is sufficient to manufacture 100 tape recorders and 100 radios.  On the same date, the company’s balance sheet is as shown in Table , and Semicond’s current ratio is 20,000/10,000 = 2.

Semicond must determine how many tape recorders and radios should be produced during December.  Demand is large enough to ensure that all goods produced will be sold.  All sales on credit, however, and payment for goods produced in December will not be received until February 1, 1998.  During December, Semicond will collect $2000 in accounts receivable, and Semicond must payoff $1000 of the outstanding loan and a monthly rent of $1000.  On January 1, 1998, Semicond will receive a shipment of raw material worth $2000, which will be paid for on February 1, 1998.  Semicond’s management has decided that the cash balance on January 1, 1998 must be at least $4000.  Also, Semicond’s bank requires that the current ratio (assets/liabilities) at the beginning of January be at least 2.  In order to maximize the contribution to profit from December production, (revenues to be received) – (variable production costs), what should Semicond produce during December?

1. a) Formulate the linear programming model for the problem.
2. b) Use Excel Solver or Lindo to solve the model you formulated. Copy and paste your spreadsheet and the Answer report in its entirety from Excel OR your Lindo input and output.

Question 4

Comfort Plus Inc. (CPI) manufactures a standard dining chair used in restaurants.  The demand forecasts for quarter 1 (January – March) and quarter 2 (April – June) are 3700 and 4200 chairs, respectively.  CPI has a policy of satisfying demand in the quarter in which it occurs.

The chair contains an upholstered seat that can be produced by CPI or purchased from DAP, a subcontractor.  DAP currently charges $12.50 per seat, but has announced a new price of $13.75 effective April 1.  CPI can produced 3800 seats per quarter at a cost of $10.25 per seat.

Seats that are produced or purchased in quarter 1 and used to satisfy demand in quarter 2 cost CPI $1.50 each to hold in inventory, but the maximum inventory cannot exceed 300 seats.

Formulate a linear program to help minimize cost while satisfying demand.  Solve the LP using Solver or LINDO and generate the range sensitivity analysis as part of the output.

Give it a shot…. we will use it next week.

1. a) Formulate the linear programming model for the problem.
2. b) Use Excel Solver or Lindo to solve the model you formulated. Copy and paste your spreadsheet and the Answer report in its entirety from Excel OR your Lindo input and output.
3. c) Generate the range sensitivity analysis as part of the output (will use next week).