Introduction to MS-Excel and Computerized Accounting Systems

Basics of Microsoft MS-Excel

• A spreadsheet (or worksheet) is a configuration of horizontal vectors (Rows) and vertical vectors (Columns) used to record and process numerical data.
• MS-Excel 2007 Dimensions: Features a total of $65,536$ Rows and $256$ Columns (labeled $A$ to $IV$), resulting in approximately $1,65,00,000$ cells per sheet.
• Workbook vs. Worksheet: A file in Excel is a "Workbook," which is a collection of "Worksheets." By default, Excel 2007 provides three sheets (Sheet 1, Sheet 2, Sheet 3).
• User Interface: The version uses a series of horizontal tabs known as the "Ribbon" and includes an "Office Button" for file operations like Save, Open, and Print.
• Values:
  • Basic Values: Independent values entered manually.
  • Derived Values: Outcomes of arithmetic expressions or functions (e.g., in $V = Q \times P$, $V$ is the derived value).

Cell Referencing and Navigation

• Active Cell: The cell currently pointed to by the cursor (default first cell is $A1$).
• Cell Address: A unique identification combining a column letter and row number (e.g., $G8$).
• Reference Types:
  • Relative: Changes when copied (e.g., $A1$).
  • Absolute: Remains fixed using dollar signs (e.g., $\$C\$4$).
  • Mixed: One component remains constant (e.g., $\$C4$ or $C\$4$).
• Navigation Shortcuts:
  • Top of worksheet: $CTRL + HOME$
  • End of data range: $CTRL + END$
  • Next cluster border: $CTRL + \text{Arrow Keys}$

Mathematical and Statistical Functions

• Order of Operations: Follows algebraic rules often remembered by acronyms GEMS, PEMDAS, or BEMDAS (Grouping/Parentheses, Exponents, Multiplication/Division, Addition/Subtraction).
• Common Functions:
  • SUM, AVERAGE, COUNT: Apply to numerical ranges.
  • SUMIF: Adds cells based on a specific criterion (Syntax: \text{SUMIF(range, criteria, sum_range)}).
  • ROUND: Rounds numbers to specified digits (\text{ROUND(number, num_digits)}). Variations include ROUNDUP and ROUNDDOWN.
  • COUNTIF: Counts cells that meet user-defined criteria.

Logical and Lookup Functions

• IF Function: Returns values based on whether a condition is $TRUE$ or $FALSE$.
  • Syntax: \text{IF(logical_test, value_if_true, value_if_false)}.
  • Nested IF: Used for multiple conditions (e.g., grading systems).
• Logical Operators: Include $=$, $<$, $>$, $<=$, $>=$ and $<>$ (not equal to).
• AND/OR Functions: Return logic values based on multiple conditions.
• Lookup Functions:
  • VLOOKUP: Vertical search in the first column of a table to return a value from the same row.
  • HLOOKUP: Horizontal search in the first row of a table to return a value from the same column.

Financial Functions

• ACCRINT: Returns accrued interest for a security.
• PV (Present Value): Calculates current value of future payments.
  • Formula: $\text{(pmt} \times \text{nper) + pv + fv = 0}$ (if rate is $0$).
• FV (Future Value): Calculates the future value of an investment.
• PMT: Calculates periodic payments for an annuity (e.g., mortgage loans).
• NPV (Net Present Value): Calculates investment value using a discount rate and future cash flows.
  • Formula: $NPV = \sum_{i=1}^n \frac{\text{values}_i}{(1 + \text{rate})^i}$

Data Management and Reports

• Data Validation: Defines restrictions on data entry (e.g., drop-down lists, numeric limits, or preventing duplicate entries).
• Data Form: A dialog box for adding, locating, or deleting records one at a time.
• Conditional Formatting: Visualizes data trends using data bars, color scales ($2$-color or $3$-color), and icon sets.
• Pivot Table: An interactive tool to summarize, analyze, and aggregate large amounts of data. It allows "pivoting" (moving rows to columns) to see different data summaries.
• Data Tables: Used for what-if analysis; includes one-variable and two-variable tables.

Common Error Messages

• #####: Column width too narrow or negative date/time.
• #DIV/0!: Division by zero.
• #N/A: Value not available to a formula.
• #NAME?: Excel does not recognize text or defined names.
• #NULL!: Incorrect range operator used ($\text{space}$ vs. $\text{comma}$).
• #NUM!: Invalid numeric values in a function.
• #REF!: Invalid cell reference (often due to deleted cells).
• #VALUE!: Wrong type of argument or operand used.

Questions & Discussion

• Q1. Which view shows margins and the rulers?
  • A: Page Layout.
• Q2. As you type a number in a cell, what mode appears in the status bar?
  • A: Enter mode.
• Q3. Which command reverses the last action performed?
  • A: Undo.
• Q4. Which mathematical operator is represented by an asterisk (*)?
  • A: Multiplication.
• Q5. How many blank worksheets are shown when a new workbook is created?
  • A: Three.