---
title: "Normal Distribution: Industry Applications"
subtitle: "Complete Excel Answer Key with Formulas"
author: "Professor Bongo Adi"
date: today
date-format: "MMMM D, YYYY"
format:
html:
theme: cosmo
toc: true
toc-depth: 3
toc-location: left
toc-title: "📋 Excel Answer Key"
number-sections: true
number-depth: 3
code-fold: true # ← CHANGED (hides code by default)
code-tools: true # ← Keeps the button/menu at the top
code-summary: "📝 Show Quarto/R Code"
highlight-style: github
fig-width: 8
fig-height: 5
fig-align: center
smooth-scroll: true
anchor-sections: true
html-math-method: mathjax
citations-hover: true
footnotes-hover: true
self-contained: true
css: styles.css
---
```{r}
#| label: setup
#| include: false
#| echo: false
library(tidyverse)
library(knitr)
library(kableExtra)
library(ggplot2)
library(patchwork)
library(scales)
theme_set(
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(face = "bold", color = "#2C3E50", size = 15),
plot.subtitle = element_text(color = "#7F8C8D", size = 11),
axis.title = element_text(color = "#2C3E50"),
panel.grid.minor = element_blank(),
plot.background = element_rect(fill = "white", color = NA)
)
)
# Color palette
palette <- c(
primary = "#2980B9",
secondary = "#E74C3C",
accent = "#27AE60",
warning = "#F39C12",
purple = "#8E44AD"
)
# Function to create Excel-style table
excel_table <- function(data, caption = NULL) {
data |>
kbl(caption = caption, align = c("l", "c", "c", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE,
font_size = 13
) |>
column_spec(1, bold = TRUE, background = "#ECF0F1", color = "#2C3E50") |>
column_spec(2, bold = TRUE, color = "#2980B9") |>
column_spec(3, background = "#F8F9F9", italic = TRUE) |>
column_spec(4, color = "#7F8C8D")
}
```
::: {.callout-tip appearance="minimal" icon=true}
## 📊 Excel Master Answer Key
This document provides **complete Excel formulas** for solving all normal distribution business problems. Each section includes:
- 🧮 **Excel function syntax** with cell references
- 📈 **Step-by-step calculations**
- 💡 **Solver/Goal Seek applications**
- 📱 **Copy-paste ready formulas**
- 🔍 **Validation checks**
:::
***
# Excel Functions Reference {#sec-excel-functions .unnumbered}
```{r}
#| label: tbl-excel-functions
#| tbl-cap: "Essential Excel Functions for Normal Distribution"
#| echo: false
tibble(
`Function` = c(
"`NORM.DIST(x, mean, std_dev, cumulative)`",
"`NORM.INV(probability, mean, std_dev)`",
"`NORM.S.DIST(z, cumulative)`",
"`NORM.S.INV(probability)`",
"`STANDARDIZE(x, mean, std_dev)`",
"`SQRT(number)`",
"`ABS(number)`"
),
`Description` = c(
"Returns probability at x (TRUE=cumulative, FALSE=PDF)",
"Returns x-value for given probability (inverse)",
"Standard normal distribution (mean=0, std=1)",
"Returns z-score for probability",
"Calculates z-score: (x-mean)/std_dev",
"Square root function",
"Absolute value"
),
`Example` = c(
"`=NORM.DIST(500, 500, 5, TRUE)` returns 0.5",
"`=NORM.INV(0.95, 35, 8)` returns 48.16",
"`=NORM.S.DIST(1.96, TRUE)` returns 0.975",
"`=NORM.S.INV(0.975)` returns 1.96",
"`=STANDARDIZE(515, 500, 5)` returns 3.00",
"`=SQRT(5)` returns 2.236",
"`=ABS(-2.5)` returns 2.5"
)
) |>
kbl(align = c("l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5", color = "#2C3E50") |>
column_spec(2, background = "#FEF9E7") |>
column_spec(3, background = "#EBF5FB", italic = TRUE)
```
***
# Manufacturing & Quality Control {#sec-manufacturing}
## Exercise 1 — Bottling Plant FDA Compliance {#sec-ex1-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex1-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Bottling Plant Analysis"
#| echo: false
tibble(
Cell = c("B2", "B3", "B4", "B5", "B6", "C2", "C3", "C4", "C5", "C6"),
`Label` = c(
"Mean fill volume (μ)", "Std deviation (σ)", "Lower bound", "Upper bound", "Daily production",
"Value", "Value", "Value", "Value", "Value"
),
`Excel Formula/Value` = c(
"← Enter", "← Enter", "← Enter", "← Enter", "← Enter",
"500", "5", "490", "515", "50000"
),
`Note` = c(
"ml", "ml", "FDA minimum", "FDA maximum", "bottles/day",
"", "", "", "", "" # Added 5 empty entries to fix the size mismatch
)
) |> excel_table()
```
### 🧮 Task 1 — Compliance Probability
```{r}
#| label: tbl-ex1-task1
#| tbl-cap: "Excel Formulas for Compliance Probability"
tibble(
Cell = c("B8", "B9", "B10", "B11", "B12"),
`Calculation` = c(
"Z-score (lower)", "Z-score (upper)", "P(compliant) - Method 1",
"P(compliant) - Method 2", "Compliance rate (%)"
),
`Excel Formula` = c(
"=(C4-C2)/C3",
"=(C5-C2)/C3",
"=NORM.DIST(C5,C2,C3,TRUE)-NORM.DIST(C4,C2,C3,TRUE)",
"=NORM.S.DIST(B9,TRUE)-NORM.S.DIST(B8,TRUE)",
"=B10*100"
),
`Result` = c(
"-2.00", "+3.00", "0.9759", "0.9759", "97.59%"
)
) |> excel_table()
```
### 📊 Task 2 — Expected Daily Failures
```{r}
#| label: tbl-ex1-task2
#| tbl-cap: "Excel Formulas for Failure Analysis"
tibble(
Cell = c("B14", "B15", "B16", "B17"),
`Calculation` = c(
"Failure probability", "Daily failures", "Annual failures (250 days)",
"Annual waste cost ($0.50/bottle)"
),
`Excel Formula` = c(
"=1-B10",
"=C6*B14",
"=B15*250",
"=B16*0.5"
),
`Result` = c(
"0.0241", "1,205", "301,250", "$150,625"
)
) |> excel_table()
```
### 🎯 Task 3 — Managerial Decision: Achieve < 1% Waste
```{r}
#| label: tbl-ex1-task3
#| tbl-cap: "Excel Formulas for Process Improvement"
#| echo: false
tibble(
`Option` = c("Option A: Shift Mean", "Option A: Shift Mean", "Option B: Reduce σ", "Option B: Reduce σ"),
`Calculation` = c(
"Target compliance", "Required μ (using Goal Seek)",
"Target compliance", "Required σ (using Goal Seek)"
),
`Excel Formula` = c(
"=0.99",
"**Goal Seek:** Set B10=0.99 by changing C2",
"=0.99",
"**Goal Seek:** Set B10=0.99 by changing C3"
),
`Result` = c(
"99%", "μ ≈ 502.3 ml", "99%", "σ ≈ 4.33 ml"
)
) |>
kbl(caption = "Using Goal Seek for Process Optimization", align = c("l", "l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, italic = TRUE) |>
column_spec(4, bold = TRUE, color = "#27AE60")
```
::: {.callout-note collapse="false"}
## 🔍 Goal Seek Instructions
**To shift the mean (Option A):**
1. Go to **Data → What-If Analysis → Goal Seek**
2. **Set cell:** `B10` (compliance probability)
3. **To value:** `0.99`
4. **By changing cell:** `C2` (mean)
5. Click **OK**
**To reduce standard deviation (Option B):**
1. **Set cell:** `B10` (compliance probability)
2. **To value:** `0.99`
3. **By changing cell:** `C3` (std deviation)
4. Click **OK**
:::
***
## Exercise 2 — Six Sigma Process Capability {#sec-ex2-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex2-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Six Sigma Analysis"
tibble(
Cell = c("B2", "B3", "B4", "B5", "C2", "C3", "C4", "C5"),
`Label` = c("Mean (μ)", "Std Dev (σ)", "LSL", "USL", "Value", "Value", "Value", "Value"),
`Excel Formula/Value` = c("← Enter", "← Enter", "← Enter", "← Enter", "100", "2", "94", "106"),
`Note` = c("ohms", "ohms", "Lower Spec Limit", "Upper Spec Limit", "", "", "", "")
) |> excel_table()
```
### 🧮 Task 1 — Process Capability Indices
```{r}
#| label: tbl-ex2-task1
#| tbl-cap: "Excel Formulas for Process Capability"
tibble(
Cell = c("B7", "B8", "B9", "B10", "B11"),
`Calculation` = c(
"Process Capability (Cp)",
"Upper Cpk",
"Lower Cpk",
"Overall Cpk",
"Process centered? (Y/N)"
),
`Excel Formula` = c(
"=(C5-C4)/(6*C3)",
"=(C5-C2)/(3*C3)",
"=(C2-C4)/(3*C3)",
"=MIN(B8:B9)",
"=IF(B8=B9,\"Yes\",\"No\")"
),
`Result` = c(
"1.00", "1.00", "1.00", "1.00", "Yes"
)
) |> excel_table()
```
### 📊 Task 2 — Defect Rate and Sigma Level
```{r}
#| label: tbl-ex2-task2
#| tbl-cap: "Excel Formulas for Defect Analysis"
tibble(
Cell = c("B13", "B14", "B15", "B16", "B17"),
`Calculation` = c(
"Acceptance probability",
"Defect probability",
"Defects per million (DPMO)",
"Sigma level (one-tailed)",
"Sigma level (two-tailed)"
),
`Excel Formula` = c(
"=NORM.DIST(C5,C2,C3,TRUE)-NORM.DIST(C4,C2,C3,TRUE)",
"=1-B13",
"=B14*1000000",
"=NORM.S.INV(1-B14)",
"=NORM.S.INV(1-B14/2)"
),
`Result` = c(
"0.9973", "0.0027", "2,700", "2.78", "3.00"
)
) |> excel_table()
```
::: {.callout-tip}
## 📈 Excel Visualization Formula
To create a process capability chart in Excel:
```excel
Column X: =SEQUENCE(80, 1, 90, 0.25) # From 90 to 110
Column Y: =NORM.DIST(X#, C2, C3, FALSE)
Add vertical lines at LSL and USL:
=IF(X#=C4, MAX(Y#), NA())
=IF(X#=C5, MAX(Y#), NA())
```
Use **Scatter with Smooth Lines** chart type.
:::
***
# Healthcare & Operations Management {#sec-healthcare-excel}
## Exercise 3 — ER Staffing Optimization {#sec-ex3-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex3-excel-setup
#| tbl-cap: "Excel Worksheet Setup for ER Staffing Analysis"
tibble(
Cell = c("B2", "B3", "B4", "B5", "B6", "C2", "C3", "C4", "C5", "C6"),
`Label` = c(
"Mean wait (μ)", "Std Dev current (σ)", "Std Dev with nurse", "Nurse annual cost",
"Patients/day", "Value", "Value", "Value", "Value", "Value"
),
`Excel Formula/Value` = c(
"← Enter", "← Enter", "← Enter", "← Enter", "← Enter",
"35", "8", "5", "80000", "100"
),
`Note` = c(
"minutes", "minutes", "minutes", "$", "patients",
"", "", "", "", "" # Added these 5 to reach size 10
)
) |> excel_table()
```
### 🧮 Task 1 — 95th Percentile Threshold
```{r}
#| label: tbl-ex3-task1
#| tbl-cap: "Excel Formulas for Wait Time Thresholds"
tibble(
Cell = c("B8", "B9"),
`Calculation` = c("95th percentile threshold", "Fast-track cutoff (30th pct)"),
`Excel Formula` = c(
"=NORM.INV(0.95, C2, C3)",
"=NORM.INV(0.30, C2, C3)"
),
`Result` = c("48.16 minutes", "30.80 minutes")
) |> excel_table()
```
### 📊 Task 2 — Fast-Track System
```{r}
#| label: tbl-ex3-task2
#| tbl-cap: "Excel Formulas for Fast-Track Analysis"
tibble(
Cell = c("B11", "B12", "B13"),
`Calculation` = c(
"Fast-track patients (%)",
"Daily fast-track patients",
"Annual fast-track patients"
),
`Excel Formula` = c(
"=0.30",
"=C6*B11",
"=B12*365"
),
`Result` = c("30%", "30 patients/day", "10,950 patients/year")
) |> excel_table()
```
### 💰 Task 3 — Investment Justification
```{r}
#| label: tbl-ex3-task3
#| tbl-cap: "Excel Formulas for Nurse Investment Analysis"
tibble(
`Scenario` = c("Current", "With New Nurse", "Difference", "Difference", "Difference"),
`Calculation` = c(
"P(wait > 45 min)",
"P(wait > 45 min)",
"Improvement (pp)",
"Annual patients helped",
"Cost per patient helped"
),
`Excel Formula` = c(
"=1-NORM.DIST(45, C2, C3, TRUE)",
"=1-NORM.DIST(45, C2, C4, TRUE)",
"=B18-B19",
"=C6*365*B20",
"=C5/B21"
),
`Result` = c(
"10.56%", "2.28%", "8.28 ppts", "3,022", "$26.47"
)
) |>
kbl(caption = "Investment Analysis for Additional Nurse", align = c("l", "l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE) |>
column_spec(4, bold = TRUE, color = "#27AE60") |>
row_spec(3, background = "#D5F5E3")
```
::: {.callout-important}
## 📊 Excel Dashboard Formula
Create a decision dashboard in Excel:
```excel
B25: "Recommendation:"
C25: =IF(B21>2000, "HIRE NURSE", "DO NOT HIRE")
D25: =IF(B21>2000, "✅", "❌")
Conditional formatting:
Select C25 → Conditional Formatting → Highlight Cell Rules
Text that Contains: "HIRE" → Green fill
Text that Contains: "DO NOT" → Red fill
```
:::
***
# Supply Chain & Inventory Management {#sec-supplychain-excel}
## Exercise 4 — Reorder Point Problem {#sec-ex4-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex4-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Inventory Management"
tibble(
Cell = c("B2", "B3", "B4", "B5", "B6", "C2", "C3", "C4", "C5", "C6"),
`Label` = c(
"Daily mean (μ)", "Daily std dev (σ)", "Lead time (days)", "Stockout cost",
"Holding cost", "Value", "Value", "Value", "Value", "Value"
),
`Excel Formula/Value` = c(
"← Enter", "← Enter", "← Enter", "← Enter", "← Enter",
"200", "25", "5", "50", "2"
),
`Note` = c(
"units/day", "units/day", "days", "$/unit", "$/unit/day",
"", "", "", "", "" # Need these to match the 10 rows above!
)
) |> excel_table()
```
### 🧮 Task 1 — Lead Time Demand Distribution
```{r}
#| label: tbl-ex4-task1
#| tbl-cap: "Excel Formulas for Lead Time Demand"
tibble(
Cell = c("B8", "B9"),
`Calculation` = c("Lead time mean (μ_LT)", "Lead time std dev (σ_LT)"),
`Excel Formula` = c(
"=C2*C4",
"=C3*SQRT(C4)"
),
`Result` = c("1,000 units", "55.90 units")
) |> excel_table()
```
### 📊 Task 2 & 3 — Reorder Points by Service Level
```{r}
#| label: tbl-ex4-task2
#| tbl-cap: "Excel Table Setup for Service Level Analysis"
tibble(
Cell = c("A12:A14", "B12:B14", "C12:C14", "D12:D14", "E12:E14", "F12:F14", "G12:G14"),
`Column Header` = c("Service Level", "Z-score", "Safety Stock", "Reorder Point",
"Annual Holding", "Annual Stockout", "Total Cost"),
`Excel Formula` = c(
"{0.90;0.95;0.99}",
"=NORM.S.INV(A12#)",
"=B12#*$B$9",
"=$B$8+C12#",
"=C12#*$C$6*365",
"**See note**",
"=E12#+F12#"
),
`Note` = c(
"Input values", "Standard normal inverse", "z × σ_LT", "μ_LT + Safety Stock",
"Safety Stock × holding cost × 365", "Complex - see below", "Sum of costs"
)
) |> excel_table()
```
::: {.callout-note collapse="false"}
## 📐 Normal Loss Function in Excel
**Stockout cost calculation requires the normal loss function:**
```excel
In cell F12 (for 90% service level):
=($C$5*($B$9*(NORM.S.DIST(B12,FALSE)-B12*(1-NORM.S.DIST(B12,TRUE))))*(365/$C$4))
```
**Where:**
- `NORM.S.DIST(B12,FALSE)` = φ(z) (PDF)
- `NORM.S.DIST(B12,TRUE)` = Φ(z) (CDF)
- `φ(z) - z*(1-Φ(z))` = Standard normal loss function L(z)
**Complete stockout cost formula:**
```excel
=StockoutCost * (σ_LT * L(z)) * (365/LeadTime)
```
**Alternative: Use helper cell for L(z):**
```excel
H12: =NORM.S.DIST(B12,FALSE)-B12*(1-NORM.S.DIST(B12,TRUE))
F12: =$C$5*$B$9*H12*(365/$C$4)
```
:::
### 🎯 Task 4 — Decision Matrix
```{r}
#| label: tbl-ex4-decision
#| tbl-cap: "Complete Inventory Decision Matrix Formulas"
tibble(
`Service Level` = c("90%", "95%", "99%"),
`Safety Stock Formula` = c("=NORM.S.INV(0.90)*B9", "=NORM.S.INV(0.95)*B9", "=NORM.S.INV(0.99)*B9"),
`Holding Cost Formula` = c("=C12*C6*365", "=C13*C6*365", "=C14*C6*365"),
`Stockout Cost Formula` = c(
"=C5*B9*(NORM.S.DIST(NORM.S.INV(0.90),FALSE)-NORM.S.INV(0.90)*(1-0.90))*(365/C4)",
"=C5*B9*(NORM.S.DIST(NORM.S.INV(0.95),FALSE)-NORM.S.INV(0.95)*(1-0.95))*(365/C4)",
"=C5*B9*(NORM.S.DIST(NORM.S.INV(0.99),FALSE)-NORM.S.INV(0.99)*(1-0.99))*(365/C4)"
),
`Total Cost` = c("$88,039", "$85,395", "$98,291")
) |>
kbl(caption = "Complete Inventory Cost Formulas by Service Level",
align = c("c", "l", "l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE) |>
# Use extra_css to handle the font size for these columns
column_spec(c(2,3,4), background = "#F8F9F9", extra_css = "font-size: 11px;") |>
column_spec(5, bold = TRUE, color = "#2980B9")
```
***
# Finance & Risk Management {#sec-finance-excel}
## Exercise 5 — Portfolio Risk Assessment {#sec-ex5-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex5-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Portfolio Risk"
tibble(
Cell = c("B2", "B3", "B4", "C2", "C3", "C4"),
`Label` = c("Mean return (μ)", "Std Dev (σ)", "Diversified σ", "Value", "Value", "Value"),
`Excel Formula/Value` = c("← Enter", "← Enter", "← Enter", "0.12", "0.08", "0.05"),
`Note` = c(
"annual return", "annual std dev", "diversified std dev",
"", "", "" # Added these 3 to reach size 6
)
) |> excel_table()
```
### 🧮 Task 1 — Probability of Losing Money
```{r}
#| label: tbl-ex5-task1
#| tbl-cap: "Excel Formulas for Portfolio Risk"
tibble(
Cell = c("B6", "B7", "B8", "B9", "B10", "B11"),
`Calculation` = c(
"Probability of loss (original)",
"Value at Risk (5% level)",
"Probability of >10% loss",
"Probability of loss (diversified)",
"VaR (5%) diversified",
"Probability of >10% loss (diversified)"
),
`Excel Formula` = c(
"=NORM.DIST(0, C2, C3, TRUE)",
"=NORM.INV(0.05, C2, C3)",
"=NORM.DIST(-0.10, C2, C3, TRUE)",
"=NORM.DIST(0, C2, C4, TRUE)",
"=NORM.INV(0.05, C2, C4)",
"=NORM.DIST(-0.10, C2, C4, TRUE)"
),
`Result` = c("6.68%", "-1.15%", "0.31%", "0.82%", "3.58%", "0.003%")
) |> excel_table()
```
## Exercise 6 — Loan Default Probability {#sec-ex6-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex6-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Credit Risk"
tibble(
Cell = c("B2", "B3", "B4", "B5", "C2", "C3", "C4", "C5"),
`Label` = c("Mean score (μ)", "Std Dev (σ)", "Approval threshold", "New threshold",
"Value", "Value", "Value", "Value"),
`Excel Formula/Value` = c("← Enter", "← Enter", "← Enter", "← Enter",
"680", "50", "620", "650"),
`Note` = c("credit score", "score std dev", "current minimum", "proposed minimum",
"", "", "", "") # Padded to reach 8 rows
) |> excel_table()
```
### 🧮 Task 1-3 — Loan Approval Analysis
```{r}
#| label: tbl-ex6-analysis
#| tbl-cap: "Excel Formulas for Loan Approval Analysis"
tibble(
`Task` = c("1", "1", "2", "2", "3", "3", "3", "3"),
`Calculation` = c(
"Current approval rate",
"Current approval rate (%)",
"Default risk in approved pool",
"Borderline approvals (620-640)",
"New approval rate",
"Change in approval",
"Monthly revenue impact (10K apps)",
"Annual revenue impact"
),
`Excel Formula` = c(
"=1-NORM.DIST(C4, C2, C3, TRUE)",
"=B6*100",
"=NORM.DIST(580, C2, C3, TRUE)/B6",
"=(NORM.DIST(640, C2, C3, TRUE)-NORM.DIST(620, C2, C3, TRUE))/B6",
"=1-NORM.DIST(C5, C2, C3, TRUE)",
"=B6-B9",
"=10000*B10*500",
"=B11*12"
),
`Result` = c(
"0.8849", "88.49%", "0%", "17.56%",
"0.7257", "-0.1592", "$796,000", "$9,552,000"
)
) |>
kbl(caption = "Complete Loan Portfolio Analysis Formulas",
align = c("c", "l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, width = "5%") |>
column_spec(2, width = "35%") |>
# Replaced font_size = 11 with extra_css
column_spec(3, background = "#F8F9F9", extra_css = "font-size: 11px;") |>
column_spec(4, bold = TRUE, color = "#2980B9", width = "15%")
```
***
# Marketing & Consumer Behavior {#sec-marketing-excel}
## Exercise 7 — Pricing Strategy Optimization {#sec-ex7-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex7-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Pricing Analysis"
tibble(
Cell = c("B2", "B3", "C2", "C3"),
`Label` = c("Mean WTP (μ)", "Std Dev (σ)", "Value", "Value"),
`Excel Formula/Value` = c("← Enter", "← Enter", "85", "18"),
`Note` = c("willingness-to-pay ($)", "std dev of WTP", "", "") # Added 2 empty entries
) |> excel_table()
```
### 🧮 Task 1-3 — Price Point Analysis
```{r}
#| label: tbl-ex7-pricing
#| tbl-cap: "Excel Formulas for Price Optimization"
# Create data for the pricing table
price_points <- c(60, 75, 85, 99, 110)
tibble(
`Price ($)` = price_points,
`Z-score Formula` = paste0("=STANDARDIZE(", price_points, ", $C$2, $C$3)"),
`% Buy Formula` = paste0("=1-NORM.DIST(", price_points, ", $C$2, $C$3, TRUE)"),
`Revenue Index Formula` = paste0("=", price_points, "*C", seq_along(price_points) + 5),
`Results` = c(
"$55.68", "$53.40", "$42.50", "$21.58", "$9.02"
)
) |>
kbl(caption = "Price Point Analysis Formulas",
align = c("c", "l", "l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE) |>
# Fix: use extra_css instead of font_size
column_spec(c(2,3,4), background = "#F8F9F9", extra_css = "font-size: 11px;") |>
column_spec(5, bold = TRUE, color = "#27AE60")
```
### 🎯 Optimal Price Calculation
```{r}
#| label: tbl-ex7-optimal
#| tbl-cap: "Excel Formulas for Finding Optimal Price"
tibble(
`Method` = c("Using Solver", "Using Goal Seek", "Analytical Approximation"),
`Excel Formula/Procedure` = c(
"Set Objective: Maximize Revenue Index\nChanging: Price cell\nConstraints: Price ≥ 0",
"Set cell: d(Revenue)/d(Price) ≈ 0\nTo value: 0\nBy changing: Price",
"=C2 + C3*NORM.S.INV(0.5) # Approx at mean"
),
`Result` = c("$96.50", "$96.50", "$85.00"),
`Note` = c(
"Most accurate", "Good for single variable", "Crude approximation"
)
) |>
kbl(align = c("l", "l", "c", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, background = "#F8F9F9", italic = TRUE) |>
column_spec(3, bold = TRUE, color = "#27AE60")
```
***
# Human Resources Management {#sec-hr-excel}
## Exercise 8 — Employee Performance Tiers {#sec-ex8-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex8-excel-setup
#| tbl-cap: "Excel Worksheet Setup for HR Analysis"
tibble(
Cell = c("B2", "B3", "B4", "B5", "C2", "C3", "C4", "C5"),
`Label` = c("Mean score (μ)", "Std Dev (σ)", "Training mean", "Employees",
"Value", "Value", "Value", "Value"),
`Excel Formula/Value` = c("← Enter", "← Enter", "← Enter", "← Enter",
"72", "12", "78", "500"),
`Note` = c("performance score", "score std dev", "after training", "total employees",
"", "", "", "") # Pad to match 8 rows
) |> excel_table()
```
### 🧮 Task 1 — Score Thresholds
```{r}
#| label: tbl-ex8-thresholds
#| tbl-cap: "Excel Formulas for Performance Thresholds"
#| echo: false
tibble(
`Tier` = c("Executive (Top 10%)", "Bonus (Top 25%)", "PIP (Bottom 15%)"),
`Threshold Formula` = c(
"=NORM.INV(0.90, C2, C3)",
"=NORM.INV(0.75, C2, C3)",
"=NORM.INV(0.15, C2, C3)"
),
`Result` = c("87.38", "80.09", "59.57"),
`Excel Check Formula` = c(
"=1-NORM.DIST(87.38, C2, C3, TRUE)",
"=NORM.DIST(87.38, C2, C3, TRUE)-NORM.DIST(80.09, C2, C3, TRUE)",
"=NORM.DIST(59.57, C2, C3, TRUE)"
)
) |>
kbl(caption = "Performance Tier Threshold Formulas",
align = c("l", "l", "c", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, background = "#F8F9F9", italic = TRUE) |>
column_spec(3, bold = TRUE, color = "#2980B9") |>
column_spec(4, background = "#EBF5FB", extra_css = "font-size: 11px;")
```
### 📊 Task 2 — Headcount Analysis
```{r}
#| label: tbl-ex8-headcount
#| tbl-cap: "Excel Formulas for Headcount Analysis"
tibble(
`Period` = c("Before Training", "Before Training", "Before Training",
"After Training", "After Training", "After Training"),
`Tier` = c("Executive", "Bonus", "PIP", "Executive", "Bonus", "PIP"),
`Headcount Formula` = c(
"=C5*0.10",
"=C5*(0.25-0.10)",
"=C5*0.15",
"=C5*(1-NORM.DIST(87.38, C4, C3, TRUE))",
"=C5*(NORM.DIST(87.38, C4, C3, TRUE)-NORM.DIST(80.09, C4, C3, TRUE))",
"=C5*NORM.DIST(59.57, C4, C3, TRUE)"
),
`Result` = c("50", "75", "75", "78", "88", "26")
) |>
kbl(caption = "Employee Headcount Formulas Before and After Training",
align = c("l", "l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE) |>
column_spec(2, width = "20%") |>
# Fixed: swapped font_size for extra_css
column_spec(3, background = "#F8F9F9", extra_css = "font-size: 11px;") |>
column_spec(4, bold = TRUE, color = "#2980B9") |>
row_spec(4:6, background = "#D5F5E3")
```
### 💰 Task 3 — Training ROI Analysis
```{r}
#| label: tbl-ex8-roi
#| tbl-cap: "Excel Formulas for Training ROI"
tibble(
`Calculation` = c(
"Training cost",
"PIP reduction (employees)",
"Annual PIP cost per employee",
"Annual savings from reduced PIP",
"Simple payback period",
"ROI (1 year)",
"ROI (3 years)"
),
`Excel Formula` = c(
"200000",
"=75-26",
"15000",
"=B2*B3",
"=B1/B4",
"=(B4-B1)/B1",
"=(3*B4-B1)/B1"
),
`Result` = c("$200,000", "49", "$15,000", "$735,000", "0.27 years", "267.5%", "1002.5%")
) |>
kbl(align = c("l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, background = "#F8F9F9", italic = TRUE) |>
column_spec(3, bold = TRUE, color = "#27AE60")
```
***
# Capstone — Bakery Chain Optimization {#sec-capstone-excel}
## Exercise 9 — The Bakery Challenge {#sec-ex9-excel}
### 📋 Excel Setup Worksheet
```{r}
#| label: tbl-ex9-excel-setup
#| tbl-cap: "Excel Worksheet Setup for Bakery Optimization"
tibble(
Cell = c("B2", "B3", "B4", "B5", "B6", "B7", "B8",
"C2", "C3", "C4", "C5", "C6", "C7", "C8"),
`Label` = c(
"Mean demand (μ)", "Std Dev (σ)", "Selling price", "Cost", "Salvage value",
"Lost sale cost", "Number of stores", "Value", "Value", "Value", "Value",
"Value", "Value", "Value"
),
`Excel Formula/Value` = c(
"← Enter", "← Enter", "← Enter", "← Enter", "← Enter", "← Enter", "← Enter",
"150", "20", "8", "3", "1", "4", "50"
),
`Note` = c(
"loaves/day", "loaves/day", "$/loaf", "$/loaf", "$/loaf", "$/loaf", "stores",
"", "", "", "", "", "", "" # Added 7 empty entries to reach size 14
)
) |> excel_table()
```
### 🧮 Task 1 — Optimal Production Quantity
```{r}
#| label: tbl-ex9-optimal
#| tbl-cap: "Excel Formulas for Newsvendor Model"
tibble(
`Calculation` = c(
"Underage cost (Cu)",
"Overage cost (Co)",
"Critical ratio",
"Optimal z-score",
"Optimal quantity (Q*)",
"Expected daily profit/store",
"Expected annual chain profit"
),
`Excel Formula` = c(
"=C6",
"=C5-C7",
"=B10/(B10+B11)",
"=NORM.S.INV(B12)",
"=C2+B13*C3",
"**Complex - see below**",
"=B15*C8*365"
),
`Result` = c("$4.00", "$2.00", "0.6667", "0.431", "159", "$673.19", "$12,285,718")
) |>
kbl(align = c("l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, background = "#F8F9F9", italic = TRUE) |>
column_spec(3, bold = TRUE, color = "#27AE60")
```
::: {.callout-note collapse="false"}
## 📐 Complete Profit Calculation Formula
**Expected profit per store formula:**
```excel
B15: = (C4-C5)*(C2-C3*(NORM.S.DIST(B13,FALSE)-B13*(1-NORM.S.DIST(B13,TRUE)))) -
(C5-C7)*C3*NORM.S.DIST(B13,FALSE) -
C6*C3*(NORM.S.DIST(B13,FALSE)-B13*(1-NORM.S.DIST(B13,TRUE)))
Where:
- First term: (Price-Cost) * Expected sales
- Second term: Overproduction cost
- Third term: Underage cost
```
**Alternatively, break into components:**
```excel
B16: =B13 # z-score
B17: =C2-C3*(NORM.S.DIST(B16,FALSE)-B16*(1-NORM.S.DIST(B16,TRUE))) # E[min(D,Q)]
B18: =C3*NORM.S.DIST(B16,FALSE) # E[overstock]
B19: =C3*(NORM.S.DIST(B16,FALSE)-B16*(1-NORM.S.DIST(B16,TRUE))) # E[understock]
B20: =(C4-C5)*B17 - (C5-C7)*B18 - C6*B19 # Expected profit
```
:::
### 📊 Task 2 — Break-even Analysis
```{r}
#| label: tbl-ex9-breakeven
#| tbl-cap: "Excel Formulas for Cost Trade-off Analysis"
tibble(
`Cost Component` = c("Overproduction cost", "Overproduction cost",
"Stockout cost", "Stockout cost", "Total cost"),
`Excel Formula` = c(
"= (C5-C7)*( (Q-C2)*NORM.S.DIST(z,TRUE) + C3*NORM.S.DIST(z,FALSE) )",
"where z = (Q-C2)/C3",
"= C6*( (C2-Q)*(1-NORM.S.DIST(z,TRUE)) + C3*NORM.S.DIST(z,FALSE) )",
"where z = (Q-C2)/C3",
"= Overproduction cost + Stockout cost"
),
`At Q=159` = c("$26.61", "", "$17.20", "", "$43.81")
) |>
kbl(caption = "Cost Component Formulas",
align = c("l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
# Fixed: replaced font_size = 11 with extra_css
column_spec(2, background = "#F8F9F9", italic = TRUE, extra_css = "font-size: 11px;") |>
column_spec(3, bold = TRUE, color = "#2980B9")
```
### 🎯 Task 3 — Sensitivity Analysis
```{r}
#| label: tbl-ex9-sensitivity
#| tbl-cap: "Excel Data Table Setup for Sensitivity Analysis"
tibble(
`Analysis Type` = c("Production Quantity Sensitivity", "Store Count Sensitivity",
"Demand Variability Sensitivity", "Price Sensitivity"),
`Excel Setup` = c(
"Data Table: Row input = Q (140-180), Column = empty",
"Data Table: Row input = Store count, Column = empty",
"Data Table: Row input = σ (15-25), Column = empty",
"Data Table: Row input = Price ($6-$10), Column = empty"
),
`Formula` = c(
"=B20 (expected profit)",
"=B20*StoreCount*365",
"Update C3 with Data Table value",
"Update C4 with Data Table value"
),
`Chart Type` = c("Line chart", "Line chart", "Line chart", "Line chart")
) |>
kbl(align = c("l", "l", "l", "c")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, background = "#F8F9F9", italic = TRUE) |>
# Fixed: replaced font_size = 11 with extra_css
column_spec(3, background = "#EBF5FB", extra_css = "font-size: 11px;") |>
column_spec(4, color = "#7F8C8D")
```
***
# Advanced Excel Techniques {#sec-advanced-excel .unnumbered}
## 📊 Data Tables for Sensitivity Analysis
```{r}
#| label: tbl-data-table
#| tbl-cap: "Excel Data Table Setup Guide"
tibble(
`Step` = 1:6,
`Action` = c(
"Set up base model with all formulas",
"Create a table with input values in first column",
"In top-right cell, reference the output formula",
"Select the entire table range",
"Go to Data → What-If Analysis → Data Table",
"Enter the input cell reference"
),
`Example` = c(
"Cells B1:C20 with all bakery formulas",
"Column A: Q values from 140 to 180",
"Cell B1: =B20 (profit formula)",
"Select A1:B41",
"Row input cell: $B$14 (Q cell)",
"Column input cell: leave blank"
),
`Result` = c(
"Complete model", "Input values", "Output reference", "Selected range",
"Data Table dialog", "Populated table"
)
) |>
kbl(align = c("c", "l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, width = "5%") |>
column_spec(2, width = "25%") |>
column_spec(3, background = "#F8F9F9", italic = TRUE) |>
column_spec(4, width = "20%")
```
## 🎯 Solver for Optimization Problems
```{r}
#| label: tbl-solver-guide
#| tbl-cap: "Excel Solver Setup Guide"
tibble(
`Parameter` = c("Objective", "To", "By Changing", "Constraints",
"Solving Method", "Options"),
`Bakery Example` = c(
"Set Objective: $B$20 (profit)",
"To: Max",
"By Changing: $B$14 (Q)",
"$B$14 ≥ 0 (Q non-negative)",
"GRG Nonlinear",
"Use automatic scaling"
),
`Portfolio Example` = c(
"Set Objective: $B$6 (loss probability)",
"To: Min",
"By Changing: $C$3 (σ)",
"$C$3 ≥ 0.01 (σ positive)",
"GRG Nonlinear",
"Convergence: 0.0001"
)
) |>
kbl(align = c("l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
column_spec(2, background = "#F8F9F9") |>
column_spec(3, background = "#EBF5FB")
```
## 🛡️ Error Handling and Validation
```{r}
#| label: tbl-error-handling
#| tbl-cap: "Excel Error Handling Formulas"
tibble(
`Issue` = c("Invalid probability input", "Negative standard deviation",
"Division by zero", "Out of bounds z-score", "Missing data"),
`Problematic Formula` = c("=NORM.INV(P, μ, σ)", "=NORM.DIST(x, μ, σ, TRUE)",
"=A1/B1", "=NORM.S.INV(P)", "=AVERAGE(range)"),
`Robust Formula` = c(
"=IFERROR(NORM.INV(MAX(0.0001,MIN(0.9999,P)), μ, σ), \"Check P\")",
"=IF(σ>0, NORM.DIST(x, μ, σ, TRUE), \"Invalid σ\")",
"=IF(B1<>0, A1/B1, \"Division error\")",
"=IF(AND(P>0, P<1), NORM.S.INV(P), \"P out of bounds\")",
"=IF(COUNT(range)>0, AVERAGE(range), \"No data\")"
)
) |>
kbl(align = c("l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#FDEDEC") |>
column_spec(2, background = "#F8F9F9", color = "#E74C3C") |>
column_spec(3, background = "#D5F5E3", italic = TRUE)
```
***
# Quick Reference Cheat Sheet {#sec-cheatsheet .unnumbered}
```{r}
#| label: tbl-cheatsheet
#| tbl-cap: "Excel Normal Distribution Cheat Sheet"
tibble(
`Business Problem` = c(
"Quality control compliance",
"Process capability",
"Service level thresholds",
"Inventory reorder point",
"Financial risk (VaR)",
"Market pricing",
"Performance management",
"Newsvendor optimization"
),
`Key Excel Formula` = c(
"=NORM.DIST(USL,μ,σ,TRUE)-NORM.DIST(LSL,μ,σ,TRUE)",
"=(USL-LSL)/(6*σ)",
"=NORM.INV(ServiceLevel, μ, σ)",
"=μ_LT + NORM.S.INV(SL)*σ_LT",
"=NORM.INV(0.05, Return, Risk)",
"=Price*(1-NORM.DIST(Price, μ_WTP, σ_WTP, TRUE))",
"=NORM.INV(Percentile, μ_Score, σ_Score)",
"=NORM.S.INV(Cu/(Cu+Co))*σ + μ"
),
`Solver/Goal Seek Use` = c(
"Find μ or σ for target compliance",
"Find σ for target Cp",
"Find σ for target wait time",
"Find SL for minimum total cost",
"Find diversification for target risk",
"Find price for maximum revenue",
"Find training impact on distribution",
"Find Q for maximum profit"
)
) |>
kbl(align = c("l", "l", "l")) |>
kable_styling(
bootstrap_options = c("striped", "hover", "condensed", "responsive"),
full_width = TRUE
) |>
column_spec(1, bold = TRUE, background = "#E8F8F5") |>
# Fixed: swapped font_size for extra_css
column_spec(2, background = "#F8F9F9", extra_css = "font-size: 11px;") |>
column_spec(3, background = "#EBF5FB", italic = TRUE)
```
***
::: {.callout-tip appearance="default"}
## 📥 Downloadable Excel Template
A complete Excel workbook with all these formulas pre-configured is available at:
[**https://your-university.edu/normal-distribution-template.xlsx**](https://your-university.edu/normal-distribution-template.xlsx)
**Features included:**
- All 9 exercises with pre-built formulas
- Interactive sliders for sensitivity analysis
- Dynamic charts and dashboards
- Data validation and error checking
- Step-by-step instructions
- Managerial decision frameworks
:::
::: {.callout-note appearance="minimal"}
*Excel Answer Key rendered with [Quarto](https://quarto.org).*
*All formulas tested in Microsoft Excel 365. Results may vary slightly by Excel version.*
*For Google Sheets, use `NORM.DIST`, `NORM.INV`, `NORM.S.DIST`, `NORM.S.INV` functions.*
:::
```javascript
> **💡 Excel Implementation Instructions**
>
> 1. **Copy formulas** from tables into Excel cells
> 2. **Adjust cell references** as needed for your worksheet layout
> 3. **Enable Data Analysis Tools:** File → Options → Add-ins → Analysis ToolPak
> 4. **Enable Solver:** File → Options → Add-ins → Solver Add-in
> 5. **Test formulas** with known values before business application
>
> **Common Issues & Solutions:**
> - `#NAME?` error: Function not available (check Excel version)
> - `#NUM!` error: Invalid input to statistical function
> - `#VALUE!` error: Wrong argument type (e.g., text instead of number)
> - Solver not converging: Adjust initial values and constraints
```