Could you please help me to solve the question B
Product emphasis, behavioral effects, strategic objectives Retley Manufacturing makes three products: Gizmos, Whizmos, and Gadgets. The products sell for $50, $90, and $100, respectively. Each product requires processing on each of two machines. The grinding machine is operated 8 hours a day for 25 days a month. Gizmos require 2 hours of grinding. Whizmos require 3 hours, and Gadgets require 6 hours. The finishing machine can theoretically be operated 8 hours per day for 25 days, but the firm usually loses 25% of this time due to breakdowns. Gizmos and Gadgets each use 2 hours of finishing time, and Whizmos use 4 hours. Final assembly is done by hand. Four Gizmos can be assembled by a laborer per hour, while it takes 2 hours to put together one Whizmo, and 30 minutes to assemble a Gadget. All three products use the same raw materials. Each Gizmo needs 3 pounds, each Whizmo needs 5 pounds, and a Gadget requires 8 pounds. Labor is paid $14 per hour, and raw materials cost $6 per pound. The firm used regression anal- ysis to estimate the following cost function for overhead: Overhead = $200 + $6 per direct labor hour A. Write out the target cell equation and the constraint equations to solve for the optima product mix B. Use Excel Solver or other linear programming software to solve for the optimal product mix C. Assume that the firm's sales representative is paid $1,000 per month, plus a sales commis- sion of 10% of sales revenue. Thus, the sales representative has an incentive to maximize sales revenue. Revise the target cell equation and solve for the product mix that maximizes sales revenue. What is the estimated cost to Retley if its sales representative maximizes sales revenue instead of contribution margin? D. Ignore part (C). Assume that Retley has a strategic objective to gain total market share (based on number of units sold) as quickly as possible. Revise the target cell equation to solve for the product mix that maximizes total number of units sold. What is the estimated operating income of this product mix? E. Ignore parts (C) and (D). Assume that Retley's marketing division insists that the company will develop greater product and brand recognition by producing and selling at least 10 units of each product per month. Revise the target cell equation and the constraint equa- tions, and solve for the optimal product mix under this new constraint. What is the esti mated opportunity cost of this action? 4.39 oi, Qs, Q6 REQUIRED Retley Manufacturing Whizmos Gizmos Gadgets $90 Product $50 $100 $58 $42 Price VC $23 $27 $70 $20 CM Constraints Hours Hours Hours Grinding Finishing 3 2 4 Total resources available Grinding hours Finishing hours 200 150 Solver Setup Product Mix Gizmos Whizmos Gadgets Changing Cells: Contribution Margin for Product Mix $0.00 Target Cell Constrained Resources Used aximum Constraints Grinding hours Finishing hours 0 200 0 250 Leather seats O N st Product emphasis, behavioral effects, strategic objectives Retley Manufacturing makes three products: Gizmos, Whizmos, and Gadgets. The products sell for $50, $90, and $100, respectively. Each product requires processing on each of two machines. The grinding machine is operated 8 hours a day for 25 days a month. Gizmos require 2 hours of grinding. Whizmos require 3 hours, and Gadgets require 6 hours. The finishing machine can theoretically be operated 8 hours per day for 25 days, but the firm usually loses 25% of this time due to breakdowns. Gizmos and Gadgets each use 2 hours of finishing time, and Whizmos use 4 hours. Final assembly is done by hand. Four Gizmos can be assembled by a laborer per hour, while it takes 2 hours to put together one Whizmo, and 30 minutes to assemble a Gadget. All three products use the same raw materials. Each Gizmo needs 3 pounds, each Whizmo needs 5 pounds, and a Gadget requires 8 pounds. Labor is paid $14 per hour, and raw materials cost $6 per pound. The firm used regression anal- ysis to estimate the following cost function for overhead: Overhead = $200 + $6 per direct labor hour A. Write out the target cell equation and the constraint equations to solve for the optima product mix B. Use Excel Solver or other linear programming software to solve for the optimal product mix C. Assume that the firm's sales representative is paid $1,000 per month, plus a sales commis- sion of 10% of sales revenue. Thus, the sales representative has an incentive to maximize sales revenue. Revise the target cell equation and solve for the product mix that maximizes sales revenue. What is the estimated cost to Retley if its sales representative maximizes sales revenue instead of contribution margin? D. Ignore part (C). Assume that Retley has a strategic objective to gain total market share (based on number of units sold) as quickly as possible. Revise the target cell equation to solve for the product mix that maximizes total number of units sold. What is the estimated operating income of this product mix? E. Ignore parts (C) and (D). Assume that Retley's marketing division insists that the company will develop greater product and brand recognition by producing and selling at least 10 units of each product per month. Revise the target cell equation and the constraint equa- tions, and solve for the optimal product mix under this new constraint. What is the esti mated opportunity cost of this action? 4.39 oi, Qs, Q6 REQUIRED Retley Manufacturing Whizmos Gizmos Gadgets $90 Product $50 $100 $58 $42 Price VC $23 $27 $70 $20 CM Constraints Hours Hours Hours Grinding Finishing 3 2 4 Total resources available Grinding hours Finishing hours 200 150 Solver Setup Product Mix Gizmos Whizmos Gadgets Changing Cells: Contribution Margin for Product Mix $0.00 Target Cell Constrained Resources Used aximum Constraints Grinding hours Finishing hours 0 200 0 250 Leather seats O N st
