Author: Wayne L. Winston
Publisher: Microsoft Press, 2010
Pages: 720
ISBN: 978-0735643369
Aimed at: Intermediate Excel business users
Rating: 5
Pros: Covers a wide range of topics with sample files and solution files available
Cons: Covers more than any single user is likely to want
Reviewed by: Janet Swift

If you like a practical and active approach to learning about spreadsheet techniques this may be the book you want.

This book isn't aimed at beginners and it isn't a book that you start at page 1 and continue to the end. It is a thick book which prompted me to look into getting the online edition which is free to purchasers of the print copy. The instructions were to locate a voucher inside the back cover and scratch off the metallic foil to reveal a code. But there was no such voucher. Going to the book's detail page, however, quickly solved the problem - it explains that the voucher code is missing from the first printing and gives instructions for obtaining it.

This book adopts a very practical learn-by-doing approach and you'll find a zip file of all the sample files used throughout the book which is again accessed via the book's details page. To get the most out of this book it's a good idea to download it and extract the files. There is a folder per chapter and subfolders for Templates, Practice Files and Solution Files in each of them.

Chapter 1 quickly outlines the new features in Excel 2010 that are helpful to the business analyst and this turns out to be a fairly comprehensive list of the latest enhancements presented first as a bullet point list and then with a short section on each with helpful screenshots where appropriate. In the case of the major new features there's just a brief description and a pointer to more material - the new Excel Solver is covered in Chapter 28-37; Slicers in Pivot Tables in Chapter 43, Sparklines - small charts that occupy a single cell in Chapter 44;  PowerPivot in the book's final chapter, Chapter 84. The new functions, many of which has been introduced for greater accuracy, are also covered in several later chapters.

From Chapter 2, which is on range names, the book settles into its own distinctive format. Each opens with a list of the questions answered in the chapter and then devotes a few pages to discussing the techniques it covers. Then comes the heading Answers to This Chapter's Questions which goes through the earlier list one by one. There are references to sample spreadsheets that hopefully you have already downloaded which make it easy to follow what is being explained. The chapter concludes with a list of problems and solutions to these are included in the downloadable zip file.

Even though the title is Data Analysis and Business Modeling the start of the book covers topics that can be considered more general. What they do have in common is that they are beyond the basics and are useful in a business environment. Each chapter covers a narrow and specific topic. After range names we move on to lookup functions, the Index and Match function and then text and date functions with time functions being covered a bit later on and more advanced functions COUNTIF, SUMIF, OFFSET and INDIRECT cropping up later still. Three chapters are devoted to financial functions, specifically NPV and IRR. Circular references, IF statements and the Paste Special command all have chapters early in the book and then after delving into 3D formulas and the auditing tool we have a set on chapters on Sensitivity Analysis, the Goal Seek command and the Scenario Manager. Sorting and Tables come at the end of what might be thought of as the "general" section of the book.

Next comes a large chunk on Optimization that covers Solver and culminates in solving sequencing problems including the traveling salesman problem.

We arrive at Data Analysis at Chapter 38. This section  start with  importing and validating data then moves on to the use of histograms and descriptive statistics to summarize data. A very long chapter (over 30 pages) is devoted to Pivot Tables and Slicers and then comes Sparklines before we return to summarizing data with database statistical functions. A chapter on filtering data and removing duplicates is slipped in next before consolidating  data and creating subtotals.

Chapters 49 onwards are on statistics. We go from straight line relationships, exponential growth, the power curve and correlations, through multiple regression and ANOVA moving averages and Winter's Method (for smoothing them) to forecasting. There are chapters on random variables and on distributions (including binomial and Poisson and also Weibull and Beta Distributions to model durations and the log normal to model stock prices.

Chapter 69 on Monte Carlo simulation takes us from forecasting to gambling starts a section that covers calculating an optimal bid, simulating stock prices and sporting event probabilities.

Pricing and inventory are covered are in a section that cover topics in economics such as the demand curve and nonlinear pricing and introduces queuing theory as well. The penultimate chapter 83 is on array formulas and functions and a short look at PowerPivot brings this roller-coaster to a halt.

I doubt that any reader will want every single chapter in this book - but having it on the shelf in case you are asked to do something out of the ordinary seems like quite a good idea. It's a treasure trove of advanced but relevant mathematical techniques.