Beyond What-If: How to Build Analytic and Decision Models in Spreadsheets
summary
Shows concrete workflows in Excel: build a Monte Carlo model (e.g. airline overbooking with Monte Carlo (1,000 trials) sampling) and find the revenue-maximizing overbook level (about 117 tickets optimal versus poor eyeballing); use Excel Solver (Simplex/GRG) and integer/binary variables, demonstrate how IF/LOOKUP breaks convexity and how the tool transforms models into MILP to recover a better solution (example profit jumps from ~$17.5k to ~$20k). Integrates Power Query/PowerPivot (PowerPivot with 2.4M rows), Risk Solver, and shows real wins (customers saving $3M–$6M).
AI-written summary · about
chapters (16)
- 0:00Analytic models in spreadsheets
- 1:28Solver model challenges
- 2:10Results from analytic models
- 9:07Competing on analytics
- 10:50Descriptive predictive and prescriptive analytics
- 17:12From what-if to analytic models
- 24:38Monte Carlo simulation
- 38:10Simulation demo takeaways
- 47:17Optimization for allocation
- 52:15Sensitivity analysis insights
- 1:00:03PowerPivot for data models
- 1:09:07Integer variables for decisions
- 1:16:31IF functions vs solver
- 1:30:12Optimization under uncertainty
- 1:48:15Business value of analytics
- 1:55:11Q&A
related talks
52mSQL Server Predictive Analytics: Customer Stories
01h 00mMastering the CUBE Functions in Excel 2013
01h 03mFrom Data to Insight – Views from Microsoft Finance
01h 01mDark Matter: Domain Experts & Nextgen Spreadsheets Are Our Future
59mSelf-Service Data Modeling
16mEXCELsioR!
01h 02mParallelizing Large Excel-Based Calculations on HPC Server & Azure
18mEmpowering Business Through Data