programme

Spreadsheet Modelling

Home/ Spreadsheet Modelling
Course TypeCourse CodeNo. Of Credits
Foundation CoreSBP2MB2282

Semester and Year Offered: 2nd Semester

Course Coordinator and Team: Nidhi Kaicker

Email of course coordinator:nidhi[at]aud[dot]ac[dot]in

Pre-requisites: None

Aim: The objective of the course is to introduce the participants to using spreadsheet for solving managerial problems.

Course Outcomes:

At the end of the course, the participants will be able to:

  1. Acquire basic knowledge of commonly used analytic tools in processing quantitative information and making quantitative business decisions
  2. Recognize and formulate business problems in Excel spreadsheets
  3. Use the techniques from other courses – accounting, statistics, management science, economics – to effectively evaluate and solve excel spreadsheet models
  4. Demonstrate the principles of a good spreadsheet design and effectively present the analysis and results

Brief description of modules/ Main modules:

Unit 1: Basics of Excel and Sensitivity Analysis

Formulae, referencing; data tables, goal seek, scenario building; excel add-ins; basics of macros

Unit 2: Investment Analysis

NPV, IRR; discounting, compounding, annuity functions

Unit 3: Lookup Functions

VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, OFFSET

Unit 4: Database Operations

Sorting, filtering, advanced filtering; conditional formatting; DSUM, DCOUNT, DAVERAGE, DMAX, DMIN; simple and complex queries; pivot tables; SUMIF, COUNTIF, AVERAGEIF; logical operators: AND, OR

Unit 5: Handling String Functions, Date and Time

LEFT, MID, RIGHT, LEN, SUBSTITUTE, REPLACE, FIND, SEARCH, DATE, DAY, MONTH, YEAR, TODAY, WEEKDAY

Unit 6: Arrays and Matrix Functions

Array functions; MMULT, MINVERSE, TRANSPOSE; solver

Unit 6: Statistical Functions

Random variables; statistical distributions – normal, student’s t, probability, permutations and combinations; regressions, LINEST, data analysis toolpack

Assessment Details with weights

Nature of Assessment

Weight

Group Project

30%

Quizzes

30%

End Term Assessment

40%

:

Reading List:

  • Sah, A.N. (2009). Data Analysis Using Microsoft Excel, 1st edition, Excel Books.
  • Walkenbach, J. (2013). Microsoft Excel 2013 Bible, Wiley
  • Whigham, D. (2007). Business Data Analysis Using Excel,1st edition, Oxford University Press.
  • Winston, W.L. (2013). Microsoft Excel 2013: Data Analysis and Business Modelling, PHI: New Delhi.

ADDITIONAL REFERENCE:

  • A set of problems that will be formulated and solved on excel in each class are provided to the participants in form of a course manual as the commencement of the session.