Housekeeping

Today’s plan 📋

  • Syllabus and Survey

  • What are Business Analytics?

    • Components
    • What to Expect
    • Examples

  • Introductory Excel Skills

    • Absolute (fixed) vs Relative cell references


In-class Polling (Session ID: bua345s25)

Course Websites and Syllabus

A little about me

  • I grew up here and went to SU and then I left for…

    • TRAVEL, GRAD SCHOOL, WORK

  • I’ve Worked in Scotland, Slovakia, Lithuania, Chile and traveled all over…

  • I went to graduate school in Oregon and Virginia

  • I’ve worked in my field in West Virginia, Virginia, Mississippi, and Rhode Island.

    • Worked for federal gov’t and in the private sector

    • Taught at an adjacent college during each each job

  • I still do Statistics/Analytics/Data Science consulting.

Business Analytics

  • Finding, analyzing, and summarizing data to answer critical questions

  • Basing decisions on data and evidence, INSTEAD of gut instinct, emotions, or anecdotes

  • Three Primary components of Business Analytics:

    • Descriptive Analytics

      • Many ways to go about describing data effectively.

      • In this class we will discuss using Excel Pivot Tables.

      • Many other options including R and Pytthon but Excel skills are essential.

    • Prescriptive Analytics

      • BUA 345 provides a short introduction of how to use the Excel Solver for prescriptive analytics.

      • In subsequent Supply Chain courses you will use this tool to go further.

Primary components of Business Analytics Cont’d

  • Predictive Analytics

    • The majority of BUA 345 will focus on predictive analytics.

    • There are MANY tools for predictive analytics.

    • This course will focus on regression, model building, and model verification.

  • In MAS 261 you (hopefully) covered

    • Simple Linear Regression (SLR)

    • An introduction to Multiple Linear Regression (MLR)

    • A introduction to linear transformations

  • BUA 345 will build on regression material from MAS 261.

  • There will also be introduction to forecasting.

Descriptive Analytics Example

  • A few years ago, the CEO of United Airlines stepped down due to bribery allegations.

  • A new CEO stepped in at a time of crisis when the airline was critisized for putting profits before customer satisfaction.

Descriptive Analytics - Airline Crisis Management

Suppose you are Oscar Munoz and you want to boost United’s lackluster reputation.

  • Which of the following issues would you address first ?

    •  Advertising  Baggage  Disability
    •  Discrimination  Fares  Flight Delays
    •  Overbooking  Refunds  Reservations

  • Where might you find data to help you decide?

  • Factual data sources:

    • Customer Complaint Data

    • Flight Delay/Cancellation/Bookings

    • Baggage transportation information

Airline Crisis Management - Examine the Data

  • Data Analytics leads us to prioritize three main areas to improve United’s reputation:

    • Decrease Flight Delays

    • Improve Baggage Handling, i.e. decrease loss and damage incidents

    • Improve Customer Service Relationships

  • These three areas comprise 63% of customer complaints.

  • Intuitively these choices make sense and the data backs that up.

💥 Lecture 1 In-class Exercise - Q1 💥

A PR firm approaches United Airlines and says they can greatly improve customer relations with a new ad campaign combined with deals on some fares. How do you respond?

  • OK. Advertising and fares together were more than 20% of our complaints.

  • That’s debatable. Advertising and fares together were between 10% and 15% of our complaints.

  • Not interested at this time. Advertising and fares together were less than 10% of our complaints.

Prescriptive Analytics

  • Apply OPTIMIZATION methods to data to find most favorable (most optimal) solution

  • Doctors ‘prescribe’ the optimal treatment for your illness or injury

  • Analysts ‘prescribe’ the optimal solution for your business

Prescriptive Analytics - Supply Chain Management Example

You are the manager of an Apple Store

  • You use a 20’ x 9’ x 9’ container to ship iPads and iMacs.

  • Based on the dimensions and prices shown, how do you MAXIMIZE the total value of goods shipped in this container?

Product Dimensions Retail Price
iPad 10”x8”x2” $380
MacBook 26”x21.5”x9” $1800

Supply Chain Management Example Cont’d

You are the manager of an Apple Store

  • You use a 20’ x 9’ x 9’ container to ship iPads and iMacs.

  • Based on the dimensions and prices shown, how do you MAXIMIZE the total value of goods shipped in this container?

Product Dimensions Retail Price
iPad 10”x8”x2” $380
MacBook 26”x21.5”x9” $1800

Steps

  1. Convert container dimensions to inches.

  2. Convert dimensions for container and products to volumes.

  3. Determine what quantity of each will result in largest retail price for full container.

