CEBU INSTITUTE OF TECHNOLOGY – UNIVERSITY

COLLEGE OF ENGINEERING AND ARCHITECTURE

DEPARTMENT OF INDUSTRIAL ENGINEERING

COURSEWARE

IE350-L01
ANALYTICAL
TOOLS AND TRENDS
Topic 4

Prepared by:

Engr. KRISTAN IAN D. CABAÑA

Instructor

MidYear Term
Academic Year 2020-2021
 ABOUT THE COURSE
Course / Section IE350 ANALYTICAL TOOLS & TRENDS Credit Units 3
Term Offered MidYear Term 2020 Total Hours 54
Instructor Engr. KRISTAN IAN D. CABAŇA
Pre-Requisite/s Second Year Standing
Co-Requisite/s None

COURSE DESCRIPTION
Analytical Tools and Trends is divided into three major areas: analytical tools in
marketing, QM tools, and system dynamics. This introduces approaches in analyzing
marketing data in order to characterize and predict marketing data through
establishing spreadsheet models using Microsoft Excel® PivotTable, Solver & Data
Analysis Add-Ins. This course also explores analytical methodologies in product
research & development. System Dynamics is also integrated to this course in order
to understand the interconnectedness of variables or elements of complex systems
over time.

TOPIC 4a

MODULE 4: PREFERENCE-BASED CUSTOMER SEGMENTATION

LEARNING OUTCOMES
L01 Predict discrete and dichotomous choices of market using logistic regression
Evaluate the product characteristics that drive a consumer’s preference for products
L02
using conjoint and discrete choice analyses
Categorize market data into groups using cluster analysis and appraise differences
L03
among clusters

MODULE 5: SYSTEMS DYNAMICS

LEARNING OUTCOMES
L01 Describe complex problem from a systems perspective
Design a model of a complex system’s problem using causal loop-diagram and
L02
stock-and-flow diagram

L03 Design a sustainable solution to a complex system’s problem

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 MODULE 4:
PREFERENCE-BASED CUSTOMER SEGMENTATION

CLUSTER ANALYSIS

Often the marketer needs to categorize objects into groups (or clusters) so that the objects
in each group are similar, and the objects in each group are substantially different from the
objects in the other groups.

• When Proctor & Gamble (P&G) test markets a new cosmetic, it may want to
group U.S. cities into groups that are similar on demographic attributes such as
percentage of Asians, percentage of Blacks, percentage of Hispanics, median
age, unemployment rate, and median income level.
• As marketing analyst at Coca-Cola wants to segment the soft drink market based
on consumer preferences for price sensitivity, preference of diet versus regular
soda, and preference of Coke versus Pepsi.
• Microsoft might cluster its corporate customer based on the price a given
customer is willing to pay for a product. For example, there might be a cluster of
construction companies that are willing to pay a lot for Microsoft Project but not
so much for Power Point.

In our discussion, we will use the first example to learn how Excel Solver makes it easy to
perform a cluster analysis. For example, in the U.S. city illustration, you can find that every
U.S. city is similar to Memphis, Omaha, Los Angeles, or San Francisco. You can also find,
for example, that the cities in the Memphis cluster are dissimilar to the cities in the other
clusters.

Now, refer to Microsoft Excel file “03-Cluster Analysis, sheet 01-cities. Suppose
you want to cluster 24 of America’s largest cities. For each city you have the
following demographic data that will be used as the basis of your cluster analysis:
(1) % age black; (2) age Hispanic, & (3) % age Asian.

For example, Atlanta’s demographic information is

as follows: Atlanta is 67 percent Black, 2 percent
Hispanic and 1 percent Asian. For now, assume
your goal is to group the cities into three clusters.
The basic idea used to identify the clusters is to
choose city to “anchor”, or “center”, each cluster.
You assign each city to the “nearest” cluster
center. Your target cell is then to minimize the sum
of the squared distances from each city to the
closest cluster anchor.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 Standardizing the Attributes

