Excel Exercise - Pet Food Scenario

Wine Example - Demand and Supply Excel Exercise

Saxum Vineyard, in Paso Robles, CA, is one of the more than 8,000 wineries in the United States. While Saxum produces a number of different kinds of wine, they focus their production on Syrah (also known as Shiraz). Saxum sells their wines all over the United States. Suppose you manage a vineyard like Saxum and want to determine how much you should charge for your Syrah. Suppose the market demand function for Syrah is as follows.

QD = 200 - 38.18PO + 8.35PS - 2Pc + 10Inc + 0.8TS + 0.5M21

Where,

• QD is monthly demand for bottle of Syrah (in millions),

• PO is the price of Syrah in the market,

• PS is the average price of substitute bottles of wine (other varieties),

• Pc is the average price of a pound of cheese and is used to gauge the price of complimentary goods,

• Inc is average US per capita income (in thousands),

• TS is the number of wine trade shows and competitions each year which firms can attend to market their wines,

• and M21 is the number (in millions) of millennials in the US over the age of 21. This last variable is included to capture a change in consumer preferences; millennials are drinking wine at a much higher rate than previous generations.

The market for Syrah also has supply, produced by wineries similar to Saxum Vineyard and your winery, which can be stated as follows.

QS = -100 + 22.93PO - 5PPI - 10PS + 8Temp + 1Sup

Where,

• QS is monthly supply of bottles of Syrah (in millions),

• PO is the price of Syrah in the market,

• PPI is the Producer Price Index (an index used to gauge changes in the costs of production in the US),

• PS is the price of substitute wines which could easily be produced instead of Syrah,

• Temp is the expected temperature during the harvest season for grapes,

• Sup is the number of wineries that supply Syrah in the market (in thousands)." Using the market supply and demand functions above and the current market condition information below, create a table that automatically calculates the quantity demand and the quantity supply and allows you to change the market conditions and instantly see the change in quantity demand and quantity supplied. Fill in the values for each of the variables except Price of Syrah. Demand:

• Price of Substitutes: $18

• Price of Cheese: $15

• Income: $53,000

• Trade Shows/Competitions: 3

• Millennials = 45 million Supply

• PPI: 111

• Price of Substitutes: $18

• Temperature: 60

• Number of Suppliers: 8,000 Price: to be determined Now that you've set up your demand and supply functions, answer the following questions.

1. When the price of Syrah increases by $1, what is the effect on quantity demanded and quantity supplied?

2. How much does a $1 decrease in the price of substitute bottles of wine shift the demand and supply curves?

3. Suppose the price of Syrah is currently $22 per bottle. How many bottles will be demanded and supplied monthly?

4. If the market price of Syrah falls to $16 per bottle, how many bottles will be demanded and supplied monthly?

5. Trying prices in $1 increments between $16 and $22, at what price and quantity does the market equilibrium occur?

6. Suppose the costs of production increase to 123.222. If the price of wine stays at $20 per bottle, what will be supplied in the market and will this create a shortage?

7. With the increase in production costs to 123.222, at what price will the market be in equilibrium again? What will be demanded and supplied at this price?