Question

This is messy - the problem was provided in excel and I have copied to Word. There is a similar problem answered, but I need excel formulas.

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 + .8TS + .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 complementary goods, Inc is the average US per capita income (in thousands), TS is the number of wine trade shows and competitions each year in 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, and Sup is the number of wineries that supply Syrah in the market (in thousands).

Using the market supply and demand functions for Syrah given, fill in the template provided with the coefficients for each function. Using the information below, 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

Market Demand

                                          Coefficient         Values

Intercept                          ?                          ?

Price of Syrah                  ?                          ?

Price of Substitute         ?                          ?           

Price of Cheese               ?                          ?

Income                             ?                          ?

Trade Shows                    ?                          ?           

Millenials                         ?                          ?

Market Supply

                                         Coefficients        Values

Intercept                          ?

Price of Syrah                  ?                          ?

PPI                                    ?                          ?

Price of Substitute          ?                          ?

Temperature                    ?                          ?

Suppliers                          ?                          ?

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 that the price of Syrah is currently $22 per bottle. How many bottles will be demanded and supplied monthly? Is there a shortage or surplus and of how much?

4. If the market price of Syrah falls to $16 per bottle, how many bottles will be demanded and supplied monthly? Is there a shortage or surplus and of how much?

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

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

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

Answer 1

I'll answer the first 5 questions asked. You're encouraged to use a similar method to answer parts 6 and 7 (refer to the answer to parts 4 and 5).

To fill the coefficients section of the table, just use the equation provided. To fill the values section, use the information given in the question. Finally, your output should look like this.

For market demand

	Coefficient	Values
Intercept	200	1
Price of Syrah	-38.18
Price of Substitute	8.35	18
Price of Cheese	-2	15
Income	10	53
Trade Shows	0.8	3
Millennials	0.5	45

For market supply

	Coefficient	Values
Intercept	-100	1
Price of Syrah	22.93
PPI	-5	111
Price of Substitute	-10	18
Temperature	8	60
Suppliers	1	8

1. The coefficient of price in both the equations show by how much the demand/supply change when prices increase by $1.

Look at the demand equation. The coefficient for the price is -38.18. This means that when price increases by $1, demand falls by 38.18.

Similarly, from the supply equation you can tell that when price increases by $1, the supply increases by 22.93.

2. Just as the coefficient for price of Syrah tells you by how much does the demand and supply change with a $1 increase in price, so does the coefficient for the price of substitutes tell you by how much does the demand and supply change with the price of substitute increases by $1.

From the information you can tell that a $1 increase in the price of substitute increases the demand for Syrah by 8.35, but it decreases the supply by 10.

3. You say that you need the excel formulas. So I've included below a screenshot of an excel worksheet. Basically, you multiply the coefficient column with the values column for each cell and add up all the products.

See the screenshots below.

Market demand at price $22 is 35.24. The market supply is 157.46. There is clearly surplus of Syrah in the market. The surplus is 157.46 - 35.24, which equals 122.22.

4. In part 4, just change the price of Syrah to $16 in your excel sheet. You'll see the following demand and supply:

When you change the price cell to 16, you'll see that quantity demanded is now 264.32 and quantity supplied is 19.88. There is a shortage of 264.32 - 19.88, which equals 244.44.

You can use a similar method to answer part 6. Put price of Syrah equal to 20. And now change PPI to 123.222. And notice the demand and supply.

5. This part asks you to find the find the change in quantity demanded and quantity supplied when price changes by $1, keeping everything else constant. Recall what the demand and supply equations are. Set price equal to P, and now calculate demand and supply. For reference, your demand schedule is as follows:

	Coefficient	Values
Intercept	200	1
Price of Syrah	-38.18	P
Price of Substitute	8.35	18
Price of Cheese	-2	15
Income	10	53
Trade Shows	0.8	3
Millennials	0.5	45

This is the exact table as in part 1, except I've set price equal to P. Now, what is your demand? You can use excel to calculate this. Calculate the demand by setting price = 0. This gives you the x-axis intercept of the demand curve. You'll find that when you set price equal to 0, the demand equals 875.2. And the slope of the demand equation is the coefficient for price, which is -38.18. So the demand equation is:

Similarly, calculate the x-axis intercept for the supply curve by setting price = 0 in the supply schedule. The x-axis intercept is -347. The slope is the coefficient for price in the supply table, which is 22.93. So the supply equation is:

Now, put these formulae in excel and calculate the values of QD and QS for prices between $16 and $22. I've included the same in the screenshots below:

Market is in equilibrium when demand = supply. This happens when price = $20. At this price, both market demand and market supply are 111.6.

You can use this approach to answer part 7 as well. Keep the demand equation the same- QD won't change. You'll have to Recalculate supply equation however (QS will change). How do you do that? Go back to your original table. Set price = 0. But now, in supply equation, change PPI to 123.222. You'll get a new x-axis intercept in the supply equation. Use that new intercept and change the formula of calculating QS.