ASRM 199. Undergraduate Open Seminar

Fall 2018: Foundations of Data Management

Student Learning Outcomes

  1. Students will learn basic computer programming concepts such as working with variables, if-then statements, and repeatable loops.
  2. Students will apply these programming skills in the programming languages R and VBA. 
  3. Students will be able to build basic financial projection spreadsheets using Excel.
  4. Students will be exposed to a range of Excel functions, which they need to understand and apply.

Course description

This course gives an introduction to the computer programs: Excel, VBA and R.  The purpose is to give freshmen an exposure to these computer programs.  In order to become familiar and confident with using these programs.  This knowledge can then be used in other actuarial courses.

Course topics: 

Excel (20 hours):

Text: Microsoft Excel 2016 - Data Analysis and Business Modelling

Topics chosen out of the following chapters of the book:

  • Chapter 1: Basic Spreadsheet modelling
  • Chapter 2: Range Names
  • Chapter 3: Lookup functions
  • Chapter 4: The INDEX function
  • Chapter 5: The MATCH function
  • Chapter 6: Text functions
  • Chapter 7: Dates and date functions
  • Chapter 8: Evaluating investments by using net present value criteria.
  • Chapter 9: Internal rate of return
  • Chapter 11: Circular references
  • Chapter 12: IF statements
  • Chapter 14: The Paste Special Command
  • Chapter 16: The auditing tool
  • Chapter 18: The Goal Seek command
  • Chapter 20: The COUNTIF, COUNTIFS, COUNT, COUNTA and COUNTBLANK functions
  • Chapter 21: The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS functions
  • Chapter 22: The OFFSET function
  • Chapter 23: The INDIRECT function
  • Chapter 24: Conditional formatting
  • Chapter 29: An introduction to optimization with Excel Solver
  • Chapter 33: Using Solver for capital budgeting
  • Chapter 39: Importing data from a text file or document
  • Chapter 40: Validating data

VBA (15 hours):

Text: An Introduction to Excel VBA Programming with Applications in Finance and Insurance

This part can be given separately or during the Excel lectures.  This gives the students a first introduction to programming.

  1. Introduction to VBA
  2. Excel Objects
  3. Variables, Data Types and Scopes
  4. Operators and Control Structure

R (15 hours):

Text: R programming for Data Science

  1. Introduction to R
  2. R Nuts and Bolts
  3. Sub setting R Objects
  4. Vectorized Operations
  5. Simple Plots in R
  6. Functions