Winery Business Analytics Project: Sonoma Valley Wine Optimization

As part of a team project, we developed a comprehensive business analytics and optimization model for Sonoma Valley Wine, a winery owned by George Clark. Our goal was to assist George in making data-driven decisions regarding wine production, marketing spending, pricing, and profitability optimization over a two-year period.

Project Overview

The project focused on financial modeling, optimization techniques, and scenario analysis to determine how George should allocate his resources for maximum profitability. We utilized linear programming (LP), Solver, and scenario analysis to create a decision-making tool that adapts to changing business conditions.

Our final optimized recommendation proposed producing and selling:

• Year 1: 4,470 bottles of Malbec and 6,703 bottles of Riesling, balancing costs and market demand.

• Year 2: 54,902 bottles of Malbec and 23,530 bottles of Riesling, based on marketing strategy and projected demand.

• Total projected profit: $617,652.19 over two years.

Key Components of the Project

1. Evaluative Model (Baseline Financial Model)

• We developed a financial model to analyze costs, revenue, and decision variables like grape purchases, advertising spend, and bottle pricing.

• The model calculated profitability based on sales estimates and marketing efforts, providing a foundational understanding of George’s business operations.

2. Optimization Model (Profit Maximization using Solver)

• We formulated an LP model to optimize wine production and marketing expenses while considering constraints like budget limits, product mix requirements, and sales forecasts.

• Using Microsoft Excel Solver, we determined the most efficient allocation of resources to maximize George’s total profit.

3. Scenario Analysis (Business Strategy Evaluation)

We tested multiple real-world business scenarios to help George make informed strategic decisions, including:

Scenario C: 'Three Buck Charles' Label

• George had the option to source cheaper grapes and sell Riesling under a private-label brand for $4 per bottle.

• We analyzed whether this move would increase profitability and how it would affect brand positioning.

Scenario E: "Save Him Some Money" Advertising Constraints

• A marketing restriction was imposed, limiting George’s advertising spend in Year 2 to five times Year 1’s budget.

• We examined how this affected sales volume, brand awareness, and total profitability.

Scenario G: Vintner’s Conference Industry Trend

• Industry experts advised George to increase Malbec production to at least 55% of total output to align with market trends.

• We evaluated whether following this trend would be financially beneficial.

Final Business Insights & Recommendations

1. Data-Driven Decision Making – The model provided a structured way to allocate resources efficiently, ensuring sustainable business growth.

2. Flexible Business Model – Our optimization framework can be adapted for different industries and products by adjusting cost structures and constraints.

3. Marketing & Production Strategy – Investing in targeted advertising and balancing product mix are crucial for long-term profitability.

4. Scalability & Future Use – The model is designed for continuous updates, allowing George to modify variables like cost of goods, marketing budgets, and production constraints as his business evolves.

Conclusion

Through this project, we integrated business analytics, financial modeling, and optimization techniques to help George maximize profit and make data-backed strategic decisions. Our model not only helps in forecasting profitability but also provides a scalable decision-making framework for future business growth.

Project problem

Project Solution