- 69 Views
- Uploaded on
- Presentation posted in: General

Example 2.2

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Example 2.2

Estimating the Relationship between Price and Demand

- The Links Company sells its golf clubs at golf outlet stores throughout the United States.
- The company knows that demand for its clubs varies considerably with price.
- In fact, the price has varied over the past 12 months, and the demand for each price level has been observed.

- The data are in the Data Sheet of this file.
- For example, during the last month, when the price was $390, 68,000 sets of clubs were sold. A sample of the data can be seen here.

- The company would like to estimate the relationship between demand and price. Then it would like to use this estimated relationship to answer the following questions.
- Assuming the unit cost of producing a set of clubs is $250 and the price must be a multiple of $10, what price should Links charge to optimize its profit?
- How does the optimal price depend on the unit cost of producing a set of clubs?
- Is the model an accurate representation of reality?

- The first of two parts of this example is estimating the relationship between price and demand.
- A scatterplot of demand versus price appears here.

- Obviously demand decreases as price increases, but we want to be more specific. Therefore, after creating this chart, select the Chart/Add Trendline menu item to bring up the following dialog box.

- This allows us to superimpose several different curves onto the scatterplot.
- We will consider the linear, power and exponential curves, defined by the general equations where y and x, a general output and input, correspond to demand and price.
- Linear: y = a+bx
- Power: y = axb
- Exponential: y = aebx

- The three functions have some general properties that should be noted because of their widespread applicability.
- The linear function is the easiest.
- Its graph is a straight line.
- When x changes by 1 unit, y change by b units.
- The constant a is called the intercept, and b is called the slope

- The power function is a curve except in the special case where the exponent b is 1. Then it is a straight line. The shape of the curve depends primarily on the exponent b.
- If b >1, y increases at an increasing rate as x increases.
- If 0 < b < 1, y increases, but at a decreasing rate, as x increases.
- If b < 0, y decreases as x increases.

- An important property of the power curve is that when x changes by 1%, ychanges by a constant percentage, a constant percentage, and this percentage is approximately equal to b%.

- The exponential function also represents a curve whose shape depends primarily on the constant b in the exponent.
- If b > 0, y increases as x increases.
- If b < 0, y decreases as x increases.

- An important property of the exponential function is that if x changes by 1 unit, y changes by a constant percentage, and this percentage is approximately equal to 100 x b%.
- Another important note about the equation is that is contains e, the special number 2.7182…. In Excel, e to any power can be calculated by the EXP function.

- If we superimpose any one of these curves on the scatterplot for demand versus price, Excel will choose the best fitting curve of that type.
- Better yet if we click the options tab of the dialog box and check Display equation on Chart option, we see the equation of this best-fitting curve.
- Doing this for each type of curve we obtain the results in the following figures.

- Each of these curves provides the best-fitting member of its “family” to the demand/price data, but which of these three is best overall?
- We answer this question by finding the mean absolute percentage error (MAPE) for each of the three curves.
- To do this, for any price in the data set and any of the three curves, we first predict demands by substituting the given price into the equation for the curve.

- The predicted demand will typically not be the same as the observed demand, so we can calculate the absolute percentage error (APE) with the general formula
- The we average these values of the APE for any curve to get its MAPE. We will consider the curve with the smallest MAPE as the best fit overall.
- The calculations appear on the following slide.

- After manually entering the parameters of the equations from the scatterplots into column B and giving them range names as shown, proceed as follows.
- Predicted demands. Substitute observed prices into the linear, power, and exponential functions to obtain the predicted demands in columns E, F, and G. Specifically, enter the formulas =Intercept+Slope*B4, =PowConst*B4^PowExpon, and =ExpConst*EXP(ExpExpon*B4) in cells E19, F19, and G19, and copy them down their respective columns.

- Average percentage errors. Apply equation 2.1 to calculate APEs in column H, I and J. Specifically, enter the general formula =ABS($C4-E19)/$C4 in cell H19 and copy it to the range H119:J30.
- MAPE. Average the APEs in each column with the AVERAGE function to get the MAPEs in row 31.

- The profit model will use the best-fitting power curve to predict demand from price.
- The model appears below. Note there is now one input variable and one decision variable.

- The profit model is straightforward. We first calculated the predicted demand from the power function with the formula =PowConst*UnitPrice^PowExpon
- This leads directly to the formulas for total revenue, total cost, and profit =UnitPrice*PredDemand, =UnitCost*PredDemand, and =TotRev-TotCost.

- We can answer the company’s questions.
- To see which price maximizes profit, we build the data table shown on the next slide.
- The column input cell is UnitPrice and the “linking” formula in cell B21 is =Profit.
- The corresponding chart shows that profit first increases, then decreases.
- We can find the maximum profit and corresponding price in at least three ways.

- First, we can attempt to read them off the chart.
- Second, we can scan down the data table for the maximum profit, which we indicate in the figure.
- The third method uses some of Excel’s most exotic features. One of these tools is conditional formatting. Excel will scan the selected range and apply a certain formatting to cells that meet certain conditions which you specify. The conditional formatting dialog box is shown on the next slide.

- What about the best price is B17? You can enter it manually or you can enter the formula=OFFSET(B21,MATCH(B16,B22:B71,0),-1)
- This formulas uses two Excel functions, MATCH and OFFSET.
- MATCH compares the first argument to the range specified in the second argument and returns the index of the cell where a match appears.
- The OFFSET function is called effectively as OFFSET(B21,28,-1). This says to start in cell B21, go 28 rows down and 1 column to the left and report the value in that cell.

- We can answer question 2: How does the best price change as the unit variable cost changes? With a two way data table.
- This is a data table with two inputs, one along the left side and the other across the top row and a single output.
- The two inputs for our problem are unit variable cost and unit price, and the single output is profit.
- The following slide shows the top part of the corresponding data table.

- To develop the table, enter desired inputs in column A and row 81, enter the “linking” formula =Profit in cell A81, highlight the entire table, select the Data/Table menu item, and enter UnitCost as the Row Input Cell and UnitPrice as the Column Input Cell.
- As before you can scan the columns of the data for the maximum profits and enter them (manually) in rows 76 and 77.
- Then you can create a chart of maximum profit versus unit cost. The chart shows that maximum profit decrease, but at a decreasing rate, as unit cost increases.

- Finally, question 3 asks us to step back from all of these details and evaluate whether the model is realistic.
- First, there is no real reason why golf club prices should be restricted to be multiples of $10. We required this only so we could use a data table to find the profit-maximizing price.
- Ideally, we would like to have a way to search over all possible prices to find the profit-maximizing price.

- Fortunately, Excel’s Solver tool enables us to accomplish this task fairly easily.
- The problem of finding a profit-maximizing price is an example of an optimization model.
- A second possible flaw in our model is that implicit assumption that price is the only factor that influences demand.
- In reality, other factors such as advertising, the state of the economy, competitors prices, strength of competition and promotional expenses also influence demand.

- A final flaw in our model is that demand might not equal sales.
- For example, if the actual demand for golf clubs during a year is 70,000 but the company’s annual capacity is only 50,000, the company would observe sales of only 50,000.
- This would cause us to underestimate actual demand, and our curve fitting method would produce biased predictions.
- As these comments indicate,. Most models are not perfect, but we have to start somewhere!