As shown in the attachment I have part a done and I need help on parts b, c and d. Please use attachment to carry out parts b, c and d. Problem: Sunblessed Juice Company sells bags of oranges and cartons of orange juice. Sunblessed grades oranges on a scale of 1 (poor) to 10 (excellent). At present, Sunblessed has 220,000 pounds of grade 6 oranges and 150,000 pounds of grade 9 oranges on hand. The average quality of oranges sold in bags must be at least 7, and the average quality of the oranges used to produce orange juice must be at least 8. Each pound of oranges that is used for juice yields a revenue of $2.25 and in- curs a variable cost (consisting of labor costs, variable overhead costs, inventory costs, and so on) of $1.35. Each pound of oranges sold in bags yields a revenue of $2.00 and incurs a variable cost of $1.20. a.    Determine how Sunblessed can maximize its profit. b.    Use SolverTable to determine how a change in the cost per bag of oranges changes the optimal solution. c.    Use SolverTable to determine how a change in the amount of grade 6 oranges available affects the optimal solution. d.    Use SolverTable to determine how simultaneous changes in the required average quality required for juice and the average quality required for bags change the optimal solution.

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...
icon
Related questions
Question
100%

As shown in the attachment I have part a done and I need help on parts b, c and d. Please use attachment to carry out parts b, c and d.

Problem: Sunblessed Juice Company sells bags of oranges and cartons of orange juice. Sunblessed grades oranges on a scale of 1 (poor) to 10 (excellent). At present, Sunblessed has 220,000 pounds of grade 6 oranges and 150,000 pounds of grade 9 oranges on hand. The average quality of oranges sold in bags must be at least 7, and the average quality of the oranges used to produce orange juice must be at least 8. Each pound of oranges that is used for juice yields a revenue of $2.25 and in- curs a variable cost (consisting of labor costs, variable overhead costs, inventory costs, and so on) of $1.35. Each pound of oranges sold in bags yields a revenue of $2.00 and incurs a variable cost of $1.20.

a.    Determine how Sunblessed can maximize its profit.

b.    Use SolverTable to determine how a change in the cost per bag of oranges changes the optimal solution.

c.    Use SolverTable to determine how a change in the amount of grade 6 oranges available affects the optimal solution.

d.    Use SolverTable to determine how simultaneous changes in the required average quality required for juice and the average quality required for bags change the optimal solution. 

 

