Question

(a) Focus for now on SKU Number 1101. This is price and quantity data for an industrial part called a centralizer plate. Notice that this firm changed its price for centralizer plates twice over the time period shown. What was the average monthly quantity of centralizer plates for each of the three prices shown? (b) Make a graph with price on the vertical axis and average monthly quantity of centralizer plates sold on the horizontal.

Answer 1

First of all we will enter the data into excel sheet. Then we will find the average quantity for each price.

We will use the Averageif formula in excel.

For Price $3.49, formula will be AVERAGEIF($E$2:$E$25,H2,$F$2:$F$25)

For Price $3.69, formula will be AVERAGEIF($E$2:$E$25,H3,$F$2:$F$25)

For Price $2.99, formula will be AVERAGEIF($E$2:$E$25,H3,$F$2:$F$25)

From calculation, we will find the below mentioned results-

Price	Average Quantity
$3.49	855
$3.69	785
$2.99	1157

After that we will prepare the graph as desired.

Screenshot of calculation & graph is as mentioned below -

From above, it is clear that with increase of price. quantity decreases and with decrease in price, quantity increases.