Wayne L. Winston is John and Esther Reese chaired Professor of Decision Sciences at the Indiana University Kelley School of Business and will be a Visiting Professor at the Bauer College of Business at the University of Houston. He has won more than 45 teaching awards at Indiana University. He has also written numerous journal articles and a dozen books, and has developed two online courses for Harvard Business School.

Introduction xxiii

I Using Excel to Summarize Marketing Data 1

1 Slicing and Dicing Marketing Data with PivotTables 3

Analyzing Sales at True Colors Hardware 3

Analyzing Sales at La Petit Bakery 14

Analyzing How Demographics Affect Sales 21

Pulling Data from a PivotTable with the GETPIVOTDATA Function 25

Summary 27

Exercises 27

2 Using Excel Charts to Summarize Marketing Data 29

Combination Charts 29

Using a PivotChart to Summarize Market Research Surveys 36

Ensuring Charts Update Automatically When New Data is Added 39

Making Chart Labels Dynamic 40

Summarizing Monthly Sales-Force Rankings 43

Using Check Boxes to Control Data in a Chart 45

Using Sparklines to Summarize Multiple Data Series 48

Using GETPIVOTDATA to Create the End-of-Week Sales Report 52

Summary 55

Exercises 55

3 Using Excel Functions to Summarize Marketing Data 59

Summarizing Data with a Histogram 59

Using Statistical Functions to Summarize Marketing Data 64

Summary 79

Exercises 80

II Pricing 83

4 Estimating Demand Curves and Using Solver to Optimize Price 85

Estimating Linear and Power Demand Curves 85

Using the Excel Solver to Optimize Price 90

Pricing Using Subjectively Estimated Demand Curves 96

Using SolverTable to Price Multiple Products 99

Summary 103

Exercises 104

5 Price Bundling 107

Why Bundle? 107

Using Evolutionary Solver to Find Optimal Bundle Prices 111

Summary 119

Exercises 119

6 Nonlinear Pricing 123

Demand Curves and Willingness to Pay 124

Profit Maximizing with Nonlinear Pricing Strategies 125

Summary 131

Exercises 132

7 Price Skimming and Sales 135

Dropping Prices Over Time 135

Why Have Sales? 138

Summary 142

Exercises 142

8 Revenue Management 143

Estimating Demand for the Bates Motel and Segmenting Customers 144

Handling Uncertainty 150

Markdown Pricing 153

Summary 156

Exercises 156

III Forecasting .159

9 Simple Linear Regression and Correlation 161

Simple Linear Regression 161

Using Correlations to Summarize Linear Relationships 170

Summary 174

Exercises 175

10 Using Multiple Regression to Forecast Sales 177

Introducing Multiple Linear Regression 178

Running a Regression with the Data Analysis Add-In 179

Interpreting the Regression Output 182

Using Qualitative Independent Variables in Regression 186

Modeling Interactions and Nonlinearities 192

Testing Validity of Regression Assumptions 195

Multicollinearity 204

Validation of a Regression 207

Summary 209

Exercises 210

11 Forecasting in the Presence of Special Events 213

Building the Basic Model 213

Summary 222

Exercises 222

12 Modeling Trend and Seasonality 225

Using Moving Averages to Smooth Data and Eliminate Seasonality 225

An Additive Model with Trends and Seasonality 228

A Multiplicative Model with Trend and Seasonality 231

Summary 234

Exercises 234

13 Ratio to Moving Average Forecasting Method 235

Using the Ratio to Moving Average Method 235

Applying the Ratio to Moving Average Method to Monthly Data 238

Summary 238

Exercises 239

14 Winter's Method 241

Parameter Definitions for Winter's Method 241

Initializing Winter's Method 243

Estimating the Smoothing Constants 244

Forecasting Future Months 246

Mean Absolute Percentage Error (MAPE) 247

Summary 248

Exercises 248

15 Using Neural Networks to Forecast Sales 249

Regression and Neural Nets 249

Using Neural Networks 250

Using NeuralTools to Predict Sales 253

Using NeuralTools to Forecast Airline Miles 258

Summary 259

Exercises 259

IV What do Customers Want? 261

16 Conjoint Analysis 263

Products, Attributes, and Levels 263

Full Profile Conjoint Analysis 265

Using Evolutionary Solver to Generate Product Profiles 272

Developing a Conjoint Simulator 277

Examining Other Forms of Conjoint Analysis 279

Summary 281

Exercises 281

17 Logistic Regression 285

Why Logistic Regression Is Necessary 286