50.M
EI
rfectly i
demand
and cur
ect...titio
NE
Retireme
nniniec
D
Home Insert Draw Page Layout
G
Data from
Get Data (Power
Query)
Refresh
All
Stocks
Picture
x fx Selling oranges and orange juice
X ✓
A
B
C
D
E
1 Selling oranges and oilange juice
2
Revenues, variable costs
4
Bags
$2.00
5 Revenue
$2.25
6
Variable cost
$1.35
$1.20
7
8
Grades used to make juice and bags
9
Grade
Juice
10
6
26666.6667
Bags
193333.333
53333 96666.6667
11
9
12
Sold
80000
290000
13
14 Required average quality
15
Juice
Bags
7
16
8
17
18 Quality constraints
19
20 Actual
Juice
640000
X=
640000
21
Bags
2030000
X
2030000
Bags
$108,000 $348,000.00.
$180,000 $580,000.00
22 Required
23
24 Total variable cost
25 Total revenue
26
27 Profit
$72,000 $232,000.00
$304,000.001
28 Total Profit
29
30
31
32
33
34
35
36
37
38
20
P4-48
Point
P4-46
O
Juice
Juice
P4-50
JUN
20
Formulas
Used
220000
150000
+
<=
Data
Review
Geography
F
Available
220000
150000
Module 3 Problem Workbook (3)
View SolverTable for Mac
Clear
ZA
Y
AZ
Sort Filter
Reapply
Advanced
G
H
1
J
K
Solver Parameters
Set Objective:
$B$28
To: O Max
Min
Value Of:
By Changing Variable Cells:
$B$10:$C$11
Subject to the Constraints:
$B$20:$C$20 >= $B$22:$C$22
$D$10:$D$11 <= $F$10:$F$11
Add
Change
Delete
Reset All
Load/Save
Make Unconstrained Variables Non-Negative
Select a Solving Method:
Simplex LP
Options
Solving Method
Select the GRG Nonlinear engine for Solver Problems that are smooth
nonlinear. Select the LP Simplex engine for linear Solver Problems,
and select the Evolutionary engine for Solver problems that are non-
smooth.
Close
Solve
ران
2
Tell me
Text to
Columns
L
0
A
M
What-If
Analysis
N
N2
X
0
28
Outline
P
Ana
? Solv
Q
Transcribed Image Text:50.M EI rfectly i demand and cur ect...titio NE Retireme nniniec D Home Insert Draw Page Layout G Data from Get Data (Power Query) Refresh All Stocks Picture x fx Selling oranges and orange juice X ✓ A B C D E 1 Selling oranges and oilange juice 2 Revenues, variable costs 4 Bags $2.00 5 Revenue $2.25 6 Variable cost $1.35 $1.20 7 8 Grades used to make juice and bags 9 Grade Juice 10 6 26666.6667 Bags 193333.333 53333 96666.6667 11 9 12 Sold 80000 290000 13 14 Required average quality 15 Juice Bags 7 16 8 17 18 Quality constraints 19 20 Actual Juice 640000 X= 640000 21 Bags 2030000 X 2030000 Bags $108,000 $348,000.00. $180,000 $580,000.00 22 Required 23 24 Total variable cost 25 Total revenue 26 27 Profit $72,000 $232,000.00 $304,000.001 28 Total Profit 29 30 31 32 33 34 35 36 37 38 20 P4-48 Point P4-46 O Juice Juice P4-50 JUN 20 Formulas Used 220000 150000 + <= Data Review Geography F Available 220000 150000 Module 3 Problem Workbook (3) View SolverTable for Mac Clear ZA Y AZ Sort Filter Reapply Advanced G H 1 J K Solver Parameters Set Objective: $B$28 To: O Max Min Value Of: By Changing Variable Cells: $B$10:$C$11 Subject to the Constraints: $B$20:$C$20 >= $B$22:$C$22 $D$10:$D$11 <= $F$10:$F$11 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth. Close Solve ران 2 Tell me Text to Columns L 0 A M What-If Analysis N N2 X 0 28 Outline P Ana ? Solv Q
10
io
AI
fx
A
1 Selling oranges and orange juice
2
3
Revenues, variable costs
4
5 Revenue
6 Variable cost
7
8 Grades used to make juice and bags
9
Grade
10 6
11 9
12
Sold
13
14 Required average quality
15
16
17
18 Quality constraints
19
20 Actual
21
22 Required
23
24 Total variable cost
25 Total revenue
26
27 Profit
28 Total Profit
29
30
me
iec
31
32
33
34
35
36
37
38
20
Ready
X
P4-46
✓
Selling oranges and orange juice
B
Juice
2.25
1.35
26666.6666666667
53333.3333333333
193333.333333333
96666.6666666667
=SUM(C10:C11)
=SUM(B10:B11)
Juice
Bags
8
Juice
Bags
=SUMPRODUCT(A10:A11,B10: =SUMPRODUCT(A10:A11,C10
>
>
=B16 B12
=C16*C12
Bags
Juice
=B6*B12
=C6*C12
=B5*B12
=C5*C12
=825-B24
=C25-C24
=B27+C27
P4-50
JUN
20
P4-48
+
B A
Juice
7
2
1.2
C
Bags
Bags
4
=B10+C10
=B11+C11
اله -
s
+
2
D
Manager
Used
Ⓒ
♫
Create from Selection
E
A
220000
150000
@
FRemove
F
Available
B
(((
X
Transcribed Image Text:10 io AI fx A 1 Selling oranges and orange juice 2 3 Revenues, variable costs 4 5 Revenue 6 Variable cost 7 8 Grades used to make juice and bags 9 Grade 10 6 11 9 12 Sold 13 14 Required average quality 15 16 17 18 Quality constraints 19 20 Actual 21 22 Required 23 24 Total variable cost 25 Total revenue 26 27 Profit 28 Total Profit 29 30 me iec 31 32 33 34 35 36 37 38 20 Ready X P4-46 ✓ Selling oranges and orange juice B Juice 2.25 1.35 26666.6666666667 53333.3333333333 193333.333333333 96666.6666666667 =SUM(C10:C11) =SUM(B10:B11) Juice Bags 8 Juice Bags =SUMPRODUCT(A10:A11,B10: =SUMPRODUCT(A10:A11,C10 > > =B16 B12 =C16*C12 Bags Juice =B6*B12 =C6*C12 =B5*B12 =C5*C12 =825-B24 =C25-C24 =B27+C27 P4-50 JUN 20 P4-48 + B A Juice 7 2 1.2 C Bags Bags 4 =B10+C10 =B11+C11 اله - s + 2 D Manager Used Ⓒ ♫ Create from Selection E A 220000 150000 @ FRemove F Available B ((( X
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps with 11 images

Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Practical Management Science
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Business in Action
Business in Action
Operations Management
ISBN:
9780135198100
Author:
BOVEE
Publisher:
PEARSON CO
Purchasing and Supply Chain Management
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…
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.