Excel Problem

profilebubbapage91
Page_Chapter_2_Demand_and_Supply_Problem_Start.xlsx
Home>Business & Finance homework help>Economics homework help>Excel Problem

Problem

Demand and Supply Problem
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 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, 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 MARKET SUPPLY
Coefficients Values Coefficients Values
Intercept Intercept
Price of Syrah Price of Syrah
Price of Substitutes PPI
Price of Cheese Price of Substitutes
Income Temperature
Trade Shows Suppliers
Millennials
a) When the price of Syrah increases by $1, do supply and demand increase or decrease?
b) By how much? What is the effect on quantity demanded and quantity supplied?
a) b)
Quantity demanded by million bottles.
Quantity supplied by million bottles.
c) Does a $1 decrease in the price of substitute bottles of wine shift the demand and supply curves to the left or right?
d) By how much?
c) d)
Demand curve is shifted to the by million bottles.
Supply curve is shifted to the by million bottles.
e) Suppose the price of Syrah is currently $22 per bottle. How many bottles will be demanded and supplied monthly?
Price of Syrah = $22.00
Bottles demanded = million
Bottles supplied = million
f) Is there a shortage or a surplus?
g) How much is the shortage or surplus?
f) g)
There is a equal to million bottles.
h) If the market price of Syrah falls to $16 per bottle, how many bottles will be demanded and supplied monthly?
Price of Syrah = $16.00
Bottles demanded = million
Bottles supplied = million
i) Is there a shortage or a surplus?
j) How much is the shortage or surplus?
i) j)
There is a equal to million bottles.
k) Trying prices in $1 increments between $16 and $22, at what price and quantity does the market equilibrium occur?
Price Quantity demanded (in millions) Quantity supplied (in millions)
$16.00
$17.00
$18.00
$19.00
$20.00
$21.00
$22.00
Equilibrium price =
Equilibrium quantity = million bottles
l) Suppose the costs of production increase to 123.222. If the price of wine stays at the point determined in part k, what will be supplied in the market?
PPI = 123.222
QS = million bottles
m) Will this create a shortage?
If the costs of production increase to 123.222 and the price of wine stays at the point determined in part k this create a shortage.
n) With the increase in production costs to 123.222, at what price will the market be in equilibrium again?
Price Quantity demanded (in millions) Quantity supplied (in millions)
$18.00
$19.00
$20.00
$21.00
$22.00
Equilibrium price =
o) What will be demanded and supplied at the price determined in part n?
Equilibrium quantity = million bottles

Instructions

Project Description: In this problem, you will calculate the quantity demanded, quantity supplied, and equilibrium price. Then you will determine the effects of changes in the market conditions on equilibrium.
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. 0
2 In cells D8-D14, E10-E14, H8-H13, and I10-I13, enter the coefficients and values for the market demand and market supply functions. Note: Do not use the equal sign when entering a numeric value. The values for income and number of suppliers should be entered in thousands and the value for millennials should be entered in millions. 1
3 In cell H18, determine whether the quantity demanded increases or decreases when the price of Syrah increases by $1. In cell J18, by using a cell reference, calculate the change in the quantity demanded. Refer to an appropriate cell among D8-D14. Note: The change in the quantity demanded must be a positive number. 1
4 In cell H19, determine whether the quantity supplied increases or decreases when the price of Syrah increases by $1. In cell J19, by using a cell reference, calculate the change in the quantity supplied. Refer to an appropriate cell among H8-H13. Note: The change in the quantity supplied must be a positive number. 1
5 In cell H23, determine the direction in which the demand curve shifts when the price of substitute bottles of wine decreases by $1. In cell J23, by using a cell reference, calculate the amount of the shift in the demand curve. Refer to an appropriate cell among D8-D14. Note: The amount of the shift in the demand curve must be a positive number. 1
6 In cell H24, determine the direction in which the supply curve shifts when the price of substitute bottles of wine decreases by $1. In cell J24, by using a cell reference, calculate the amount of the shift in the supply curve. Refer to an appropriate cell among H8-H13. Note: The amount of the shift in the supply curve must be a positive number. 1
7 In cells H27 and H28, by using cell references, calculate the bottles demanded and bottles supplied, respectively, corresponding to Price of Syrah in cell H26. Use the Excel SUMPRODUCT function as a part of the formula. Use cell H26 and the appropriate cells among D8-D14, E10-E14, H8-H13, and I10-I13. 1
8 In cell H32, determine whether there is a shortage or surplus corresponding to Price of Syrah in cell H26. In cell J32, by using cell references, calculate the amount of shortage/surplus. Use cells H27 and H28. Note: The amount of shortage/surplus must be a positive number. 1
9 In cells H35 and H36, by using cell references, calculate the bottles demanded and bottles supplied, respectively, corresponding to Price of Syrah in cell H34. Use the Excel SUMPRODUCT function as a part of the formula. Use cell H34 and the appropriate cells among D8-D14, E10-E14, H8-H13, and I10-I13. 1
10 In cell H40, determine whether there is a shortage or surplus corresponding to Price of Syrah in cell H34. In cell J40, by using cell references, calculate the amount of shortage/surplus. Use cells H35 and H36. Note: The amount of shortage/surplus must be a positive number. 1
11 In cells E43-F49, do the following: In cell E43, by using relative and absolute cell references, calculate the quantity demanded corresponding to Price of Syrah in cell D43. Use the Excel SUMPRODUCT function as a part of the formula. Use cells D43, D8-D14, E10-E14. Copy the formula from cell E43 down the column to cell E49. In cell F43, by using relative and absolute cell references, calculate the quantity supplied corresponding to Price of Syrah in cell D43. Use the Excel SUMPRODUCT function as a part of the formula. Use cells D43, H8-H13, and I10-I13. Copy the formula from cell F43 down the column to cell F49. 2
12 In cell H50, by using a cell reference, determine the equilibrium price. Refer to an appropriate cell among D43-D49. In cell H51, by using a cell reference, determine the equilibrium quantity. Refer to an appropriate cell among E43-F49. 1
13 In cell H54, by using cell references, calculate the quantity supplied corresponding to Price of Syrah in cell H50 and Costs of production in cell H53. Use the Excel SUMPRODUCT function as a part of the formula. Use cells H50, H53, H8-H13, I11-I13. 1
14 In cell K56, determine whether the change in the costs creates a shortage. 1
15 In cells E59-F63, do the following: In cell E59, by using relative and absolute cell references, calculate the quantity demanded corresponding to Price in cell D59. Use the Excel SUMPRODUCT function as a part of the formula. Use cells D59, D8-D14, and E10-E14. Copy the formula from cell E59 down the column to cell E63. In cell F59, by using relative and absolute cell references, calculate the quantity supplied corresponding to Price in cell D59. Use the Excel SUMPRODUCT function as a part of the formula. Use cells D59, H53, H8-H13, and I11-I13. Copy the formula from cell F59 down the column to cell F63. 2
16 In cell H64, by using a cell reference, determine the equilibrium price. Refer to an appropriate cell among D59-D63. 1
17 In cell H66, by using a cell reference, determine the equilibrium quantity. Refer to an appropriate cell among E59-F63. 1
18 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0