I need explaination and spreadsheet with formulas and step to step interpretation please.....
The attached are first two pages of required assisance and below is the rest of instructions please consider all of them:
c. Suppose the CEO says, “Projects 3 and 4 must be undertaken but not both.”
Describe the constraint.
d. Suppose the CEO says, “Projects 4 cannot be undertaken unless projects 1 and 3
also are both undertaken.” Describe the constraints. Hint: You may need to add more than one constraint for this part.
3. Multi-product Production Planning. Hart Manufacturing makes three products. Each product requires manufacturing operations in three departments: A, B, and C. The labor-hour requirements, by department, are as follows:
Department Product 1 Product 2 Product 3
A 1.50 3.00 2.00
B 2.00 1.00 2.50
C 0.25 0.25 0.25
During the next production period, the labor-hours available are 450 in department A, 350 in department B, and 50 in department C. The profit contributions per unit are $25 for product 1, $28 for product 2, and $30 for product 3.
a. [Math model] Formulate a linear programming model algebraically for maximizing total profit contribution.
b. [Spreadsheet model] Solve the linear program formulated in part (a) by using Excel Solver. How much of each product should be produced, and what is the projected total profit contribution?
c. After evaluating the solution obtained in part (b), one of the production supervisors noted that production setup costs had not been taken into account. She noted that setup costs are $400 for product 1, $550 for product 2, and $600 for product 3. If the solution developed in part (b) is to be used, what is the total
profit contribution after taking into account the setup costs? d. [Math model] Management realized that the optimal product mix, taking setup costs into account, might be different from the one recommended in part (b).
Formulate a mixed-integer linear program that takes setup costs into account.
e. [Spreadsheet model] Solve the mixed-integer linear program formulated in part (d) by using Excel Solver. How much of each product should be produced, and what is the projected total profit contribution? Compare this profit contribution to that obtained in part (c).
Transcribed Image Text:For each of the following parts, answer the question as specifically and completely as is possible without re-solving the problem with the Excel Solver. Note: Each part is independent (i.e., any change made in one part does not apply to any other parts). a. Suppose the profit per plate decreases from $3.10 to $2.80. Will this change the optimal production quantities? What can be said about the change in total profit? b. Suppose the profit per stein increases by $0.30 and the profit per plate decreases by $0.25. Will this change the optimal production quantities? What can be said about the change in total profit? c. Suppose a worker in the molding department calls in sick. Now eight fewer hours are available that day in the molding department. How much would this affect total profit? Would it change the optimal production quantities? d. Suppose one of the workers in the molding department is also trained to do finishing. Would it be a good idea to have this worker shift some of her time from the molding department to the finishing department? Indicate the rate at which this would increase or decrease total profit per minute shifted. How many minutes can be shifted before this rate might change? e. The allowable decrease for the available clay constraint is missing from the sensitivity report. What numbers should be there? Explain how you were able to deduce each number? 2. The Ice-Cold Refrigerator Company is considering investing in several projects that have varying capital requirements over the next four years. Faced with limited capital each year, management would like to select the most profitable projects. The estimated net present value for each project, the capital requirements, and the available capital over the four-year period are shown in the table below. Project Plant Expansion Warehouse Expansion New Machinery New Product Research Present Value $90,000 $40,000 $10,000 $37,000 Total Capital Available Year 1 Cap Rqmt $15,000 $10,000 $10,000 $15,000 $40,000 Year 2 Cap Rqmt $20,000 $15,000 $10,000 $50,000 Year 3 Cap Rqmt $20,000 $20,000 $10,000 $40,000 Year 4 Cap Rqmt $15,000 $ 5,000 $ 4,000 $10,000 $35,000 a. You have been asked to recommend which projects should be funded. Your goal is to achieve the highest total expected return on the investment. Please note that (1) a project can be either fully funded or not funded, but not partially funded; (2) Capital that is not used in current year can NOT be carried over to the next year. Please provide the mathematic model of your formulation. b. Suppose the CEO says, "exactly two of the projects 1, 2, and 4 must be undertaken." Describe the constraint.
Transcribed Image Text:1. University Ceramics manufactures plates, mugs, and steins that include the campus name and logo for sale in campus bookstores. The time required for each item to go through the two stages of production (molding and finishing), the material required (clay), and the corresponding unit profits are given in the following table, along with the amount of each resource available for tomorrow's eight-hour shift. Available Plates Mugs Steins Molding (minutes) Finishing (minutes) 4 6 3 2,400 8 14 12 7,200 Clay (ounces) 5 4 3 3,000 Unit Profit $3.10 $4.75 $4.00 A linear programming model has been formulated in a spreadsheet to determine the production levels that would maximize profit. The solved spreadsheet model and corresponding sensitivity report are shown below. A 1 B C D E F G Plates 2 3 Unit Profit $3.10 Mugs $4.75 Steins $4.00 4 Resource Required per Unit Used Available 5 Molding (minutes) 3 2,400 <= 2.400 6 Finishing (minutes) 8 14 12 7.200 <<= 7.200 7 Clay (ounces) 5 4 3 2,700 <= 3,000 8 9 Plates Mugs Steins 10 Production 300 0 400 Total Profit $2.530 Variable Cells Cell Name Final Value Reduced Cost Objective Coefficient Allowable Increase Allowable Decrease $B$10 Production Plates 300 0 3.10 2.23 0.37 $C$10 Production Mugs 0 -0.46 4.75 0.46 $D$10 Production Steins 400 ° 4.00 0.65 1.37 Constraints Final Cell Name Value Shadow Price Constraint R. H. Side Allowable Increase Allowable Decrease SE$5 Molding (minutes) Used 2,400 0.22 2400 200 600 $E$6 Finishing (minutes) Used 7,200 0.28 7200 2400 2400 $E$7 Clay (ounces) Used 2,700 0 3000 1E+30