Logistic Regression Model 289

Maximum Likelihood Estimate of Logistic Regression Model 290

Using StatTools to Estimate and Test Logistic Regression Hypotheses 293

Performing a Logistic Regression with Count Data 298

Summary 300

Exercises 300

18 Discrete Choice Analysis 303

Random Utility Theory 303

Discrete Choice Analysis of Chocolate Preferences 305

Incorporating Price and Brand Equity into Discrete Choice Analysis 309

Dynamic Discrete Choice 315

Independence of Irrelevant Alternatives (IIA) Assumption 316

Discrete Choice and Price Elasticity 317

Summary 318

Exercises 319

19 Calculating Lifetime Customer Value 327

Basic Customer Value Template 328

Measuring Sensitivity Analysis with Two-way Tables 330

An Explicit Formula for the Multiplier r 331

Varying Margins 331

DIRECTV, Customer Value, and Friday Night Lights (FNL) 333

Estimating the Chance a Customer Is Still Active 334

Going Beyond the Basic Customer Lifetime Value Model 335

Summary 336

Exercises 336

20 Using Customer Value to Value a Business 339

A Primer on Valuation 339

Using Customer Value to Value a Business 340

Measuring Sensitivity Analysis with a One-way Table 343

Using Customer Value to Estimate a Firm's Market Value 344

Summary 344

Exercises 345

21 Customer Value, Monte Carlo Simulation, and Marketing Decision Making 347

A Markov Chain Model of Customer Value 347

Using Monte Carlo Simulation to Predict Success of a Marketing Initiative 353

Summary 359

Exercises 360

22 Allocating Marketing Resources between Customer Acquisition and Retention 347

Modeling the Relationship between Spending and Customer Acquisition and Retention 365

Basic Model for Optimizing Retention and Acquisition Spending 368

An Improvement in the Basic Model 371

Summary 373

Exercises 374

VI Market Segmentation 375

23 Cluster Analysis 377

Clustering U.S. Cities 378

Using Conjoint Analysis to Segment a Market 386

Summary 391

Exercises 391

24 Collaborative Filtering 393

User-Based Collaborative Filtering 393

Item-Based Filtering 398

Comparing Item- and User-Based Collaborative Filtering 400

The Netflix Competition 401

Summary 401

Exercises 402

25 Using Classification Trees for Segmentation 403

Introducing Decision Trees 403

Constructing a Decision Tree 404

Pruning Trees and CART 409

Summary 410

Exercises 410

26 Using S Curves to Forecast Sales of a New Product 415

Examining S Curves 415

Fitting the Pearl or Logistic Curve 418

Fitting an S Curve with Seasonality 420

Fitting the Gompertz Curve 422

Pearl Curve versus Gompertz Curve 425

Summary 425

Exercises 425

27 The Bass Diffusion Model 427

Introducing the Bass Model 427

Estimating the Bass Model 428

Using the Bass Model to Forecast New Product Sales 431

Deflating Intentions Data 434

Using the Bass Model to Simulate Sales of a New Product 435

Modifications of the Bass Model 437

Summary 438

Exercises 438

28 Using the Copernican Principle to Predict Duration of Future Sales 439

Using the Copernican Principle 439

Simulating Remaining Life of Product 440

Summary 441

Exercises 441

29 Market Basket Analysis and Lift 445

Computing Lift for Two Products 445

Computing Three-Way Lifts 449

A Data Mining Legend Debunked! 453

Using Lift to Optimize Store Layout 454

Summary 456

Exercises 456

30 RFM Analysis and Optimizing Direct Mail Campaigns 459

RFM Analysis 459

An RFM Success Story 465

Using the Evolutionary Solver to Optimize a Direct Mail Campaign 465

Summary 468

Exercises 468

31 Using the SCAN*PRO Model and Its Variants 471

Introducing the SCAN*PRO Model 471

Modeling Sales of Snickers Bars 472

Forecasting Software Sales 475

Summary 480

Exercises 480

32 Allocating Retail Space and Sales Resources 483

Identifying the Sales to Marketing Effort Relationship 483

Modeling the Marketing Response to Sales Force Effort 484

Optimizing Allocation of Sales Effort 489

Using the Gompertz Curve to Allocate

Supermarket Shelf Space 492

Summary 492

Exercises 493

33 Forecasting Sales from Few Data Points 495

Predicting Movie Revenues 495

Modifying the Model to Improve Forecast Accuracy 498

Using 3 Weeks of Revenue to Forecast Movie Revenues 499

Summary 501

Exercises 501

34...