Spreadsheet Analysis
Spring, 2013

Check here for any important course announcements!!

Day/Time:  Tuesday, 1:00-4:00
Credits: 3
Prerequisites BUSN 206, or placement on Technology Assessment exam, or permission of instructor
Instructor: Dr. Channah F. Naiman
email: naiman@htc.edu  Please be sure to include the course# in the subject line!!
Office hours: By appointment, generally before or after class.
Texts: Excel for marketing Managers, by Ivana Taylor and Bill Jelen.
ISBN:  9781932802139
Publisher:  Holy Macro, 2006

VBA for Modelers, by S. Christian Albright.
ISBN:  9781133190875
Publisher:  Southwestern Cengage Learning

Additional course Materials:  We will also be referencing several online sites, as well as using some videos in class (and some available videos free online!)

Course Description (from catalog):  Development of an advanced level of proficiency in the use of spreadsheet applications to turn data into information; to analyze, visualize, communicate, and share results toward solving business problems. Analyzing business cases using spreadsheet software.  Spreadsheet automation using visual programming.

Course Goal and Emphasis (and what this course is NOT):   This course is not about learning a set of Excel functions.  It is not about learning which mouse clicks you need to build a Pivot Table.  It is not about learning a step-by-step methodology that you can routinely apply to data analysis.  This course is about trying to understand the information requirements of a business user, and figuring out how to use Excel to process that information out of available raw data. It is about looking at the toolset that Excel has to offer and applying it in both standard and creative ways to a variety of both “clean” and “dirty” data sets, regardless of the source. It is about finding the appropriate resources to search for a solution when the textbook doesn’t have one.  You will be required to go beyond the textbooks. You will be required to learn and present something beyond what we learn in class. A primary goal of this course is to learn the parameters of what Excel can do and to learn how to find a solution when you need one.  A second goal of this course is to learn the basics of programming, using the VBA language.

Course Objectives: Upon completion of the course, the students should have a good understanding of the issues  presented in analyzing data, and be competent in:
Course Structure:  The first half of the course focuses on acquiring data from various sources and "cleansing" or "scrubbing" it.  The second half of the course focuses on developing macros, especially using VBA, as well as work on a class project drawn from a real project.

Course Components
Attendance/Participation 10%
Lab/In-class/Homework Assignments/Quizzes 30%
Topic Presentation(s) 10%
Project 20%
Exams 30%

Course Schedule
This schedule is a guide. After each class, I will re-evaluate how much material was covered. Updates will be posted on a weekly basis, depending on what was covered in class. This will be announced in class. Please check this syllabus well in advance of every class.

The Course Schedule is accurate only for the current week. It is fairly accurate until the midterm exam.  After that, the schedule will depend on how quickly we can master macros and basic programming skills.  Topics will be changed as the semester progresses.  
Topic Text/Materials Assignments/Files
Introduction to Course
Syllabus Review
What Excel can do:
  • Demo:  project files
  • Demo:  form controls
  • Demo:  file access (VBA)
  • Demo:  form design (VBA)
Finding sources for Excel
Prerequisite knowledge:

MM, selected pages (listed below per topic)

Mr. Excel.com
The Smart Method

In-class assignment:
---Baseline Project:  saving for something special
Baseline Project Solution (if you are stuck)

---Headings and footers pp. 10-11

---copying worksheets p. 12

---customized formats pp. 14-16

---quicksum pp. 17-24 file:  File-8-quicksum-2010


---special autofills p. 27

---see formulas (w and w/o goto) pp. 40-41

---arrays (CSE formulas) p. 44 file:  File-37-CSE

---advanced filters pp. 56-62 file:  23-Autofilter-2010

---conditional formatting pp. 66-68 file:  16-conditional-formatting-2010
 video: formula-driven conditional formatting
file:  SalesSummaryFirstQuarter2008

---VLOOKUP/HLOOKUP pp. 80-86 file:  looking-up-data-2010
file:  Cust-Name-and-rep-number-no-repname-2010-st
file:  rep-number-and-name-2010
file:  rep-number-no-repname-no-CustName-2010-st

---Text()  and Value()

Fixing bad data p. 86 file:  34-Bad-Data-2010

Match Function p. 87 file:  LookingUpData-2010
     sheet:  IndexOriginal

we will revisit index/match several more times
  • reviewing string functions
  • more VLOOKUP
  • discuss data collection assignment

DUE:  First pass at String Assignment
file:  Cleaning Text
file:  compare-2-lists
file: instructions for comparing 2 lists

continuing Strings

  • Logical functions
  • advanced conditional formatting
class notes file:  compare-2-lists
file: instructions for comparing 2 lists

Logic, continued
cond. format, cont'd

free tutorial  for conditional  formatting
microsoft's tutorial
Create Student Data raw data

Custom Sorts p. 90 file:  custom sorts

Dates:  formatting, arithmetic pp. 93-96 file:  4-Dates-2010

joining text pp. 96-99 file:  28-CalculatingText-2010
    sheet:  JoinOriginal

Transpose pp. 99-100 file:  transpose-2010

counting records pp.101-102 file:  24-CountIf-2010

combine Sum and If
--array formulas revisited
pp. 102-103

Text functions revisited pp. 103-104 file:  26-Textfunctions-2010

Data Cleaning
In-class assignment:  Student Data
catch-up, review

Midterm Exam (hands on)

What is VBA? Albright, Ch.1

Excel Object Model Albright, Ch. 2

Object-oriented programming

VBE Albright, Ch. 3 file:  first program
file:  first program finished

Recording macros Albright, Ch. 4 file:  recording macros
file:  recording macros finished

playing with macros
various short assignments
Getting Started VBA Albright, Ch. 5

(Intro Programming)


Control Structures Albright, Ch. 7

Forms Albright, Ch. 11