The Alpine Sports Company makes a line of winter sports equipment, including skis, snowboards, and sleds. Each product uses time on an extruder machine and requires time in final assembly. In addition, each product is largely made of fiberglass. They have formulated a linear programming spreadsheet model to determine the production levels that would maximize profit. The spreadsheet model and sensitivity report are shown below. Answer the following questions as completely as is possible without re-solving the problem with Solver. Justify your answers using the results from the sensitivity report. All problems are independent (i.e., any change made in one part does not affect the other parts). В D E F G H 1 Alpine Sports Skis (pair) $90 Snowboard Sled 4 Profit Per Unit $140 $110 5 6 Resources Resources Required per Unit Produced Totals Available Extruder (minutes) Final Assembly (minutes) Fiberglass (pounds) 7 8 4 4 7200 7200 8 10 12 8 12780 <= 14400 4 12 15 9000 <= 9000 10 11 Skis (pair) Snowboard Sled Total Profit 12 Production 630 540 $132,300 Variable Cells Reduced Allowable Increase Final Objective Coefficient Allowable Cell Name Value Cost Decrease Production Skis (pair) 90 $C$12 $D$12 $E$12 630 190 43.33 Production Snowboard Production Sled 540 140 130 45 -58.5 110 58.5 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $F$7 $F$8 $F$9 Extruder (minutes) Totals Final Assembly (minutes) Totals 12780 Fiberglass (pounds) Totals 1800 1E+30 2314.29 7200 6.5 7200 4200 14400 9000 1620 9000 9.5 5400 a. Suppose the profit per snowboard decreases from $140 to $120. Will this change the optimal production quantities? What can be said about the change in total profit? b. How much would the selling price of sleds need to be increased before it might become profitable to produce this product? c. Suppose the profit per pair of skis increases by $45 and the profit per snowboard decreases by $40. Will this change the optimal production quantities? What can be said about the change in
The Alpine Sports Company makes a line of winter sports equipment, including skis, snowboards, and sleds. Each product uses time on an extruder machine and requires time in final assembly. In addition, each product is largely made of fiberglass. They have formulated a linear programming spreadsheet model to determine the production levels that would maximize profit. The spreadsheet model and sensitivity report are shown below. Answer the following questions as completely as is possible without re-solving the problem with Solver. Justify your answers using the results from the sensitivity report. All problems are independent (i.e., any change made in one part does not affect the other parts). В D E F G H 1 Alpine Sports Skis (pair) $90 Snowboard Sled 4 Profit Per Unit $140 $110 5 6 Resources Resources Required per Unit Produced Totals Available Extruder (minutes) Final Assembly (minutes) Fiberglass (pounds) 7 8 4 4 7200 7200 8 10 12 8 12780 <= 14400 4 12 15 9000 <= 9000 10 11 Skis (pair) Snowboard Sled Total Profit 12 Production 630 540 $132,300 Variable Cells Reduced Allowable Increase Final Objective Coefficient Allowable Cell Name Value Cost Decrease Production Skis (pair) 90 $C$12 $D$12 $E$12 630 190 43.33 Production Snowboard Production Sled 540 140 130 45 -58.5 110 58.5 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $F$7 $F$8 $F$9 Extruder (minutes) Totals Final Assembly (minutes) Totals 12780 Fiberglass (pounds) Totals 1800 1E+30 2314.29 7200 6.5 7200 4200 14400 9000 1620 9000 9.5 5400 a. Suppose the profit per snowboard decreases from $140 to $120. Will this change the optimal production quantities? What can be said about the change in total profit? b. How much would the selling price of sleds need to be increased before it might become profitable to produce this product? c. Suppose the profit per pair of skis increases by $45 and the profit per snowboard decreases by $40. Will this change the optimal production quantities? What can be said about the change in
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
Transcribed Image Text:Alpine Sports
The Alpine Sports Company makes a line of winter sports equipment, including skis, snowboards, and sleds. Each
product uses time on an extruder machine and requires time in final assembly. In addition, each product is largely
made of fiberglass. They have formulated a linear programming spreadsheet model to determine the production
levels that would maximize profit. The spreadsheet model and sensitivity report are shown below. Answer the
following questions as completely as is possible without re-solving the problem with Solver. Justify your answers
using the results from the sensitivity report. All problems are independent (i.e., any change made in one part
does not affect the other parts).
A
C
F
G
H
1 Alpine Sports
Skis (pair)
$90
Snowboard
Sled
4
Profit Per Unit
$140
$110
6.
Resources
Resources Required per Unit Produced
Totals
Available
Extruder (minutes)
Final Assembly (minutes)
Fiberglass (pounds)
7
8
4
4
7200
<3=
7200
8
10
12
8.
12780
<=
14400
9.
4
12
15
9000
9000
10
11
Skis (pair)
Snowboard
Sled
Total Profit
12
Production
630
540
$132,300
Variable Cells
Objective
Coefficient
Final
Reduced
Allowable
Allowable
Cell
Name
Value
Cost
Increase
Decrease
Production Skis (pair)
190
$C$12
$D$12
$E$12
630
90
43.33
Production Snowboard
540
140
130
45
Production Sled
-58.5
110
58.5
1E+30
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$F$7
Extruder (minutes) Totals
Final Assembly (minutes) Totals 12780
Fiberglass (pounds) Totals
7200
6.5
7200
1800
4200
$F$8
$F$9
14400
1E+30
1620
9000
9.5
9000
2314.29
5400
a. Suppose the profit per snowboard decreases from $140 to $120. Will this change the optimal
production quantities? What can be said about the change in total profit?
b. How much would the selling price of sleds need to be increased before it might become
profitable to produce this product?
c. Suppose the profit per pair of skis increases by $45 and the profit per snowboard decreases by
$40. Will this change the optimal production quantities? What can be said about the change in
total profit?
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 2 steps
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.