QMB 3302 - Week 4 Study Notes

What-if Analysis and Breakeven Analysis

• Many business problems require analysis to find the appropriate level of some activity. This could be to:
  • Maximize profit
  • Minimize cost
  • Achieve breakeven (no profit, no loss)

Example 1: Breakeven Analysis at Quality Sweaters

• Quality Sweaters Company specializes in selling hand-knitted sweaters.
• The company plans to:
  • Print a $20,000 catalog plus $0.10 per catalog printed.
  • Mail catalogs at $0.15 each (includes postage and marketing data).
  • Include a direct reply envelope costing $0.20 per envelope used by the respondent.
• Average customer order value: $40
• Variable cost per order (labor/material): 80% of order value = $32
• Total catalogs planned to mail: 100,000

Key Questions for Analysis:

• How does a change in response rate impact profit?
• For what response rate does the company break even?

Creating the Spreadsheet Model for Breakeven Analysis

• To create a range name in Excel:
  • Highlight the desired cell(s)
  • Click the Name Box next to the Formula Bar and type a name.
• To use a range name:
  • Select a cell (e.g., cell G2), access the Use in Formula dropdown in the Formulas ribbon, and select Paste List.

Impact of Response Rate on Profit

• A data table, known as a what-if table, can show the effect of changes in inputs on outputs.
• A one-way data table can be set up to examine the effect of response rate (input) on profit (output).

Determining the Company's Breakeven Point

• Breakeven point lies between 5% and 6% response rates.
• To find the exact breakeven point using Goal Seek in Excel:
  • Go to What-If Analysis dropdown and select Goal Seek. Fill out the dialog box:
  • Set cell: Profit
  • To value: 0
  • By changing cell: Response_rate
• Result from Goal Seek:
  • Respond Rate: 5.77%, Profit: $0
  • If response rate > 5.77%, the company makes a profit.
  • If response rate < 5.77%, the company incurs a loss.

Formula Auditing Tool in Excel

• The Formula Auditing tool helps visualize relationships between parts in the spreadsheet model.
• Example: Traces dependents related to the Number of responses in cell E5, impacting:
  • Total Revenue (cell E8)
  • Total Variable Cost of Orders (cell E11)

Descriptive Analytics Overview

• Explanation of Descriptive Statistics:
  • Summarizing data for a categorical variable.
  • Utilizes labels or names for categories (e.g., types of soft drinks).
  • Summarizing data for quantitative variables utilizing numerical values (e.g., Age).
  • Summarizing data for two variables to understand their relationships.

Summarizing Categorical Data

• Types of Data Summarization:
  • Frequency Distribution
  • Relative Frequency Distribution
  • Percent Frequency Distribution
  • Graphical Representations: Bar Chart, Pie Chart

Frequency Distribution

• A tabular summary showing the frequency (number of observations) for each category.
• Provides insights that visual observation of raw data cannot yield.

Example: Marada Inn Quality Ratings

• Guests rated accommodations as:
  • Excellent
  • Above Average
  • Average
  • Below Average
  • Poor

Relative and Percent Frequency Distributions

• Relative Frequency: Proportion of total data items in each class.
• Percent Frequency: Relative frequency expressed as a percentage.
  • Example: For a class with 1/20 items, relative frequency is 0.05, and percent frequency is 5%.

Bar Chart Representation

• Displays qualitative data effectively:
  • One axis for class labels, another for frequency or percent.
  • Fixed-width bars above class labels, separated to reflect distinct categories.

Pie Chart Representation

• Graphical display for relative and percent frequency distributions:
  • Circle subdivided using relative frequencies for each class.
  • E.g., A relative frequency of 0.25 corresponds to 90 degrees in the pie chart.

In-Class Exercise: Excel

• Students to download an Excel exercise file from Canvas to practice.

Summarizing Quantitative Data

• Methods include:
  • Frequency Distribution
  • Relative Frequency and Percent Frequency Distributions
  • Histogram
  • Cumulative Distributions.

Frequency Distribution - Quantitative Data Example

• Study of parts costs for engine tune-ups based on 50 customer invoices focusing on:
  1. Non-overlapping classes
  2. Width of each class
  3. Class limits

Guidelines for Class Limits

• Each data point must belong to one class only:
  • Lower class limit: smallest data value in the class
  • Upper class limit: largest data value in the class
• Open-end classes only require one limit.

Relative Frequency Insights from Hudson Auto Repair Example

• Analysis indicates specific cost ranges and their frequencies (e.g., 32% of costs are in $70-$79 class).

Cumulative Distributions

• Cumulative frequency distribution represents the count of items with values ≤ upper limit.
• Cumulative relative distributions show the proportion below limits, while cumulative percent shows percentage.

