MARK3054, T1 2023, S Dong 1
Tutorial Week 7: Work Sheet
Before the tutorial
 Try the exercises as much as possible.
About this tutorial
 We will exercise cluster analysis using R
 We will exercise price elasticity and price optimization using Excel
About this worksheet
 Use the data files “New PDA Dataset 2 for R.csv” and “New PDA Dataset 2_Exercise.xlsx”,
and R script “New_PDA_Dataset_2_RScript.Rmd”
o Exercise hierarchical cluster analysis
o Exercise PAM cluster analysis
 Use the data file “Price Elasticity_Exercise.xlsx”
o Exercise price elasticity calculation
 Use the data files “Price Optimization_Exercise.xlsx”
o Exercise price optimization
 The solution of the exercises will be available on Moodle once all tutorial classes are
completed.
MARK3054, T1 2023, S Dong 2
Hierarchical cluster analysis
Firstly, load the datasets from csv files into R (make sure the csv files are in the same folder with
the R script).
Questions:
Q1. Which data items will you choose for the cluster analysis?
Q2. Why do the data items need to be standardized before going to the cluster analysis?
Choose the appropriate variables and standardize the data
Conduct hierarchical cluster analysis
Question:
Q3. Try to plot dendrograms with different numbers of groups. Based on the dendrograms
you draw, how many clusters do you think are most appropriate? Why?
MARK3054, T1 2023, S Dong 3
Investigate the optimal number of clusters
Load the packages needed to investigate the optimal number of clusters
Use the following code to find out the software suggestion of the optimal N of clusters
Questions
Q4. How many indices does R use here?
Q5. Based on the software suggestions, how many clusters will you try?
MARK3054, T1 2023, S Dong 4
PAM cluster analysis
Load the package needed to run Partitioning Around Medoids (PAM)
Conduct PAM cluster analysis with 3 groups
Check cluster results and export them to a csv file
Question
 Repeat these processes to conduct PAM cluster analysis with 2 groups.
MARK3054, T1 2023, S Dong 5
Interpret cluster profiles
Operations:
 Use Excel file “New PDA Dataset 2_Exercise.xlsx”
 Open the cluster analysis membership result “Cluster_Membership_G3.csv”. Copy the
membership data in column B, and paste them in column AH of the “Membership (3G)”
worksheet in the “New PDA Dataset 2_Exercise.xlsx” file.
 Cell AI143: “=COUNTIFS($AH$2:$AH$136,AH143)”, copy it to Cell AI144 and Cell AI145
 Cell B143: “=AVERAGEIFS(B$2:B$136,$AH$2:$AH$136,$AH143)”, copy it to the entire
range B143:AG145.
o To copy the formula without changing the format of the existing cells, first copy
cell B143, then select the entire range B143:AG145. Right-click on the selected
range. In the pop-up menu, select “Paste Special”, then select “Formulas” and
click “OK”.
 Copy the data in the Range AI143:AI145 to the range C5:E5 in the “Clusters (3G)”
worksheet. (Copy values only and transpose.)
 Copy the data in the Range B143:P145 to the range C10:E24 in the “Clusters (3G)”
worksheet. (Copy values only and transpose.)
 Copy the data in the Range Q143:AG145 to the range I10:K26 in the “Clusters (3G)”
worksheet. (Copy values only and transpose.)
Interpret the cluster profiles
 What is the market size of each cluster?
 What are the needs of the customers in each cluster?
 Who are they in each cluster?
 How to promote the products to the consumers in each of the clusters?
Question
 Repeat these processes and interpret the PAM cluster results of 2 groups.
MARK3054, T1 2023, S Dong 6
Price Elasticity Calculation
Question:
 Cost is $5 per unit
 Current price $10 and the demand is 1000
 If increase price to $11, how would the profit change if the price elasticity is -0.5?
 What if the price elasticity is -1 or -2?
Operations:
1) New demand in Cell D6: use the elasticity equation
“=D3*(D5-$C$5)/$C$5*$C$6+$C$6”
2) Revenue in Cell D7: “=D5*D6”
3) Cost in Cell D8: “=D4*D6”
4) Profit in Cell D9: “=D7-D8”
5) Follow steps 1 ~ 4 to calculate for elasticity -1 and -2.
6) Follow steps 2 ~ 4 to calculate revenue, cost, and profit for the current situation.
More question: how would the profit change if the price is decreased to $9?
MARK3054, T1 2023, S Dong 7
Price Optimization
Question: find out the optimal price which maximizes the profit in the given case.
Operations:
1) Select both price and demand data (Range C4:D10)
2) Select “Insert” ribbon -> “Charts” -> “Scatter”. Select the first scatter plot type (the one
without any lines). This operation will scatter plot price and demand data.

3) On the scatter plot, right click on any spot; in the pop-up menu, select “Add Trendline”
 Try different trend types to find the best option which fits the data best. We will use
polynomial (order: 2) in the rest of the exercise.
 Select “Display Equation on chart” to get the demand function
4) Cell G5: setup an initial price, e.g., $2
5) Cell G6: setup up the demand using the demand function. That is, “=16*G5^2-
106.51*G5+197.66”
6) Cell G7: setup the profit function using the demand, price, and the unit cost. That is,
“=G6*(G5-C2)”
7) Select “Data” ribbon -> “Solver”, use the solver to find out the optimal price (G5) that
maximize the profit (G7).
 Set the target to maximize profit (G7)
 Set the “by changing variable cells” to be the price (G5)
 Set the constraints by “Add” price (G5) >= $1, and price (G5) <= $3.5
 Unclick “Make Unconstrained Variables Non-Negative”
 Choose “GRG Nonlinear” as the solving method
 The settings can be found in the screenshot in the next page
 Click “Solve” to run the solver
 When the solver converges, “Keep Solver Solution”
MARK3054, T1 2023, S Dong 8
8) Try different initial values within the price range (i.e., $1.00 - $3.50), but avoid the corner
values ($1.00 and $3.50) because they may lead to unexpected non-optimal solutions
(corner solutions). For each initial value, run the solver again with the same setting. Check if
the results are consistent.
9) If the results are consistent, we get a confident result of the optimal price, which maximize
the profit.