INSY 2299 · Information Systems

Session 7: Charting & Excel Roundup

Descriptive analytics: charts and visualizations · Amazon HQ2 ranking

Dominik Molitor · Fall 2026 | Session 7

Session 7

Sign-in via Blackboard

Today’s code

Click the number to set it for today.

Session 7

Agenda

Click a line to jump straight to it.

Excel Charting

Excel charting

Every chart in this part is drawn from the cells of this week’s files, so you can change a number and watch the chart follow.

Excel Charting

Three different types of business analytics (in Excel)

Last week was pivot tables. This week stays on the left with charts.

Descriptive This week’s focus

Analyzes historical data to understand past business performance (e.g., describe sales from last three years).

Predictive

Uses (statistical) techniques to forecast future outcomes (e.g., predict next year’s sales based on historical data).

Prescriptive

Recommends actions to optimize business outcomes (e.g., find the profit-maximizing price for a new product).

Excel Charting

Best practices for visualization

Keep it simple

Focus on the key message.

Know your audience

A CEO may need a different visual than the technical teams.

Label charts well

Axis, legend, title.
But delete legends with one data series (no additional information).

Remove noise

From your chart background (e.g., gridlines).

Clean up your axes

– Avoid cluttered axes
– Remove unnecessary decimal places
– Avoid misleading colors (e.g., red may imply negativity)

Which practices does that chart break?

Excel Charting

A chart is a picture of cells

The Portfolio Performance chart plots four numbers. Change a gain or loss and the chart redraws, axis included.

Stock	Gain/Loss (USD)

Total = the three stocks added up.

Excel Charting

Chart types

Relative Comparison

Pie Chart

Stacked Column Chart

Absolute Comparison

Bar Chart

Column Chart

Change Over Time

Column Chart
(less data points)

Line Chart
(more data points)

Relationship

Scatter Chart
(2 variable)

Bubble Chart
(3 variables)

Distribution

Column Histogram

Line Histogram

Press → to add the next column.

Excel Charting

Different charts should be used for different purposes

Pie charts

Show pieces of a whole—that is, each slice of the pie shows how big one component of the whole is.

So, pie charts are only meaningful if all the slices are positive and together all the slices add up to something meaningful (e.g., comparison of different expense-types).

Pie charts should never be used to plot time-series data.

Column charts

Compare differences in groups (e.g., expense-types over time).

Line charts

Show trends over time (e.g., net income over several years).

Remarks on Charting Types

Same data, three chart types: which one tells the truth?

Data (Ram Retail Planning Model)

Chart type

Values: Visualization - Solution.xlsx, rows 17 (Marketing), 18 (Administration) and 23 (Net Income), years in B11:E11.

Chart Elements

Get to know the elements of a chart

Click an element in the list, or press → to step through them.

Chart Design

Good chart design?

Column chart of five expense categories: default green gradient bars, axis labels with three decimals, a Series1 legend, a truncated first category label

What would you change?

Chart Design

Fix the chart, one best practice at a time

Column chart of 2026 expenses by type, as built in Excel: gradient bars, gridlines, Series1 legend, three decimals

More Charting

Go to Blackboard / Datasets and download the Visualization file

Datasets

Simulation
Pivot Table Practice File
Visualization

More

CHARTING

Open it beside the deck. It holds the Ram Retail Planning Model and four years of monthly stock prices for the mini lab.

Mini Lab Exercise

Mini lab exercise

→ Open the Visualization file:

Navigate to the Ram Retail Planning Model sheet and create a Line Chart showing the trend of “Sales Revenue”, “Gross Profit”, “Earnings before Taxes” and “Net Income” over four years (2025–2028). Put the years on the X-axis (horizontal). Be sure to label the chart well and to include a legend.
Navigate to the Stocks sheet and create a Scatter (X,Y) Chart to examine the relationship between “ADBE” (Series X Values) and “ORCL” (Series Y Values). Hint: Create an empty Scatter Chart and then in the “Select Data” dialog box, add the appropriate data series. Name the series “Correlation Stocks”.

Mini Lab Exercise

Mini lab: one possible solution

1 · Line chart, Ram Retail Planning Model

Click a series in the legend to hide or show it. Revenue grows 5% a year, yet net income falls every year.

2 · Scatter chart, Stocks: ADBE (x) vs. ORCL (y)

Trendline

Hover a point to see its month. 60 months, Jan 2017 – Dec 2021.

More Charting

Two series on very different scales

Nasdaq Composite (IXIC) vs. Oracle (ORCL), monthly, 2017–2021

