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 | ||
| Constraints: | ||||||
| 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.
Click following link to download this document
QSO 320 Final Project - Milestone Four.docx
Place an Order
Plagiarism Free!
Create an Account
Create an account at Top Tutor Online
- Allows you to track orders.
- Receive personal messages.
- Send messages to a tutor.
Post a Question/ Assignment
Post your specific assignment
- Tutors will be notified of your assignment.
- Review your question and include all the details.
- A payment Link will be sent to you.
Wait for your Answer!
Make payment and wait for your answer
- Make payment in accordance with the number of pages to be written.
- Wait for your Answer as a professional works on your paper.
- You will be notified when your Answer is ready.