Histogram Explanation

• Graphical representation of quantitative data:
  • Variable on horizontal axis, rectangles above each class interval corresponding to frequency.
  • No gaps between rectangles, unlike bar graphs.

Skewness in Histograms

• Types of Skewness:
  • Moderately Skewed Left (tail on left)
  • Symmetrical (equal tails)
  • Moderately Right Skewed (tail on right)

In-Class Exercise: Excel

• Another exercise is accessible via Canvas.

Summarizing Two Variables - Crosstabulation

• Method for understanding relationships between two variables:
  • Can be used for categorical with quantitative, both categorical, or both quantitative variables.
• The table’s margins define classes for each variable.

Example: Finger Lakes Homes

• Homes sold across styles and prices analyzed:
  • Highest count: split-level under $250,000.
  • Very few A-Frame homes priced > $250,000.

Crosstabulation - Row Percentages

• Converting entries to percentages for better insight:
  • Example of row percentages revealing relationships.

Crosstabulation - Column Percentages

• Calculating column percentages:
  • Example using home styles and their prices under and over $250,000.

Simpson’s Paradox in Crosstabulation

• Caution in summarizing data: aggregated data can reverse conclusions observed in unaggregated data.

Summarizing Two Variables - Scatter Diagrams

• Graphical presentation for two quantitative variable relationships:
  • One variable on horizontal and another on vertical axis.
  • Points suggest overall relationship, with trendline showing approximation.

Example: Panthers Football Team

• Analyzing relationship between interceptions and points scored.
• Insights:
  • The data shows a positive correlation; as interceptions rise, points scored also rise, though not perfectly linear.

Tabular and Graphical Methods Summary

• Methods comparing categorical and quantitative data with various tabular and graphical approaches.

Descriptive Statistics: Numerical Measures Recap

• Key Measures:
  • Central Tendency (Location) Measures
  • Variability (Dispersion) Measures
  • Sample Statistics vs. Population Parameters (Sample statistic = point estimator for population parameter).

Mean (Average)

• Critical measure of central location:
  • Defined as the total of all data values divided by the number of values.
  • Sample mean denoted as $ar{x}$, population mean as $bc$.

Median Definition

• Value separating the higher half from the lower half of data:
• Most effective when dealing with extreme values.
• Examples provided with odd and even number observations to illustrate calculation.

Mode Explanation

• The mode represents the most frequently occurring value in a dataset:
  • Data can be unimodal, bimodal, or multimodal based on frequency distribution.

Percentiles Overview

• A percentile indicates how data is spread across the range from smallest to largest:
  • The p-th percentile has at least p percent of the values at or below it.
  • Steps involve arranging data in ascending order and locating p-th percentile.

Quartiles Explanation

• Quartiles are specific percentiles that segment data:
  • 1st Quartile = 25th Percentile
  • 2nd Quartile = 50th Percentile = Median
  • 3rd Quartile = 75th Percentile

Measures of Variability

• Important to assess variability alongside central measures:
  • Common measures of variability include:
  1. Range
  2. Interquartile Range (IQR)
  3. Variance
  4. Standard Deviation

Range Definition

• The difference between maximum and minimum values:
  • Calculation: Range = largest value - smallest value

Interquartile Range (IQR) Explanation

• Difference between the first and third quartiles:
  • Represents the spread of the middle 50% of data, less sensitive to extremes.

Variance Definition

• Measures how much the data varies from the mean:
  • Sample variance considers sample's differences and requires an adjustment (n-1).

Standard Deviation Definition

• The standard deviation is the square root of the variance, facilitating easier interpretation due to compatible units with original data.

Z-Score Understanding

• Standardized value indicating how many standard deviations an observation is from the mean:
  • A value < mean gives negative z-score, = mean gives 0, and > mean gives positive.
  • Excel’s STANDARDIZE function can compute z-scores.

Detecting Outliers

• An outlier possesses a z-score < -3 or > +3, indicating an unusual value:
  • May result from errors in data recording or legitimate variability.

Summary of Data Functions in Excel

• Functions available for various measures in Excel:
  • Mean: =average(data_range)
  • Median: =median(data_range)
  • Mode: =mode(data_range)
  • Percentile: =percentile(data_range, p/100)
  • Quartiles: =quartile(data_range, 1) and =quartile(data_range, 3)
  • Range: =max(data_range) - min(data_range)
  • IQR: =quartile(data_range, 3) - quartile(data_range, 1)
  • Variance: =varp(data_range) / =var(data_range)
  • Standard Deviation: =stdevp(data_range) / =stdev(data_range)