BUSN/CAIS 306 Syllabus--Spring, 2012

BUSN/CAIS 306
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:

Importing data from a variety of sources into an Excel spreadsheet, including text-to-columns, web imports, and more.
Consolidating data from different workbooks into one workbook.
Basic "data scrubbing" techniques, to reformat and validate data in preparation for analysis, including TEXT, DATE and various string functions (among others).
Advanced functions, such as index/match, (possibly indirect/offset), more advanced uses of VLOOKUP and HLOOKUP across workbooks and worksheets.
Form controls, time permitting
And introduction to macros and VBA, specifically as applied to importing and formatting data, time permitting.
Identifying, finding and using various videos, blogs and Excel support sites online.

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

Lecture: The course is problem and project driven. Lectures will include demonstrations of various data analysis skills, videos, sample projects and actual projects. We will often work through an example of a problem from one of the texts, followed by a hands-on, in-class assignment applying those skills.
Texts: We are using two texts. The first (Marketing Managers, or “MM”) is actually older than Excel 2007. It is written by “Mr. Excel”, Bill Jelen. The data manipulation techniques are still valid, and written in a manner that is both concise and easy to understand. There are a few modifications for Excel 2007/2010. I will tell you what they are when we encounter them; however, it would be much better if you would attempt them and discover the updates for yourselves. The word “text” is a misnomer. This is not a textbook. It is a professional reference book by one of the leaders in the field. The second text is a textbook on VBA. You will also use this book as a reference for your project.
Data Files: Both books come with data files. The data files don’t always seem to match the figures in the books. This is a good thing—it gives you more work to do in preparing the data. We will also use data files from several projects. These have unmatched data, referred to as “dirty” data. Your job will be to “cleanse” or “scrub” this data until it is ready to be used in analysis. Then your job will be to analyze the data.
Lab: The lab is a mixed bag of computers. Some work better than others. Some have 2007 installed, most have 2010 installed. Expect that some won’t work from time to time. Do not store anything, ever on a local computer. If you store something on a local computer, I will not give you any extra time to redo the assignment. Store everything on the server, in the Shared Student Files directory, as instructed in class. In addition, store everything on a flash drive. I strongly recommend that you buy two flash drives. You can also store your data on Google Drive. Backup everything!! Backup your backups!! In real life, no one counts on a lab computer to be stable or always working. No one counts on someone else to safeguard their data. You alone are responsible for your data. No excuses.
Attendance: You are required to attend all classes. If you do not attend a class, you will not get full credit for the lab work that was completed during that class session. Yet, you still have to make up the work if you are going to know what’s going on in the next class. Yes, life can be tough. If I find you surfing the web or sending emails during class, I will deduct points from your attendance/participation grade. If I see you texting during class, I will deduct points. If you do not come prepared with your books or files, I will deduct points. If you do not have a flash drive, I will deduct points. If you leave class for extended periods of time for any reason whatsoever, I will deduct points. If I call on you and you don’t know where we are or what exercise we are working on, I will deduct points. This is an advanced-level course intended to prepare you at a professional level, and you are expected to have a professional attitude towards your projects and your work in this course.

If you think that you will have to miss class for work, for travel or to get engaged or married, I strongly suggest that you take this course another semester. I will NOT excuse you from any work for any reason, and you will not earn full credit for attendance if you miss class. The most important reason for this is that much of the work cannot be made up outside of class. We will be experimenting with data, taking short detours with various data, and so forth. The learning is in the process. You can't simply fill out a spreadsheet, turn it in, and consider that you have "made up the work". In the event of an emergency or illness, we can discuss whether a late drop can be arranged.

Lab Assignments: We will often be working on small trial exercises of functions or techniques that are demonstrated in the books. In addition, there are usually many different versions of the same exercise, as we continue to process the data. Pay attention to how I would like each version or exercise saved in your spreadsheets. If they are saved incorrectly and I cannot find them, you will not get credit, even if they were complete. I will supply you instructions on how to get the data files that we will need.
Team “topic” presentations: At various times, we will have questions that may require additional research into a function or skill that is not being covered in class. I will assign a team of 2-3 students to research that topic and present to the class for the following session. Presentations should take less than 10 minutes and must include:

