Directions: This is a team assignment, so you must work with your assigned team to create one MS Word or PDF ie in response to the questions in the attached document. One member of each team wiii submit your MS Word or PDF document for the whoie team. No other formats will be accepted. Please make sure that your file does not exceed 10 MBs in size. As with aii other assignments, please utiiize well-executed, cieariy constructed graphs where requested. Piease remember to: - Labei aii axes Label aii tines/curves I - indicate direction of the shift(s) in curves with arrows, as appropriate. - You do not have to indicate the intercepts for the curves for this assignment. This assignment consists of 3 (three) questions. Please make sure that you complete all of them. We suggest that you utilize Excel for completing this assignment; and then transfer your graphs and insert appropriate narratives into this Microsoft Word template (and print it, as necessary, to PDF). Setting: Applicable to All Questions Consider W a medium-size nation that both produces and consumes oil and does not either export or import oil from other countries. As a result of W industrial policy, its oil extractip and rening sites (30 plants located in North, East, South-East, South, and Central regions of the country) are all separately owned and are highly competitive in the provision of oil to its consumers. The plant information is shown in the Excel document associated with this assignment. The document also provides the plant capacity (in thousands of barrels of oil per day); the marginal cost (MC) of extraction and rening; and indicates whether the extraction technology used is either conventional (typically, less expensive) or non-conventional (shale oil extraction, typically more expensive). You can also assume that the marginal cost (MC) of extraction and rening is constant for the purposes of your analysis throughout the productive capacity of the plant; and thus, MC is the same as the average variable cost (AVG). The demand for oil in Wis relatively stable. The demand equation can be approximated by Qg = -20P + 2,200. Alternatively, you can re-write this equation as an inverse demand equation, P = -0.050d + 110. Page 1 of 3 Question 1 (15 points) Using Excel, create a graph that clearly shows demand and supply for oil in W, and determine the equilibrium quantity and price. Helpful Hints: Use the marginal cost and quantity data to graph your supply curve, assuming that the individual plants will be willing and able to produce oil it the market price is at least equal to their marginal cost of extraction and rening. You can assume that the transportation costs are already included in the marginal cost gures. You have enough information in the Excel table to determine the Quantity Demanded and Quantity Supplied that are required to create the two curves. in determining quantity supplied, it might be useful to sort your data by price {from lowest to highest) and then create a formula for determining the quantity supplied at each increment of the marginal cost. For example, at a price of $10 (lowest marginal cost associated with Plant 81), only this plant will operate, and Qs will be equal to the plant's capacity of 120 (120, 000 barrels per day). However, when price rises to $12, both Plant 81 and 810 will operate, producing Qs=120+120=240 units or 240,000 barrels per day). a Make sure that you appropriately label your axes and include legends for your curves. Please recall that your Q should be on the x-axis and your P should be on the y-axis, per standard convention in economics. - Insert the graph below (copy and paste from Excel). Please scale the graph as appropriate to show detail clearly. a What are the equilibrium quantity and price of oil in W? How many plants operate to produce oil, and how many plants will shut down in the short run? Why? What types of plants primarily remain shut down, and why? I'Acihiluestion 2 (15 points) Now, suppose that as population and incomes grow, demand for oil shifts, and can now be approximated by the demand function 96 = -20P + 3,300. Using Excel, create a graph that clearly shows (new) demand and (unchanged) supply for oil in Wand, and determine the equilibrium quantity and price. a Make sure that you appropriately label your axes and include legends for your curves. Please recall that your Q should be on the x-axis and your P should be on the y-axis, per standard convention in economics. - Insert the graph below (copy and paste from Excel). Please scale the graph as appropriate to show detail clearly. a What are the equilibrium quantity and price of oil in W? As demand changed, how many plants will now operate to produce oil, and how many plants will remain shut down in the short run? Why? Page 2 of 3 Question 3 (20 points) This question builds on Questions 1 and 2 and assumes that the demand curve is as described in Question 2, Q= -20P + 3,300. Additionally, due to a considerable improvement in the shale oil extraction technology, the shale oil plants (3 plants in the South-East, numbered SE1 through SE3; and 4 plants in the East, numbered E1 through E4) a_r9_ab_le_t_q reduce their respective marginal costs by 60% and increase capacity by 100% (double the capacity, essentially). For example, for Plant SE2, marginal cost was $32 with capacity of 50 (50 thousand barrels per day); now, these become an MC of $12.80 and capacity of 100 (100 thousand barrels per day). Using Excel, create a graph that clearly shows demand curve from Question 2 and the new supply for oil in W, and determine the equilibrium quantity and price. a Make sure that you appropriately label your axes and include legends for your curves. Please recall that your Q should be on the x-axis and your P should be on the y-axis, per standard convention in economics. - Insert the graph below (copy and paste from Excel). Please scale the graph as appropriate to show detail clearly. a What are the equilibrium quantity and price of oil in W? As supply changed, how many plants will now operate to produce oil, and how many plants will remain shut down in the short run? Why? What types of plants are primarily impacted by closures? 3 Plant Identifier Location Extraction Type Plant Capacity, '000 b/d |MC (Extraction/Refining), $/b Qs (Quantity Supplied) |Qd (Quantity Demanded) 4 Plant C1 Central Conventional 70 $ 30.00 5 Plant C2 Central Conventional 80 $ 35.00 6 Plant C3 Central Conventional 30 $ 36.00 Plant C4 Central Conventional 90 $ 31.00 8 Plant C5 Central Conventional 60 $ 37.00 Plant E1 East Shale 60 $ 52.00 10 Plant E2 East Shale 80 $ 38.00 11 Plant E3 East Shale 90 $ 40.00 12 Plant E4 East Shale 60 $ 42.00 13 Plant N1 North Conventional 210 $ 28.00 14 Plant N2 North Conventional 80 $ 33.00 15 Plant N3 North Conventional 70 $ 34.00 16 Plant N4 North Conventional 100 $ 43.00 17 Plant N5 North Conventional 100 $ 44.00 18 Plant N6 North Conventional 150 $ 20.00 19 Plant S1 South Conventional 120 $ 10.00 20 Plant S10 South Conventional 120 $ 12.00 21 Plant S11 South Conventional 120 S 15.00 22 Plant S12 South Conventional 60 $ 22.00 23 Plant S2 South Conventional 100 $ 16.00 24 Plant S3 South Conventiona 60 $ 18.00 25 Plant S4 South Conventiona 70 $ 19.00 26 Plant S5 South Conventiona 70 $ 21.00 27 Plant S6 South Conventional 60 $ 23.00 28 Plant $7 South Conventional 60 $ 24.00 29 Plant S8 South Conventional 60 $ 25.00 30 Plant S9 South Conventional 90 $ 26.00 31 Plant SE1 South-East Shale 70 $ 46.00 32 Plant SE2 South-East Shale 50 $ 32.00 33 Plant SE3 South-East Shale 70 $ 48.00 34 35 36 37 Data for Team Assignment 2 +