Question

I need explaination and spreadsheet with formulas and step to step interpretation please.....
The attached are first two pages of required assisance and below is the rest of instructions please consider all of them:
c. Suppose the CEO says, “Projects 3 and 4 must be undertaken but not both.”
Describe the constraint.
d. Suppose the CEO says, “Projects 4 cannot be undertaken unless projects 1 and 3
also are both undertaken.” Describe the constraints. Hint: You may need to add more than one constraint for this part.
3. Multi-product Production Planning. Hart Manufacturing makes three products. Each product requires manufacturing operations in three departments: A, B, and C. The labor-hour requirements, by department, are as follows:
Department Product 1 Product 2 Product 3
A 1.50 3.00 2.00
B 2.00 1.00 2.50
C 0.25 0.25 0.25
During the next production period, the labor-hours available are 450 in department A, 350 in department B, and 50 in department C. The profit contributions per unit are $25 for product 1, $28 for product 2, and $30 for product 3.
a. [Math model] Formulate a linear programming model algebraically for maximizing total profit contribution.
b. [Spreadsheet model] Solve the linear program formulated in part (a) by using Excel Solver. How much of each product should be produced, and what is the projected total profit contribution?
c. After evaluating the solution obtained in part (b), one of the production supervisors noted that production setup costs had not been taken into account. She noted that setup costs are $400 for product 1, $550 for product 2, and $600 for product 3. If the solution developed in part (b) is to be used, what is the total
profit contribution after taking into account the setup costs? d. [Math model] Management realized that the optimal product mix, taking setup costs into account, might be different from the one recommended in part (b).
Formulate a mixed-integer linear program that takes setup costs into account.
e. [Spreadsheet model] Solve the mixed-integer linear program formulated in part (d) by using Excel Solver. How much of each product should be produced, and what is the projected total profit contribution? Compare this profit contribution to that obtained in part (c).

For each of the following parts, answer the question as specifically and completely as is
possible without re-solving the problem with the Excel Solver. Note: Each part is
independent (i.e., any change made in one part does not apply to any other parts).
a. Suppose the profit per plate decreases from $3.10 to $2.80. Will this change the
optimal production quantities? What can be said about the change in total
profit?
b. Suppose the profit per stein increases by $0.30 and the profit per plate
decreases by $0.25. Will this change the optimal production quantities? What
can be said about the change in total profit?
c. Suppose a worker in the molding department calls in sick. Now eight fewer hours
are available that day in the molding department. How much would this affect
total profit? Would it change the optimal production quantities?
d. Suppose one of the workers in the molding department is also trained to do
finishing. Would it be a good idea to have this worker shift some of her time
from the molding department to the finishing department? Indicate the rate at
which this would increase or decrease total profit per minute shifted. How many
minutes can be shifted before this rate might change?
e. The allowable decrease for the available clay constraint is missing from the
sensitivity report. What numbers should be there? Explain how you were able to
deduce each number?
2. The Ice-Cold Refrigerator Company is considering investing in several projects
that have varying capital requirements over the next four years. Faced with limited
capital each year, management would like to select the most profitable projects. The
estimated net present value for each project, the capital requirements, and the
available capital over the four-year period are shown in the table below.
Project
Plant
Expansion
Warehouse
Expansion
New
Machinery
New Product
Research
Present Value
$90,000
$40,000
$10,000
$37,000
Total Capital
Available
Year 1 Cap Rqmt
$15,000
$10,000
$10,000
$15,000
$40,000
Year 2 Cap Rqmt
$20,000
$15,000
$10,000
$50,000
Year 3 Cap Rqmt
$20,000
$20,000
$10,000
$40,000
Year 4 Cap Rqmt
$15,000
$ 5,000
$ 4,000
$10,000
$35,000
a. You have been asked to recommend which projects should be funded. Your goal
is to achieve the highest total expected return on the investment. Please note
that (1) a project can be either fully funded or not funded, but not partially
funded; (2) Capital that is not used in current year can NOT be carried over to
the next year. Please provide the mathematic model of your formulation.
b. Suppose the CEO says, "exactly two of the projects 1, 2, and 4 must be
undertaken." Describe the constraint.

Transcribed Image Text:1. University Ceramics manufactures plates, mugs, and steins that include the campus name and logo for sale in campus bookstores. The time required for each item to go through the two stages of production (molding and finishing), the material required (clay), and the corresponding unit profits are given in the following table, along with the amount of each resource available for tomorrow's eight-hour shift. Available Plates Mugs Steins Molding (minutes) Finishing (minutes) 4 6 3 2,400 8 14 12 7,200 Clay (ounces) 5 4 3 3,000 Unit Profit $3.10 $4.75 $4.00 A linear programming model has been formulated in a spreadsheet to determine the production levels that would maximize profit. The solved spreadsheet model and corresponding sensitivity report are shown below. A 1 B C D E F G Plates 2 3 Unit Profit $3.10 Mugs $4.75 Steins $4.00 4 Resource Required per Unit Used Available 5 Molding (minutes) 3 2,400 <= 2.400 6 Finishing (minutes) 8 14 12 7.200 <<= 7.200 7 Clay (ounces) 5 4 3 2,700 <= 3,000 8 9 Plates Mugs Steins 10 Production 300 0 400 Total Profit $2.530 Variable Cells Cell Name Final Value Reduced Cost Objective Coefficient Allowable Increase Allowable Decrease $B$10 Production Plates 300 0 3.10 2.23 0.37 $C$10 Production Mugs 0 -0.46 4.75 0.46 $D$10 Production Steins 400 ° 4.00 0.65 1.37 Constraints Final Cell Name Value Shadow Price Constraint R. H. Side Allowable Increase Allowable Decrease SE$5 Molding (minutes) Used 2,400 0.22 2400 200 600 $E$6 Finishing (minutes) Used 7,200 0.28 7200 2400 2400 $E$7 Clay (ounces) Used 2,700 0 3000 1E+30

Accepted Answer

Find the given details below:Since you have submitted multiple questions with multiple subparts, as…