Undergraduate Business Analytics Minor Spreadsheet Analytics - PDF document

Spreadsheet Analytics 17 Nov 2013 Undergraduate Business Analytics Minor Spreadsheet Analytics • BANA-2081 Business Analytics I (statistics and data analysis) • BANA-2082 Business Analytics II (optimization/simulation) • BANA-4080 Data Mining and Analysis James R. Evans • BANA-4085 Spreadsheet Analytics Operations, Business Analytics, and • Choose two from the following: – BANA-4090 Forecasting and Risk Analysis Information Systems – BANA-6095 Case Studies in Business Analytics University of Cincinnati – FIN-4041 Financial Modeling – MKTG-3080 Marketing Research BANA 4080: Spreadsheet Analytics Perspective Analytics • Second year; still “ U nder C onstruction”! • Intermediate to advanced Excel concepts with a focus on applications and analytics topics (visualization, optimization, simulation). • Hands-on (laptops required for all courses in the College of Business) • Students have varied backgrounds and knowledge of Excel and analytics Textbooks Software • Winston, Microsoft Excel 2010: Data • Excel for Windows Analysis and Business Modeling , • Analytic Solver Platform (Frontline Microsoft Press, 2011. ISBN 978-0- 7356-4336-9. Systems) • Custom published version of selected chapters from Evans, Business Analytics, Methods, Models and Decisions , Prentice-Hall 2013. 2013-Evans-DSI-MSMESB-Slides.pdf 1

Spreadsheet Analytics 17 Nov 2013 Topics Topics (continued) • Advanced Excel Functions: Text, Date, and Time • Excel Basics Review Functions; LOOKUP, INDEX, MATCH Functions • Range Names, Circular References, Paste Special, • Spreadsheet Modeling Auditing Tool • Developing User-Friendly Applications: Validating Data, • Excel Functions: IF, COUNT-type, Something-IF Excel Controls, Array Formulas • Data Visualization, Conditional Formatting, Sparklines • Data-driven Modeling, Trendlines • Dashboard workshop • Model Analysis Tools: Data Tables, Goal Seek, • Introduction to Excel Macros Scenario Manager • Business Intelligence Tools: Sorting, Tables, Filtering • More about Macros and VBA PivotTables and Slicers • Guest speaker – emphasizes simplicity, communication Topics (continued) Ask the Audience • Review of Optimization Modeling • Questions? • Optimization Model Analysis • Other topics that might be added? • Parameter Analysis in Spreadsheet Optimization • New ideas for pedagogy? • Additional Topics in Spreadsheet Optimization • Workshop on Optimization Results Visualization • Statistical Tools: Analysis Toolpak • Probability Modeling • Monte Carlo Simulation with Excel • Monte-Carlo Simulation with Risk Solver 2013-Evans-DSI-MSMESB-Slides.pdf 2

Spreadsheet Analytics James R. Evans Operations, Business Analytics, and Information Systems University of Cincinnati

Undergraduate Business Analytics Minor • BANA-2081 Business Analytics I (statistics and data analysis) • BANA-2082 Business Analytics II (optimization/simulation) • BANA-4080 Data Mining and Analysis • BANA-4085 Spreadsheet Analytics • Choose two from the following: – BANA-4090 Forecasting and Risk Analysis – BANA-6095 Case Studies in Business Analytics – FIN-4041 Financial Modeling – MKTG-3080 Marketing Research

BANA 4080: Spreadsheet Analytics • Second year; still “ U nder C onstruction”! • Intermediate to advanced Excel concepts with a focus on applications and analytics topics (visualization, optimization, simulation). • Hands-on (laptops required for all courses in the College of Business) • Students have varied backgrounds and knowledge of Excel and analytics

Analytics Perspective

Textbooks • Winston, Microsoft Excel 2010: Data Analysis and Business Modeling , Microsoft Press, 2011. ISBN 978-0- 7356-4336-9. • Custom published version of selected chapters from Evans, Business Analytics, Methods, Models and Decisions , Prentice-Hall 2013.

Software • Excel for Windows • Analytic Solver Platform (Frontline Systems)

Topics • Excel Basics Review • Range Names, Circular References, Paste Special, Auditing Tool • Excel Functions: IF, COUNT-type, Something-IF • Data Visualization, Conditional Formatting, Sparklines • Dashboard workshop • Introduction to Excel Macros • Business Intelligence Tools: Sorting, Tables, Filtering PivotTables and Slicers

Topics (continued) • Advanced Excel Functions: Text, Date, and Time Functions; LOOKUP, INDEX, MATCH Functions • Spreadsheet Modeling • Developing User-Friendly Applications: Validating Data, Excel Controls, Array Formulas • Data-driven Modeling, Trendlines • Model Analysis Tools: Data Tables, Goal Seek, Scenario Manager • More about Macros and VBA • Guest speaker – emphasizes simplicity, communication

Topics (continued) • Review of Optimization Modeling • Optimization Model Analysis • Parameter Analysis in Spreadsheet Optimization • Additional Topics in Spreadsheet Optimization • Workshop on Optimization Results Visualization • Statistical Tools: Analysis Toolpak • Probability Modeling • Monte Carlo Simulation with Excel • Monte-Carlo Simulation with Risk Solver

Ask the Audience • Questions? • Other topics that might be added? • New ideas for pedagogy?