Quantitative Decision Models I
1. For each question, your mathematical model and answers to each individual part should be typed in Microsoft Word. For Questions 2 and 3, state your model by clearly defining your decision variables (with appropriate units), the objective function, and all the relevant (including the non-negativity) constraints. The graphical solution to Question #3could be hand-drawn, but you must submit a jpg or pdf file of your graph.
2. Excel spreadsheet models are required for each question. You are required to submit a singleworkbook containing all the separate spreadsheets. Make sure that each sheet carries the name of the question you are answering. Note: The spreadsheet model for each question is given a good portion of the marks.
3. For how to use Excel for breakeven analysis and for solving linear programming models, refer to Appendix 1.1 page 26 and Appendix 2.2 page 89 in your textbook 14e, respectively. Also, see APPENDIX A (textbook, 14e) for an overview of how to build spreadsheet models in Excel.
Question 1:Schmaltx, Ltd., produces a variety of specialty beverages. One of its products is made in a separate facility for which monthly rent, administrative costs, and equipment leasing is $50,000. Eight workers handle production and shipping. Each receives salary and fringe benefits of $2,000 per month. Packaging and distribution costs are $1.5 per case, and ingredients cost $3 per case. The product is sold for $9 a case. Determine the following:
a. Write an expression for the cost function, revenue function and profit function in terms of the number of cases of beverage produced, Q. (Assume there is no demand restriction on sales).
i. Find the break-even point algebraically.
ii. Formulate a spreadsheet model that will give the profit. Use the Excel goal seek command (Data > What-If-Analysis > Goal Seek) to calculate the break-even point in part a.
iii. Using the above spread sheet model find the profit or loss if Schmaltx produces and sells 10,000 cases.
iv. Using the above spread sheet model find the profit or loss if Schmaltx produces and sells 30,000 cases.

b. Write an expression for the profit function in terms of demand (in cases) for the beverage (S) and the number of cases of beverage produced (Q).
i. Formulate a spreadsheet model that will find the profit.
ii. Find the cases of beverage to be produced when S= 20,000 cases using the above spreadsheet model.
iii. Find the cases of beverage to be produced when S= 10,000 cases using the above spreadsheet model.

Question 2: Modern Textile Mills produces two types of cotton cloth, denim and corduroy. Corduroy is a heavier grade of cotton cloth and requires 5 pounds of raw cotton per yard, whereas denim requires 3 pounds of raw cotton per yard. A yard of corduroy requires 3.6 hours of processing time; a yard of denim requires 3.0 hours. Although the demand for denim is practically unlimited, the maximum demand for corduroy is 20 yards per month. The manufacturer has 150 pounds of cotton and 90 hours of processing time available each month. The manufacturer wants to make sure that it produces denim at least as twice the size of the corduroy produced. The manufacturer makes a profit of $5.00 per yard of corduroy and $2.50 per yard of denim. The manufacturer wants to know how many yards of each type of cloth to produce to maximize profit.
a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit.
b. Find the optimal solution using graphical approach.
a. Develop an excel spreadsheet model.
b. Find the optimal solution by solving the spreadsheet model.
Question 3:Problem 4 (page 191-192 in the text book 14e).
a. Formulate LP.
b. Develop an excel spreadsheet model and answer to parts (a), (b), and (c).

Question 4:A firm that assembles computers and computer equipment is about to start production of two new products, Product1 and Product2. Each product type will require assembly time, inspection time, and storage space. The amount of each of these resources that can be devoted to the production is limited.
The manager has met with design and manufacturing personnel and obtained the following information:
Product1 Product2
Profit per unit $60 $50
Assembly time per unit 4 hours 10 hours
Inspection time per unit 2 hours 1 hour
Storage space per unit 3 cubic feet 3 cubic feet

The manager has also acquired the information on the availability of company resources for these products.
Resource Daily availability
Assembly time 100 hours
Inspection time 22 hours
Storage space 39 cubic feet

Management wants to make sure that the number of units of Product1 produced must not exceed the number of units of Product2 produced. It is found that all of the produced products can be sold in the market.
a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit.
b. Develop an excel spreadsheet model.
c. Find the optimal solution by solving the spreadsheet model.

