CMPS 163: Business Analytics

Introduction

In this course we will be using Excel and we will start with review basic skills, as well as a few more advanced ones. Excel is spreadsheet software developed by Microsoft and offers a lot of flexibility in terms of implementing formulas and other things, which is exactly what we need for this course. The book almost exclusively Excel, except for the last chapter which we will not cover, and Chapter 5 (Cluster Analysis Part II) where we will also look at a powerful tool for clustering called Gephi.

If you have a lot of experience with Excel this will be an easy start, but if you only Excel superficially (or not at all!) please make sure that you spend enough time on understanding all the topics covered in Chapter 1 of the book. In both cases, it should be emphasized that you have to learn to be very precise so try to develop an eye for detail; getting one letter or parenthesis wrong will result in error or incorrect output. Finally, while Excel is ‘just a spreadsheet program’ you will be surprised how powerful it can be for solving a wide variety of business analytics problems.

Module Objectives

• Summarize data using pivot tables
• Apply array formulas
• Solve optimization problems with the Excel solver and OpenSolver

Excel Functionality

• Pivot tables
• Array formulas
• Solver
• OpenSolver

Learning Resources

• Module 2 Readings: Chapter 1
• Module 2 Slides: Chapter 1

Learning Activities

• Module 2 Assignment

Videos

How to create pivot tables:

Transpose and array formulas:

For Further Study

• Read more about Microsoft Excel on Wikipedia