In the example, if you cluster using the attribute levels, the percentage of Blacks and
Hispanics in each city will drive the clusters because these values are more spread out than
the other demographic attributes. To remedy this problem, you can standardize each
demographic attribute by subtracting off the attribute’s mean and dividing by the attribute’s
standard deviation.
1. You may want to add at least 5 rows above your data for other calculations. Starting
with, computing for AVERAGE and STDEVA per demographic attribute (%Black,
%Hispanics, %Asian) then proceed to STANDARDIZE.
2. Let us label STANDARDIZE column of %Black as Z1, Z2 for %Hispanic and Z3 for
%Asian.

Input formula in cell F3 as =STANDARDIZE(X,MEAN,STDDEV). Click F4 for the mean and

standard deviation for it to be fixed. Drag down or double click the lower right dot/box at the
end of the cell.

3. Repeat step in #2 but this time for Z2 and Z3. It should show values in the illustration below.

To check if you run correct STANDARDIZE values, you can check the mean of Z1/Z2/Z3 is
0 and the standard deviation is 1.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 Choosing the clusters
You can use the Solver to identify a given number of clusters. The key in doing so is to
ensure that the cities in each cluster are demographically similar and cities in different
clusters are demographically different. Using few clusters enables the marketing analyst to
reduce the 49 U.S. cities into a few (in your case three) easily interpreted market segments.
Since the tool is intended for getting the preference of customers, we can assume that the
highest percentage (MAXIMUM) per demographic attribute is the clusters we are looking
for. Which means that Detroit is the highest %Black, El Paso for %Hispanic and Honolulu in
%Asian. We call these as your Anchors/Data Centers.

Note: The values of the anchors are just their respective Z1, Z2 and
Z3 values.

EXCEL HACKS: Before we proceed , let’s talk about a more visual way or colorful
way rather to find the maximum value. We all know, =MAX right? This time we will
explore CONDITIONAL FORMATTING.

1. Highlight all values in the demographic attributes.

2. Go to Conditional Formatting in the Home View and select Color Scales.

3. Choose any theme of colors. If you select the first one for example, the value with the
darkest red is the maximum value. Or you may try using MORE RULES, because I want
to see only one color in different shades to identify the minimum and maximum values.
Take for example the illustration below.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
Doing this, we would get these result. You will see Detroit, El Paso and Honulu.

Now, back to the topic.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 Finding Optimal Clusters
Let each point’s contribution to the target cell be the squared distance to the closest anchor.
Then choose one anchor from each group to minimize the target cell. This ensures each
point is “close” to an anchor. To do this, you have to compute for the squared distances of
this anchors.

1. Compute for the distance square of Z1.

=SUMXMY2(Z1 value to Z3 values, anchor values of 1)

2. Repeat step #1 for D2 to 2 and D2 to 3. It should show the following values.

3. Get the minimum distribution among these distances. =MIN(I8:K8)

4. Match the Clusters from its MIN distance value to other distance values.
=MATCH(MIN,D2 to 1 to D2 to 3,Ascending Order). It show values presented below.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
5. Look which anchor the city will be clustered.
=VLOOKUP(CLUSTER,ANCHORS)

6. Compute for the SUM of all MINIMUM DISTANCE.

7. Run the Excel Solver by setting the sum as the objective, to minimize our anchors. It
could the cities in the anchors and the sum data. If not, then we have already correctly
identified the clusters.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
Lastly, summarize the clusters. Copy/paste data to a different blank sheet and SORT DATA
by last column. The clusters now are

Interpretation: You can find that Detroit cluster consists of 15 of 24 cities in the study. El
Paso consists of 8 cities and Honolulu alone is one cluster.

Determining the Correct Number of Clusters

After a while, adding clusters often yields a diminishing improvement in the target cell. To
determine the “correct” number of clusters, you can add one cluster at a time and see if the
additional complexity of adding a cluster yields improved insights into the demographics of
the cities. You can usually start by running three clusters and in this case you have a sum
of 122.3770. Should adding another cluster would just lead you to a sum value close to
122.3770 it means that three is just correct. But if it would give a difference like a sum
value of 90, you may consider adding another cluster.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 CONJOINT ANALYSIS

This tool is used to segment customers in a given market. Conjoint Analysis answers the
question, “What do Customers Want?”
Often the marketing analyst is asked to determine the attributes of a product that are most
(and least) preferred in driving a consumer’s product choice. For example, when a soda
drinker chooses between Coke & Pepsi, what is the relevant importance of the following:

• Price
• Brand (Coke or Pepsi)
• Type of soda (diet or regular)

After showing a consumer several products (called product profiles) and asking the
customer to rank these product profiles, the analyst can use full profile conjoint analysis to
determine the relative importance of various attributes. This module shows how the basic
ideas behind conjoint analysis are simply an application of multiple regression.

After understanding how to estimate a conjoint model, you will learn to use conjoint analysis
to develop a market simulator, which can determine how a product’s market share can
change if the product’s attributes are changed or if a new product is introduced into the
market.

Products, Attributes, and Levels

Essentially, conjoint analysis enables the marketing analyst to determine the product
characteristics that drive a consumer’s preference for products. For example, in purchasing
a new car what matters most: brand, price, fuel efficiency, styling, or engine power? Conjoint
analysis analyzes the consumer decision process by identifying the number of product
choices available; listing the main characteristics used by consumers when choosing among
products; and ranking each attribute offered in each product.
The product set is a set of objects from which the consumer must make a choice. For
example, a product set might be luxury sedans, laptop computers, shampoos, soda and so
on. Conjoint analysis is also used in fields such as human resources, so product sets don’t
necessarily have to be consumer goods. For example, the HR analyst might want to
determine what type of compensation mix (salary, bonus, stock options, vacation days, and
telecommuting) is most attractive to prospective hires.
Each product is defined by the level of several product attributes. Attributes are the variables
that describe the product. The levels for each attribute are the possible values of the
attributes. Table below shows four examples of levels and attributes.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 Now, refer to 04-ConjointAnalysis file worksheet 01-Milktea &
01-Milktea2. “MILKTEA CASE PROBLEM”

The consumer is shown a set of products (called product profiles) and asked to rank them
in order from best (#1) to worst. In the given data below, there are a total of 3 × 3 x 2 = 18
possible product profiles. The 18 product profiles cannot be randomly chosen. For example,
if every profile with pearl, then the analyst could not determine whether the consumer
preferred a profile because of nata or coffee jelly. Essentially, you want the attributes to be
uncorrelated. This combination of product profiles is called an orthogonal design (as table
shown below).
Choices Sinker/Floater Flavor Size
1 Pearl Okinawa Medium
2 Pearl Okinawa Large
3 Nata Okinawa Medium
4 Nata Okinawa Large
5 Coffee Jelly Okinawa Medium
6 Coffee Jelly Okinawa Large
7 Pearl Wintermelon Medium
8 Pearl Wintermelon Large
9 Nata Wintermelon Medium
10 Nata Wintermelon Large
11 Coffee Jelly Wintermelon Medium
12 Coffee Jelly Wintermelon Large
13 Pearl Classic Medium
14 Pearl Classic Large
15 Nata Classic Medium
16 Nata Classic Large
17 Coffee Jelly Classic Medium
18 Coffee Jelly Classic Large

Proof of Orthogonality

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 Conjoint Analysis Method
Step 1: Set your standard product profile.

Attributes Levels
Milk Tea Sinkers / Floaters Pearl
For example, we set the following profile:
Nata
Coffee Jelly Pearl, Wintermelon, Large
Flavor Okinawa
Wintermelon
Classic
Size Medium
Large

Step 2: Create columns that asks for a preference, coding 0 for no, and 1 as yes. Do not
include columns that would ask our standard profile (Pearl, Wintermelon, Large) or simply
omit our standard profile. For example: since our sinker/floater is Pearl, add columns for
Nata and Coffee Jelly, and do the same for flavor and size.

Choice 1 profile is a Pearl, Okinawa, Medium. Put 0 if it doesn’t match the product profile,
and 1 if yes it matches what is in the profile.

Step 3: Rank the profiles as whether the most preferred (best), or the least preferred (worst
method). To get the worst in rank, = (Total Number of Profiles + 1) - RankoftheBest).

The Rank for the Best here is assumed for practice use. It should be the rank given by the customer themselves.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 Step 4: Run the Regression Model with the Rescale (Worst=1) as the dependent variable
(Y) and Nata to Medium as your independent variables (X’s). To get this result, as shown
below:

