This course is designed to enable students understand this powerful tool to manipulate huge amounts of data, automate tasks and present complex information in a professional manner. This course will create more job opportunities for you and you will be a valuable candidate for your employers for your great analyzing skills.
At the end of the course, students should be able to:
Unit I (3 weeks)
Excel Advanced Techniques:
Templates, Efficiency and Risk, Data Validation; Functions and Super Powers, Array Formulae, Tables, Advanced Range Names, What If Analysis, Problem Solving using Solver
References:
Chandan Sengupta, Financial Analysis and Modeling Using Excel and VBA [Part 1, Chapter 4-8]
Wayne Winston, MS Excel 2016, Data Analysis & Business Modelling [Chapter 29-35, 40, 88]
Unit II (3 weeks)
Excel Interactivity and Automation:
Index and Match, Offset, Dynamic Charting, Database functions, Text functions and Error functions: IfError, IsError, Aggregate, Circular Reference, Formula Auditing, Floating Point Errors, Form Controls, Visual Basic and Macros, Automating other applications from Excel
References:
Wayne Winston, MS Excel 2016, Data Analysis & Business Modelling [Chapter 4-6, 10-23, 89]
Unit III (3weeks)
Introduction to VBA:
Conditional Formatting, Charts that Inspire, Slicers, Sparklines, Graphics Tricks and Techniques, Worksheet Automation using Macros: Absolute and relative macros, Editing macros, Creating new functions, Use of spinner buttons and command buttons.
References:
Alexander Michael, Kusleika Dick , Excel 2016 Power Programming with VBA[Part I, Chapter 5
Wayne Winston, MS Excel 2016, Data Analysis & Business Modelling [Chapter 24, 27, 47- 52]
Unit IV (3 weeks)
Data Analysis and Decision-Making:
Working with External Data, Advanced Uses of PivotTables, PowerPivot, Reporting with PowerPivot, Dashboard, Creating spreadsheet in the area of: Loan and Lease statement; Ratio Analysis; Payroll Accounting; Capital Budgeting, Portfolio Management, Breakeven analysis and Sensitivity analysis; Operations Management: Constraint optimization, Assignment Problems; Depreciation Accounting; Graphical representation of data; Frequency distribution and its statistical parameters; Correlation and Regression Analysis
References:
Alexander Michael, Kusleika Dick , Excel 2016 Power Programming with VBA[Part I, Chapter 5,7, Part II: Chapter 8-12]
Wayne Winston, MS Excel 2016, Data Analysis & Business Modelling [Chapter 53-59]
Disclaimer: Details on this page are subject to change as per University of Delhi guidelines. For latest update in this regard please refer to the University of Delhi website here.