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.MEIrfectly idemandand curect...titioNERetiremenniniecDHome Insert Draw Page LayoutGData fromGet Data (PowerQuery)RefreshAllStocksPicturex fx Selling oranges and orange juiceX ✓ABCDE1 Selling oranges and oilange juice2Revenues, variable costs4Bags$2.005 Revenue$2.256Variable cost$1.35$1.2078Grades used to make juice and bags9GradeJuice10626666.6667Bags193333.33353333 96666.666711912Sold800002900001314 Required average quality15JuiceBags71681718 Quality constraints1920 ActualJuice640000X=64000021Bags2030000X2030000Bags$108,000 $348,000.00.$180,000 $580,000.0022 Required2324 Total variable cost25 Total revenue2627 Profit$72,000 $232,000.00$304,000.00128 Total Profit2930313233343536373820P4-48PointP4-46OJuiceJuiceP4-50JUN20FormulasUsed220000150000+<=DataReviewGeographyFAvailable220000150000Module 3 Problem Workbook (3)View SolverTable for MacClearZAYAZSort FilterReapplyAdvancedGH1JKSolver ParametersSet Objective:$B$28To: O MaxMinValue Of:By Changing Variable Cells:$B$10:$C$11Subject to the Constraints:$B$20:$C$20 >= $B$22:$C$22$D$10:$D$11 <= $F$10:$F$11AddChangeDeleteReset AllLoad/SaveMake Unconstrained Variables Non-NegativeSelect a Solving Method:Simplex LPOptionsSolving MethodSelect the GRG Nonlinear engine for Solver Problems that are smoothnonlinear. Select the LP Simplex engine for linear Solver Problems,and select the Evolutionary engine for Solver problems that are non-smooth.CloseSolveران2Tell meText toColumnsL0AMWhat-IfAnalysisNN2X028OutlinePAna? SolvQ 10ioAIfxA1 Selling oranges and orange juice23Revenues, variable costs45 Revenue6 Variable cost78 Grades used to make juice and bags9Grade10 611 912Sold1314 Required average quality15161718 Quality constraints1920 Actual2122 Required2324 Total variable cost25 Total revenue2627 Profit28 Total Profit2930meiec313233343536373820ReadyXP4-46✓Selling oranges and orange juiceBJuice2.251.3526666.666666666753333.3333333333193333.33333333396666.6666666667=SUM(C10:C11)=SUM(B10:B11)JuiceBags8JuiceBags=SUMPRODUCT(A10:A11,B10: =SUMPRODUCT(A10:A11,C10>>=B16 B12=C16*C12BagsJuice=B6*B12=C6*C12=B5*B12=C5*C12=825-B24=C25-C24=B27+C27P4-50JUN20P4-48+B AJuice721.2CBagsBags4=B10+C10=B11+C11اله -s+2DManagerUsedⒸ♫Create from SelectionEA220000150000@FRemoveFAvailableB(((X

Find the Calculations below: Excel Declarations: Solver Declarations: Results:

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.

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...