solve using a linear programming model with the excel template provided.

After graduating from business school, George Clark went to work for a Big Six accounting ?rm in San Francisco. Because his hobby has always been wine making, when he had the opportunity a few years later, he purchased 5 acres plus an option to buy 35 additional 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 ?gures 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.

Because wine making is 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 inexperience in wine making on a large scale, although he has long made wine at home. His plan is ?rst to plant the grapes on his land to get the vines started. Then he needs to set up a small 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 ?ow during the ?rst few years, he also plans to buy grapes from other nearby growers so he can make his own label wine. He pro-poses 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 ?nance the initial purchase of grapes from which he will make his ?rst 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 of?cer will ask for a business plan; so he is trying to pull together some numbers for himself ?rst. This way he will have a rough notion of the pro?tability and cash ?ows 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 pro?t over that period. His most immediate task is to decide how much of the $10,000 should be allocated to purchasing grapes for the ?rst 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 Syrah and how much to purchasing grapes to make the more popular Sauvignon Blanc that seems to have been capturing the attention of a wider market during the past few years in California.

In the ?rst 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 in the ?rst year and for $8.25 in the second year, whereas his Sauvignon Blanc's price remains the same in both years at $7.00 a bottle.

Besides the decisions about the amounts of grapes purchased 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 doany market research about sales levels due to his 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 spent in the ?rst year promoting the Petite Sirah, a demand for 5 bottles will be created; and for each dollar spe in the second year, a demand for 6 bottles will result. Similarly, for each dollar spent on advertising for the Sauvignon Blanc in the ?rst 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 wine sales in the ?rst year is available in the second year. A personal concern George has is that he maintain 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.

Questions:

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 pro?t he can expect to earn over the two-year period. Develop a spreadsheet LP model to help him.

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

AutoSave . off H Case Study-Sonoma Valley Wines-Template (2) - Excel 10971429 X File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share Comments Insert Calibri - 11 A A - ab General E . Ex Delete AY O Paste B I U - M Sort & Find & A FE $ % Conditional Format as Cell Formatting * Table Styles Format D . Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing AW88 X V A B C D E F G H K L M N O P Q R S A W N Sonoma Valley Wines Cash in year 1 $10,000 Costs of grapes per bottle Selling prices per bottle Year 1 Year 2 Year 1 Year 2 7 1 Petite Sirah $0.80 $0.75 Petite Sirah $8.00 $8.25 8 Sauvignon Blanc $0.70 $0.85 Sauvignon Blanc $7.00 $7.00 10 Demand created (bottles) per dollar of advertising 11 Year 1 Year 2 12 Petite Sirah 5 6 13 Sauvignon Blanc 8 10 14 15 Percentage constraints on Petite Sirah sold each year 16 Min 40% 17 Max 70% 18 19 Production, sales plan Dollars on advertising Cash constraints 20 Year 1 Year 2 Year 1 Year 2 Year 1 Year 2 21 Petite Sirah produced and Sold Petite Sirah Spent 2 Sauvignon Blanc produced and Sold Sauvignon Blanc