1. For each question, your mathematical model and answers to each individual part should be typed in Microsoft Word. For Questions 2 and 3, state your model by clearly defining your decision variables (with appropriate units), the objective function, and all the relevant (including the non-negativity) constraints. The graphical solution to Question #3could be hand-drawn, but you must submit a jpg or pdf file of your graph.
2. Excel spreadsheet models are required for each question. You are required to submit a singleworkbook containing all the separate spreadsheets. Make sure that each sheet carries the name of the question you are answering. Note: The spreadsheet model for each question is given a good portion of the marks.
3. For how to use Excel for breakeven analysis and for solving linear programming models, refer to Appendix 1.1 page 26 and Appendix 2.2 page 89 in your textbook 14e, respectively. Also, see APPENDIX A (textbook, 14e) for an overview of how to build spreadsheet models in Excel.
Question 1:Schmaltx, Ltd., produces a variety of specialty beverages. One of its products is made in a separate facility for which monthly rent, administrative costs, and equipment leasing is $50,000. Eight workers handle production and shipping. Each receives salary and fringe benefits of $2,000 per month. Packaging and distribution costs are $1.5 per case, and ingredients cost $3 per case. The product is sold for $9 a case. Determine the following:
a. Write an expression for the cost function, revenue function and profit function in terms of the number of cases of beverage produced, Q. (Assume there is no demand restriction on sales).
i. Find the break-even point algebraically.
ii. Formulate a spreadsheet model that will give the profit. Use the Excel goal seek command (Data > What-If-Analysis > Goal Seek) to calculate the break-even point in part a.
iii. Using the above spread sheet model find the profit or loss if Schmaltx produces and sells 10,000 cases.
iv. Using the above spread sheet model find the profit or loss if Schmaltx produces and sells 30,000 cases.

b. Write an expression for the profit function in terms of demand (in cases) for the beverage (S) and the number of cases of beverage produced (Q).
i. Formulate a spreadsheet model that will find the profit.
ii. Find the cases of beverage to be produced when S= 20,000 cases using the above spreadsheet model.
iii. Find the cases of beverage to be produced when S= 10,000 cases using the above spreadsheet model.

Question 2: Modern Textile Mills produces two types of cotton cloth, denim and corduroy. Corduroy is a heavier grade of cotton cloth and requires 5 pounds of raw cotton per yard, whereas denim requires 3 pounds of raw cotton per yard. A yard of corduroy requires 3.6 hours of processing time; a yard of denim requires 3.0 hours. Although the demand for denim is practically unlimited, the maximum demand for corduroy is 20 yards per month. The manufacturer has 150 pounds of cotton and 90 hours of processing time available each month. The manufacturer wants to make sure that it produces denim at least as twice the size of the corduroy produced. The manufacturer makes a profit of $5.00 per yard of corduroy and $2.50 per yard of denim. The manufacturer wants to know how many yards of each type of cloth to produce to maximize profit.
a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit.
b. Find the optimal solution using graphical approach.
a. Develop an excel spreadsheet model.
b. Find the optimal solution by solving the spreadsheet model.
Question 3:Problem 4 (page 191-192 in the text book 14e).
a. Formulate LP.
b. Develop an excel spreadsheet model and answer to parts (a), (b), and (c).

Question 4:A firm that assembles computers and computer equipment is about to start production of two new products, Product1 and Product2. Each product type will require assembly time, inspection time, and storage space. The amount of each of these resources that can be devoted to the production is limited.
The manager has met with design and manufacturing personnel and obtained the following information:
Product1 Product2
Profit per unit $60 $50
Assembly time per unit 4 hours 10 hours
Inspection time per unit 2 hours 1 hour
Storage space per unit 3 cubic feet 3 cubic feet

The manager has also acquired the information on the availability of company resources for these products.
Resource Daily availability
Assembly time 100 hours
Inspection time 22 hours
Storage space 39 cubic feet

Management wants to make sure that the number of units of Product1 produced must not exceed the number of units of Product2 produced. It is found that all of the produced products can be sold in the market.
a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit.
b. Develop an excel spreadsheet model.
c. Find the optimal solution by solving the spreadsheet model.

