Provides permanent access to slides, lecture notes and R projects
Provides semester-long access to R files for assignments andpractice questions, lecture recordings and video demos.
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
Convert container dimensions to inches.
Convert dimensions for container and products to volumes.
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
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
Copy cell D2 and paste it into cells D3 through D43.
Select column D and convert to Number.
Calculate Big Mac Exchange Rate Cont’d
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.
In cell G2, type the following formula: =(D2-F2)/F2
Over/Under Valued Calculation Cont’d
Copy cell G2 and paste it into cells G3 through G43.
Select column G and convert to Percentage.
Over/Under Valued Calculation Cont’d
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.
Key Points from Today
Components of Business Analytics
Descriptive Analytics
Prescriptive Analytics
Predictive Analytics
Basics of Excel Spreadsheet skills
Relative cell references
Absolute cell references
Row: Set as ABSOLUTE (fixed) with a $ before ROW number
Column: Set as ABSOLUTE (fixed) with a $ before COLUMN letter(s)
Using RELATIVE and ABSOLUTE references makes EXCEL very efficient for data calculations.
To submit an Engagement Question or Comment about material from Lecture 1: Submit it by midnight today (day of lecture).
Source Code
---title: "BUA 345 - Lecture 1"subtitle: "Welcome and Introduction to Business Analytics"author: "Penelope Pooler Eisenbies"date: last-modifiedlightbox: truetoc: truetoc-depth: 3toc-location: lefttoc-title: "Table of Contents"toc-expand: 1format: html: code-line-numbers: true code-fold: true code-tools: trueexecute: echo: fenced---## Housekeeping```{r setup, echo=FALSE, warning=F, message=F, include=F}#| include: false# this line specifies options for default options for all R Chunksknitr::opts_chunk$set(echo=F)# suppress scientific notationoptions(scipen=100)# install helper package that loads and installs other packages, if neededif (!require("pacman")) install.packages("pacman", repos = "http://lib.stat.cmu.edu/R/CRAN/")# install and load required packagespacman::p_load(pacman,tidyverse, magrittr, olsrr, shadowtext, mapproj, knitr, kableExtra, countrycode, usdata, maps, RColorBrewer, gridExtra, ggthemes, gt, mosaicData, epiDisplay, vistributions, psych, tidyquant, dygraphs)# verify packages# p_loaded()```### Today's plan- Syllabus and Survey- What are Business Analytics? - Components - What to Expect - Examples- Introductory Excel Skills - Absolute (fixed) vs Relative cell references<br>::: fragment**In-class Polling (Session ID: bua345s25)**:::## Course Websites and Syllabus- [**Syllabus - Updated January 2025**](https://docs.google.com/document/d/1dCUtO4i1dVEMzd1g37eNVAB9IindzNajMiz0w_HMJ3s/edit?usp=sharing){target="_blank"}- [**Absence Explanation and Point Solutions Technical Issue Form**](https://forms.gle/GZqC75Uxnk9BPJBZ7){target="_blank"}- [**BUA 345 Blackboard Site**](https://blackboard.syracuse.edu/ultra/courses/_541987_1/outline){target="_blank"} - Used for submitting engagement questions, homework assignments and tests- [**Professor Pooler's BUA 345 Website**](https://penelope2040.quarto.pub/bua-345/){target="_blank"} - Provides permanent access to slides, lecture notes and R projects - Provides semester-long access to R files for assignments andpractice questions, lecture recordings and video demos.## ### A little about me:::::: columns::: {.column width="58%"}- 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.:::::: {.column width="4%"}:::::: {.column width="38%"}{fig-align="center"}:::::::::## ### 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:::::: columns::: {.column width="48%"}- 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.:::::: {.column width="4%"}:::::: {.column width="48%"}{fig-align="center"}:::::::::## ### Descriptive Analytics - Airline Crisis ManagementSuppose 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:::::: columns::: {.column width="48%"}- 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.:::::: {.column width="4%"}:::::: {.column width="48%"}{fig-align="center"}:::::::::## ### Lecture 1 In-class Exercise - Q1:::::: columns::: {.column width="58%"}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.:::::: {.column width="4%"}:::::: {.column width="38%"}{fig-align="center"}:::::::::## Prescriptive Analytics:::::: columns::: {.column width="48%"}- 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:::::: {.column width="4%"}:::::: {.column width="48%"}{fig-align="center"}:::::::::## ### Prescriptive Analytics - Supply Chain Management Example```{r prescrip_data}Product <- c("iPad", "MacBook")Dimensions <- c('10"x8"x2"', '26"x21.5"x9"')`Retail Price` <- c("$380", "$1800")prescrip_data <- tibble(Product, Dimensions, `Retail Price`)```::::::: columns:::: {.column width="58%"}**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?::: fragment```{r data_table}prescrip_data |> kable()```:::::::::: {.column width="4%"}:::::: {.column width="38%"}{fig-align="center"}::::::::::## ### Supply Chain Management Example Cont'd::::::: columns:::: {.column width="48%"}**You are the manager of an Apple Store**::: nonincremental- 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?:::```{r data_table1}prescrip_data |> kable()```::::::: {.column width="4%"}:::::: {.column width="48%"}**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:::::: columns::: {.column width="58%"}- 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:::::: {.column width="4%"}:::::: {.column width="38%"}{fig-align="center"}:::::::::## ### Predictive Analytics - Stock Market Example:::::: columns::: {.column width="68%"}- 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.:::::: {.column width="4%"}:::::: {.column width="28%"}{fig-align="center"}:::::::::## ### What to Expect in this course:::::: columns::: {.column width="58%"}- 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:::::: {.column width="4%"}:::::: {.column width="38%"}{fig-align="center"}:::::::::## ### Business Analytics Example - The Cincinnati Zoo:::::: columns::: {.column width="58%"}- 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.:::::: {.column width="4%"}:::::: {.column width="38%"}{fig-align="center"}:::::::::## Excel Cell References### ```{r bmind_data_prep, eval=F}# download data from statista:# Global price of a Big Mac as of July 2024, by country# https://www.statista.com/statistics/274326/big-mac-index-global-prices-for-a-big-mac/# U.S. dollar (USD) exchange rate against the forex currencies of countries and # territories featured in the Big Mac Index from January 2024 to June 2024# https://www-statista-com.libezproxy2.syr.edu/statistics/1039342/average-annual-exchange-rates-developed-emerging-countries/bm_ind <- read_csv("data/big_mac_index_07012024.csv", show_col_types = F, skip=3, col_names = c("Country", "Price")) |> arrange(Country)bm_ind_exch <- read_csv("data/big_mac_countries_exchange_rates_06302024.csv", show_col_types = F, skip=3, col_name = c("Currency", "d1", "d2", "d3", "d4", "d5", "Exchange")) |> dplyr::select(1,7) |> glimpse() |> separate_wider_delim(Currency, delim="(", names=c("Currency_full","Currency")) |> mutate(Currency = gsub(")","",Currency,fixed = T)) |> arrange(Currency) |> dplyr::select(2,3)country_currency <- read_csv("data/country_currency.csv", show_col_types = F) bm_ind <- full_join(bm_ind, country_currency) |> distinct(Country, .keep_all = T)uspr <- big_mac_all$Index[big_mac_all$Country=="United States"]big_mac_all <- full_join(bm_ind, bm_ind_exch) |> mutate(Exchange = ifelse(Country=="United States", 1, Exchange)) |> filter(Exchange != "n/a") |> mutate(Exchange = gsub(",", "", Exchange, fixed = T) |> as.numeric()) |> mutate(Price_Local = Price*Exchange, Index = Price/uspr)big_mac_all_exp <- big_mac_all |> dplyr::select(Country, Currency, Price_Local, Exchange) |> rename("Price (Local Currency)" = "Price_Local", "Exchange Rate" = "Exchange") |> write_csv("data/Big_Mac_Index.csv")```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](https://www.visualcapitalist.com/cp/big-mac-index-purchasing-power-parity-burger-inflation/) 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).::: fragment{fig-align="center"}:::## ### Calculate Big Mac Exchange Rate1. 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`::: fragment{fig-align="left"}:::## ### Calculate Big Mac Exchange Rate Cont'd2. Copy cell `D2` and paste it into cells `D3` through `D43`.::: fragment{fig-align="center"}:::::::::: columns::: {.column width="38%"}3. Select column D and convert to `Number`.:::::: {.column width="2%"}::::::: {.column width="60%"}::: fragment{fig-align="center"}::::::::::::::## ### Calculate Big Mac Exchange Rate Cont'd4. 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`.::: fragment{fig-align="center"}:::- 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<br>What is the Big Mac Exchange Rate for Norway?<br>**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`::: fragment{fig-align="center"}:::## ### Over/Under Valued Calculation Cont'd2. Copy cell `G2` and paste it into cells `G3` through `G43`.::: fragment{fig-align="center"}:::::::::: columns::: {.column width="38%"}3. Select column G and convert to `Percentage`.:::::: {.column width="2%"}::::::: {.column width="60%"}::: fragment{fig-align="center"}::::::::::::::## ### Over/Under Valued Calculation Cont'd4. 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`.::: fragment{fig-align="center"}:::- 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<br>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)`<br>**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<br>What is one (or more) possible weakness of this informal Big Mac Index?**Multiple answers are correct. Choose ONE of the correct answers.**<br>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.## ### Key Points from Today- Components of Business Analytics - Descriptive Analytics - Prescriptive Analytics - Predictive Analytics- Basics of Excel Spreadsheet skills - Relative cell references - Absolute cell references - Row: Set as ABSOLUTE (fixed) with a \$ before ROW number - Column: Set as ABSOLUTE (fixed) with a \$ before COLUMN letter(s) - Using RELATIVE and ABSOLUTE references makes EXCEL very efficient for data calculations.::: fragment**To submit an Engagement Question or Comment about material from Lecture 1:** Submit it by midnight today (day of lecture).:::