A short introduction to the problem motivating the research. This can include a screen shot or view of a spreadsheet with the problematic situation.
What sources you used to find a solution—and how you found these sources
A short discussion of the function/technique that will solve the problem.
A short walkthrough on a spreadsheet with an example that applies the solution to some sample data.

Project:About halfway through the course, you will be assigned a project that requires consolidation and data cleansing, as well as other types of preparation. You will then be required to design a form and write a short VBA program that collects the data on that form. This project may use real data from real files. The names and social security numbers have been changed, but the project is an actual project. There are alternative textbook projects that we may use.
Exams: There are two exams planned at this point. This is subject to change, depending on the progress that we make on the material. If only one exam is administered, the grading scale will be modified accordingly, with additional weight proportionally assigned to the other categories.
Things I never want to hear: In an effort to keep this course at an advanced level, I am instituting some extra caveats:

Do not tell me, "I typed in exactly what you did, and it's not working." If you type in exactly what I do, it will work the same. Try it again. And if necessary, try it after class.
Do not tell me, "Don't erase that." If I demonstrate something on the board, it is to illustrate a concept. Pay attention to the concept. Writing the specifics of the example in your notes will not help you; making me wait for you to do so will hold back the rest of the class. When you apply the concept, you will probably have to google it again anyway (just as I do much of the time). Your notes won't help you as much as paying attention.
Do not ask me to wait while you copy my keystrokes. I will enter them as slowly as I think is necessary both to physically enter the commands/data and to explain the concepts and purpose of the keystrokes. I will allow some catch-up time at a reasonable breaking point. Don't fall further behind by trying to duplicate some technique in Excel while I have moved on. It is better to pay attention to what I am doing and catch up on the keystrokes later. This class is about the bigger picture. If you get the big picture, you can always pick up the specific sequence of keystrokes later.
Do not, under any circumstances, ask your neighbor to show you how to do something while I am demonstrating Excel, unless we are at a breaking point. There will be plenty of independent working time, and you may work together then. If you talk while I am trying to explain something, it is unfair to the rest of the class, and it probably won't help you understand things any better anyway.
Do not tell me, "I didn't have time to work on it." It doesn't matter if "it" is the lab work, an assignment or the project. If you don't have time to work on it, please consider dropping the course. Can you imagine telling your boss that you didn't have time to work on it?
Do not tell me how many hours you spent trying to figure something out (unless you figured it out--then it's okay.) In the real world, sometimes you get it faster, and sometimes you have to work a little harder.
Do not tell me how many other courses you are taking. This comes under the category of "not my problem." If you have chosen to overextend yourself, you can choose to either work extra hard to meet your commitments or drop one of your courses. Or two, if necessary.

Please refer to the Syllabus Addendum for additional HTC policies.

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

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.


Date	Topic	Text/Materials	Assignments/Files
2/05	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 google youtube	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
	---subtotals
	---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
2/12	---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
	Index/Match		we will revisit index/match several more times
2/19	reviewing string functions more VLOOKUP IFERROR discuss data collection assignment		DUE: First pass at String Assignment file: Cleaning Text file: compare-2-lists file: instructions for comparing 2 lists
2/26	continuing Strings
3/05	Logical functions advanced conditional formatting	class notes	file: compare-2-lists file: instructions for comparing 2 lists
3/12	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

4/09	Data Cleaning		In-class assignment: Student Data
4/16	catch-up, review
4/23	Midterm Exam (hands on)
4/30	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
5/07	Recording macros	Albright, Ch. 4	file: recording macros file: recording macros finished
5/07	playing with macros		various short assignments
5/14	Getting Started VBA	Albright, Ch. 5
	(Intro Programming)
	Exercises/Assignment
5/21	Control Structures	Albright, Ch. 7
5/28	Forms	Albright, Ch. 11
6/04
6/11