Excel Roundup

Excel roundup

One decision, all the tools from the last weeks: describe the data with MIN, MAX and AVERAGE, weight it with absolute references, pick a winner with IF, rank with RANK, and chart the result.

Excel Roundup

Let’s use Excel to help Amazon decide where its HQ2 should go

Moody’s Analytics, Economy.com, October 12, 2017: Where Amazon’s Next Headquarters Should Go. We offer a data driven approach to selecting the best metro area for HQ2. Analysis by Mark Zandi and Adam Ozimek.

Fortune, October 8, 2018: Two Cities Get Second Looks From Amazon’s HQ2 Search Committee. Amazon visited all 20 cities on the short list.

Winners!? Two maps: Crystal City, VA, near Washington, and Long Island City, NY

Winner! Map: Crystal City, VA, near Washington

More info can be found here and here.

Excel Roundup

Where should Amazon have opened its HQ2?

Datasets

Pivot Table Practice File
Visualization
Amazon HQ2

Amazon HQ2

SELECTION

Amazon HQ2 selection example: download the raw data from Blackboard (Amazon_Ranking - Raw 2026.xlsx).

Data source: Moody Analytics

Excel Roundup

Data description: six criteria, 19 metro areas

(1) Business Environment, (2) Human Capital, (3) Costs, (4) Quality of Life, (5) Transportation, (6) Geography. Geography is a bit more subjective than the other criteria, which are backed by “hard” data. Click a column header to sort; the leader gets a maroon outline.

Data source: Moody Analytics; note that we included Seattle as reference point (italic)

Excel Roundup

Ranking category details

Business environment

State incentives, metro credit rating, state credit rating, 5/10 yr. job/tech job growth, tax environment

Human capital

New degrees in engineering, computers, math, new BA degrees, total pop degrees, supply/demand ratio

Cost

Single-family house prices, salary (biz/fin, mgmt, comp/math), costs of doing business

Quality of life

Crime rate, arts/entertainment/recreation & restaurants per 1,000 people, QOL score, schools, diversity

Transportation

Public transit, walking, biking, car commute, public transit commute, airport

Geography

Regional points, distance of Amazon, closest to Bezos home, #state fulfillment centers

Metro Area Selection Example

Our goal is to select the candidates for Amazon’s HQ2. How should we proceed?

1Explore/describe the different attributes/categories (B22:G24) (Max, Min, Mean) by adding new rows below the given data set
2Think about the weight for each attribute (B26:G26) (defaults added; they should add up to 1 (100%))
3Calculate a score for each metro area (H2:H20) (and validate the score)
4Select the most appropriate metro area (“Winner!” in I2:I20) by using a function of your choice (e.g., IF function)
5Create a column that includes the rank of each city (J2:J20) (1 should indicate the highest rank, for example by using the RANK function)
6Chart results (horizontal bar chart, optional)

Metro Area Selection Example

Possible solution

Click a cell to see its value · type new weights in B26:G26 (Input style).

Metro Area Selection Example

Chart the results: the weights pick the winner

Sum (H26)1

Seattle (reference point) competes

Green = “Winner!” (IF on the maximum) · maroon = Washington metro, home of Crystal City, VA · grey = Seattle (reference)

Assignment & Logistics

Assignment reminder & announcements

Reminder

Pivot Table Assignment is due today (day of class), 11 PM.

Before Session 8

Reminder: install Claude for Excel before next class

Claude Pro is required for this class

A one-month subscription is sufficient for this module. Choose monthly billing at claude.ai. The free plan is not sufficient.

Your computer

Before you begin

Your Claude account, access to its email inbox, and an updated Excel:

Excel on the web is also supported.

Find the add-in. In Excel, and search for Claude. The listing includes Claude for Excel (and Claude for Microsoft 365).
Install it. Select the add-in, follow the Add / Continue prompts, then open the Claude panel.
Sign in. Choose Log in to Claude with your Pro account. If prompted, use the sign-in code from your email.
Connect Excel. If Excel shows an account-connection code, copy it into the browser page. This is separate from the email code.
Check that it works. Open a practice workbook and ask: “What worksheets are in this workbook?”

If setup stalls: confirm you signed in with your Pro account, then update and restart Excel. If your school account blocks add-ins, contact Fordham IT or your instructor.

Only need one month? Subscriptions renew automatically: cancel at least 24 hours before the next billing date (Settings › Billing › Cancel). You keep access until the paid period ends.

Full details: Claude for Excel installation guide (Session 8) · Setup and compatibility · Microsoft Marketplace listing · Cancel a subscription