Computer Data Analysis
CGS 2518
Spring Semester 2010
All documents are in MS Word '97 format.
Students should download, print, and bring copies of all documents for the
current tutorial to class!
Table of Contents
- Student Data Files
-
To download a zip file containing all the data files for the course, right-click on the link below and choose "Save Target As..." or "Save Link As..." (depending on what browser you are using)
This is a "self-extracting" zip file. After saving it to your computer or flash drive,
simply double-click on the file icon to extract the individual data files
Data files for the Excel Tutorials, Review
Assignments, and Case Problems (3.24 MB)
- Download Microsoft Office Professional Plus 2007
-
Microsoft Office Professional Plus 2007 is available from FIU University Technology
Services (UTS) by download for $85 - includes Excel, Access, Word, and PowerPoint
Visit the UTS Online Store
Prerequisite: The Windows XP Operating System
- Basic Mouse Skills
- Clicking, double-clicking, right-clicking, dragging, and right-dragging
- Creating a Folder
- How to create a folder on the Desktop or in an existing folder
- Windows File Management
- Selecting, copying, moving, deleting, and renaming files and folders
- Saving a File
- "Save" vs. "Save As...", specifying destination drive and folder,
file name, and file type
Microsoft Excel 2007
Tutorial 1 - Getting Started with Excel
- Skills Checklist and Notes - Session 1.1
- Entering Data
- Entering labels, values, and formulas into a worksheet
- Skills Checklist and Notes - Session 1.2
- Selecting Cell Ranges
- How to select any number of cells, ranges, rows, and columns
- Excel Arithmetic
- Arithmetic Operators and Operator Precedence
- Excel Editing
- Erasing and replacing cell contents, modifying cell contents,
and clearing cell contents, formats, and comments
- Assignment #1 - Getting Started with Excel
- Altac.xlsx - Workbook file to be used
(Note - due to a bug in Internet Explorer, you must use
Firefox to download this file!)
- Class Standards for Formula
Printouts
- Read this before handing in any assignments!
Tutorial 2 - Formatting a Workbook
- Skills Checklist and Notes - Session 2.1
- Skills Checklist and Notes - Session 2.2
- Fonts and Text Formatting
- Fonts, font sizes, and font styles
- Assignment #2 - Formatting a Workbook
- Frosti.xlsx - Workbook file to be used
(Remember - due to a bug in Internet Explorer, you must use
Firefox to download this file!)
Tutorial 3 - Working with Formulas and Functions
- Skills Checklist and Notes - Session 3.1
- Relative vs. Absolute Cell Referencing
- Skills Checklist and Notes - Session 3.2
- The IF Function
- The IF function will display one of two possible values in a cell,
depending on whether a condition is true or false
- Assignment #3 - Working with Formulas and Functions
- Sonic.xls - Workbook file to be used
Tutorial 4 - Working with Charts and Graphics
- Skills Checklist and Notes - Sesson 4.1
- Column Charts
- All about column charts (and bar charts)!
- Skills Checklist and Notes - Sesson 4.2
- Working with Graphic Shapes
- Creating, moving, resizing, formatting, aligning, copying, grouping, and
ungrouping graphic shapes
- Assignment #4 - Working with Charts and
Graphics
- Kenai.xlsx - Workbook file to be used with the
assignment
(Remember - due to a bug in Internet Explorer, you must use
Firefox to download this file!)
- Charts with Multiple Data Series
- Vega.xlsx - (Sample Workbook)
Tutorial 5 - Working with Excel Tables, PivotTables, and PivotCharts
- Skills Checklist and Notes - Session 5.1
- Skills Checklist and Notes - Sesson 5.2
- Generating Automatic Subtotals
- Excel makes it easy to generate subtotals for each group of matching values in a specified field,
known as the "control field"
- Skills Checklist and Notes - Session 5.3
- Assignment #5 - Working with Excel Tables, PivotTables, and PivotCharts
- Pledges.xlsx - Workbook file to be used
Tutorial 6 - Managing Multiple Worksheets and Workbooks
- Skills Checklist and Notes - Session 6.1
- Skills Checklist and Notes - Session 6.2
- Skills Checklist and Notes - Session 6.3
- Assignment #6 - Managing Multiple Worksheets and Workbooks
- Europe.xlsx | North America.xlsx |
South America.xlsx - Workbook files to be used
- Workbooks to be used for reference:
- New Mexico.xlsx |
Utah.xlsx | Colorado.xlsx |
TravelTotals.xlsx
- In each of the state workbooks, the Summary sheet has worksheet references and "3-D" worksheet references
to access the data on the Quarter 1 through Quarter 4 sheets for each state
- The TravelTotals book features "external" (i.e., workbook) references to access the data on the Summary sheets of
the 3 state books
Tutorial 7 - Using Advanced Functions, Conditional Formatting, and Filtering
- Skills Checklist and Notes - Session 7.1
- The VLOOKUP Function
- The VLOOKUP function searches a table for a particular value, retrieves a
related value from the table, and stores it in the cell containing the function
- Skills Checklist and Notes - Session 7.2
- The Conditional Functions - COUNTIF, SUMIF, and AVERAGEIF
- These functions are similar to COUNT, SUM, and AVERAGE, but operate only on
cells that meet specified criteria. The criteria are passed as arguments to the functions
- Skills Checklist and Notes - Session 7.3
- Using Criteria Ranges for Advanced Filtering
- Database Functions
- Database functions DCOUNT, DSUM, DAVERAGE, etc, are similar to COUNT, SUM,
AVERAGE, etc, but are used only with database tables where a filter is applied via a
criteria range
- Assignment #7 - Using Advanced Functions
and Filtering
- Leave.xlsx - Workbook file to be used
Tutorial 8 - Developing an Excel Application
- Skills Checklist and Notes - Session 8.1
- Naming Cells and Ranges
- How to assign descriptive names to cells and cell ranges, and the advantages
of using names
- Skills Checklist and Notes - Session 8.2
- Skills Checklist and Notes - Session 8.3
- Assignment #8 - Developing an Excel Application
- Popcorn.xlsx - Workbook file to be used
Tutorial 9 - Developing a Financial Analysis
- Financial Functions PMT and FV
- The PMT function computes the "equal payment amount" to be paid each period for a fixed-rate loan such as an auto loan or fixed-rate mortgage. The FV function computes the value of an annuity (such as a 401(k)) after a specified number of payments have been made, given a constant rate of interest and regular payments of a fixed amount
- Sample Workbooks for PMT Function
- PaymentCalculator.xls |
MortgageComparator.xls
- Sample Workbook for FV Function
- AnnuityCalculator.xls
- Dates and Date Functions
- Dates are stored as "serial numbers" (aka: integers or "whole
numbers"). This is what enables us to do computations using dates. Useful functions
for creating and manipulating dates include DATE, YEAR, MONTH, and DAY
- Amortization Tables
- Also known as a "loan payment schedule," each line of an amortization table
shows the payment number, date, beginning balance, payment amount, amount of payment
applied to interest, amount applied to reduce the principal, and the ending balance
- Assignment #9 - Creating an
Amortization Table
- Amortization.xlsx - Workbook file to be used
Tutorial 10 - Performing "What If" Analyses
- Skills Checklist and Notes - Session 10.1
- Goal Seek
- Like a what-if analysis in reverse, Goal Seek lets us specify the result we want to see in a cell, and then finds the value needed in an input cell to achieve it
- One-Variable Data Tables
- A one-variable data table enables us to perform a series of what-if analyses simultaneously, using a range of new values for the input cell
- Two-Variable Data Tables
- A two-variable data table enables us to perform a series of what-if analyses simultaneously, using various combinations of values for two input cells
- Skills Checklist and Notes - Session 10.2
- Using Scenario Manager to Analyze Data
- A scenario is a more sophisticated what-if analysis involving a set of values for multiple input cells. The Scenario Manager lets us create and summarize any number of scenarios
- Skills Checklist and Notes - Session 10.3
- Using Solver to Find an Optimal Solution
- Solver is like a high-powered version of Goal Seek. Solver finds a solution to a problem - maximize profits, minimize shipping costs, etc - by trying multiple values for multiple input cells. Unlike Goal Seek, Solver allows us to place data validation constraints on the input values used in order to avoid meaningless results
- Assignment #10 - Developing an Excel Application
- Creative Ventures.xlsx - Workbook file to be used for reference
Top
Home