Chapter 2 Demand and Supply Problem (v2)
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.
Steps to Perform:
Step Instructions Points Possible
1 Start Excel. Download and open the workbook named:
Chapter_2_Demand_and_Supply_Problem_Start.
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.
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.
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 value of the shift. Refer to an appropriate cell among H8-H13.
Note: The change in the quantity supplied must be a positive number.
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.
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.
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 your formula. Use cell H26 and the appropriate cells among D8-D14, E10-E14, H8-H13, and I10-I13.
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.
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 your formula. Use cell H34 and the appropriate cells among D8-D14, E10-E14, H8-H13, and I10-I13.
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.
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 your 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 your formula. Use cells D43, H8-H13, and I10-I13. Copy the formula from cell F43 down the column to cell F49.
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.
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 your formula. Use cells H50, H53, H8-H13, I11-I13.
14 In cell K56, determine whether the change in the costs creates a shortage.
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 your 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 your formula. Use cells D59, H53, H8-H13, and I11-I13. Copy the formula from cell F59 down the column to cell F63.
16 In cell H64, by using a cell reference, determine the equilibrium price. Refer to an appropriate cell among D59-D63.
17 In cell H66, by using a cell reference, determine the equilibrium quantity. Refer to an appropriate cell among E59-F63.
18 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