1. For each question, your mathematical model and answers to each individual part should be typed in Microsoft Word. For Questions 2 and 3, state your model by clearly defining your decision variables (with appropriate units), the objective function, and all the relevant (including the non-negativity) constraints. The graphical solution to Question #3could be hand-drawn, but you must submit a jpg or pdf file of your graph.
2. Excel spreadsheet models are required for each question. You are required to submit a singleworkbook containing all the separate spreadsheets. Make sure that each sheet carries the name of the question you are answering. Note: The spreadsheet model for each question is given a good portion of the marks.
3. For how to use Excel for breakeven analysis and for solving linear programming models, refer to Appendix 1.1 page 26 and Appendix 2.2 page 89 in your textbook 14e, respectively. Also, see APPENDIX A (textbook, 14e) for an overview of how to build spreadsheet models in Excel.
Question 1:Schmaltx, Ltd., produces a variety of specialty beverages. One of its products is made in a separate facility for which monthly rent, administrative costs, and equipment leasing is $50,000. Eight workers handle production and shipping. Each receives salary and fringe benefits of $2,000 per month. Packaging and distribution costs are $1.5 per case, and ingredients cost $3 per case. The product is sold for $9 a case. Determine the following:
a. Write an expression for the cost function, revenue function and profit function in terms of the number of cases of beverage produced, Q. (Assume there is no demand restriction on sales).
i. Find the break-even point algebraically.
ii. Formulate a spreadsheet model that will give the profit. Use the Excel goal seek command (Data > What-If-Analysis > Goal Seek) to calculate the break-even point in part a.
iii. Using the above spread sheet model find the profit or loss if Schmaltx produces and sells 10,000 cases.
iv. Using the above spread sheet model find the profit or loss if Schmaltx produces and sells 30,000 cases.

b. Write an expression for the profit function in terms of demand (in cases) for the beverage (S) and the number of cases of beverage produced (Q).
i. Formulate a spreadsheet model that will find the profit.
ii. Find the cases of beverage to be produced when S= 20,000 cases using the above spreadsheet model.
iii. Find the cases of beverage to be produced when S= 10,000 cases using the above spreadsheet model.

Question 2: Modern Textile Mills produces two types of cotton cloth, denim and corduroy. Corduroy is a heavier grade of cotton cloth and requires 5 pounds of raw cotton per yard, whereas denim requires 3 pounds of raw cotton per yard. A yard of corduroy requires 3.6 hours of processing time; a yard of denim requires 3.0 hours. Although the demand for denim is practically unlimited, the maximum demand for corduroy is 20 yards per month. The manufacturer has 150 pounds of cotton and 90 hours of processing time available each month. The manufacturer wants to make sure that it produces denim at least as twice the size of the corduroy produced. The manufacturer makes a profit of $5.00 per yard of corduroy and $2.50 per yard of denim. The manufacturer wants to know how many yards of each type of cloth to produce to maximize profit.
a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit.
b. Find the optimal solution using graphical approach.
a. Develop an excel spreadsheet model.
b. Find the optimal solution by solving the spreadsheet model.
Question 3:Problem 4 (page 191-192 in the text book 14e).
a. Formulate LP.
b. Develop an excel spreadsheet model and answer to parts (a), (b), and (c).

Question 4:A firm that assembles computers and computer equipment is about to start production of two new products, Product1 and Product2. Each product type will require assembly time, inspection time, and storage space. The amount of each of these resources that can be devoted to the production is limited.
The manager has met with design and manufacturing personnel and obtained the following information:
Product1 Product2
Profit per unit $60 $50
Assembly time per unit 4 hours 10 hours
Inspection time per unit 2 hours 1 hour
Storage space per unit 3 cubic feet 3 cubic feet

The manager has also acquired the information on the availability of company resources for these products.
Resource Daily availability
Assembly time 100 hours
Inspection time 22 hours
Storage space 39 cubic feet

Management wants to make sure that the number of units of Product1 produced must not exceed the number of units of Product2 produced. It is found that all of the produced products can be sold in the market.
a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit.
b. Develop an excel spreadsheet model.
c. Find the optimal solution by solving the spreadsheet model.