Hello, I need assistance with this problem. I have to make it in excel and am lost. I will work with someone to finish it.

Question:

Sonoma Valley Wines

Story:

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.

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 but 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 Syrah 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 Syrah 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

Syrah will sell for $8.00 a bottle for the first year and for %8.25 in the second year,

whereas Sauvignon Blancs 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

Syrah, 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 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 Syrah 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 profit he can expect to earn

over the 2-year period. Develop a spreadsheet LP model to help him.

2. Solve the linear programming model formulated in Question 1

Here is what I have so far.

Objective

To find out 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.

Inputs

Requirements of grapes per bottle

How much each bottle is sold for

Advertising demand

ps

1y for every $1 <= 5 bottles

2y $1 <= 6 bottles

sb

1y $1<= 8 bottles

2y $1 <= 10 bottles

Petite Syrah = 40-70%

1y

Grapes and advertising<= 10000 (initial start up)

2y

Grapes and advertising <= initial start up + 1st year revenue

Please help me