Final Project: Milestone Four

Southern New Hampshire University

The last step of this project, milestone four, is to predict the right mix product mix of types of about Northern California Winery to management. Previous information calculated included total sales broken down by salesperson, red wine sales in the past six months in California, the top sellers further detailed by the type of wine, overall salespersons’ performance and commissions received by each salesperson, in addition to the measures of central tendency and profits per wine type.

Milestone Four will calculate and analyzes the following items:

1: Relevant Decision Variables and Constraints

  • What are the relevant decision variables? What are the constraints? Explain.
  • What is the right mix of sales for the optimized solution? Be sure to use the linear programming features in spreadsheet software and include a sensitivity analysis.
  • Create a decision tree or table for management to support your recommendation that takes into account your estimation of the probabilities and likely outcomes for alternative states of nature.

The relevant decision variables are directly related to four main constraints. Maximum production ceiling for each type of wine is the first constraint: 15,000 bottles of red wine, 15,000 bottles of white wine, and 5,500 bottles of organic wine. The second constraint is the maximum amount of grapes available for wine production for six months. The cap is 100,000 pounds of grapes with each bottle of wine requiring approximately 2.5 pounds each.

2: Right Mix of Sales for the Optimized Solution

Management would like to know the right mix of sales of each type of wine to maximize profit while maximizing the use of the available grapes for production. This solution is best calculated using linear programming with the results shown below.

The final calculations show maximum profit by producing 16,000 bottes each of red and white wine and 6,000 bottles of organic wine. These production numbers will use 95,000 pounds of the 100,000 grape maximum allotment.

  Red White Organic      
Solution 16,000 16,000 6,000      
Profit Per Bottle 17.60 16.60 23.70 $ 689,400    
Red wine ceiling 1     16,000 <= 16,000
White wine ceiling   1   16,000 <= 16,000
Organic wine ceiling     1 6,000 <= 6,000
Pounds Required Per Bottle 2.5 2.5 2.5 95,000 <= 100,000
        LHS Sign RHS

3: Decision Tree

City Organic Red White
Los Angeles $ 40,259 $ 74,875 $ 92,380
San Diego $ 73,562 $ 79,468 $ 98,481
San Francisco $ 52,353 $ 145,018 $ 110,843
City Organic Red White
Los Angeles 1,342 3,744 4,619
San Diego 2,452 3,973 4,924
San Francisco 1,745 7,251 5,542
TOTAL BOTTLES 5,539 14,968 15,085

Based on the above decision tree, below is a table of marketing recommendations.

1 Increase marketing efforts of organic wine in Los Angeles. A goal of 1,500 bottles of organic wine is a good place to start.
2 Continue with the current marketing of organic wine in San Diego. 2,500 bottles of organic wine sold should be the goal.
3 San Francisco’s organic wine marketing should be increased some. A goal of 2,000 bottles should be set.
4 Red wine marketing should be more focused in Los Angeles and San Diego, with more attention given to LA. Goals of 4,000 bottles should be set up.
5 Red wine is a big seller in San Francisco. Marketing should be increased to help sell 8,000 bottles.
6 White wine sales are pretty even between the 3 cities. Marketing efforts should be increased slightly to improve sales by 15%.

Best regards,

Tammi Kile

None for this milestone.