In order to build more sophisticated models using Excel, GBS uses an Excel add-in from FrontlineSolvers. Their website is www.solver.com. The software we will be using is called the Analytic Solver Platform, and it includes tools for simulation, optimization and decision analysis. Here is a brief video about installing the software and how we will use it:
Excel INDEX and Named Ranges
Excel allows you to refer to cells as just B6 or C17:D26 (for example), but ranges can also be given meaningful names like Market_Share. Here is a video to introduce named ranges and then using INDEX to access cells within a named range:
Creating graphs of data with the use of INDEX is quite useful. Here are some examples:
Excel Graphs
There are many kinds of graphs in Excel, and this page looks at a few of the most common types of graphs.
The first is just to do a scatterplot of all the data points. This allows us to “see” the variability of the data:
Similar to this is to graph each point as a column. This gives us a bit more sense of how “big” each value is:
Similar to a column chart (one column per data point) is a histogram. Instead of plotting each point, the graph shows the number of points in various ranges (for example, 1-10, 11-20, etc.). There is a built-in tool for creating histograms in the Data Analysis tool pack:
Histograms using the Data Analysis Tool Pack
Histograms can also be constructed using the Excel function FREQUENCY():
Histograms using the FREQUENCY() function
And the last graph type we will look at is an XY scatterplot:
Excel Help
There are a number of videos for using Excel for data and decision analysis. They are organized into small pages, and this page is a table of contents. If you don’t find what you need, let me know and I’ll either add a new video or make video easier to find. Thanks.
Descriptive Statistics using Excel
Named Ranges, INDEX(), and Simple Graphs using the INDEX() function
Analytic Solver Platform Add-in for Excel
Excel Statistics
We can use Excel to create descriptive statistics for a data set. Here are a few videos with some help on how to do some basic tasks.
The filter command is often a good place to start with a new dataset:
In the Analysis Toolpack, there is a way to quickly calculate the descriptive statistics for a dataset:
Analysis Toolpack Descriptive Statistics
Sometimes, the Analysis Toolpack is missing from the Data menu tab. If it is, here is the way to install it:
Descriptive statistics can also be computed using the built-in functions Excel provides:
Excel Overview
This is a quick start for people new to Excel.
Here is a quick overview of the features of Excel, focusing on those we will use in the business school:
There are short-cut keys for navigating around Excel:
Referring to cells in Excel has some conventions that are useful to understand:
SAS/JMP Quick Start Guide
This is a quick start guide for SAS/JMP, the one of the statistical software packages used at Goizueta. It is Excel-friendly and requires no programming. I have created some videos to help get you started, once you have the software installed on your computer.
Here is a quick overview and some helpful preference changes that will make JMP a bit easier to use:
JMP will easily import data from Excel:
Descriptive statistics with JMP:
JMP version of Excel pivot tables:
Scatterplots:
Create single regression models:
Multiple regression models, with categorical data and nonlinear relationships:
Soon we are all going to be programmers?
Nice resource if you are thinking that you would like to write programs for you computer/phone.
Happy pi day
or happy
day. Try it with Excel.