Let's walk through the problem. 1. A steel mill produces two types of steel alloy: boral and chromal. This is the first important piece of informationthe product mix (i.e., these are 2. Production of each alloy requires three process: Box anneal, Cold Roll, and Strand anneal. The is the next important piece of information. Each of these three processes is i. Box anneal: 4000 hours/month ii. Cold Roll: 500 hours/month iii. Strand anneal: 1,000 hours/month Note that these values are the right-hand side of the constraints. 3. Now look at the table of production rates for each constraint at the top of page 236. These v The problem is that they give us these values as tons/hour. But the final answer is going to be ho For example, if we set up the Box anneal constraint as is, it looks like this: 4 tons/hour*Boral + 2 tons/hour*Chromal <= 4000 hours/month Once we set up the constraint, Solver will replace the \"Boral\" placeholder with a number of tons For example, let's substitute 2 for Boral and 3 for Chromal and calculate: 4 tons/hour*2 tons of Boral + 2 tons/hour*3 tons of Chromal = 14 tons2/hour But we can't compare this to the right hand side because the units don't make sense (tons 2/hour So what we have to do is to invert the values in the left hand side of the constraint so that the un For example: 4 tons/hour = 1 hour/ 4 tons or hour/ton. So the inverted constraint is: hour/tons*2 tons of Boral + hour/tons * 3 tons of Chromal = 2/4 + 3/2 = 2 hours (the tons un Now we can compare this to the right hand side of the constraint and see that the inequality is m 2 hours <= 4000 hours/month You will need to do this for every constraint in the table. Note that the inverted values may be ve 4. The last thing to set up is the objective function (goal) of the problem. This is a mathematica In this case, we want to maximize the contribution/ton of the alloys. We are told that the contrib Right now we don't know how many tons of the boral and chromal we will use, so let's set up the Goal = $/ton contribution*tons of Boral + $/ton contribution*tons of Chromal Or: Maximize contribution = _____dollars/ton*tons of Boral + _____dollars/ton*tons of Chroma Once we know exactly how many tons of Boral and tons of Chromal to use that will meet all of th For example (and I am making these numbers up so don't use them as check figures): $5/ton*10 tons of Boral + $10/ton* 5 tons of Chromal = $100 (note that the tons units cancel leav These 3 steps are the hardest part of setting up the problem. IF you do everything correctly, once you run Solv I can't emphasize enough, the importance of watching the recommended YouTube videos. They will walk you through all this and talk about the reports, hopefully making it much clearer. e product mix (i.e., these are the decision variables, what it is we are trying to produce). So we are interested in producin d Roll, and Strand anneal. ch of these three processes is considered to be a constraint (restriction) because we only have a limited amount 0 hours/month ours/month 1,000 hours/month the top of page 236. These values will make up the left-hand side of the constraint. For example, for the Box anneal cons final answer is going to be how many tons of boral and how many tons of chromal we should use to meet these restriction older with a number of tons and the \"Chormal\" placeholder with a number of tons and calculate the left-hand side of the on't make sense (tons 2/hour vs hours/month). the constraint so that the units are hours/tons. 4 + 3/2 = 2 hours (the tons units cancel and leave only hours). d see that the inequality is met. he inverted values may be very, very small numbers so please carry them out 4 or 5 decimal places. blem. This is a mathematical statement of the goal to either maximize or minimize something (e.g., maximize profit or m We are told that the contribution/ton for the boral is $25 and the contribution/ton for the chromal is $35. we will use, so let's set up the objective function like this: _dollars/ton*tons of Chromal to use that will meet all of the constraints, we simply plug those values into the objective function and calculate the maxi as check figures): hat the tons units cancel leaving only $) g correctly, once you run Solver, you should end up with a total contribution of $348,275.86. much clearer. we are interested in producing steel using boral and chromal alloys. The questions is - how much of each do we use? e a limited amount of time per month for these processes. How much time? mple, for the Box anneal constraint, it takes 4 tons/hour to process the Boral and 2 tons/hour to process the Chromal in th use to meet these restrictions. late the left-hand side of the constraint to see if it meets the inequality conditions of the right-hand side. ng (e.g., maximize profit or minimize cost). chromal is $35. nction and calculate the maximum contribution. uch of each do we use? to process the Chromal in the steel making process. ht-hand side. Problems 7-2 Name: 1. Identify the decision variables, objective function, and constraints. NOTE: There is a similar example in Bell & Zaric on pages 210-224 Objective Function Goal (Maximize or Minimize) Max Contribution Boral Alloy user: user: Put here the Put here the contribution for contribution for the Chromal the Boral Alloy Alloy Chromal Alloy PLEASE READ!! 2. Mathematically formulate a linear optimization model (type the formulas). Here is the tricky part to the problem. Note that the table on page 236 gives you the production rates in tons/hour. However, the Resources are in hours/months. If we were to set up our constraint as: 4tons/hour*Boral + 2tons/hour*Chromal <= 4000hours/month then when Solver replaces "Boral" and "Chromal" with actual numbers in tons, we would end up with tons2/hour <= hours/month which makes no sense. So, what if we invert tons/hour to hour/tons (e.g., 4tons/hour = 1hour/4tons). Then when Solver runs, the tons units would cancel on the left hand side and we would be left with hours <= hours/month user: which makes user:perfect sense. user: Enter the the So, as you set up your constraints, be sure to invert the values foruser: the first threeEnter constraints. user: Enter the total coefficient for Enter the Decision Variables user: this decision Constraints Box Anneal Cold Roll (Both Passes) Strand Anneal Boral Max Demand Chromal Max Demand Boral Alloy coefficient for Enter thehere. variable this decision coefficient for See the above variable here. Chromal Alloy this decision Instructions. See the above variable here. Instructions. See the above Instructions. 1 0 0 1 coefficient Enter the for user: this decision coefficient for Enter thehere. variable this decision coefficient for See the above variable here. Resources this decision Instructions. See the above variable here. Instructions. See the above Instructions. 10500 6000 2. Mathematically formulate a linear optimization model (type the formulas, see page 213). Objective Function: Subject to: Box Anneal Cold Roll (both passes) Strand Anneal Boral Max Demand Chromal Max Demand Nonnegativity Assumption user: amount Enter theoftotal user: resource amount Enter theof(given total available resource amount of in the problem). available resource (given in the problem). available (given in the problem). user: State the objective function in user: formula format. State this user: constraint State this in user: formula format. constraint in State this user: formula format. constraint State this in user: formula format. constraint State this in user: formula format. constraint State this in formula format. constraint in formula format. 5. Explain the Answer Report and Sensitivity Report as specifed below. Be detailed but brief. Type your answers below. Watch this first: https://www.youtube.com/watch?v=mT9Hjylw_kQ Answer Report: Otimal Solution: What is the maximum contribution? How much of the Boral Alloy should we use? How much of the Chromal Alloy should we use? Binding/Non-Binding Constraints: Which constraints are binding? How do you know they are binding? Which constraints are non-binding? State the Slack for each and what that means with respect to this problem: Sensitivity Report: Shadow Price: Explain the Shadow Price for the following constraints: Box Anneal Cold Roll (both passes) Strand Anneal 3. Implement the linear optimization model and use Solver to find the optimal solution. user: user: Report before closing the SolverUse the Be sure to specify Answer Report and Sensitivity dialog. user: Leave blank. SUMPRODUCT() Leave blank. These reports will be given in separate tabs in isthe worksheet. This Solver's function here to Boral Alloy Tons to produce onth tons ser: nter the total ser: mount nter theoftotal ser: source mount oftotal nter the ailable (given to compute RightFormulas source mount ofhand side of constraint the problem). ailable (given source the problem). ailable (given the problem). ser: ate the jective nction in ser: rmula format. ate this ser: nstraint ate this in ser: rmula format. nstraint in ate this ser: rmula format. nstraint ate this in ser: rmula format. nstraint ate this in ser: rmula format. nstraint ate this in rmula format. nstraint in rmula format. wers below. This is Solver's working space. working space. TOTAL Chromal Alloy CONTRIBUTION compute the total contribution. NOTE: Leave cells J6:K6 empty. This is Solver's workspace or "Changing Cells". When Solver is done, it will put the optimal solution there. NOTE: Use SUMPRODUCT() to compute the total profit in cell M6 and the Right-hand-side of the constraints in cells I11 - I15. Solver will use these formulas to compute the optimal solution. For example: =(SUMPRODUCT(J6:K6,E6:F6) user: For more Use information on the SUMPRODUCT() function, see user: SUMPRODUCT() Use user: here to compute SUMPRODUCT() Use the total user: here to compute SUMPRODUCT() resources used. Use the total user: here to compute SUMPRODUCT() resources used. Use the total here to compute SUMPRODUCT() resources used. the heretotal to compute resources the total used. resources used