• header Image

Spreadsheet Modelling

Home/ Spreadsheet Modelling
Course Type Course Code No. Of Credits
Foundation Core SBP2MB228 2

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.
Top