Helping tech-savvy marketers and data analysts solve real-world business problems with Excel Using datadriven 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, lowcost ways to process and make the most of all that data. This expert book offers the perfect solution. Written by data analysis expert Wayne L. Winston, this practical resource shows you how to tap a simple and costeffective tool, Microsoft Excel, to solve specific business problems using powerful analytic techniquesand achieve optimum results. Practical exercises in each chapter help you apply and reinforce techniques as you learn. * Shows you how to perform sophisticated business analyses using the cost-effective and widely available Microsoft Excel instead of expensive, proprietary analytical tools * Reveals how to target and retain profitable customers and avoid high-risk customers * Helps you forecast sales and improve response rates for marketing campaigns * Explores how to optimize price points for products and services, optimize store layouts, and improve online advertising * Covers social media, viral marketing, and how to exploit both effectively Improve your marketing results with Microsoft Excel and the invaluable techniques and ideas in Marketing Analytics: Data-Driven Techniques with Microsoft Excel.

InhaltsangabeIntroduction 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 2

Leseprobe