Created by Turbolearn AI
Business Math and Statistics for Data Professionals
This tutorial series focuses on the essential math and statistics needed for data analyst, business
analyst, and data scientist roles. The goal is to learn the 20% of math and stats that handles 80% of the
work. A data scientist role may require a bit more advanced math.
The Intersection of Skills
There is an intersection of skills between business math and statistics, business logic, and tool skills.
Business Logic
Domain understanding relevant to the business. For example, understanding hotel bookings,
occupancy rates, and booking capacity if working for a hospitality company like Expedia or
Rooms.
Math and Statistics
Basic concepts such as sums, percentages, variance, standard deviation, correlation, mean,
mode, and median.
The combination of business logic and math/statistics forms business metrics or KPIs (Key Performance
Indicators).
KPIs are key performance indicators such as revenue, profit, iPhone sales (for Apple), top
products, and top customers (for service industries).
Tool Skills
Proficiency in tools like Excel, Python, R, and Power BI is crucial. Data analysis requires the
right combination of business logic, math/statistics, and tool skills.
Microsoft Excel Basics
The series will use Microsoft Excel, a widely used tool for data analysis, especially on smaller datasets.
Launching and Navigating Excel
If you have Excel installed on a Windows machine, launch the application. If not, consider the online
version or discounts for students. The Excel interface includes:
A menu bar at the top with various options.
A spreadsheet area composed of rows (numbered 1, 2, 3...) and columns (labeled A, B, C...).
Sheets at the bottom, allowing multiple tabs within a single file.
Page 1
Created by Turbolearn AI
Basic Use Case: Tracking Monthly Expenses
A fundamental use case is maintaining monthly home expenses in an Excel file.
1. Create separate sheets for each month (e.g., May 2023, June 2023).
2. Input expenses with categories like rent, food, and utilities, along with dates and amounts.
3. Use drag and drop to auto-fill dates; Excel recognizes the pattern and increments the dates
accordingly.
Formatting and Highlighting
To enhance readability, format the header row with a special color and bold text. Conditional formatting
can highlight expenses that exceed a certain amount (e.g., expenses > $100). This can be found under
highlight and greater than.
Basic Formulas and Operations
Calculating Sums
To find the total expense, highlight the cells containing the expenses, and the sum will be displayed at
the bottom. Alternatively, use the sum formula: = sum(D2 : D11)
You can also use autosum to accomplish the same result.
Sorting Data
Sort numbers or dates using the sort function. For example, sort expenses from smallest to largest or
dates from newest to oldest.
Terminology
Term Description
Cell Each individual block in the spreadsheet
Spreadsheet Also known as a sheet
Workbook The entire Excel file containing one or more spreadsheets (sheets)
Saving Files
Save your work by navigating to file and then save. The file extension for Excel files is .xlsx.
Formatting Numbers
Format numbers as currency (e.g., dollars) by clicking the dollar sign symbol. Remove formatting by
selecting "General".
Page 2
Created by Turbolearn AI
Calculating Total Expense Per Category
Create a summary table to calculate the total expense for each category (e.g., food, utilities).
SumIf Formula
The SumIf formula is used to sum values based on a specific criterion.
The syntax is: SumIf(range, criteria, sum_range)
range:The range of cells to evaluate the criteria against.
criteria:The condition that determines which cells to sum.
sum_range: The range of cells to sum if the criteria is met.
For example, to sum only the "food" expenses from a list of expenses, you would specify the category
column as the range, "food" as the criteria, and the expense amount column as the sum range.
Using Tables in Excel
Creating a Table
To create a table in Excel:
1. Select the range of cells you want to include in the table.
2. Go to the "Insert" tab.
3. Click on "Table".
4. Confirm the selected range and check the box if your table has headers.
5. Click "OK".
Benefits of Using Tables
Readability: Table names and column names make formulas more readable (e.g., expenses[amount]
is more understandable than D2:D12).
Dynamic Range: Tables automatically adjust when you add new rows or columns. Formulas that
refer to table columns update automatically to include the new data.
Slicers: Tables allow you to insert slicers for easy filtering. Slicers are visual filters that allow you to
quickly filter the table data based on the selected values.
Total Row: Tables can include a total row that automatically calculates sums, averages, counts, or
other aggregate functions.
Example Scenario: Expense Tracking
Page 3
Created by Turbolearn AI
1. Convert Data to Table: Select the expense data and convert it into a table named "expenses."
2. Use Formulas: Use formulas like =SUM(expenses[amount]) to calculate the total expenses.
3. Add New Rows: Add a new expense entry. The table automatically expands, and the total
expense formula updates to include the new entry.
4. Insert Slicer: Insert a slicer for the "category" column to filter expenses by category (e.g., food, rent,
utilities).
5. Add Total Row: Add a total row to the table and configure it to display the sum of the expense
amounts.
Introduction to the Movies Dataset
The movies dataset consists of multiple sheets containing information about movies, financials, actors,
and languages.
Movies: Contains movie details such as name, industry (Bollywood/Hollywood), studio, IMDb
rating, budget, and revenue.
Financials: Contains financial data related to movies, linked to the Movies sheet using a movie ID.
Movie Actor: Lists the actors in each movie, linked by movie ID and actor ID.
Languages: Provides a mapping of language IDs to language names.
Linking Tables with Movie ID
The Movie ID is used to link tables together. For example, the KGF Chapter 2 movie is 101. Using this,
the budget and revenue was 1 and 12.5 billions INR.
X Lookup Formula
The X Lookup formula can be used to combine data from multiple tables based on a common identifier
(e.g., movie ID).
X Lookup allows one to search a range for a search term and return a result from another
range
Basic Business Arithmetic on the Movie Dataset
Standardizing Currency and Units
When performing calculations on financial data, it is important to standardize the currency and units.
The steps are:
Page 4
Created by Turbolearn AI
1. Convert to a Common Unit: Convert all values to a common unit (e.g., millions). If the unit is in
billions, multiply the number by 1000 to convert it to millions.
$IF(unit = "billions", value * 1000, value)$
2. Convert to a Common Currency: Convert all values to a common currency (e.g., INR). If the
currency is in USD, multiply the number by the USD to INR conversion rate.
$IF(currency = "USD", value * 80, value)$
3. Sum the Standardized Values: After converting all values to a common unit and currency, you can
sum them up to get the total budget and revenue.
$SUM(standardized_values)$
Example: Converting Budget and Revenue to INR Millions
Movie Name Budget Unit Revenue Currency
KGF Chapter 2 1 Billions 12.5 INR
Pathaan 200 Millions 954 USD
1. Convert to Millions:
KGF Chapter 2: Budget = 1 * 1000 = 1000 Millions
Pathaan: Budget = 200 Millions
2. Convert to INR:
KGF Chapter 2: Budget = 1000 (already in INR)
Pathaan: Revenue = 954 * 80 = 76320 INR
Currency Conversion in Financial Analysis
When dealing with financial data from different regions, it's essential to convert currencies for accurate
comparison.
Converting INR to USD
When converting Indian Rupees (INR) to United States Dollars (USD), a conversion rate is necessary.
Average Conversion Rate: Use an average conversion rate, such as 80 INR to 1 USD. This is an
average that can be provided by brokers or obtained from financial APIs.
Spot Rate: A spot rate refers to the current currency conversion rate.
Formula: To convert INR to USD, use the following formula:
U SD = I N R/80
Page 5
Created by Turbolearn AI
Example
If a movie's budget in INR is 1, 600 million, the equivalent budget in USD would be:
U SD = 1600/80 = 20 million
Verifying the Conversion
To verify, divide the budget in INR by the conversion rate (80):
1600 million INR /80 = 20 million USD
Movie Industry Analysis
Analyzing movie industry data involves counting movies and calculating revenue metrics for different
regions, such as Bollywood.
Counting Total Movies
To find the total number of movies, use the COUNT function on a column like "Movie ID."
Bollywood Movie Analysis
Total Bollywood Movies: To count only Bollywood movies, use the COUNTIF function. This function
counts entries that meet a specific criterion.
Formula: COUNTIF(range, criteria)
range:The column to apply the criteria (e.g., "Industry").
criteria: The condition to count (e.g., "Bollywood").
Total Bollywood Movie Revenue: To calculate the total revenue from Bollywood movies, use the
SUMIF function. This function sums values based on a specified criterion.
Formula: SUMIF(range, criteria, sum_range)
range:The column to apply the criteria (e.g., "Industry").
criteria: The condition to sum (e.g., "Bollywood").
sum_range: The column to sum (e.g., "Revenue in INR").
Average Bollywood Revenue: This is the simple average of the revenues from Bollywood movies.
Percentage of Bollywood Revenue: This is calculated by dividing the total Bollywood revenue by
the total revenue from all movies.
Formula: (Total Bollywood Revenue / Total Revenue) * 100
Page 6
Created by Turbolearn AI
Factors Affecting Revenue
Currency Conversion: Hollywood movies may generate more revenue in USD due to currency
conversion rates (e.g., 1 USD = 80 INR).
Data Completeness: The accuracy of the analysis depends on the completeness of the dataset. If
the dataset is incomplete, then the analysis will be inaccurate.
Profit and Loss Statement Basics
A profit and loss (P&L) statement is a financial statement that summarizes the revenues, costs, and
expenses incurred during a specific period.
The profit and loss statement, also known as the income statement, calculates net income by
subtracting total expenses from total revenues.
P rof it = Revenue − Expenses
Revenue: The income generated from business activities (e.g., movie ticket sales).
Expenses: The costs incurred to generate revenue (e.g., movie budget).
Profit: The amount remaining after deducting expenses from revenue.
Loss: Occurs when expenses exceed revenue.
Example: Marvel Studios
Revenue: 66.53 (million/billion)
Expenses: Total costs incurred
Loss: 16 (Expenses > Revenue)
Net Profit: Profit after deducting taxes.
Marvel Studios Financial Analysis
Analyzing Marvel Studios' financials involves calculating revenue, budget, profit, and profit/loss
percentage.
Total Marvel Revenue
To calculate the total revenue from Marvel movies, use the SUMIF function.
Formula: SUMIF(range, criteria, sum_range)
range:The column to filter (e.g., "Studio").
criteria: The condition to filter by (e.g., "Marvel Studios").
sum_range: The column to sum (e.g., "Revenue USD").
Total Marvel Budget
Similar to revenue, use the SUMIF function to calculate the total budget for Marvel movies.
Page 7
Created by Turbolearn AI
Profit Calculation
Formula:
P rof it = Revenue − Budget
Percentage Profit/Loss
Formula:
P ercentage P rof it/Loss = (P rof it/Budget) ∗ 100
This is calculated with respect to the budget, reflecting the return on investment for the
movie producer.
Data Selection Consistency
When selecting data ranges in Excel formulas, be consistent. Select either the data rows only or the
entire column (including the header) to avoid calculation errors.
Profit and Loss (P&L) Statement Basics
Calculating Profit Percentage
To calculate profit percentage:
Profit Percentage = (Profit / Budget) * 100
Example: If your profit is 7, 100andyourbudgetis2,000, the profit percentage is:
(7100 / 2000) * 100 = 355%
Understanding Market Share
Market share is the percentage of total revenue earned by a company within a specific
market. It's visualized as a piece of a pie, indicating how much revenue comes from a
particular entity compared to the total market revenue.
To calculate market share:
1. Calculate the total revenue of the entire market (e.g., all Hollywood movies). You can use the
SUMIF function to find the sum of revenues for all records.
2. Determine the company's total revenue (e.g., Marvel's total revenue).
3. Divide the company's revenue by the total market revenue.
Page 8
Created by Turbolearn AI
Market Share = (Company Revenue / Total Market Revenue) * 100
Example: If Marvel's total revenue is 20millionandthetotalH ollywoodrevenueis100 million, then:
Market Share = (20 / 100) * 100 = 20%
This means Marvel's market share is 20%, representing its portion of the total Hollywood revenue pie.
Business Communication for Analysts
When communicating insights, use business-friendly language. Instead of saying "Marvel's revenue is
35%," try:
"Marvel's revenue is almost one third of the total Hollywood industry."
"Marvel's revenue is almost half of what is generated by the entire Hollywood industry."
This helps stakeholders understand the information more effectively.
Real-World Examples of Market Share
Indian Wireless Companies: Reliance Jio vs. Airtel
If Reliance Jio has a 53.92% market share, and Airtel has 26%, it means that for every 100
rupees of revenue generated by the Indian wireless industry, approximately 53.92 rupees go
to Reliance Jio and 26 rupees go to Airtel.
Indian Food Delivery Services: Zomato vs. Swiggy
Zomato and Swiggy cover 80% of the online food delivery market in India, with Uber Eats at
12% and others at 8%.
Targets and Actuals
Targets are financial goals set at the beginning of a business cycle. Actuals are the real
numbers achieved. Comparing targets with actuals helps evaluate performance.
Example Scenario
Metric Target Actual Difference (Target - Actual)
Revenue $8,000 $9,000 $1,000
Budget $2,000 $1,000 $1,000
Profit Goal $6,000 $11,000 $5,000
Market Share 55% 49% -6%
Page 9
Created by Turbolearn AI
Interpretation
Revenue: Achieved $1,000 above the target.
Budget: Spent $1,000 less than the target.
Profit: Achieved $5,000 more than the profit goal.
Market Share: Fell 6% short of the target.
Financial Statement Conventions
In financial statements, numbers in brackets usually indicate negative values.
Bonuses and Evaluation
Bonuses for sales, marketing, and engineering teams are often based on achieving targets. If targets are
exceeded, bonuses are distributed. If targets are not met, evaluations are conducted to identify areas for
improvement.
The "surprise factor" indicates how much a company exceeded its revenue target, influencing stock
prices.
Surprise Factor = ((Actual Revenue - Target Revenue) / Target Revenue) * 100
Example: If a company's revenue target was 100millionbuttheyachieved120 million:
Surprise Factor = (($120 - $100) / $100) * 100 = 20%
Conditional Formatting
Conditional formatting can highlight metrics that fall below a certain threshold. For example, if a market
share target is missed by more than 10%, it can be highlighted in red to draw attention to the
underperforming area.
Basic Statistics
Basic statistics (mean, median, mode) are sufficient for 80% of data analysis work, especially for data
analysts and Excel analysts. Advanced topics like hypothesis testing and chi-square tests are more
relevant for data scientists.
For instance, if you want to open a luxurious car showroom, you would analyze the income levels of the
town's residents using mean or median income to determine if the population can afford luxury cars.
Descriptive Analytics: Mean, Median, and Mode
Page 10
Created by Turbolearn AI
When opening a new store, such as a luxurious car showroom, income analysis is a usual industry use
case.
Calculating the Mean
The mean, also known as the average, is calculated by summing all the values in a dataset and dividing
by the total count.
M ean = (Sum of all values)/(T otal count)
For example, if six people in a town have the following monthly incomes:
5, 000,6,000, 5, 500,7,000, 6, 500,8,000
The mean income would be:
5,000 + 6, 000+5,500 + 7, 000+6,500 + 8, 000)/6 =6,333.33$
(
If the mean income is not high enough to afford luxurious cars, you would not open a showroom in that
town.
Handling Outliers with the Median
Outliers are extreme values that can skew the average. For example, if Elon Musk lives in the town and
earns $10,000,000 per month, the average income would not represent the true picture.
In such cases, the median is a better measure. The median is the middle value in a sorted dataset.
1. Sort the numbers in ascending order.
2. If the number of data points is odd, the median is the middle number.
3. If the number of data points is even, the median is the average of the two middle numbers.
For example, if the incomes are:
5, 000,5,500, 6, 000,6,500, 7, 000,8,000
The median would be the average of 6, 000and6,500, which is $6,250.
Determining the Mode
The mode is the most frequently occurring value in a dataset.
Mode: the most frequently occurring value in a dataset.
For example, when deciding on a restaurant with friends, a survey might reveal the following
preferences:
Page 11
Created by Turbolearn AI
South Indian: 2
Chinese: 1
Italian: 2
Mexican: 3
The mode is Mexican, as it is the most frequently chosen option.
Using Mean, Median, and Mode in Excel
In Excel, you can use the following formulas:
Mean: =AVERAGE(table_name[column_name])
Median: =MEDIAN(table_name[column_name])
Mode: =MODE(table_name[column_name])
For example, to calculate the mean, median, and mode of IMDb ratings in a movie dataset, you would
use:
Mean: =AVERAGE(movies[IMDb rating])
Median: =MEDIAN(movies[IMDb rating])
Mode: =MODE(movies[IMDb rating])
Variance and Standard Deviation
Variance measures how far each number is from each other in a given data set.
Variance: measures how far each number is from each other in a given data set.
Understanding Variance with an Example
Consider two imaginary countries, Juganda and Krans, each with eight people. The income levels (in
thousands of US dollars) are as follows:
Person Juganda Krans
1 71 42
2 65 55
3 58 60
4 60 61
5 62 63
6 64 64
7 61 70
8 55 91
Mean 62 62
Although the average income in both countries is the same ($62,000), the income inequality is higher in
Krans. In Krans, the data points are far apart, indicating a higher variance.
Page 12
Created by Turbolearn AI
Calculating Variance
1. Calculate the mean of the dataset.
2. Find the difference between each data point and the mean.
3. Square each of these differences.
4. Sum the squared differences.
5. Divide the sum by the total count of data points.
Why Square the Differences?
Squaring the differences serves two primary purposes:
1. Ensures all values are positive, avoiding the cancellation of positive and negative differences.
2. Penalizes larger differences more heavily.
For example, consider a class that starts at 9:00 AM.
Arriving at 9:05 AM might result in one round around the football ground as punishment.
Arriving at 9:10 AM might result in five rounds.
Arriving at 9:15 AM might result in twenty rounds.
This increasing penalty illustrates how squaring the differences penalizes larger deviations
more significantly, providing a better measure of variance.
Variance and Standard Deviation
Understanding Variance
Variance is a measure of how spread out a set of data points are. It's calculated using the following
formula:
2
∑(xi−μ)
V ariance =
n
Where:
xi is each individual data point.
μ is the mean of the data set.
n is the number of data points.
The formula involves subtracting the mean from each data point, squaring the result, summing these
squared differences, and then dividing by the number of data points.
Interpreting Variance
If we calculate a variance of 13,895, it's not immediately clear what that number represents in real
terms. Because the values are squared, the number is on a different scale. To make sense of it, we need
to scale it back down using the square root.
Page 13
Created by Turbolearn AI
Standard Deviation
Standard deviation is the square root of the variance. It provides a more interpretable measure of data
spread.
StandardDeviation = √ V ariance
For example:
If the variance of income in one city (e.g., Trenton) is calculated, and the standard deviation is
around 5, 000, thismeansthatonaverage, aperson sincomeis5,000 plus or minus the mean income.
′
In contrast, if the standard deviation of income in another city (e.g., Juwanda) is $30,000, incomes
there vary more widely from the average.
Real-Life Applications
Stock Market Volatility
Variance and standard deviation are useful in assessing risk. For instance, if you're choosing between
two stocks, Tata Motors and Tesla, and both have an average price of
100overthelastmonth, youmightconsidervariance. I f T esla sstockpricef luctuateswildly(e. g. ,100, 200,
′
300, 200), whileT ataM otors stockremainsmorestable(e. g. ,100, 99,104), Tesla has higher variance. In
′
this scenario, Tata Motors might be the less risky investment.
School Selection
Variance can also be applied to academic performance. If you're choosing between two schools for your
child, Daily Public School and New York Public School, and both have an average 10th-grade score of
62%, you can look at the variance. If Daily Public School has scores that vary widely (some students
score very high, others very low), while New York Public School has more consistent scores, the lower
variance at New York Public School might suggest better overall teaching quality.
Excel Formulas for Variance and Standard Deviation
In Excel, you can use the following formulas:
Variance for the entire population: VAR.P
Standard deviation for the entire population: STDEV.P
Example: If you have a dataset of movie IMDb ratings, you can calculate the variance and standard
deviation to see how consistent the ratings are. A lower standard deviation indicates that the ratings are
clustered closer to the mean.
Correlation
Page 14
Created by Turbolearn AI
Definition of Correlation
Correlation measures how related two variables are to each other.
Positive Correlation
In the context of real estate, there's a positive correlation between the square footage of a property and
its price. As the square footage increases, the price tends to increase as well. A correlation value close to
1 indicates a strong positive relationship. For instance, a correlation of 0.86 between square footage and
price suggests a strong relationship.
Visualizing Correlation
Scatter plots can visually represent correlation:
If the data points cluster closely around a straight line, it indicates a strong correlation.
In a positive correlation, as one variable increases, the other tends to increase as well, and the line
slopes upward.
Example: Circle Area and Radius
There is a strong positive correlation between the radius of a circle and its area. As the radius increases,
the area increases as well.
The formula for a circle's area is:
2
Area = πr
No Correlation
No correlation indicates that there is no meaningful relationship between the two variables.
Example: Height and Blood Sugar
There is little to no correlation between a person's height and their fasting blood sugar level. One does
not influence the other.
Negative Correlation
Negative correlation indicates that as one variable increases, the other variable tends to decrease.
Example: Commute Time and Employee Happiness
Page 15
Created by Turbolearn AI
There is a negative correlation between commute time and employee happiness. As commute time
increases, employee happiness tends to decrease.
Types of Correlations
Type of Correlation Description
Strong Positive As one variable increases, the other variable increases significantly.
Strong Negative As one variable increases, the other variable decreases significantly.
Real-Life Applications
Correlations can be helpful in many scenarios, such as investment decisions.## Portfolio Balancing with
Correlation
When making investment decisions, it's important to consider the correlation between different assets
to manage risk.
Negative Correlation
Negative correlation means that two assets tend to move in opposite directions.
For example, stocks and gold often exhibit a negative correlation. When the stock market is performing
well, investors tend to shift their money into stocks, causing the price of gold to decrease. Conversely,
when the stock market declines, investors may seek the safety of gold, driving its price up.
Hedging
Financial advisors often recommend hedging to balance a portfolio.
Hedging involves investing in a mix of assets with negative correlations to reduce overall risk.
By allocating investments across stocks and gold, an investor can create a balanced portfolio that
performs consistently, regardless of market conditions. If the stock market declines, gains in gold can
offset losses in stocks, and vice versa.
Risk Tolerance
An investor's risk tolerance should guide their investment strategy.
High-Risk Tolerance: An investor who is comfortable with risk may choose to invest a larger
portion of their portfolio in stocks.
Low-Risk Tolerance: An investor who prefers a steady return may opt for a portfolio with a higher
allocation to gold, cash, and bonds (debt instruments).
Page 16
Created by Turbolearn AI
Portfolio Composition
Here's a summary of portfolio composition based on risk tolerance:
Investor Type Investment Strategy
High-Risk
Invest a larger percentage in stocks for potentially higher returns.
Tolerance
Low-Risk Invest in assets with negative correlations, such as gold, cash, and bonds, for a more
Tolerance stable and predictable return.
In general, if you prefer steady returns, it's important to have negative correlation between the assets in
your portfolio.
Continuous Learning
The field of data analysis and data science is constantly evolving. It's essential to adopt a student
mindset and continuously update your skills. When encountering new topics, take the time to pause,
research, and experiment with tools like Excel and Python to improve your understanding.
Page 17