L11 D Н K 6 Data on ingots Caution: Ingot 1 Ingot 2 Ingot 3 Ingot 4 Do not alter the structure of this Excel file like 8 Weight (Tons) 4 4 adding/deleting columns/rows, moving contents from their 9 Cost per ton Ingot 350 330 310 280 cells, or renaming sheet names. Use the specified 10 Carbon % (Ingot) output cell range. You may change the column widths slightly to accommodate proper formatting, . 0.05 0.04 0.03 0.03 11 Molybdenum % (Ingot) 0.03 0.03 0.04 0.04 12 but changing the widths in a significant manner is prohibited 13 Data on alloys slightly to accommodate proper formatting, 14 Alloy 1 Alloy 2 Alloy 3 Scrap but changing the widths in a significant manner is prohibited. 15 Cost per ton Other 500 450 400 Not following these instructions can result in serious loss of points. 16 Carbon % (Alloy) 0.08 0.07 0.06 17 Molybdenum % (Alloy) 0.06 0.07 0.08 All cells in all sheets must be properly formatted. 18 These include aligning contents, using appropriate 19 Required percentages number format and decimal 20 Carbon required places for numerical contents, use of bolding, italics, borders, etc. in an 21 Molybdenum required appropriate fashion so that the readability of the content is enhanced. 22 23 Buy ingot or not (1 if purchased, O if not) 24 Ingot 1 Ingot 2 Ingot 3 Ingot 4 25 Buy ingot? 26 Ingot in tons bought 27 28 Tons of others to purchase 29 Alloy 1 Alloy 2 Alloy 3 Scrap 30 Tons of alloy purchased F22 G Н K 32 Constraint on total steel (in tons) purchased 33 From Ingot From Other Total steel Steel required 34 35 36 Percentage requirements (in tons) Hint: For steel production the requirement of alloy elements must be exactly equal, not more, not less. From ingots Total tons Required Tons 37 From others 38 Carbon (Tons) 39 Molybdenum (Tons) 40 41 For ingots For others Total cost 42 Total cost Answer key: 8240 43
L11 D Н K 6 Data on ingots Caution: Ingot 1 Ingot 2 Ingot 3 Ingot 4 Do not alter the structure of this Excel file like 8 Weight (Tons) 4 4 adding/deleting columns/rows, moving contents from their 9 Cost per ton Ingot 350 330 310 280 cells, or renaming sheet names. Use the specified 10 Carbon % (Ingot) output cell range. You may change the column widths slightly to accommodate proper formatting, . 0.05 0.04 0.03 0.03 11 Molybdenum % (Ingot) 0.03 0.03 0.04 0.04 12 but changing the widths in a significant manner is prohibited 13 Data on alloys slightly to accommodate proper formatting, 14 Alloy 1 Alloy 2 Alloy 3 Scrap but changing the widths in a significant manner is prohibited. 15 Cost per ton Other 500 450 400 Not following these instructions can result in serious loss of points. 16 Carbon % (Alloy) 0.08 0.07 0.06 17 Molybdenum % (Alloy) 0.06 0.07 0.08 All cells in all sheets must be properly formatted. 18 These include aligning contents, using appropriate 19 Required percentages number format and decimal 20 Carbon required places for numerical contents, use of bolding, italics, borders, etc. in an 21 Molybdenum required appropriate fashion so that the readability of the content is enhanced. 22 23 Buy ingot or not (1 if purchased, O if not) 24 Ingot 1 Ingot 2 Ingot 3 Ingot 4 25 Buy ingot? 26 Ingot in tons bought 27 28 Tons of others to purchase 29 Alloy 1 Alloy 2 Alloy 3 Scrap 30 Tons of alloy purchased F22 G Н K 32 Constraint on total steel (in tons) purchased 33 From Ingot From Other Total steel Steel required 34 35 36 Percentage requirements (in tons) Hint: For steel production the requirement of alloy elements must be exactly equal, not more, not less. From ingots Total tons Required Tons 37 From others 38 Carbon (Tons) 39 Molybdenum (Tons) 40 41 For ingots For others Total cost 42 Total cost Answer key: 8240 43
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...
Related questions
Question
68.
Based on Westerberg, Bjorklund, and Hultman (1977). Newcor’s steel mill has received an order for 25 tons of steel. The steel must be 5% carbon and 6% molybdenum by weight. The steel is manufactured by combining three types of metal: steel ingots, scrap steel, and alloys. Four individual steel ingots are available. At most, one of each can be purchased. The weight (in tons), cost per ton, and the carbon and molybdenum content of each ingot are given in the cells filled in light blue. Three types of alloys can be purchased. The cost per ton and chemical makeup of each alloy are given in the same file. Steel scrap can be purchased at a cost of $100 per ton. Steel scrap contains 3% carbon and 9% molybdenum. Determine how Newcor can minimize the cost of filling its order.
Transcribed Image Text:L11
D
Н
K
6 Data on ingots
Caution:
Ingot 1
Ingot 2
Ingot 3
Ingot 4
Do not alter the structure of this Excel file like
8 Weight (Tons)
4
4
adding/deleting columns/rows, moving contents from their
9 Cost per ton Ingot
350
330
310
280
cells, or renaming sheet names. Use the specified
10 Carbon % (Ingot)
output cell range. You may change the column widths
slightly to accommodate proper formatting, .
0.05
0.04
0.03
0.03
11 Molybdenum % (Ingot)
0.03
0.03
0.04
0.04
12
but changing the widths in a significant manner is prohibited
13 Data on alloys
slightly to accommodate proper formatting,
14
Alloy 1
Alloy 2
Alloy 3
Scrap
but changing the widths in a significant manner is prohibited.
15 Cost per ton Other
500
450
400
Not following these instructions can result in serious loss of points.
16 Carbon % (Alloy)
0.08
0.07
0.06
17 Molybdenum % (Alloy)
0.06
0.07
0.08
All cells in all sheets must be properly formatted.
18
These include aligning contents, using appropriate
19 Required percentages
number format and decimal
20 Carbon required
places for numerical contents, use of bolding, italics, borders, etc. in an
21 Molybdenum required
appropriate fashion so that the readability of the content is enhanced.
22
23 Buy ingot or not (1 if purchased, O if not)
24
Ingot 1
Ingot 2
Ingot 3
Ingot 4
25 Buy ingot?
26 Ingot in tons bought
27
28 Tons of others to purchase
29
Alloy 1
Alloy 2
Alloy 3
Scrap
30 Tons of alloy purchased
Transcribed Image Text:F22
G
Н
K
32 Constraint on total steel (in tons) purchased
33
From Ingot
From Other
Total steel
Steel required
34
35
36 Percentage requirements (in tons)
Hint:
For steel production the requirement of alloy elements
must be exactly equal, not more, not less.
From ingots
Total tons
Required Tons
37
From others
38 Carbon (Tons)
39 Molybdenum (Tons)
40
41
For ingots
For others
Total cost
42 Total cost
Answer key: 8240
43
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 3 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, operations-management and related others by exploring similar questions and additional content below.Recommended textbooks for you
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Purchasing and Supply Chain Management
Operations Management
ISBN:
9781285869681
Author:
Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:
Cengage Learning
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.