Spreadsheet Applications in Business

Introduction to Computers Part III: Excel

• Excel is a spreadsheet application within the Microsoft Office suite
• Other applications within the Microsoft Office suite include Word (word processor), Access (database management), PowerPoint (presentation software), Outlook, and Publisher.

Spreadsheets and their Uses

• Spreadsheets are electronic equivalents of accounting worksheets
• Primarily used for storing, manipulating, and comparing data
• Used in research, industry, and business for planning, forecasting, and more
• Applications in accounting include budget preparation, balance sheets, trial balances, and cash flow analyses
• Used for managing inventory, payroll, and other financial tasks
• Useful in scientific fields such as mathematics, engineering, physics, chemistry, meteorology, statistics, and biology, enabling statistical computations like averages, standard deviations, variance, and R-squared, regression coefficients, ANOVA analysis, simultaneous equation solutions, and frequency distribution tables
• Used for graphical representations like pie charts, histograms, and line graphs
• Employed by financial institutions like KNUST, CSIR, SSNIT, and the Bank of Ghana, for financial and non-financial operations
• Also used for forecasting, investment proposals, other tasks, and currency conversions
• Used in database management, for data maintenance, sorting data, extracting information, pay vouchers, and more
• Valuable tool for "what if" scenarios in business, particularly for designing storm drains and other analyses based on changing variables in financial statements or budgets
• Spreadsheet programs include AS-EASY-AS, SUPERCALC, LOTUS 1-2-3, SYMPHONY, QUATROPRO, FRAMEWORK IV, and EXCEL to name a few

Spreadsheets - Overview of Spreadsheet Programs

• A spreadsheet is composed of cells, rows, and columns
• Cell references (e.g., B10) identify cells based on their row and column location

Starting and Quitting Excel

• Steps to start Excel: Click the Start button, navigate to the program, and select Excel
• Another way to start Excel: Double-click the shortcut icon on the desktop

Starting and Quitting Excel

• Steps to close Excel: Confirm that work is saved, click the close button on the title bar

Cells

• To refer to a specific cell, use the row and column headings.
• Identifying and inputting data to a spreadsheet cell
• Data entry options (text, numbers, formulas and functions)

Working in a Spreadsheet

• Three types of data, text data, numbers, and formulas
• Keying data into a cell: selecting a cell and typing the info, then pressing ENTER
• To replace data, select the cell and type the new data
• Data limits (255 characters in a cell)

Entering Text and Numbers

• Inputting numerical data and text data into spreadsheet cells
• Numbers in text format cannot be used in computations
• Numerical data that exceeds cell width may display in scientific notation

Entering Dates and Times

• Excel recognizes and converts date and time entries into serial numbers
• A date corresponds to the number of days since 1/1/1900
• Dates appear in m/d/yyyy format in the formula bar regardless of cell formatting

Cutting and Pasting Data

• Steps to copy data: Click the cell, select Home ->Copy, or Ctrl+C
• Steps to paste data: Click the cell, select Home ->Paste, or Ctrl+V
• How to select a range of cells: left-click and drag the mouse

Adding Rows and Columns

• Steps to insert a row or column: Select the row or column, select the Home tab Insert -> Cells

Deleting Rows and Columns

• Steps to delete a row or column: Select the row or column, and select the Home tab -> Delete -> Cells

Assignment 1

• Auto-complete
• Auto-fill
• Fill series

Finding the Right Size

• Expanding column width
• Expanding row height

Inserting a Chart

• Inserting chart using the data in the worksheet

Formulas

• Formulas are the core of the Excel worksheet
• A formula may be any data that does not place itself in a cell but generates the result
• Formulas can perform simple calculations or more complex financial, statistical and scientific ones
• Formulas start with an equal (=) sign and include constants, operators, functions, parentheses, cell references
• Formulas automatically recalculate values when cell entries change.

Cell Referencing

• Cell references, or addresses, in formulas refer to contents of cells or groups of cells
• Allows use of values from different parts of a worksheet
• Types of referencing (absolute, relative, and mixed)

Relative, Absolute, and Mixed References

• Relative referencing adjusts when copied
• Absolute referencing remains constant when copied
• Mixed referencing adjusts either column or row while keeping the other fixed.

3-D References

• Allows referencing cells across multiple worksheets

Operators in Formulas

• Arithmetic operators (e.g., +, −, *, /, %, ^)
• Comparison operators (e.g., =, >, <, >=, <=, <>)

Assignment

• Rules governing the evaluation of operators in Excel
• Naming cells and ranges
• Cell referencing

Functions and Arguments

• Excel has built-in functions for calculations
• Functions consist of a function name and arguments contained in parentheses
• Separate arguments with commas
• Some functions may accept variable numbers of arguments
• Using the Insert Function button can simplify entering functions

Logical Functions

• Used to test conditions for TRUE or FALSE
• Return a result based on the logical test
• TRUE equals 1, FALSE equals 0

The FALSE Function

• Returns a logical FALSE value
• No arguments, formatted as FALSE()

The TRUE Function

• Returns a logical TRUE value
• No arguments, formatted as TRUE()

The NOT Function

• Reverses a logical argument (TRUE to FALSE and vice-versa)

The AND Function

• Returns TRUE if all logical arguments are TRUE, otherwise FALSE.

The OR Function

• Returns TRUE if at least one logical argument is TRUE, otherwise FALSE.

The IF Function

• Allows testing logical conditions and generating specific values depending on the result of the test. (logical test, value-if-true, value-if-false)

Exercise 1 (Employee Pay)

• Employees are paid hourly, with overtime for more than 40 hours
• Regular pay: 50 cedis per hour
• Overtime pay: 1.5 times the regular rate per hour
• Deductions: Income Tax (15%), NHIL (2.5%), District Tax (1%)

Exercise 2 (Student Grades)

• Marking criteria for mid semester and end of semester grades has changed, from the old 30/70 to 100/100

Printing Spreadsheet Data

• Click File -> Print on Excel to print the worksheet

Sorting

• Sorting arranges data numerically or alphabetically
• Each row in a worksheet describes a record
• Columns describe fields

Sorting Data based on more than one Column

• Sorting based on multiple columns: Data is first sorted according to the first column, then the second and on
• Additional sorting conditions depend on the data

Filtering

• Filtering displays specific rows based on specified criteria
• Using AutoFilter (selecting filtering criteria)