1. University Ceramics manufactures plates, mugs, and steins that include thecampus name and logo for sale in campus bookstores. The time required for each itemto go through the two stages of production (molding and finishing), the materialrequired (clay), and the corresponding unit profits are given in the following table, alongwith the amount of each resource available for tomorrow's eight-hour shift.AvailablePlatesMugsSteinsMolding (minutes)Finishing (minutes)4632,400814127,200Clay (ounces)5433,000Unit Profit$3.10$4.75$4.00A linear programming model has been formulated in a spreadsheet to determine theproduction levels that would maximize profit. The solved spreadsheet model andcorresponding sensitivity report are shown below.A1BCDEFGPlates23Unit Profit$3.10Mugs$4.75Steins$4.004Resource Required per UnitUsedAvailable5Molding (minutes)32,400<=2.4006Finishing (minutes)814127.200<<=7.2007Clay (ounces)5432,700<=3,00089PlatesMugsSteins10Production3000400Total Profit$2.530Variable CellsCellNameFinalValueReducedCostObjectiveCoefficientAllowableIncreaseAllowableDecrease$B$10 Production Plates30003.102.230.37$C$10 Production Mugs0-0.464.750.46$D$10 Production Steins 400°4.000.651.37ConstraintsFinalCellNameValueShadowPriceConstraint R. H.SideAllowableIncreaseAllowableDecreaseSE$5 Molding (minutes) Used2,4000.222400200600$E$6 Finishing (minutes) Used7,2000.28720024002400$E$7 Clay (ounces) Used2,700030001E+30 For each of the following parts, answer the question as specifically and completely as ispossible without re-solving the problem with the Excel Solver. Note: Each part isindependent (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 theoptimal production quantities? What can be said about the change in totalprofit?b. Suppose the profit per stein increases by $0.30 and the profit per platedecreases by $0.25. Will this change the optimal production quantities? Whatcan be said about the change in total profit?c. Suppose a worker in the molding department calls in sick. Now eight fewer hoursare available that day in the molding department. How much would this affecttotal profit? Would it change the optimal production quantities?d. Suppose one of the workers in the molding department is also trained to dofinishing. Would it be a good idea to have this worker shift some of her timefrom the molding department to the finishing department? Indicate the rate atwhich this would increase or decrease total profit per minute shifted. How manyminutes can be shifted before this rate might change?e. The allowable decrease for the available clay constraint is missing from thesensitivity report. What numbers should be there? Explain how you were able todeduce each number?2. The Ice-Cold Refrigerator Company is considering investing in several projectsthat have varying capital requirements over the next four years. Faced with limitedcapital each year, management would like to select the most profitable projects. Theestimated net present value for each project, the capital requirements, and theavailable capital over the four-year period are shown in the table below.ProjectPlantExpansionWarehouseExpansionNewMachineryNew ProductResearchPresent Value$90,000$40,000$10,000$37,000Total CapitalAvailableYear 1 Cap Rqmt$15,000$10,000$10,000$15,000$40,000Year 2 Cap Rqmt$20,000$15,000$10,000$50,000Year 3 Cap Rqmt$20,000$20,000$10,000$40,000Year 4 Cap Rqmt$15,000$ 5,000$ 4,000$10,000$35,000a. You have been asked to recommend which projects should be funded. Your goalis to achieve the highest total expected return on the investment. Please notethat (1) a project can be either fully funded or not funded, but not partiallyfunded; (2) Capital that is not used in current year can NOT be carried over tothe 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 beundertaken." Describe the constraint.

Note:Since you have posted multiple questions, we will provide the solution only to the first…

Answered: For each of the following parts, answer…

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Related questions

Question

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

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.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 5 steps with 3 images

SEE SOLUTION Check out a sample Q&A here