All independent variables except MEDIUM, is significant at 0.05 level (based on p-values).
But in this case, you don’t need to rerun the model since we are getting preference, and not
significance of the relationships.

The r2 value of 0.9477 indicates that the attributes explain 94.77% of the variation of the
consumer’s ranking. The best prediction for the rescaled rank of a product is as follows:

Predicted Rescaled Rank (Best Rank) = 11.67 - 3.33(Nata) - 4.67(CoffeeJelly) +

6.67(Okinawa) - 4.67(Classic) - 0.33(Medium)

The result implies the following:

• Pearl leads to a rank 3.33 higher than Nata and 4.67 higher than Coffee Jelly.
• Wintermelon leads to a rank 6.67 lower than Okinawa and 4.67 higher than Classic.
• Large leads to a rank 0.33 higher than Medium.

Step 5: Rank the Attributes and Levels

Now, we already have the coefficients. What we need to do is make a comparison/

differences of the levels.

You can see that FLAVOR is the most preferred or BEST attribute, and the least preferred
attribute or WORST is the size. This customer ranks levels as follows:
• Flavor: Okinawa, Wintermelon, Classic
• Sinkers/Floaters: Pearl, Nata, Coffee Jelly
• Size: Large, Medium
Within each attribute you can rank the levels from most preferred to least preferred. In this
customer’s example, Okinawa is most preferred, and Classic is least preferred.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 TOPIC 4 LEARNING EVIDENCE

MICROSOFT EXCEL SPREADSHEET MODEL

Deadline: 11:59PM, July 14, 2021

GENERAL DIRECTION:
Refer to worksheet 04-ConjointAnalysis, complete the objectives set in each
assigned sheet. Please see table below.

SHEET OBJECTIVES

Create your own rank of preference of product profiles. Run the

02-CokevsPepsi
conjoint analysis model.

Create your own rank of preference of product profiles. Run the

03-Pizza
conjoint analysis model.

SPREADSHEET MODELLING RUBRIC

Evaluation Poor Developing Satisfactory Exemplary
Criteria 1.0 1.5 2.0 2.5 3.0 3.5 4.0 4.5 5.0 5.5 6.0 6.5 7.0 7.5 8.0 8.5 9.0 9.5 10.0
Objectives
Spreadsheet fails to Spreadsheet Spreadsheet
(50%) Spreadsheet meets
include and make clear include objectives include objectives
all objectives and
the objectives needed but 2 of 3 are not and only 1 of 3 are
are clearly defined.
to be done. clear. not clear.
The spreadsheet
The spreadsheet
The spreadsheet functions are well-
function cannot be
Use of Excel functions will developed and will
The spreadsheet has no used to correctly
Functions determine the correctly
functions used. determine the
(40%) needed determine the
needed
information. needed
information.
information.
The spreadsheet is The spreadsheet
The spreadsheet somewhat has attractive
The spreadsheet
formatting and organized. formatting and is
has exceptional
Neatness and organization can be Readability needs somewhat
formatting and the
Organization confusing to observer. improvement, as organized. Data
information is well
(10%) Spreadsheet there is great can be read and
organized. It’s easy
information is not difficult in interpreted, but
to read.
readable. interpreting with some
information. difficulty.

Submit your output in excel format via Moodle on or before 11:59PM, Wednesday, July 14.
File Name: LE4-IE350-FamilyName.xlsx

ADDITIONAL ASSIGNMENT: Download and install VensimPLE software

Download the Vensim software from https://vensim.com/free-download/ and install in your

PC. The software will be used in developing models. Ensure that you choose the PLE
academic/student evaluation version, not the 60-day free version, so that your use will not
expire. Vensim requires you to register your email.

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01
 END OF WEEK 4
MODULE 4: PREFERENCE-BASED CUSTOMER SEGMENTATION

SOURCES
1. Wayne Winston, “Marketing Analytics: Data Driven Techniques with
Microsoft Excel”, 2014
2. Wayne Winston, “Microsoft Excel 2013: Data Analysis and Business
Modelling”, 2014

IE350 ANALYTICAL TOOLS AND TRENDS

Version: EIOR-01 | KIDC-01