xlf :: 0 Spreadsheet modeling

Outline

Topics covered in this preliminary module:

  1. Preliminary concepts
    • Spreadsheeting – directions, expectations, paths, and outcomes
    • Spreadsheets
    • Microsoft Office – Excel (Windows). Programming language – Visual Basic for Applications (VBA)
    • Excel Online. Programming language – JavaScript API for Office
    • LibreOffice – Calc. Programming language – LibreOffice Basic
    • Google docs – sheets. Programming language – Google Apps Script (GAS), based on JavaScript

  2. Spreadsheets on Collins St / Pitt St / Wall St / Century Ave – Pudong / Singapore / …
    • Baker, Powell, Lawson and Foster-Johnson (2009) – who use a questionnaire to examine the development and use of spreadsheets in organizations
            Fig 2. Group B – spreadsheet purpose: Analyzing data 90%, evaluating alternatives 71%, and determining trends and making projections 69%
            Fig 3. Relative frequency of use of selected Excel features: IF function, LOOKUP functions, …
    • McDaid, MacRuairi, Clynch, Logue and Hayes (2011) – who examine the content, structure and usage of spreadsheets using the a proprietary technology (luminous)
            Table x. Distribution of percentage of rows, columns and sheets
            Table y. Distribution of occurrences of functions
    • Excel versions – 2016 – Office 365 subscription – home, personal, university, pro plus. Office – home and student, home and business, and professional plus
    • Data, analysis, and presentation

  3. Dates and dollars
    • 90045 uses Windows Control Panel > Region and Language > Australia
    • Dates – dd mmm yyyy
    • Rule of 2029 – applies to yy date entries
    • Epoch – Excel windows – 0 January 1900 (treated as a leap year), serial dates must be positive. Note: Excel Mac uses the 1904 date system
    • Epoch – Google sheets – 31 December 1899, serial dates can be negative
    • Dollars – floating point numbers with a decimal separator (period). Format – currency or accounting with a thousands separator (comma). Dollar symbol.
    • The time value of money – timing and magnitude of cash flows

  4. Finding your way
    • The get cell formula GetCF function
    • Compare GetCF to the FORMULATEXT function available in Excel 2013
    • Workbook security – macro enabled workbooks and the #NAME? error
    • Keyboards, and the mouse

The module workbook is restricted access, and is available to session participants only.

Revised:       Last modified: