Question

Optimizing Performance: Finally, you will determine an optimal solution that will maximize the organization’s objectives. You will need to consider the level of sensitivity and uncertainty of alternative solutions in supporting your optimal solution. The analyses need to be submitted in an annotated excel file and include a rationale. A. Determine the values of the constraints to be used to generate the target number when running Solver. [QSO-320-03] B. Using Solver, calculate the level of sensitivity of decision variables and describe the significance of the report. [QSO-320-03] C. Using Solver, calculate the limits of decision variables and describe the significance of the report. [QSO-320-03] 3 D. Discuss solutions that will maximize profits for the organization based on the Solver analysis. [QSO-320-03

based on this data:

For the Lodi Winery, you have been asked by management to examine the data collected and analyzed in the previous modules. The objective is for you to help management decide on the right mix of wine bottles to sell based on newly derived profit information while considering the limitations of the particular types of grapes available for production.While doing more research on wine production, you realize that it takes 3.5 pounds of grapes to make a bottle of wine. In addition, you already were provided the price per bottle that the distributors are paying for each variety of wine:

Price for Red Wine ($)	Price for White Wine ($)	Price for Organic Wine ($)
7.5	8	12

After discussing wine production with the operations manager, you also learn that the wineries that supply the grapes to produce the above types of wine can produce up to a total of 200,000 pounds of grapes for a six-month supply of wine bottles for the three markets, with the following expected distribution constraints based on types of grapes. Note that current market demand will not support more than the below constraints for each type:

Red wine ceiling	22,000 bottles
White wine ceiling	24,000 bottles
Organic wine ceiling	12,000 bottles

Note that the production cost per bottle remains the same as before, that is, 32% of sales or revenue for red wine, 42.5% of sales for white wine, and 52.5% for organic wine. With additional information you have gathered, you are now ready to determine the optimum production mix to maximize profit.

Answer 1

A. Determine the values of the constraints to be used to generate the target number when running Solver. [QSO-320-03]
Particulars	Red Wine	White Wine	Organic Wine	Total
Maximum Market Demand	22000	24000	12000
How may pounds of Grapes required per Bottle of wine	3.5	3.5	3.5
Total Grapes Required in Pounds	77000	84000	42000	203000
Total GRAPES Available during the six months period				200000
Quantity of Constraints Grpes in pounds				3000
B. Using Solver, calculate the level of sensitivity of decision variables and describe the significance of the report. [QSO-320-03]
Particulars	Red Wine	White Wine	Organic Wine	Total
Quantity of Constraints				3000
How may pounds of Grapes required per Bottle of wine				3.5
Maximum production lost on the basis of Bottles				857.1429
Maximum production lost on the basis of Bottles (Round off)				858
Note:- All category of wines required 3.5 pounds of Grapes. So quanity will remain same
C. Using Solver, calculate the limits of decision variables and describe the significance of the report. [QSO-320-03]
Particulars	Red Wine	White Wine	Organic Wine	Total
Maximum Market Demand	22000	24000	12000
How may pounds of Grapes required per Bottle of wine	3.5	3.5	3.5
Total Grapes Required in Pounds	77000	84000	42000	203000
Total GRAPES Available during the six months period				200000
Quantity of Constraints Grpes in pounds				3000
How may pounds of Grapes required per Bottle of wine				3.5
Maximum production lost on the basis of Bottles				857.1429
Maximum production lost on the basis of Bottles (Round off)				858
Contribution per Bottle	5.1	4.6	5.7
Maximum Contribution	112200	110400	68400
Maximum Contribution loss per bottle	4375.8	3946.8	4890.6
D. Discuss solutions that will maximize profits for the organization based on the Solver analysis. [QSO-320-03
Particulars	Red Wine	White Wine	Organic Wine	Total
Maximum Market Demand	22000	24000	12000
Sales Price per Bottle of Wines ($0	7.5	8	12
Production Cost per Bottle of Wine in %	32	42.5	52.5
Total Production Cost per Bottle	2.4	3.4	6.3
Contribution per Bottle	5.1	4.6	5.7
Rank on the basis of Contribution per Bottle	2	3	1
Total GRAPES Available during the six months period				200000
Total Grapes allotted to Organic Wine				42000
Balance Available				158000
Total Grapes allotted to Red Wine				77000
Balance Available for production of White Wine				81000
Number of Maximum production of White White Wine possible				23142.86
Number of Maximum production of White White Wine possible (Round off)				23142
Maximum Sales Possible	22000	ekkarill92 Next > < Previous Related Solutions Now that you’ve identified the organization’s SWOT, you need to determine the project and its objectives... Now that you’ve identified the organization’s SWOT, you need to determine the project and its objectives and metrics. This project should be based on an unmet opportunity for the organization, or to minimize a potential threat. What does the organization need to do to advance its goals and/or expand its competitive advantage? How will you measure their progress? Complete the following: Explain why this opportunity/threat was selected, and how it is anticipated to benefit the organization. Create at least 3... Use the simplex method to determine whether the following LOP is optimal, unbounded, or infeasible. Maximize... Use the simplex method to determine whether the following LOP is optimal, unbounded, or infeasible. Maximize z = x1 − x2 Subject to 2x1 − x2 = −5 x1 − 2x2 ≤ 3 −x1 + x2 ≤ −1 and x1 ≥ 0. Graph the following LP problem and indicate the optimal solution point: Maximize profit= $3X + 2Y... Graph the following LP problem and indicate the optimal solution point: Maximize profit= $3X + 2Y Subject to 2X+ Y ≤ 150 2X + 3Y ≤ 300 a) Does the optimal solution change if the profit per unit of X changes to $4.50? b ) What happens if the profit function should have been $3X + 3Y? I need help solving this problem using solver in excel Find the optimal solution for the following problem. (Round your answers to 3 decimal places.) Maximize... Find the optimal solution for the following problem. (Round your answers to 3 decimal places.) Maximize C = 16x + 21y subject to 9x + 15y ≤ 22 10x + 3y ≤ 29 and x ≥ 0, y ≥ 0. What is the optimal value of x? What is the optimal value of y? What is the maximum value of the objective function? For the following LP problem, determine the optimal solution by the graphical solution method. Min Z=... For the following LP problem, determine the optimal solution by the graphical solution method. Min Z= 3x1+2x2 Subject to 2x1+x2 >10                    -3x1+2x2 < 6                      X1+x2 > 6                      X1,x1 > 0 Graph and shade the feasible region For the following linear programming problem, determine the optimal solution by the graphical solution method Max... For the following linear programming problem, determine the optimal solution by the graphical solution method Max -x + 2y s.t. 6x - 2y <= 3 -2x + 3y <= 6     x +   y <= 3         x, y >= 0 Apply the KKT conditions to determine whether or not the solution XT = (1,1,1) is optimal... Apply the KKT conditions to determine whether or not the solution XT = (1,1,1) is optimal for the following problem: minimize 2X1 + X2^3 + X3^2 subject to 2X1^2 + 2X2^2 + X3^2 >=4 X1, X2, X3 >=0 4) Finally, you wish to determine if the mean monthly sales of the shirt in the... 4) Finally, you wish to determine if the mean monthly sales of the shirt in the superstores that comprise part of the chain in four regions of the country that you have defined differ from each other. Random samples of the sales of the item in stores chosen from each region are selected. The sample data comprising these sales figures is shown in appendix four below. At the 1% level of significance, are there any differences in the mean monthly... 4) Finally, you wish to determine if the mean monthly sales of the shirt in the... 4) Finally, you wish to determine if the mean monthly sales of the shirt in the superstores that comprise part of the chain in four regions of the country that you have defined differ from each other. Random samples of the sales of the item in stores chosen from each region are selected. The sample data comprising these sales figures is shown in appendix four below. At the 1% level of significance, are there any differences in the mean monthly... Finally, you wish to determine if the mean monthly sales of the shirt in the superstores... Finally, you wish to determine if the mean monthly sales of the shirt in the superstores that comprise part of the chain in four regions of the country that you have defined differ from each other. Random samples of the sales of the item in stores chosen from each region are selected. The sample data comprising these sales figures is shown in appendix four below. At the 1% level of significance, are there any differences in the mean monthly sales... ADVERTISEMENT Subjects Accounting Advanced Math Anatomy and Physiology Biology Chemistry Civil Engineering Computer Science Economics Electrical Engineering Finance History Math Mechanical Engineering Operations Management Physics Psychology Statistics and Probability Nursing Other ADVERTISEMENT Latest Questions Case Study: Personal Beliefs in the Workplace Don Shakow was a prominent professional who worked in... 1: Competency framework usage is the modern best practice in management. What is meant by competency... A 19.34 g mixture of sugar (C12H22O11) and table salt (NaCl) is dissolved in 231 g... Young and Company claims that its pressurized diving bell will, on average, maintain its integrity to... 1) Alternative compiled code sequence using instructions in classes A, B, C. What is the average... An asset is purchased on January 1 for $48,700. It is expected to have a useful... Do we have any online or offline tool to convert c and cpp programs into MIPS... ADVERTISEMENT About @ 2020 WizEdu