AMHERST MANUFACTURING The Amherst Manufacturing Company is considering opportunities to produce a new customized piece of electronic equipment and provide them to five customers. The component can be produced at all of its three plants. Each customer has stated their individual requirements and will take no more than the specified amount but will be willing to settle for less. Table 1 shows the shipping costs per unit, the requirements and sales price for each customer.Table 1. Shipping costs in $ per unit, requirements and sales process in $ per unit.Shipping costs from plant to customerCustomer 1Customer 2Customer 3Customer 4Customer 5Plant 11218131729Plant 22414251117Plant382522913Requirements4040608060Sales Price103105106107111Each plant can dedicate enough labor and equipment to satisfy the demand of all five customers (i.e., there is no specific capacity limit relative to this sales opportunity). However, due to the disruption in existing production schedules, they estimate an increasing variable cost per unit given by the formula Unit cost of producing x units = a + bx.Therefore, the total cost of producing x units at a given plant is ax + bx2. In addition to the variables cost, there is a fixed cost for producing the new product of each plant. The values estimated for a and b at the three plants are given in Table 2.Table 2. Cost function parameters at the plantsabFixed CostPlant 1720.061200Plant 2650.121600Plant 3680.081500For example, producing 50 units at plant 1, 100 units at plant 2 and 200 units at plants 3 would costPlant 1: 1200 + 72(50)+0.06(50)2 = 1200 + 3600 + 150 = 4950Plant 2: 1600 + 65(100)+0.12(100)2 = 1600 + 6500 + 1200 = 9300Plant 3: 1500 +68(200)+0.08(200)2 = 1500 + 13600 + 3200 = 18300for a total manufacturing cost of 4950 + 9300 + 18300 = 32550.

Complete a SOLVER enabled spreadsheet that will determine which plants will produce the product, how much each plant should produce and how much each plant should ship to each customer in order to maximize total profit.Comments and Hints:Be sure to place all problem parameters in cells, i.e., do not embed them as constants in formulas.Create formula cells for total variable production cost, total shipping cost, total fixed cost, total revenue and total profit.Your will need linking constraints and therefore a value for Big M. Since there is no stated capacity limit at any plant, use the total demand (obviously no plant will produce more than that).You need not constrain the quantities shipped to integer values. If you do, it may take several minutes to solve.2) HOT TUB PRICINGA company produces 3 models of hot tubs. The minimum and maximum sales prices and marginal costs appear in the Table 1.Table 1. Minimum and maximum sales prices and marginal costs in $ per unitAqua SpaHydro LuxSuper SoakerMinimum sales price225020503200Maximum sales price270023003600Marginal cost87511001275Four resources limit the actual quantities of the products that can be produced. Table 2 shows the monthly availabilities and the per unit requirements for these four resources:Table 2. Per unit requirements of resources and monthly availabilityAqua SpaHydro LuxSuper SoakerAvailablePumps112200Labor (hours)612141570Tubing (feet)1218322800Fiberglass Resin (lbs)22532575056000The company has done detailed marketing analysis to determine the impact of pricing on monthly demand. It has estimated the following predictors of monthly demand as a function of sales price:Aqua Spa: Demand = 600 -0.250(Sales price)Hydro Lux: Demand = 425 -0.150(Sales price)Super soaker: Demand = 475 -0.125(Sales price)Complete a SOLVER enabled spreadsheet that will determine optimum pricing of the hot tubs to be produced monthly in order to maximize monthly profit. Restrict the number to be produced monthly to integer values.Comments and Hints:Place the slope and intercept coefficients for each of the three demand prediction equations in cells in the spreadsheet, i.e., do not embed them as constants in demand formulas.Create formula cells for total revenue, total cost and total profit.In addition to changing cells for sales prices, you will need changing cells for the number produced each month in order to place integer restrictions.AMHERST MANUFACTURINGThe Amherst Manufacturing Company is considering opportunities to produce a new customized piece of electronic equipment and provide them to five customers. The component can be produced at all of its three plants. Each customer has stated their individual requirements and will take no more than the specified amount but will be willing to settle for less. Table 1 shows the shipping costs per unit, the requirements and sales price for each customer.Table 1. Shipping costs in $ per unit, requirements and sales process in $ per unit.Shipping costs from plant to customerCustomer 1Customer 2Customer 3Customer 4Customer 5Plant 11218131729Plant 22414251117Plant382522913Requirements4040608060Sales Price103105106107111Each plant can dedicate enough labor and equipment to satisfy the demand of all five customers (i.e., there is no specific capacity limit relative to this sales opportunity). However, due to the disruption in existing production schedules, they estimate an increasing variable cost per unit given by the formula Unit cost of producing x units = a + bx.Therefore, the total cost of producing x units at a given plant is ax + bx2. In addition to the variables cost, there is a fixed cost for producing the new product of each plant. The values estimated for a and b at the three plants are given in Table 2.Table 2. Cost function parameters at the plantsabFixed CostPlant 1720.061200Plant 2650.121600Plant 3680.081500For example, producing 50 units at plant 1, 100 units at plant 2 and 200 units at plants 3 would costPlant 1: 1200 + 72(50)+0.06(50)2 = 1200 + 3600 + 150 = 4950Plant 2: 1600 + 65(100)+0.12(100)2 = 1600 + 6500 + 1200 = 9300Plant 3: 1500 +68(200)+0.08(200)2 = 1500 + 13600 + 3200 = 18300for a total manufacturing cost of 4950 + 9300 + 18300 = 32550.Complete a SOLVER enabled spreadsheet that will determine which plants will produce the product, how much each plant should produce and how much each plant should ship to each customer in order to maximize total profit.Comments and Hints:Be sure to place all problem parameters in cells, i.e., do not embed them as constants in formulas.Create formula cells for total variable production cost, total shipping cost, total fixed cost, total revenue and total profit.Your will need linking constraints and therefore a value for Big M. Since there is no stated capacity limit at any plant, use the total demand (obviously no plant will produce more than that).You need not constrain the quantities shipped to integer values. If you do, it may take several minutes to solve.2) HOT TUB PRICINGA company produces 3 models of hot tubs. The minimum and maximum sales prices and marginal costs appear in the Table 1.Table 1. Minimum and maximum sales prices and marginal costs in $ per unitAqua SpaHydro LuxSuper SoakerMinimum sales price225020503200Maximum sales price270023003600Marginal cost87511001275Four resources limit the actual quantities of the products that can be produced. Table 2 shows the monthly availabilities and the per unit requirements for these four resources:Table 2. Per unit requirements of resources and monthly availabilityAqua SpaHydro LuxSuper SoakerAvailablePumps112200Labor (hours)612141570Tubing (feet)1218322800Fiberglass Resin (lbs)22532575056000The company has done detailed marketing analysis to determine the impact of pricing on monthly demand. It has estimated the following predictors of monthly demand as a function of sales price:Aqua Spa: Demand = 600 -0.250(Sales price)Hydro Lux: Demand = 425 -0.150(Sales price)Super soaker: Demand = 475 -0.125(Sales price)Complete a SOLVER enabled spreadsheet that will determine optimum pricing of the hot tubs to be produced monthly in order to maximize monthly profit. Restrict the number to be produced monthly to integer values.Comments and Hints:Place the slope and intercept coefficients for each of the three demand prediction equations in cells in the spreadsheet, i.e., do not embed them as constants in demand formulas.Create formula cells for total revenue, total cost and total profit.In addition to changing cells for sales prices, you will need changing cells for the number produced each month in order to place integer restrictions.