Quantitative Business Analysis

Group Project

SONOMA VALLEY WINE
After graduating from business school, George Clark went to work for a Big Six accounting firm in San Francisco. Because his hobby has always been wine making, when he had the opportunity a few years later, he purchased 5 acres of land in Sonoma Valley in Northern California. He plans eventually to grow grapes on that land and make wine with them. George knows that this is a big undertaking and that it will require more capital than he has at the present. However, he figures that, if he persists, he will be able to leave accounting and live full-time from his winery earnings by the time he is 40 years old.

Because wine making is a capital-intensive and growing commercial-quality grapes with a full yield of 5 tons per acre takes at least 8 years, George is planning to start small. This is necessitated by both his lack of capital and his experience in wine making on a large scale, although he has long made wine at home. His plan is first to plant the grapes on his land to get the vines started. Then he needs to set up a smaller trailer where he can live on weekends while he installs the irrigation system and does the required work to the vines, such as pruning and fertilizing. To help maintain a positive cash flow during the first few years, he also plans to buy grapes from other nearby growers so he can make his own label wine. He proposes to market it through a small tasting room that he will build on his land and keep open on weekends during the spring-summer season.

To begin, George is going to use $10,000 in savings to finance the initial purchase of grapes from which he will make his first batch of wine. He is also thinking about going to the Bank of Sonoma and asking for a loan. He knows that if he goes to the bank, the loan officer will ask for a business plan; so, he is trying to pull together some numbers for himself first. This way he will have a rough notion of the profitability and cash flows associated with his ideas before he develops a formal plan with a pro forma income statement and balance sheet. He has decided to make the preliminary planning horizon two years and would like to estimate the profit over that period. His most immediate task is to decide how much of the $10,000 should be allocated to purchasing grapes for the first year and how much to purchasing grapes for the second year. In addition, each year he must decide how much he should allocate to purchasing grapes to make his favorite Petite Sirah and how much to purchasing grapes to make the more popular Sauvignon Blanc that seems to have been capturing the attention of the wider market during the past few years in California.

In the first year, each bottle of Petite Sirah requires $0.80 worth of grapes and each bottle of Sauvignon Blanc uses $0.70 worth of grapes. For the second year, the costs of the grapes per bottle are $0.75 and $0.85, respectively.

George anticipates that his Petite Sirah will sell for $8.00 a bottle for the first year and for $8.25 in the second year, whereas Sauvignon Blanc’s price remains the same in both years at $7.00 a bottle.

Besides the decisions about the amounts of grapes to purchase in the 2 years, George must make estimates of the sales levels for the two wines during the 2 years. The local wine-making association has told him that marketing is the key to success in any wine business; generally, demand is directly proportional to the amount of effort spent on marketing. Thus, because George cannot afford to do any market research about sales levels due to lack of capital; he is pondering how much money he should spend to promote each wine each year. The wine-making association has given him a rule of thumb that relates estimated demand to the amount of money spent on advertising. For instance, they estimate that, for each dollar spend in the first year promoting the Petite Sirah, a demand for 5 bottles will be created; and for each dollar spent in the second year, a demand for 6 bottles will result. Similarly, for each dollar spent on advertising for the Sauvignon Blanc in the first year, up to 8 bottles can be sold; and for each dollar spent in the second year, up to 10 bottles can be sold.

The initial funds for the advertising will come from the $10,000 savings. Assume that the cash earned from the wine sales in the first year is available in the second year.

A personal concern George has is that he maintains a proper balance of wine products so that he will be well positioned to expand his marketing capabilities when he moves to the winery and makes it his full-time job. Thus, in his mind, it is important to ensure that the number of bottles of Petite Sirah sold each year falls in the range between 40% and 70% of the overall number of bottles sold.

Question 1
George needs help to decide how many grapes to buy, how much money to spend on advertising, how many bottles of wine to sell, and how much profit he can expect to earn over the 2-year period. Develop a spreadsheet LP model to help him.

Question 2
Solve the linear programming model formulated in Question 1.

Analytical Work in the Business World

The way that people do analytical work in the business world follows a common pattern. The work proceeds over time. You spend time working on the analytical project, and also spend time working on other things. You produce new results over time. You steadily discover new things about the problem you are working on. You frequently discuss your work informally with other people.

Eventually, you give a formal presentation of your work and obtain feedback and new insights. Based on your presentation, your boss asks you for follow-on work (which, believe it or not, is the best feedback that you can receive). Your work sometimes also culminates with a written report and a high-quality piece of spreadsheet software suitable for long-term use.

Your work on the Winery is based on this pattern. You have Winery case activities spread out over two homework assignments and a case presentation, culminating in not only a powerful spreadsheet model but also in business insights and recommendations to business leaders.

Your Role

You are consultants working for George. George has asked you to develop a spreadsheet tool that can be used help with the production process, and to do analysis to generate useful business insight.

You will develop and refine the model over a period of weeks. You will present your work to George and his assistant managers.

Schedule

The schedule of deliverables is as follows:

Evaluative Model
Optimization Model
Presentation.

1. Evaluative Model

The purpose of the Evaluative Model is to compute the values of all Performance Measures necessary to determine the cost and effectiveness of any proposed wine producing plan.

To complete this assignment, you will first need to model the underlying business problem described in the case (in particular, you must clearly identify each of the Decision Variables and each of the many performance measures). Next, you will need to design a spreadsheet that will implement the model. Finally, you will construct the spreadsheet, doing your best to “build it right the first time”.

This is a sizable spreadsheet modelling task and you will have a few weeks to work on it with your team. You will want to be careful to stay on track and not fall behind. Your team should expect to work together regularly on this project. The professor and teaching assistants will be available to answer questions through this process as well.

For this Deliverable, you will need to update your initial model on direct feedback, new concepts and techniques presented in class, and any other changes/additions that your group chooses to make to your initial model.

2. Optimization Model

The Updated Model that you turn in should be the final model that you use in preparation for your presentation. You should also include a short description (in a separate worksheet) that explains the major modifications that you have made to your initial model.

Note for essay pro writer- I’m attaching an example file which is the format my professor requires the data to be formatted.