Predictive Analytics

  • Fit statistical models to relevant data data.

  • Predict the future (forecast) using statistical models.

  • Predict or reveal patterns or relationships in the data.

  • Descriptive Analytics is often a precursor to Predictive Analytics.

  • Can be used to provide information for Prescriptive analytics

Predictive Analytics - Stock Market Example

  • You are investor who has invested in Starbucks on the NASDAQ stock market.

  • Using predictive analytics, you can FORECAST the closing price of your Starbucks stocks.

  • Based on the forecasted price and your plans, you can decide to

    • keep all of your stocks OR
    • sell some of your stocks OR
    • sell all of your stocks.

  • Predictive Analytics allows you to make better, more informed investment decisions.

  • We will cover an introduction to forecasting at the end of this course.

What to Expect in this course

  • No ‘heavy’ theoretical math

    • No calculus or trigonometry

  • Some ‘light’ applied math

    • some basic algebra and a little basic geometry

  • Spreadsheet Software Skills

    • Excel skills (demo videos provided)

  • A little coding in R to create models

    • Demo videos provided

  • Challenging ‘Fun’ questions and satisfaction in answering them

  • Useful skills that can be applied to ANY business

Business Analytics Example - The Cincinnati Zoo

  • The zoo’s team analyzed visitor data and food and gift purchase data.

  • Based on the analysis, they

    • eliminated ineffective campaigns and reduced advertising expenditures by 43%.

    • cut annual marketing expenditures by $40K.

    • matched merchandise and food offerings to peak visitor times and increased sales revenue by 25%

    • increased overall revenue by $738K.

  • Business Analytics is essential for effective business management and growth.

Excel Cell References

This short Exercise will demonstrate how to use cell references in Excel efficiently.

  • By default, Excel Cell References are RELATIVE, but we can modify them to be ABSOLUTE.

  • The Big Mac Index is an informal way of comparing purchasing power in different countries.

  • In the US, a Big Mac costs $5.69 (based on average of prices in four US Cities in 2024).

Calculate Big Mac Exchange Rate

  1. Divide Price (Local Currency) by US Price of Big Mac for each country.

    • To do this, we use $ to create a ABSOLUTE ROW reference to compare each country’s price to the United State’s price.

    • If we wanted to fix the column (not needed here), we would put the $ before the column letter.

    • In cell D2 type the following formula: =C2/C$42

Calculate Big Mac Exchange Rate Cont’d

  1. Copy cell D2 and paste it into cells D3 through D43.

  1. Select column D and convert to Number.

Calculate Big Mac Exchange Rate Cont’d

  1. Optional but useful: Use =FORMULATEXT() in Column E to see formulas in column D.
    • In cell E2 type =FORMULATEXT(D2)
    • Copy cell E2 and paste it into cell E3 through E43.

  • Notice:
    • The copy and paste operations copied the formula, NOT the value.
    • The relative cell reference in the formula changed RELATIVE TO THE ROW LOCATION.
    • The ABSOLUTE ROW reference stayed the same for every row.

💥 Lecture 1 In-class Exercise - Q2 💥


What is the Big Mac Exchange Rate for Norway?


Also a word of caution: If you sort data with an ABSOLUTE ROW reference, the data will be incorrect. BE CAREFUL!

Over/Under Valued Calculation

  • This calculation compares the Big Mac Exchange rate to the actual exchange rate.

  • In this calculation, all of the cell references are RELATIVE.

  1. In cell G2, type the following formula: =(D2-F2)/F2

Over/Under Valued Calculation Cont’d

  1. Copy cell G2 and paste it into cells G3 through G43.

  1. Select column G and convert to Percentage.

Over/Under Valued Calculation Cont’d

  1. Optional but useful: Use =FORMULATEXT() in Column H to see formulas in column G.
    • In cell H2 type =FORMULATEXT(G2)
    • Copy cell H2 and paste it into cell H3 through H43.

  • Notice:
    • The copy and paste operations copied the formula, NOT the value.
    • The relative cell reference in the formula changed RELATIVE TO THE ROW LOCATION.
    • There are no ABSOLUTE row or column references in this formula.

💥 Lecture 1 In-class Exercise - Q3 💥


The purchasing power for Switzerland is 41.83% higher than the purchasing power in the United States.

Is this the country with the highest purchasing power?

  • To answer this question, type the following calculation in cell J2 to calculate the maximum: =MAX(G2:G43)


Again a word of caution: If you sort data with an ABSOLUTE ROW reference, the data will be incorrect. BE CAREFUL!

💥 Lecture 1 In-class Exercise - Q4 💥


What is one (or more) possible weakness of this informal Big Mac Index?

Multiple answers are correct. Choose ONE of the correct answers.


A. Some ethnicities don’t eat beef.

B. Some countries don’t consume as much fast food as the U.S.

C. Average prices don’t indicate variability.

D. I don’t like Big Macs.