Helping tech-savvy marketers and data analysts solve real-world business problems with Excel Using data-driven business analytics to understand customers and improve results is a great idea in theory, but in today's busy offices, marketers and analysts need simple, low-cost ways to process and make the most of all that data. Helping tech-savvy marketers and data analysts solve real-world business problems with Excel Using data-driven business analytics to understand customers and improve results is a great idea in theory, but in today's busy offices, marketers and analysts need simple, low-cost ways to process and make the most of all that data. 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 V Customer Value 325 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 VII Forecasting New Product Sales  413 26 Using S Curves to Forecast Sales of a New Product  415 Examining S Curves  415 Fitting the Pearl or Logistic Curve418 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 VIII Retailing 443 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 IX Advertising 503 34 Measuring the Effectiveness of Advertising 505 The Adstock Model  505 Another Model for Estimating Ad Effectiveness 509 Optimizing Advertising: Pulsing versus Continuous Spending 511 Summary 514 Exercises  515 35 Media Selection Models   517 A Linear Media Allocation Model 517 Quantity Discounts 520 A Monte Carlo Media Allocation Simulation 522 Summary 527 Exercises  527 36 Pay per Click (PPC) Online Advertising 529 Defi ning Pay per Click Advertising 529 Profi tability Model for PPC Advertising   531 Google AdWords Auction  533 Using Bid Simulator to Optimize Your Bid 536 Summary 537 Exercises  537 X Marketing Research Tools    539 37 Principal Components Analysis (PCA)  541 Defining PCA 541 Linear Combinations, Variances, and Covariances   542 Diving into Principal Components Analysis   548 Other Applications of PCA  556 Summary 557 Exercises  558 38 Multidimensional Scaling (MDS) 559 Similarity Data559 MDS Analysis of U.S. City Distances   560 MDS Analysis of Breakfast Foods    566 Finding a Consumer’s Ideal Point 570 Summary 574 Exercises  574 39 Classification Algorithms: Naive Bayes Classifier and Discriminant Analysis 577 Conditional Probability 578 Bayes’ Theorem 579 Naive Bayes Classifier    581 Linear Discriminant Analysis    586 Model Validation    591 The Surprising Virtues of Naive Bayes 592 Summary 592 Exercises  593 40 Analysis of Variance: One-way ANOVA 595 Testing Whether Group Means Are Different 595 Example of One-way ANOVA 596 The Role of Variance in ANOVA    598 Forecasting with One-way ANOVA 599 Contrasts 601 Summary 603 Exercises  604 41 Analysis of Variance: Two-way ANOVA 607 Introducing Two-way ANOVA 607 Two-way ANOVA without Replication 608 Two-way ANOVA with Replication 611 Summary 616 Exercises  617 XI Internet and Social Marketing 619 42 Networks 621 Measuring the Importance of a Node 621 Measuring the Importance of a Link   626 Summarizing Network Structure628 Random and Regular Networks    631 The Rich Get Richer  634 Klout Score636 Summary 637 Exercises  638 43 The Mathematics Behind The Tipping Point 641 Network Contagion  641 A Bass Version of the Tipping Point   646 Summary 650 Exercises  650 44 Viral Marketing 653 Watts’ Model 654 A More Complex Viral Marketing Model 655 Summary 660 Exercises  661 45 Text Mining 663 Text Mining Definitions 664 Giving Structure to Unstructured Text   664 Applying Text Mining in Real Life Scenarios 668 Summary 671 Exercises  671 Index 673