Formulas · Learn · Sunflower Intelligence

F00 Foundations of Finance

1. What finance is: money, time and risk

Future value of a single amount (annual compounding)

\mathit{FV} = \mathit{PV}\,(1 + r)^n

$\mathit{FV}$: Future value: what the amount will be worth after n years$
$\mathit{PV}$: Present value: the amount invested today$
$r$: Interest rate per year, as a decimal (1.5% is 0.015)per year
$n$: Number of yearsyears

Assumes. The rate stays the same, interest is added once a year and left to earn interest, and nothing else is paid in or taken out.

Present value of a single amount (annual discounting)

\mathit{PV} = \dfrac{\mathit{FV}}{(1 + r)^n}

$\mathit{PV}$: Present value: what the future amount is worth today$
$\mathit{FV}$: The amount to be received in the future$
$r$: Discount rate per year, as a decimal: the return available elsewhere at similar riskper year
$n$: Number of years until the amount is receivedyears

Assumes. One amount, received at the end of year n; the discount rate is the same in every year.

Expected value of an uncertain outcome

E[X] = p_1 x_1 + p_2 x_2 + \cdots + p_k x_k

$E[X]$: Expected value of the uncertain outcome Xsame unit as the outcomes
$x_i$: The i-th possible outcomee.g. $
$p_i$: The probability of the i-th outcome; the probabilities add up to 1
$k$: The number of possible outcomes

Assumes. The list of outcomes is complete and the probabilities add up to 1 (100%).

Risk premium

\text{Risk premium} = E[R] - r_f

$E[R]$: Expected return on the risky investment% per year
$r_f$: Risk-free rate: the return on an investment whose payoff is certain% per year

Assumes. Both returns are measured over the same period.

2. Finance, accounting and economics

Economic profit

\text{Economic profit} = \text{Accounting profit} - \text{Opportunity costs not recorded}

$\text{Accounting profit}$: Revenue minus expenses as recorded under the accounting rules (net income)$
$\text{Opportunity costs not recorded}$: The value of alternatives given up that involve no transaction, such as the owner’s forgone salary and the return her money could earn elsewhere$

Assumes. Both are measured over the same period. The opportunity costs are estimates of what the best alternatives would really have paid.

Operating cash flow (simplified)

\text{Operating cash flow} = \text{Accounting profit} + \text{Non-cash expenses}

$\text{Accounting profit}$: Net income for the period$
$\text{Non-cash expenses}$: Expenses that used no cash in the period, chiefly depreciation$

Assumes. A simplification: it assumes customers paid in cash, bills were paid as they arose, and stocks of supplies did not change. When they did, timing differences must also be adjusted for.

3. Six principles behind financial decisions

Incremental cash flow of a decision

\text{Incremental cash flow} = \mathit{CF}_{\text{with}} - \mathit{CF}_{\text{without}}

$\mathit{CF}_{\text{with}}$: Cash flows of the business (or person) if the decision is taken, in a given period$
$\mathit{CF}_{\text{without}}$: Cash flows in the same period if it is not taken$

Assumes. Opportunity costs are included as cash given up; sunk costs, which are the same in both cases, cancel out.

Return on a portfolio of two investments

R_p = w\,R_A + (1 - w)\,R_B

$R_p$: Return on the whole portfolio%
$w$: Share of the money invested in A, as a decimal (the rest is in B)
$R_A$: Return on investment A%
$R_B$: Return on investment B%

Assumes. The shares w and 1 − w are fixed for the period.

No-arbitrage price of a safe future payment

P = \dfrac{X}{(1 + r_f)^n}

$P$: Today’s price of the promise$
$X$: The certain amount to be paid$
$r_f$: Risk-free rate at which money can be borrowed and lent, as a decimalper year
$n$: Years until paymentyears

Assumes. The payment is certain, and anyone can borrow and lend at the risk-free rate with no transaction costs.

4. Capital and liquidity

Working capital

\text{Working capital} = \text{Current assets} - \text{Current liabilities}

$\text{Current assets}$: Cash and assets expected to become cash or be used up within a year: receivables, inventory, prepayments$
$\text{Current liabilities}$: Obligations due within a year: amounts owed to suppliers, wages and taxes due, loan repayments due within the year$

Assumes. Both are measured at the same date, from the same balance sheet.

Current ratio

\text{Current ratio} = \dfrac{\text{Current assets}}{\text{Current liabilities}}

$\text{Current assets}$: As above$
$\text{Current liabilities}$: As above; must be greater than zero$

Assumes. Treats every current asset as equally able to pay the bills.

Quick (acid-test) ratio

\text{Quick ratio} = \dfrac{\text{Cash} + \text{Short-term investments} + \text{Receivables}}{\text{Current liabilities}}

$\text{Cash}$: Cash and bank balances$
$\text{Short-term investments}$: Investments that can be sold quickly at close to their value$
$\text{Receivables}$: Amounts customers owe for goods or services already delivered$
$\text{Current liabilities}$: Obligations due within a year$

Assumes. Inventory and prepayments are left out because they are slower, or never, turned into cash.

Debt-to-equity ratio

\text{Debt-to-equity} = \dfrac{\text{Debt}}{\text{Equity}}

$\text{Debt}$: Interest-bearing borrowing (loans and bonds); some analysts use total liabilities instead$
$\text{Equity}$: Shareholders’ equity as recorded in the balance sheet (book value)$

Assumes. Uses book values unless stated otherwise.

Emergency-fund cover (months)

\text{Months covered} = \dfrac{\text{Liquid savings}}{\text{Essential spending per month}}

$\text{Liquid savings}$: Money that can be reached quickly at full value, such as cash and bank deposits$
$\text{Essential spending per month}$: Costs that cannot easily be cut: housing, food, insurance, transport, loan payments$ per month

Assumes. Essential spending stays the same and no income arrives while the fund is used.

5. Value and valuation words

Market capitalisation

\text{Market cap} = P \times N

$P$: Current share price$ per share
$N$: Number of shares outstandingshares

Assumes. Uses the latest traded price; changes whenever the price does.

Enterprise value

\text{EV} = \text{Market cap} + \text{Debt} - \text{Cash}

$\text{Market cap}$: Market value of the equity$
$\text{Debt}$: Interest-bearing debt: loans and bonds$
$\text{Cash}$: Cash and short-term investments not needed to run the business; this course subtracts all cash$

Assumes. Debt at its balance sheet amount, a common simplification.

EBITDA

\text{EBITDA} = \text{EBIT} + \text{D\&A}

$\text{EBIT}$: Earnings before interest and taxes: net income + interest + taxes (equal to operating profit when, as at Harbourline, there is no income or cost outside operations)$
$\text{D\&A}$: Depreciation and amortisation for the period$

Assumes. Both from the same period’s income statement.

EV/EBITDA multiple

\text{EV/EBITDA} = \dfrac{\text{EV}}{\text{EBITDA}}

$\text{EV}$: Enterprise value$
$\text{EBITDA}$: Usually the last twelve months or the next year’s forecast; must be positive to be meaningful$

Assumes. Numerator and denominator both belong to all providers of capital.

Price-to-earnings (P/E) ratio

\text{P/E} = \dfrac{\text{Market cap}}{\text{Net income}} = \dfrac{P}{\text{EPS}}, \quad \text{EPS} = \dfrac{\text{Net income}}{N}

$P$: Share price$ per share
$\text{EPS}$: Earnings per share$ per share
$N$: Shares outstandingshares
$\text{Market cap}$: Share price × shares outstanding$
$\text{Net income}$: Profit after interest and tax; must be positive for a meaningful P/E$

Assumes. Numerator and denominator both belong to shareholders.

Market-to-book ratio

\text{Market-to-book} = \dfrac{\text{Market cap}}{\text{Book value of equity}}

$\text{Market cap}$: Market value of the equity$
$\text{Book value of equity}$: Total assets minus total liabilities, as recorded$

Assumes. Book value is positive.

6. Interest and inflation

Simple interest

I = P \times r \times t, \qquad FV = P(1 + rt)

$I$: Total interest earned or paid$
$P$: Principal: the amount lent or borrowed$
$r$: Annual interest rate, as a decimal (6% = 0.06)per year
$t$: Timeyears
$FV$: Balance at the end, if the interest is added to it rather than paid out$

Assumes. Interest is earned only on the original principal.

Compound interest with m periods a year

FV = P\left(1 + \frac{r}{m}\right)^{m n}

$FV$: Future value: the balance after n years$
$P$: Principal$
$r$: Quoted (nominal) annual rate, as a decimalper year
$m$: Compounding periods per year (1 annual, 4 quarterly, 12 monthly, 365 daily)
$n$: Number of yearsyears

Assumes. The rate stays the same and no money is added or withdrawn.

Effective annual rate

\text{EAR} = \left(1 + \frac{r}{m}\right)^{m} - 1

$\text{EAR}$: The equivalent rate compounded once a yearper year
$r$: Quoted (nominal) annual rate, such as an APR in its simple senseper year
$m$: Compounding periods per year

Assumes. Ignores fees; the APR disclosed on a US mortgage or car loan may include some.

Inflation rate from a price index

i = \frac{\text{Index}_1 - \text{Index}_0}{\text{Index}_0}

$i$: Inflation rate over the periodper period
$\text{Index}_0$: Price index at the startindex points
$\text{Index}_1$: Price index at the endindex points

Purchasing power in today’s dollars

\text{Real value} = \frac{\text{Future dollars}}{(1 + i)^{n}}

$\text{Future dollars}$: An amount of money n years from now$
$i$: Annual inflation rate, as a decimalper year
$n$: Number of yearsyears
$\text{Real value}$: What that amount will buy, measured in today’s prices$ of today

Assumes. Inflation is constant at i each year.

Real return (Fisher relation)

r_{\text{real}} = \frac{1 + r_{\text{nom}}}{1 + i} - 1 \;\approx\; r_{\text{nom}} - i

$r_{\text{real}}$: Real return: growth in purchasing powerper year
$r_{\text{nom}}$: Nominal return: growth in dollars (use the effective annual rate)per year
$i$: Inflation rateper year

Assumes. All three over the same period.

7. Financial markets

Intermediary’s interest-rate spread

s = r_L - r_D

$s$: The spread: the gap between the lending rate and the deposit ratepercentage points
$r_L$: The rate the intermediary charges borrowers% a year
$r_D$: The rate the intermediary pays savers% a year

Assumes. Both rates are stated on the same basis (here, simple annual rates).

Converting between currencies

A_{\$} = A_{F} \times S

$A_{\$}$: The amount in US dollars$
$A_{F}$: The amount in the foreign currencyunits of foreign currency
$S$: The exchange rate quoted as dollars per one unit of the foreign currency$ per unit

Assumes. The rate is quoted as dollars per unit of foreign currency. If it is quoted the other way (units of foreign currency per dollar), divide instead of multiplying.

8. Market participants

Bid-ask spread and relative spread

\text{Spread} = P_{\text{ask}} - P_{\text{bid}}, \qquad \text{Relative spread} = \dfrac{P_{\text{ask}} - P_{\text{bid}}}{(P_{\text{ask}} + P_{\text{bid}}) \div 2}

$P_{\text{ask}}$: The price at which the dealer will sell to you$ per share
$P_{\text{bid}}$: The price at which the dealer will buy from you$ per share

Assumes. Quotes for the same quantity at the same moment. The relative spread is often written as a percentage.

Net proceeds of an underwritten offering

\text{Net proceeds} = N \times P \times (1 - g)

$N$: Number of new shares soldshares
$P$: Offer price paid by investors$ per share
$g$: Underwriting spread as a fraction of the offer price

Assumes. Before the issuer’s other costs, such as legal, audit and registration fees.

Net asset value per fund share

\text{NAV per share} = \dfrac{\text{Assets} - \text{Liabilities}}{N}

$\text{Assets}$: Market value of everything the fund owns$
$\text{Liabilities}$: Everything the fund owes, such as fees due$
$N$: Number of fund shares outstandingshares

Assumes. Valued at the end of the business day, using that day’s closing prices.

FDIC-insured amount in one ownership category at one bank

\text{Insured} = \min(B,\ 250{,}000), \qquad \text{Uninsured} = B - \text{Insured}

$B$: The depositor’s combined deposits in that ownership category at that bank$

Assumes. One depositor, one insured bank, one ownership category. For joint accounts apply the limit to each co-owner’s share; trust accounts follow their own rule (per beneficiary, with a cap).

9. Governance and the agency problem

Agency costs (Jensen and Meckling)

\text{Agency costs} = M + B + L

$M$: Monitoring costs paid by principals (audits, boards, time spent checking reports)$
$B$: Bonding costs paid by agents to assure principals (detailed reports, penalty clauses)$
$L$: Residual loss: value still lost because the agent’s decisions differ from the principal’s ideal$

Assumes. A way of organising the costs, not something measured precisely in practice; residual loss in particular can only be estimated.

Who bears the cost of a perk

C_{\text{manager}} = \alpha \times P, \qquad C_{\text{others}} = (1 - \alpha) \times P

$\alpha$: The fraction of the company’s shares the manager ownsbetween 0 and 1
$P$: The cost of a perk that adds nothing to the company’s value$
$C_{\text{manager}}$: The fall in the value of the manager’s own shares$
$C_{\text{others}}$: The cost borne by the other shareholders$

Assumes. The perk reduces the company’s value dollar for dollar and is not offset by any gain, such as better effort.

Payoffs to shareholders and lenders when debt is due

\text{Shareholders} = \max(V - D,\ 0), \qquad \text{Lenders} = \min(V,\ D)

$V$: Value of the company’s assets when the debt falls due$
$D$: Amount owed to lenders (principal plus interest due)$

Assumes. One class of debt; shareholders have limited liability, so their payoff cannot fall below zero.

F01 Introduction to Financial Accounting

3. The accounting equation

The accounting equation

\text{Assets} = \text{Liabilities} + \text{Equity}

$\text{Assets}$: Resources the business controls that are expected to bring economic benefits$
$\text{Liabilities}$: Present obligations to transfer resources to outsiders$
$\text{Equity}$: The owners’ residual claim on the assets$

Assumes. All three are measured at the same moment, in the same currency.

Equity as net assets

\text{Equity} = \text{Assets} - \text{Liabilities}

$\text{Equity}$: The residual claim: what would be left for owners after every liability is settled at book amounts$

The expanded accounting equation

A = L + CS + RE_{\text{beg}} + R - E - D

$A,\ L$: Assets and liabilities at the end of the period$
$CS$: Common stock: capital contributed by owners$
$RE_{\text{beg}}$: Retained earnings at the start of the period$
$R,\ E$: Revenue and expenses for the period$
$D$: Dividends declared in the period$

Assumes. Revenue, expenses and dividends are the period’s totals, not yet moved into retained earnings.

Net income

\text{Net income} = \text{Revenue} - \text{Expenses}

$\text{Net income}$: Profit for the period; negative means a net loss$

Retained earnings at the end of a period

RE_{\text{end}} = RE_{\text{beg}} + \text{Net income} - \text{Dividends}

$RE_{\text{end}}$: Profit kept in the business since it began, at the end of the period$

4. Debits, credits and normal balances

Balance of an account with a normal debit balance

\text{Ending balance} = \text{Beginning balance} + \textstyle\sum \text{Debits} - \sum \text{Credits}

$\text{Beginning balance}$: The balance brought forward from the previous period (zero for a new account)$
$\textstyle\sum \text{Debits}$: Total of the amounts entered on the left side during the period$
$\textstyle\sum \text{Credits}$: Total of the amounts entered on the right side during the period$

Assumes. For assets, expenses and dividends.

Balance of an account with a normal credit balance

\text{Ending balance} = \text{Beginning balance} + \textstyle\sum \text{Credits} - \sum \text{Debits}

$\text{Beginning balance}$: The credit balance brought forward from the previous period$
$\textstyle\sum \text{Credits}$: Total amounts entered on the right side (increases) during the period$
$\textstyle\sum \text{Debits}$: Total amounts entered on the left side (decreases) during the period$

Assumes. For liabilities, equity, revenue and contra-asset accounts.

The double-entry rule

\textstyle\sum \text{Debits} = \sum \text{Credits}

$\textstyle\sum \text{Debits}$: Total of the amounts debited in an entry$
$\textstyle\sum \text{Credits}$: Total of the amounts credited in the same entry$

Assumes. Holds for every entry, and therefore for all entries together.

5. Journal entries

A journal entry balances

\textstyle\sum_{\text{lines}} \text{Debits} = \sum_{\text{lines}} \text{Credits}

$\textstyle\sum_{\text{lines}} \text{Debits}$: Total of the debit amounts on all lines of the entry$
$\textstyle\sum_{\text{lines}} \text{Credits}$: Total of the credit amounts on all lines of the entry$

Assumes. Every entry has at least one debit line and one credit line; the numbers of lines may differ.

6. Posting to the general ledger

Balance of a debit-normal account

\text{Balance} = \sum \text{Debits} - \sum \text{Credits}

$\sum \text{Debits}$: Total of all amounts posted to the debit side, including any opening debit balance$
$\sum \text{Credits}$: Total of all amounts posted to the credit side$

Assumes. Use for assets, expenses and dividends. A negative result means the account has an abnormal (credit) balance: investigate it.

Balance of a credit-normal account

\text{Balance} = \sum \text{Credits} - \sum \text{Debits}

$\sum \text{Credits}$: Total of all amounts posted to the credit side, including any opening credit balance$
$\sum \text{Debits}$: Total of all amounts posted to the debit side$

Assumes. Use for liabilities, equity, revenue and contra accounts.

Running balance after a posting (debit-normal account)

B_n = B_{n-1} + \text{Dr}_n - \text{Cr}_n

$B_n$: Balance after the n-th posting$
$B_{n-1}$: Balance before it (zero for a new account)$
$\text{Dr}_n,\ \text{Cr}_n$: The debit or credit posted (one of them is zero)$

Control account check

\text{Control account balance} = \sum_{i} \text{Subsidiary balance}_i

$\text{Subsidiary balance}_i$: Balance of customer (or supplier) account i in the subsidiary ledger$

Assumes. Every amount was posted to both the control account and the subsidiary ledger.

7. The trial balance

Trial balance check

\sum \text{Debit balances} = \sum \text{Credit balances}

$\sum \text{Debit balances}$: Total of the debit column: every account whose debits exceed its credits$
$\sum \text{Credit balances}$: Total of the credit column: every account whose credits exceed its debits$

Assumes. Every journal entry balanced and was posted in full.

Difference between the totals

d = \left|\, \sum \text{Debit balances} - \sum \text{Credit balances} \,\right|

$d$: The amount to be explained$

Amount on the wrong side

x = \dfrac{d}{2}

$x$: The amount posted to the wrong side, or the balance listed in the wrong column$
$d$: Difference between the totals$

Assumes. A single error. In whole-dollar records an odd difference rules this out.

Difference left by swapping two neighbouring digits

d = 9\,|a - b| \times 10^{k}

$a,\ b$: The two digits that were swapped
$k$: Place of the lower digit: 0 for units and tens, 1 for tens and hundreds, and so on
$d$: Difference the transposition leaves$

Difference left by a slide

d = (10^{n} - 1)\, y

$d$: Difference the slide leaves$
$y$: The smaller of the correct and the recorded amounts$
$n$: Number of places the decimal point moved

8. Accrual versus cash accounting

Cash-basis profit

\text{Cash-basis profit} = \text{Operating receipts} - \text{Operating payments}

$\text{Operating receipts}$: Cash received from customers in the period, whatever period the work belongs to$
$\text{Operating payments}$: Cash paid in the period for running costs (wages, rent, supplies, insurance…)$

Assumes. Owner investment, borrowing, loan repayments, dividends and payments for long-lived assets are left out: they are not revenue or expenses on either basis.

Accrual-basis net income

\text{Net income} = \text{Revenue earned} - \text{Expenses incurred}

$\text{Revenue earned}$: Revenue for performance obligations satisfied in the period, received or not$
$\text{Expenses incurred}$: Resources used up and obligations incurred in the period’s operations, paid or not$

From cash-basis profit to accrual net income

NI = CP + A_R - D_R + D_E - A_E

$NI$: Accrual-basis net income for the period$
$CP$: Cash-basis profit for the period$
$A_R$: Revenue earned this period but not yet received (accrued revenue, unpaid invoices)$
$D_R$: Cash received this period for revenue of later periods (unearned revenue)$
$D_E$: Cash paid this period for costs of later periods (prepayments, paid-for supplies still on hand)$
$A_E$: Expenses of this period not paid from this period’s operating cash (unpaid bills, wages and interest owed, depreciation)$

Assumes. Nothing carried over from earlier periods, as in Lantern’s first month.

9. Adjusting entries

Straight-line depreciation (per year)

\text{Depreciation per year} = \dfrac{\text{Cost} - \text{Residual value}}{\text{Useful life}}

$\text{Cost}$: What the asset cost to buy and get ready for use$
$\text{Residual value}$: What the business expects to get for the asset at the end of its useful life$
$\text{Useful life}$: How long the business expects to use the assetyears

Assumes. The asset’s benefits are used up evenly over its life.

Straight-line depreciation for part of a year

\text{Depreciation} = \dfrac{\text{Cost} - \text{Residual value}}{\text{Useful life}} \times \dfrac{m}{12}

$m$: Months the asset is in use during the period, counted by the business’s conventionmonths

Book value (carrying amount)

\text{Book value} = \text{Cost} - \text{Accumulated depreciation}

$\text{Accumulated depreciation}$: All depreciation recorded on the asset since it was bought$

Simple interest for a period

I = P \times r \times t

$I$: Interest for the period$
$P$: Principal: the amount borrowed or lent$
$r$: Annual interest rate, as a decimal (6% = 0.06)
$t$: Time in years (one month = 1/12)years

Assumes. Interest is not compounded within the period.

Supplies used in a period

\text{Supplies used} = \text{Supplies at start} + \text{Purchases} - \text{Supplies on hand}

$\text{Supplies at start}$: Balance of the Supplies account at the start of the period$
$\text{Purchases}$: Supplies bought during the period$
$\text{Supplies on hand}$: Cost of supplies counted at the end of the period$

10. Financial statements from the adjusted trial balance

Net income (single-step income statement)

\text{Net income} = \sum \text{Revenues} - \sum \text{Expenses}

$\sum$: The total of every account of that type in the adjusted trial balance

Ending retained earnings

RE_{\text{end}} = RE_{\text{beg}} + \text{Net income} - \text{Dividends}

$RE_{\text{beg}}$: Retained earnings at the start of the period$
$RE_{\text{end}}$: Retained earnings at the end of the period, reported on the balance sheet$

Net change in cash

\Delta \text{Cash} = \text{CF}_{\text{operating}} + \text{CF}_{\text{investing}} + \text{CF}_{\text{financing}}

$\Delta \text{Cash}$: Ending cash minus beginning cash$
$\text{CF}$: Net cash flow from each kind of activity (negative when cash is used)$

11. Closing entries

Income Summary after closing revenue and expenses

\text{Income Summary balance} = \sum \text{Revenue} - \sum \text{Expenses} = \text{Net income}

$\sum \text{Revenue}$: Total of the revenue account balances for the period$
$\sum \text{Expenses}$: Total of the expense account balances for the period$
$\text{Net income}$: Profit for the period; a negative result is a net loss$

Assumes. Dividends are not included: they are closed directly to Retained Earnings.

Retained Earnings after closing

RE_{\text{after}} = RE_{\text{before}} + \text{Net income} - \text{Dividends}

$RE_{\text{before}}$: Retained Earnings balance before the closing entries, which is its balance at the start of the period$
$RE_{\text{after}}$: Retained Earnings balance after the closing entries are posted$
$\text{Dividends}$: Dividends declared in the period$

Post-closing trial balance and the balance sheet

\text{Total debits} = \text{Total assets} + \text{Contra-asset balances}

$\text{Total assets}$: Total assets on the balance sheet, after contra-assets are deducted$
$\text{Contra-asset balances}$: Credit balances such as accumulated depreciation, which a trial balance lists on the credit side$

12. The complete accounting cycle

Correcting entry

\text{Correcting entry} = \text{Correct entry} - \text{Entry made}

$\text{Correct entry}$: The entry that should have been recorded$
$\text{Entry made}$: The entry that was actually recorded and posted$

Assumes. Work account by account, treating debits as positive and credits as negative. A positive difference is a debit line in the correction, a negative one a credit line.

Wages accrued at the end of a period

\text{Accrued wages} = \text{Pay for the pay period} \times \dfrac{\text{Days worked by the period end}}{\text{Days in the pay period}}

$\text{Pay for the pay period}$: The total that will be paid for the whole pay period$
$\text{Days worked by the period end}$: Days of the pay period that fall in the period being closeddays
$\text{Days in the pay period}$: Length of the pay perioddays

Assumes. Pay builds up evenly over the days of the pay period.

F02 Financial Statement Preparation

1. Revenue recognition

Allocating a transaction price (relative stand-alone selling prices)

\text{Allocated price}_i = \text{Transaction price} \times \dfrac{\text{SSP}_i}{\sum_j \text{SSP}_j}

$\text{Allocated price}_i$: The part of the transaction price assigned to performance obligation i$
$\text{Transaction price}$: What the business expects to be entitled to for the whole contract$
$\text{SSP}_i$: The stand-alone selling price of obligation i: what it would sell for on its own$
$\sum_j \text{SSP}_j$: The total of the stand-alone selling prices of all the obligations in the contract$

Assumes. Any discount for buying the bundle belongs to all the obligations in proportion.

Revenue to date on an obligation satisfied evenly over time

\text{Revenue to date} = \text{Allocated price} \times \dfrac{t}{T}

$\text{Allocated price}$: The part of the transaction price assigned to the service$
$t$: The part of the service period that has passedmonths
$T$: The whole service periodmonths

Assumes. The service is provided evenly, so time elapsed measures progress.

Contract liability (unearned revenue) at the end of a period

\text{CL}_{\text{end}} = \text{CL}_{\text{beg}} + \text{Received in advance} - \text{Recognised from advances}

$\text{CL}_{\text{beg}}, \text{CL}_{\text{end}}$: The contract liability at the start and end of the period$
$\text{Received in advance}$: Payments received (or due) for goods and services not yet transferred$
$\text{Recognised from advances}$: Revenue recognised in the period for obligations that had been paid for in advance$

Net revenue

\text{Net revenue} = \text{Gross revenue} - \text{Sales returns and allowances} - \text{Sales discounts}

$\text{Gross revenue}$: Revenue recognised for goods and services transferred, before reductions$
$\text{Sales returns and allowances}$: Refunds and price reductions for returned or unsatisfactory goods (a contra-revenue account)$
$\text{Sales discounts}$: Reductions for prompt payment taken by customers (a contra-revenue account)$

2. Inventory and cost of goods sold

Cost of goods sold (periodic formula)

\text{COGS} = \text{BI} + \text{P} - \text{EI}

$\text{COGS}$: Cost of goods sold for the period$
$\text{BI}$: Beginning inventory: the cost of goods on hand at the start of the period$
$\text{P}$: Net purchases: purchases plus freight-in, less purchase returns and discounts$
$\text{EI}$: Ending inventory: the cost of goods on hand at the end, from a count, after any write-down$

Assumes. Every unit available was either sold or is still on hand; losses found by the count end up in cost of goods sold.

Cost of goods sold by FIFO or LIFO

\text{COGS} = \sum_k q_k \times c_k

$q_k$: Units taken from cost layer k: oldest layers first under FIFO, newest first under LIFO, until the units sold are coveredunits
$c_k$: Unit cost of layer k$ per unit

Assumes. Periodic: layers are taken from the whole period’s purchases. Perpetual LIFO takes only the layers on hand at the date of each sale.

Weighted average cost

\bar{c} = \dfrac{\text{Cost of goods available}}{\text{Units available}}, \quad \text{COGS} = \bar{c} \times \text{Units sold}

$\bar{c}$: Average cost a unit$ per unit
$\text{Cost of goods available}$: Beginning inventory plus purchases, at cost$
$\text{Units available}$: Units on hand at the start plus units boughtunits

Assumes. Periodic: one average for the period. The moving average recalculates it after each purchase.

Gross profit and gross margin

\text{Gross profit} = \text{Net sales} - \text{COGS}, \quad \text{Gross margin} = \dfrac{\text{Gross profit}}{\text{Net sales}}

$\text{Net sales}$: Revenue from goods sold, after returns, allowances and discounts$
$\text{COGS}$: Cost of goods sold for the same period$

Lower of cost and net realisable value

\text{NRV} = \text{Estimated selling price} - \text{Costs to complete} - \text{Costs to sell}, \quad \text{Carrying amount} = \min(\text{Cost}, \text{NRV})

$\text{NRV}$: Net realisable value of the item$
$\text{Estimated selling price}$: The price expected in the ordinary course of business$
$\text{Costs to complete}$: Costs still needed to finish the goods (none for goods bought ready to sell)$
$\text{Costs to sell}$: Costs of making the sale, such as fees, commissions and shipping$

Assumes. Usually applied item by item, or to groups of similar items. US GAAP applies it to inventory measured other than by LIFO or the retail inventory method.

3. The multi-step income statement

Gross profit and gross margin (multi-step)

\text{Gross profit} = \text{Net revenue} - \text{Cost of sales}, \qquad \text{Gross margin} = \dfrac{\text{Gross profit}}{\text{Net revenue}}

$\text{Net revenue}$: Revenue less returns, allowances and discounts$
$\text{Cost of sales}$: Cost of goods sold plus cost of services: the direct cost of what was sold$

Operating income

\text{Operating income} = \text{Gross profit} - \text{Operating expenses} + \text{Gains on disposals}

$\text{Gross profit}$: Net revenue less cost of sales$
$\text{Operating expenses}$: Selling, general and administrative and other costs of running the business, including depreciation$
$\text{Gains on disposals}$: Gains on selling long-lived assets such as equipment; losses on disposals are included in operating expenses (ASC 360-10-45-5)$

From operating income to net income

\text{IBT} = \text{Operating income} + \text{Other income} - \text{Other expenses}, \qquad \text{Net income} = \text{IBT} - \text{Income tax expense}

$\text{IBT}$: Income before income tax$
$\text{Other income}, \text{Other expenses}$: Non-operating items such as interest revenue and interest expense (under US GAAP, gains and losses on disposals are operating items)$
$\text{Income tax expense}$: The tax charged on the period’s income (Lantern: 25% of IBT)$

EBIT and EBITDA from net income

\text{EBIT} = \text{NI} + \text{Interest expense} + \text{Income tax expense}, \qquad \text{EBITDA} = \text{EBIT} + \text{D\&A}

$\text{NI}$: Net income as reported under GAAP$
$\text{Interest expense}$: Interest charged in the period (net of interest revenue, if the company defines it that way)$
$\text{D\&A}$: Depreciation and amortisation expense$

Assumes. The SEC staff’s definition. Measures with further adjustments need another name, such as Adjusted EBITDA, and a reconciliation to net income.

Effective tax rate

\text{Effective tax rate} = \dfrac{\text{Income tax expense}}{\text{Income before income tax}}

$\text{Income tax expense}$: Total tax charged in the income statement for the period$
$\text{Income before income tax}$: The subtotal before tax$

Basic earnings per share

\text{Basic EPS} = \dfrac{\text{Net income} - \text{Preferred dividends}}{\text{Weighted shares}}

$\text{Net income}$: Net income for the period$
$\text{Preferred dividends}$: Dividends due to preferred shareholders for the period$
$\text{Weighted shares}$: The weighted average number of common shares outstanding: each share weighted by the fraction of the period it was outstandingshares

4. Receivables and payables

Accounts receivable roll-forward

\text{AR}_{\text{end}} = \text{AR}_{\text{beg}} + \text{Credit sales} - \text{Collections} - \text{Write-offs}

$\text{AR}_{\text{beg}}, \text{AR}_{\text{end}}$: Accounts receivable at the start and end of the period (gross, before the allowance)$
$\text{Credit sales}$: Sales invoiced to customers on credit during the period$
$\text{Collections}$: Cash received from customers against their accounts$
$\text{Write-offs}$: Customer balances removed as uncollectible during the period$

Allowance required by an aging of receivables

\text{Required allowance} = \sum_{i} B_i \times r_i

$B_i$: Receivables in age band i (for example, not yet due, 31–60 days, over 60 days)$
$r_i$: Expected loss rate for band i, as a decimal; higher for older bands
$\sum_{i}$: Sum over all the age bands

Assumes. The rates reflect past losses adjusted for current conditions and reasonable forecasts.

Bad debt expense under the balance sheet approach

\text{Bad debt expense} = \text{Required allowance} - \text{Unadjusted allowance}

$\text{Required allowance}$: The credit balance the allowance must have after adjustment (from the aging or a percentage of receivables)$
$\text{Unadjusted allowance}$: The allowance’s balance before the adjustment: positive if a credit balance, negative if a debit balance$

Bad debt expense under the income statement approach

\text{Bad debt expense} = p \times \text{Credit sales}

$p$: Expected loss rate on credit sales, as a decimal
$\text{Credit sales}$: Sales on credit for the period (cash and card sales carry no credit risk)$

Net receivables (net realisable value of receivables)

\text{Net receivables} = \text{Accounts receivable} - \text{Allowance for doubtful accounts}

$\text{Accounts receivable}$: Gross amount customers owe at the balance sheet date$
$\text{Allowance for doubtful accounts}$: Credit balance of the allowance after adjustment$

Interest on a note for part of a year

I = P \times r \times t

$I$: Interest earned (or owed) for the period$
$P$: Principal of the note$
$r$: Annual interest rate, as a decimal
$t$: Time in years: months outstanding ÷ 12years

Annualised cost of skipping a cash discount

\text{Cost} = \dfrac{d}{1 - d} \times \dfrac{365}{n - p}

$d$: Discount for early payment, as a decimal (2% = 0.02)
$n$: Days until the full amount is due (30 in 2/10, n/30)days
$p$: Last day of the discount period (10 in 2/10, n/30)days

Assumes. A simple (not compounded) annual rate; the buyer would otherwise pay on the last day of each period.

5. Long-lived assets

Depreciable amount

\text{Depreciable amount} = \text{Cost} - \text{Residual value}

$\text{Cost}$: Everything paid to acquire the asset and get it ready for use$
$\text{Residual value}$: What the business expects to get for the asset at the end of its useful life$

Double-declining-balance depreciation

D_t = \dfrac{2}{n} \times BV_{t-1}, \quad \text{but never taking } BV_t \text{ below the residual value}

$D_t$: Depreciation for year t (for a part year, multiply by months used ÷ 12)$
$n$: Useful lifeyears
$BV_{t-1}$: Book value at the start of year t: cost minus accumulated depreciation$
$BV_t$: Book value at the end of year t$

Assumes. Residual value is not deducted from the base; it is a floor.

Units-of-production depreciation

D = \dfrac{\text{Cost} - \text{Residual value}}{\text{Total expected units}} \times \text{Units used}

$D$: Depreciation for the period$
$\text{Cost}$: Everything paid to acquire the asset and get it ready for use$
$\text{Residual value}$: What the business expects to get for the asset at the end of its useful life$
$\text{Total expected units}$: Output expected over the asset’s life: miles, hours, units madeunits
$\text{Units used}$: Output in the periodunits

Depreciation after a change in estimate

\text{New depreciation per year} = \dfrac{BV - \text{New residual value}}{\text{Remaining useful life}}

$BV$: Book value at the date of the change$
$\text{New residual value}$: Revised estimate of what the asset will fetch at the end$
$\text{Remaining useful life}$: Revised years of use left from the date of the changeyears

Assumes. Straight line from the date of change; earlier periods are not restated.

Gain or loss on disposal

\text{Gain (loss)} = \text{Proceeds} - BV_{\text{disposal}}

$\text{Proceeds}$: Cash or the fair value of other assets received, less costs of selling$
$BV_{\text{disposal}}$: Cost minus accumulated depreciation, updated to the date of disposal$

Straight-line amortisation of a finite-lived intangible

\text{Amortisation per year} = \dfrac{\text{Cost}}{\min(\text{Legal life left}, \ \text{Useful life})}

$\text{Cost}$: Price paid for the intangible (residual value usually nil)$
$\text{Legal life left}$: Years of legal protection remaining (20 years from filing for a US utility patent)years
$\text{Useful life}$: Years the business expects the intangible to bring benefitsyears

Goodwill in an acquisition

\text{Goodwill} = \text{Price paid} - (\text{FV of identifiable assets} - \text{FV of liabilities assumed})

$\text{Price paid}$: Consideration transferred for the business$
$\text{FV of identifiable assets}$: Fair value of the assets that can be recognised separately, including intangibles$
$\text{FV of liabilities assumed}$: Fair value of the liabilities the buyer takes on$

6. The balance sheet and equity

Working capital

\text{Working capital} = \text{Current assets} - \text{Current liabilities}

$\text{Current assets}$: Cash and assets expected to become cash, be sold or be used up within one year or the operating cycle$
$\text{Current liabilities}$: Obligations due within the same horizon, including the current portion of long-term debt$

Current ratio

\text{Current ratio} = \dfrac{\text{Current assets}}{\text{Current liabilities}}

$\text{Current assets}$: As above$
$\text{Current liabilities}$: As above; must be greater than zero$

Splitting a loan at the balance sheet date

\text{Noncurrent portion} = \text{Principal outstanding} - \text{Principal due within 12 months}

$\text{Principal outstanding}$: Loan principal owed at the balance sheet date$
$\text{Principal due within 12 months}$: Instalments due in the next year: the current portion of long-term debt$

Common stock and additional paid-in capital on an issue of shares

\text{Common stock} = N \times \text{Par}, \qquad \text{APIC} = N \times (P - \text{Par})

$N$: Number of shares issuedshares
$P$: Issue price per share$ per share
$\text{Par}$: Par (or stated) value per share; for no-par stock without a stated value, all proceeds go to common stock$ per share

Cash dividend declared

\text{Dividend} = d \times N_{\text{out}}

$d$: Dividend per share declared by the board$ per share
$N_{\text{out}}$: Shares outstanding (issued shares less treasury shares) on the date of recordshares

Largest cash payment that keeps a minimum current ratio

\text{Maximum payment} = \text{CA} - k \times \text{CL}

$\text{CA}, \text{CL}$: Current assets and current liabilities before the payment$
$k$: Minimum current ratio required, for example by a loan agreement

Assumes. The payment is made in cash from current assets and settles nothing in current liabilities (for example, a dividend paid at once).

7. The statement of cash flows

Opening cash to closing cash

\text{Cash}_{\text{end}} = \text{Cash}_{\text{beg}} + \text{CFO} + \text{CFI} + \text{CFF}

$\text{Cash}_{\text{beg}},\ \text{Cash}_{\text{end}}$: Cash and cash equivalents at the start and the end of the period$
$\text{CFO}$: Net cash from operating activities$
$\text{CFI}$: Net cash from investing activities (negative when cash is used)$
$\text{CFF}$: Net cash from financing activities (negative when cash is used)$

Assumes. Noncash investing and financing activities are left out; they are disclosed separately.

Cash received from customers

\text{Cash collected} = \text{Revenue} - \Delta\text{Receivables} - \text{Write-offs} + \Delta\text{Unearned revenue}

$\text{Revenue}$: Net revenue on the income statement (after returns and discounts)$
$\Delta\text{Receivables}$: Closing minus opening accounts receivable, before the allowance$
$\text{Write-offs}$: Receivables written off during the period as uncollectible$
$\Delta\text{Unearned revenue}$: Closing minus opening unearned revenue (customer deposits and prepayments)$

Cash paid for inventory

\text{Cash paid} = \text{COGS} + \Delta\text{Inventory} - \Delta\text{Payables}

$\text{COGS}$: Cost of goods sold for the period$
$\Delta\text{Inventory}$: Closing minus opening inventory$
$\Delta\text{Payables}$: Closing minus opening accounts payable to inventory suppliers$

Assumes. All inventory is bought on account from the suppliers whose payables are used.

Cash paid for an operating expense

\text{Cash paid} = \text{Expense} + \Delta\text{Prepaid} - \Delta\text{Accrued liability}

$\text{Expense}$: The expense on the income statement (wages, insurance, interest, income tax …)$
$\Delta\text{Prepaid}$: Closing minus opening prepayment for that expense, if any$
$\Delta\text{Accrued liability}$: Closing minus opening payable for that expense (wages payable, interest payable …)$

8. The indirect method

Net cash from operating activities (indirect method)

\text{CFO} = \text{NI} + \text{Noncash expenses} + \text{Losses} - \text{Gains} - \Delta\text{CA}_{\text{op}} + \Delta\text{CL}_{\text{op}}

$\text{CFO}$: Net cash from operating activities$
$\text{NI}$: Net income for the period$
$\text{Noncash expenses}$: Depreciation, amortisation, impairment and similar charges$
$\text{Losses},\ \text{Gains}$: Losses and gains on selling long-term assets or settling debt$
$\Delta\text{CA}_{\text{op}}$: Closing minus opening operating current assets other than cash (receivables net of the allowance, inventory, prepayments)$
$\Delta\text{CL}_{\text{op}}$: Closing minus opening operating current liabilities (payables, accruals, unearned revenue, income taxes payable)$

Assumes. Current items that belong to investing or financing (current portion of long-term debt, dividends payable, short-term loans and investments) are excluded.

The change in cash from the balance sheet

\Delta\text{Cash} = \Delta\text{Liabilities} + \Delta\text{Equity} - \Delta\text{Noncash assets}

$\Delta$: Closing balance minus opening balance
$\text{Noncash assets}$: Every asset except cash and cash equivalents, net of contra accounts$

Cash proceeds from selling a long-term asset

\text{Proceeds} = (\text{Cost} - \text{Accumulated depreciation}) + \text{Gain} - \text{Loss}

$\text{Cost} - \text{Accumulated depreciation}$: The asset’s book value at the date of sale$
$\text{Gain},\ \text{Loss}$: The gain or loss recorded on the sale (only one is non-zero)$
$\text{Proceeds}$: Cash received: an investing inflow$

Account roll-forward

\text{Closing} = \text{Opening} + \text{Additions} - \text{Reductions}

$\text{Opening},\ \text{Closing}$: The account’s balances on the two balance sheets$
$\text{Additions},\ \text{Reductions}$: What increased and decreased the account during the year (purchases and disposals, borrowing and repayments, net income and dividends)$

9. Linking the statements

Reading the size of a failed cash link

\text{Gap} = \begin{cases} X & \text{item omitted, counted twice, or noncash treated as cash} \\ 2X & \text{item entered with the wrong sign} \end{cases}

$\text{Gap}$: Ending cash on the draft statement of cash flows minus cash on the balance sheet (or the reverse)$
$X$: The amount of the item that is wrong$

Assumes. One error at a time. Two errors can partly offset, so measure the gap again after correcting the first.

After-tax effect of a change in a pretax expense

\Delta NI = -(1 - t)\,\Delta X

$\Delta NI$: Change in net income$
$\Delta X$: Change in an expense deducted before tax (positive when the expense rises)$
$t$: Tax rate on income before tax, as a decimal

Assumes. Tax is a fixed share of income before tax, as in the Lantern case (25%).

Cash and tax payable after a change in a noncash expense

\Delta \text{CFO} = t \times p \times \Delta N, \qquad \Delta \text{Tax payable} = -t \times (1 - p) \times \Delta N

$\Delta \text{CFO}$: Change in cash from operating activities$
$\Delta \text{Tax payable}$: Change in income taxes payable at the year end$
$\Delta N$: Change in a tax-deductible noncash expense, such as depreciation or a write-down$
$t$: Tax rate on income before tax, as a decimal
$p$: Share of the change in tax that is paid within the year (1 if all of it, 0 if none)

Assumes. The expense itself moves no cash; only the tax it changes does, and only when that tax is paid.

10. US GAAP and IFRS

Revaluation surplus and depreciation after revaluation (IAS 16)

\text{Surplus} = \text{FV} - \text{CA}, \qquad \text{Depreciation} = \dfrac{\text{FV} - \text{Residual value}}{\text{Remaining life}}

$\text{FV}$: Fair value of the asset at the revaluation date$
$\text{CA}$: Carrying amount just before the revaluation (cost less accumulated depreciation)$
$\text{Residual value}$: Expected value at the end of the asset’s use$
$\text{Remaining life}$: Years of use left after the revaluationyears

Assumes. The surplus goes to other comprehensive income and equity unless it reverses an earlier revaluation loss on the same asset recognised in profit or loss.

A later fall in value under the revaluation model (IAS 16)

\text{OCI part} = \min(\text{Decrease},\ \text{Surplus}), \qquad \text{Loss in profit} = \text{Decrease} - \text{OCI part}

$\text{Decrease}$: Carrying amount just before the valuation minus the new fair value$
$\text{Surplus}$: Revaluation surplus still held in equity for the same asset$
$\text{OCI part}$: Part of the decrease taken against the surplus, through other comprehensive income$
$\text{Loss in profit}$: Part of the decrease recognised in profit or loss$

Assumes. Only the surplus on the same asset can absorb a decrease. No part of the surplus has been transferred to retained earnings (IAS 16 permits but doesn’t require such transfers).

Profit difference from capitalised development costs

\Delta \text{Profit}_{\text{IFRS} - \text{US}} = \text{Development capitalised} - \text{Amortisation}

$\Delta \text{Profit}_{\text{IFRS} - \text{US}}$: IFRS profit before tax minus US GAAP profit before tax for the period$
$\text{Development capitalised}$: Development costs recognised as an intangible asset in the period (zero under US GAAP)$
$\text{Amortisation}$: Amortisation of capitalised development costs charged in the period$

Assumes. Research costs and development costs incurred before the six criteria were met are expensed under both frameworks.

Restating IFRS profit before tax on a US GAAP basis

\text{PBT}_{\text{US}} = \text{PBT}_{\text{IFRS}} - \text{DC} + \text{AM} - \text{RV} + \text{XD}

$\text{PBT}_{\text{IFRS}},\ \text{PBT}_{\text{US}}$: Profit before tax under each framework$
$\text{DC}$: Development costs capitalised under IAS 38 in the period$
$\text{AM}$: Amortisation of development costs capitalised earlier$
$\text{RV}$: Reversals of inventory write-downs recognised in the period$
$\text{XD}$: Depreciation on revalued amounts above what cost-based depreciation would be, plus any revaluation losses in profit$

Assumes. Only the differences listed; revaluation surpluses go to other comprehensive income and so don’t enter. Classification differences (interest, leases) change subtotals, not profit before tax, except where lease expense patterns differ.

Operating cash flow: from US GAAP to IAS 7 as amended by IFRS 18

\text{CFO}_{\text{IFRS 18}} = \text{CFO}_{\text{US}} + \text{IP} - \text{IR} - \text{DR}

$\text{CFO}_{\text{US}}$: Cash from operating activities under US GAAP$
$\text{IP}$: Interest paid (moves to financing)$
$\text{IR}$: Interest received (moves to investing)$
$\text{DR}$: Dividends received (move to investing)$

Assumes. A company without a specified main business activity and no leases accounted for differently. Dividends paid are financing under both, so they don’t enter.

F04 Mathematics of Finance

1. Simple and compound interest

Simple interest

I = P \times r \times t, \qquad \text{FV} = P(1 + rt)

$I$: Interest for the whole time$
$P$: Principal: the amount lent, borrowed or deposited (its present value)$
$r$: Annual interest rate, as a decimal (6% = 0.06)per year
$t$: Timeyears
$\text{FV}$: Balance at the end, if the interest is added rather than paid out$

Assumes. Interest is earned on the original principal only.

Interest for part of a year (day count)

I = P \times r \times \frac{D}{B}

$I$: Interest accrued over the days counted$
$P$: Principal$
$r$: Annual interest rate, as a decimalper year
$D$: Days counted under the convention: actual calendar days, or 30 a month under 30/360days
$B$: Days in the convention’s year: 365 (actual/365) or 360 (actual/360 and 30/360)days

Assumes. Simple interest within the period.

Future value under compound interest

\text{FV} = \text{PV}(1 + r)^n

$\text{FV}$: Future value: the balance after n periods$
$\text{PV}$: Present value: the amount today$
$r$: Interest rate per period, as a decimalper period
$n$: Number of periods (may be fractional)periods

Assumes. A constant rate; interest stays in the balance; nothing is added or withdrawn.

Rate linking two amounts (compound annual growth rate)

r = \left(\frac{\text{FV}}{\text{PV}}\right)^{1/n} - 1

$r$: Constant compound rate per periodper period
$\text{FV}$: Amount at the end$
$\text{PV}$: Amount at the start$
$n$: Number of periods between themperiods

Number of periods to reach a target

n = \frac{\ln(\text{FV}/\text{PV})}{\ln(1 + r)}

$n$: Number of periods neededperiods
$\text{FV}$: Target amount$
$\text{PV}$: Starting amount$
$r$: Interest rate per period, as a decimalper period

Assumes. Smooth compounding at a constant rate.

Doubling time and the rule of 72

n_{\text{double}} = \frac{\ln 2}{\ln(1 + r)} \;\approx\; \frac{72}{100\,r}

$n_{\text{double}}$: Periods needed for an amount to doubleperiods
$r$: Interest rate per period, as a decimalper period

2. Effective annual rates

Periodic rate from a nominal annual rate

r = \frac{\text{APR}}{m}

$r$: Interest rate per compounding period, as a decimalper period
$\text{APR}$: Nominal annual rate: the quoted yearly rate, ignoring compoundingper year
$m$: Compounding periods per year (1, 2, 4, 12, 52, 365)

Effective annual rate

\text{EAR} = \left(1 + \frac{\text{APR}}{m}\right)^{m} - 1

$\text{EAR}$: Effective annual rate: the equivalent rate compounded once a yearper year
$\text{APR}$: Nominal annual rateper year
$m$: Compounding periods per year

Assumes. The rate stays the same for the year; no fees.

Nominal rate from an effective annual rate

\text{APR} = m\left[(1 + \text{EAR})^{1/m} - 1\right]

$\text{APR}$: Nominal annual rate compounded m times a yearper year
$\text{EAR}$: Effective annual rateper year
$m$: Compounding periods per year

Equivalent rate at another compounding frequency

\text{APR}_2 = m_2\left[\left(1 + \frac{\text{APR}_1}{m_1}\right)^{m_1/m_2} - 1\right]

$\text{APR}_1$: The quoted nominal annual rateper year
$m_1$: Compounding periods per year of the quoted rate
$\text{APR}_2$: The equivalent nominal annual rateper year
$m_2$: Compounding periods per year wanted

Continuous compounding

\text{FV} = \text{PV}\,e^{\text{APR}\,t}, \qquad \text{EAR} = e^{\text{APR}} - 1

$\text{FV}$: Future value after t years$
$\text{PV}$: Amount today$
$\text{APR}$: Nominal annual rate, compounded continuouslyper year
$t$: Timeyears
$e$: The constant 2.718282…, base of natural logarithms
$\text{EAR}$: Effective annual rateper year

Annual percentage yield (Regulation DD, Appendix A)

\text{APY} = 100\left[\left(1 + \frac{\text{Interest}}{\text{Principal}}\right)^{365/\text{Days in term}} - 1\right]

$\text{APY}$: Annual percentage yield%
$\text{Interest}$: Total interest earned on the principal over the term$
$\text{Principal}$: Amount assumed deposited at the start$
$\text{Days in term}$: Actual number of days in the term (365 for an account with no fixed term)days

Assumes. US deposit accounts; banks may use 366 days in a leap year.

3. Present and future value

Future value of a single amount

\text{FV} = \text{PV}\,(1 + r)^{n}

$\text{FV}$: Future value: what the amount is worth after n periods$
$\text{PV}$: Present value: the amount today (time 0)$
$r$: Interest rate per period, as a decimalper period
$n$: Number of periods; need not be a whole numberperiods

Assumes. One rate in every period; interest is added each period and left to earn interest; nothing is paid in or taken out.

Present value and the discount factor

\text{PV} = \frac{\text{FV}}{(1 + r)^{n}} = \text{FV} \times \text{DF}_n, \qquad \text{DF}_n = \frac{1}{(1 + r)^{n}}

$\text{PV}$: Present value: what the future amount is worth today$
$\text{FV}$: The amount received after n periods$
$\text{DF}_n$: Discount factor: the present value of $1 received after n periods
$r$: Discount rate per period, as a decimal: the return available elsewhere at similar riskper period
$n$: Number of periods until the amount is receivedperiods

Assumes. One amount at a single date; one discount rate throughout.

Rate per period and number of periods from a quoted annual rate

r = \frac{\text{APR}}{m}, \qquad n = m\,t, \qquad \text{PV} = \text{FV}\left(1 + \frac{\text{APR}}{m}\right)^{-m t}

$r$: Rate per compounding period, as a decimalper period
$\text{APR}$: Quoted (nominal) annual rate, as a decimalper year
$m$: Compounding periods per year (2 semi-annual, 4 quarterly, 12 monthly)per year
$n$: Number of compounding periodsperiods
$t$: Time until the amount is receivedyears
$\text{PV}$: Present value$
$\text{FV}$: The future amount$

Assumes. Interest is compounded m times a year at APR ÷ m each time.

Value at any date (moving an amount)

V_k = X\,(1 + r)^{k - j}

$V_k$: Value of the amount at the comparison date, period k$
$X$: The amount, which falls due at period j$
$r$: Rate per period, as a decimalper period
$j$: The period when the amount falls dueperiods
$k$: The comparison dateperiods

Assumes. One rate throughout.

Changing rates and the equivalent constant rate

\text{FV} = \text{PV}\,(1 + r_1)(1 + r_2)\cdots(1 + r_n), \qquad \bar{r} = \big[(1 + r_1)(1 + r_2)\cdots(1 + r_n)\big]^{1/n} - 1

$\text{FV}$: Value at the end of period n$
$\text{PV}$: Value at time 0$
$r_i$: Rate in period i, as a decimalper period
$n$: Number of periodsperiods
$\bar{r}$: The constant rate per period that gives the same growth (a geometric mean)per period

Assumes. Each period’s rate applies to the balance at the start of that period.

Solving for the rate or the number of periods

r = \left(\frac{\text{FV}}{\text{PV}}\right)^{1/n} - 1, \qquad n = \frac{\ln(\text{FV} / \text{PV})}{\ln(1 + r)}

$r$: Rate per period, as a decimalper period
$n$: Number of periodsperiods
$\text{PV}$: Amount at time 0$
$\text{FV}$: Amount after n periods$

Assumes. PV and FV have the same sign here (in a calculator or spreadsheet, enter them with opposite signs).

4. Discounting cash flows

Present value of an uneven stream

\text{PV} = \sum_{t=1}^{n} \frac{C_t}{(1 + r)^{t}}

$\text{PV}$: Present value of the stream at time 0$
$C_t$: Cash flow at the end of period t (positive if received, negative if paid)$
$r$: Discount rate per period, as a decimalper period
$t$: Period in which the cash flow arrivesperiods
$n$: Last period with a cash flowperiods

Assumes. Cash flows at the end of each period; one rate per period throughout.

Value of a stream at any date

V_T = \sum_{t} C_t\,(1 + r)^{T - t} = \text{PV} \times (1 + r)^{T}

$V_T$: Value of the whole stream at period T$
$C_t$: Cash flow at period t$
$r$: Rate per period, as a decimalper period
$T$: The comparison dateperiods
$t$: Period in which the cash flow arrivesperiods
$\text{PV}$: Value of the stream at time 0$

Net present value

\text{NPV} = \sum_{t=0}^{n} \frac{C_t}{(1 + r)^{t}} = C_0 + \frac{C_1}{1 + r} + \cdots + \frac{C_n}{(1 + r)^{n}}

$\text{NPV}$: Net present value of the decision$
$C_0$: Cash flow today, usually a cost (negative)$
$C_t$: Incremental cash flow at the end of period t$
$r$: Discount rate per period: the return available elsewhere at similar riskper period
$t$: Period in which the cash flow arrivesperiods
$n$: Last period with a cash flowperiods

Assumes. Only incremental cash flows; sunk costs left out; the rate reflects the risk of the cash flows.

Present value with irregular dates

\text{PV} = \sum_{i} \frac{C_i}{(1 + \text{EAR})^{t_i}}

$\text{PV}$: Present value of the cash flows$
$C_i$: The i-th cash flow$
$t_i$: Time from today until the i-th cash flowyears
$\text{EAR}$: Effective annual discount rate, as a decimalper year

Internal rate of return

0 = \sum_{t=0}^{n} \frac{C_t}{(1 + \text{IRR})^{t}}

$\text{IRR}$: Internal rate of return: the rate per period at which the NPV is zeroper period
$C_t$: Cash flow at period t, including the time-0 cost$
$t$: Period in which the cash flow arrivesperiods
$n$: Last period with a cash flowperiods

Assumes. Found by trial, interpolation or a spreadsheet; a unique IRR is guaranteed only when the cash flows change sign once.

Linear interpolation between two trial rates

\text{IRR} \approx r_L + (r_H - r_L)\,\frac{\text{NPV}_L}{\text{NPV}_L - \text{NPV}_H}

$r_L$: Lower trial rate, where the NPV is positiveper period
$r_H$: Higher trial rate, where the NPV is negativeper period
$\text{NPV}_L$: NPV at the lower rate$
$\text{NPV}_H$: NPV at the higher rate$

Assumes. The two trial rates bracket the IRR.

5. Annuities

Present value of an ordinary annuity

\text{PV} = C \times \frac{1 - (1 + r)^{-n}}{r}

$\text{PV}$: Present value: the value of the whole stream one period before the first payment$
$C$: Payment per period$
$r$: Interest rate per period, as a decimal (an APR compounded monthly ÷ 12 for monthly payments)per period
$n$: Number of paymentsperiods

Assumes. Equal payments at the end of each period; one constant rate; the rate and the payments refer to the same period.

Future value of an ordinary annuity

\text{FV} = C \times \frac{(1 + r)^{n} - 1}{r}

$\text{FV}$: Future value: the value of the stream at the date of the last payment$
$C$: Payment (deposit) per period$
$r$: Interest rate per period, as a decimalper period
$n$: Number of paymentsperiods

Assumes. Deposits at the end of each period, each earning the same rate until the last deposit is made.

Annuity due (payments at the start of each period)

\text{PV}_{\text{due}} = \text{PV}_{\text{ord}} \times (1 + r), \qquad \text{FV}_{\text{due}} = \text{FV}_{\text{ord}} \times (1 + r)

$\text{PV}_{\text{due}}, \text{FV}_{\text{due}}$: Values when each payment is made at the start of its period: the present value today (time 0, the date of the first payment) and the future value at the end of period n, one period after the last payment$
$\text{PV}_{\text{ord}}, \text{FV}_{\text{ord}}$: The same values for an ordinary annuity with the same payment, rate and number of payments$
$r$: Interest rate per period, as a decimalper period

Assumes. Same payment, rate and number of payments; only the timing differs.

Deferred annuity (first payment at the end of period k + 1)

\text{PV}_0 = \frac{1}{(1 + r)^{k}} \times C \times \frac{1 - (1 + r)^{-n}}{r}

$\text{PV}_0$: Value today$
$C$: Payment per period$
$r$: Interest rate per period, as a decimalper period
$n$: Number of paymentsperiods
$k$: Number of periods before the annuity formula’s valuation date: one less than the period of the first paymentperiods

Assumes. An ordinary annuity whose first payment is at the end of period k + 1.

Present value of a growing annuity

\text{PV} = \frac{C_1}{r - g}\left[1 - \left(\frac{1 + g}{1 + r}\right)^{n}\right]

$\text{PV}$: Present value, one period before the first payment$
$C_1$: The first payment, made at the end of period 1$
$r$: Interest rate per period, as a decimalper period
$g$: Growth rate of the payment per period, as a decimalper period
$n$: Number of paymentsperiods

Assumes. Each payment is (1 + g) times the one before; r ≠ g.

The payment of an annuity

C = \frac{\text{PV} \times r}{1 - (1 + r)^{-n}} \qquad\text{or}\qquad C = \frac{\text{FV} \times r}{(1 + r)^{n} - 1}

$C$: Payment per period$
$\text{PV}$: Amount to be repaid or drawn down (a loan, a fund)$
$\text{FV}$: Target to be reached by saving (a sinking fund)$
$r$: Interest rate per period, as a decimalper period
$n$: Number of paymentsperiods

Assumes. Ordinary annuity; for payments at the start of each period divide the result by (1 + r).

The number of payments

n = \frac{-\ln\left(1 - \dfrac{\text{PV} \times r}{C}\right)}{\ln(1 + r)}

$n$: Number of payments (usually not a whole number: round up, with a smaller last payment)periods
$\text{PV}$: Amount to be repaid$
$C$: Payment per period; must exceed PV × r$
$r$: Interest rate per period, as a decimalper period

Assumes. Ordinary annuity with C > PV × r.

6. Perpetuities and growth

Present value of a level perpetuity

\text{PV} = \frac{C}{r}

$\text{PV}$: Value one period before the first payment$
$C$: Payment per period, for ever$
$r$: Interest (discount) rate per period, as a decimalper period

Assumes. Equal payments at the end of each period, for ever; a constant rate.

Present value of a growing perpetuity

\text{PV} = \frac{C_1}{r - g}

$\text{PV}$: Value one period before the first payment$
$C_1$: The first payment, one period from now (C₁ = C₀(1 + g) if C₀ has just been paid)$
$r$: Discount rate per period, as a decimalper period
$g$: Constant growth rate of the payment per period, as a decimalper period

Assumes. r > g; the payment grows at g for ever.

Perpetuity due (first payment today)

\text{PV}_{\text{due}} = C + \frac{C}{r} = \frac{C(1 + r)}{r}

$\text{PV}_{\text{due}}$: Value today, including today’s payment$
$C$: Payment per period$
$r$: Rate per period, as a decimalper period

Assumes. Equal payments at the start of each period, for ever.

Deferred perpetuity (first payment at the end of period k + 1)

\text{PV}_0 = \frac{C}{r} \times \frac{1}{(1 + r)^{k}}

$\text{PV}_0$: Value today$
$C$: Payment per period$
$r$: Rate per period, as a decimalper period
$k$: One less than the period of the first paymentperiods

Assumes. A level perpetuity whose first payment is at the end of period k + 1.

Terminal value with constant growth

\text{TV}_N = \frac{\text{CF}_{N+1}}{r - g}

$\text{TV}_N$: Value at the end of year N of all cash flows after year N$
$\text{CF}_{N+1}$: Cash flow in the first year after the forecast, = CF_N × (1 + g)$
$N$: Last year of the explicit forecastyears
$r$: Discount rateper year
$g$: Long-run growth rate of the cash flows, below rper year

Assumes. From year N + 1 on, cash flows grow at a constant g < r for ever.

7. Loan amortisation

Payment on an amortising loan

C = \frac{\text{PV} \times r}{1 - (1 + r)^{-n}}

$C$: Level payment per period$
$\text{PV}$: Amount borrowed$
$r$: Interest rate per period, as a decimal (the nominal annual rate ÷ 12 for monthly payments)per period
$n$: Number of paymentsperiods

Assumes. Fixed rate; equal payments at the end of each period; the last payment repays the loan in full.

One row of an amortisation schedule

I_k = r \times B_{k-1}, \qquad P_k = C - I_k, \qquad B_k = B_{k-1} - P_k

$I_k$: Interest in payment k$
$P_k$: Principal repaid by payment k$
$B_k$: Balance after payment k (B₀ is the amount borrowed)$
$C$: Payment$
$r$: Interest rate per period, as a decimalper period

Assumes. Interest is charged on the balance at the start of each period.

Balance outstanding: prospective method

B_k = C \times \frac{1 - (1 + r)^{-(n-k)}}{r}

$B_k$: Balance after k payments$
$C$: Payment$
$r$: Interest rate per period, as a decimalper period
$n$: Total number of paymentsperiods
$k$: Number of payments already madeperiods

Assumes. All remaining payments are made as scheduled.

Balance outstanding: retrospective method

B_k = \text{PV}(1 + r)^{k} - C \times \frac{(1 + r)^{k} - 1}{r}

$B_k$: Balance after k payments$
$\text{PV}$: Amount borrowed$
$C$: Payment$
$r$: Interest rate per period, as a decimalper period
$k$: Number of payments already madeperiods

Assumes. The first k payments were all C, made at the end of each period.

Total interest, and interest paid between two dates

\text{Total interest} = nC - \text{PV}, \qquad \text{Interest in payments } j \text{ to } k = (k - j + 1)\,C - (B_{j-1} - B_k)

$n$: Number of paymentsperiods
$C$: Payment$
$\text{PV}$: Amount borrowed$
$j, k$: First and last payment of the stretchperiods
$B_{j-1}, B_k$: Balance before payment j and after payment k$

Assumes. Level payments.

APR of a loan with an up-front fee

\text{PV} - F = C \times \frac{1 - (1 + r)^{-n}}{r}, \qquad \text{APR} = m \times r

$\text{PV}$: Amount of the loan$
$F$: Fees paid up front (deducted from the loan or paid at signing)$
$C$: Payment per period$
$n$: Number of paymentsperiods
$r$: The rate per period that solves the equation, found by trial and error or =RATE(n, −C, PV − F)per period
$m$: Payment periods per year
$\text{APR}$: Annual percentage rate including the feeper year

Assumes. Level payments; the fee is the only extra charge.

8. Inflation and real returns

Cumulative and average annual inflation

I_n = \frac{\text{CPI}_n}{\text{CPI}_0} - 1, \qquad i_{\text{avg}} = \left(\frac{\text{CPI}_n}{\text{CPI}_0}\right)^{1/n} - 1

$I_n$: Cumulative inflation over the whole interval, as a decimal
$i_{\text{avg}}$: Average annual inflation rate: the constant yearly rate giving the same cumulative inflationper year
$\text{CPI}_0$: Price index at the startindex points
$\text{CPI}_n$: Price index at the endindex points
$n$: Number of years in the intervalyears

Assumes. Any price index can stand in for the CPI; use the same series at both dates.

Restating an amount in base-period dollars

\text{Real amount} = \text{Nominal amount}_t \times \frac{\text{CPI}_{\text{base}}}{\text{CPI}_t} = \frac{\text{Nominal amount}_t}{(1 + i)^{t}}

$\text{Nominal amount}_t$: An amount paid or received at date t, in that date’s dollars$
$\text{CPI}_t$: Price index at date tindex points
$\text{CPI}_{\text{base}}$: Price index at the base dateindex points
$i$: Constant annual inflation rate (the second form only, for forecasts)per year
$t$: Years from the base date to the paymentyears
$\text{Real amount}$: What the amount buys at base-date prices$ of the base date

Assumes. The index (or the inflation rate) measures the price changes that matter to the person or business.

Fisher relation (exact)

1 + r_{\text{nom}} = (1 + r_{\text{real}})(1 + i) \quad\Longleftrightarrow\quad r_{\text{real}} = \frac{1 + r_{\text{nom}}}{1 + i} - 1

$r_{\text{nom}}$: Nominal rate: growth in dollars, as an effective rate for the periodper period
$r_{\text{real}}$: Real rate: growth in purchasing power over the same periodper period
$i$: Inflation over the same periodper period

Assumes. All three rates cover the same period and are effective rates (convert an APR to an EAR first).

Real and nominal cash flows

C^{\text{nom}}_t = C^{\text{real}}_t\,(1 + i)^t

$C^{\text{nom}}_t$: Cash flow in year t, in the dollars of year t$
$C^{\text{real}}_t$: The same cash flow in today’s dollars$ of today
$i$: Expected annual inflationper year
$t$: Years from todayyears

Assumes. Inflation is expected to be constant at i; with a forecast that varies, multiply the yearly factors.

Consistent discounting

\text{PV} = \sum_{t=1}^{n} \frac{C^{\text{nom}}_t}{(1 + r_{\text{nom}})^t} = \sum_{t=1}^{n} \frac{C^{\text{real}}_t}{(1 + r_{\text{real}})^t}

$\text{PV}$: Present value today$
$C^{\text{nom}}_t$: Nominal cash flow at the end of year t$
$C^{\text{real}}_t$: Real cash flow at the end of year t, in today’s dollars$ of today
$r_{\text{nom}}$: Nominal discount rateper year
$r_{\text{real}}$: Real discount rate, linked to the nominal rate by the Fisher relationper year
$n$: Number of yearsyears
$t$: Year of each cash flowyears

Assumes. The same inflation rate links the cash flows and the discount rates.

Real growth of a salary or price

g_{\text{real}} = \frac{1 + g}{1 + i} - 1

$g_{\text{real}}$: Growth in purchasing power per yearper year
$g$: Nominal growth rate per year (for example a pay rise)per year
$i$: Inflation per yearper year

TIPS principal and interest (simplified)

\text{Adjusted principal}_t = \text{Principal} \times \frac{\text{CPI}_t}{\text{CPI}_0}, \qquad \text{Interest}_t = \text{Adjusted principal}_t \times \frac{r_{\text{fixed}}}{2}

$\text{Principal}$: Original principal (par value)$
$\text{Adjusted principal}_t$: Principal after the inflation adjustment at date t$
$\text{CPI}_0$: Reference price index when the security was issuedindex points
$\text{CPI}_t$: Reference price index at payment date tindex points
$r_{\text{fixed}}$: Fixed annual interest rate set at auctionper year
$\text{Interest}_t$: Interest paid at the six-monthly payment date t$

Assumes. Treasury uses a daily index ratio based on the CPI; the ratio CPI_t ÷ CPI_0 here stands in for it.

9. Mortgages

Fixed-rate mortgage payment (principal and interest)

C = \text{PV} \times \frac{r}{1 - (1 + r)^{-n}}, \qquad r = \frac{\text{annual rate}}{12}, \quad n = 12t

$C$: Monthly payment of principal and interest$ per month
$\text{PV}$: Amount borrowed: the price less the down payment$
$r$: Monthly interest rate: the annual interest rate in the loan contract ÷ 12, as a decimal (not the disclosed APR, which also includes points and fees)per month
$n$: Number of monthly paymentsmonths
$t$: Term of the loanyears

Assumes. Payments at the end of each month; the rate is fixed for the whole term.

Mortgage balance after k monthly payments

B_k = C \times \frac{1 - (1 + r)^{-(n - k)}}{r}

$B_k$: Balance still owed immediately after payment k$
$C$: Monthly payment$ per month
$r$: Monthly interest rateper month
$n$: Total number of payments in the termmonths
$k$: Number of payments already mademonths

Discount points: cost and simple break-even

\text{Cost of points} = p \times 1\% \times \text{PV}, \qquad \text{Break-even} \approx \frac{\text{Cost of points}}{C_0 - C_1}

$p$: Number of points bought (may be fractional)points
$\text{PV}$: Loan amount$
$C_0$: Monthly payment without points$ per month
$C_1$: Monthly payment at the lower rate$ per month
$\text{Break-even}$: Months of saving needed to recover the cost, ignoring time valuemonths

Assumes. Ignores the faster fall in the balance and the time value of money; the present-value test in “In depth” includes both.

APR with points (points as the only finance charge)

\text{PV} - F = C \times \frac{1 - (1 + q)^{-n}}{q}, \qquad \text{APR} = 12 \times q

$\text{PV}$: Loan amount$
$F$: Fees paid up front: here the cost of the points, paid at closing$
$C$: Monthly payment at the contract rate$ per month
$q$: Monthly rate that solves the equation, found by trial or a spreadsheet RATE functionper month
$n$: Number of monthly paymentsmonths
$\text{APR}$: Annual percentage rate including the pointsper year

Assumes. Points are the only finance charge beyond interest and the loan runs its full term.

Months to repay with a larger payment

n = -\frac{\ln\left(1 - \dfrac{r \times \text{PV}}{C}\right)}{\ln(1 + r)}

$n$: Months needed to repay the balance (round up: the last payment is smaller)months
$r$: Monthly interest rateper month
$\text{PV}$: Balance to be repaid$
$C$: Monthly payment, including the extra amount$ per month

Assumes. The larger payment is kept up every month; no prepayment penalty applies.

Real value of a fixed payment

\text{Real value in year } t = \frac{C}{(1 + i)^{t}}

$C$: Fixed nominal payment$
$i$: Annual inflation rateper year
$t$: Years from todayyears

Adjustable-rate mortgage: the new rate

\text{Fully indexed rate} = \text{Index} + \text{Margin}; \quad \text{new rate within } \text{current rate} \pm \text{adjustment cap} \text{ and } \text{initial rate} \pm \text{lifetime cap}

$\text{Fully indexed rate}$: Index plus margin: the rate before any caps apply% per year
$\text{Index}$: A market interest rate named in the loan, which changes over time% per year
$\text{Margin}$: Percentage points added to the index, fixed in the loan agreementpercentage points
$\text{adjustment cap}$: Most the rate can move at one reset (the initial cap at the first reset, the subsequent cap after)percentage points
$\text{lifetime cap}$: Most the rate can move over the life of the loanpercentage points
$\text{current rate}$: The rate charged before this reset% per year
$\text{initial rate}$: The loan’s starting rate% per year

F03 Financial Statement Analysis

1. Horizontal analysis

Dollar change

\Delta X = X_{t} - X_{b}

$X_{t}$: The line’s amount in the period analysed$
$X_{b}$: The same line in the base (earlier) period$

Percentage change

\%\Delta X = \dfrac{X_{t} - X_{b}}{\lvert X_{b} \rvert} \times 100

$X_{t}$: The line’s amount in the period analysed$
$X_{b}$: The amount in the base period; the absolute value keeps the sign right when the base is negative$

Assumes. The base is not zero. When it is, or when it is negative and the reader would be misled, report n/m and describe the change in dollars.

Trend percentage (base-year index)

\text{Index}_{t} = \dfrac{X_{t}}{X_{0}} \times 100

$X_{t}$: The line’s amount in year t$
$X_{0}$: The amount in the base year, which reads as 100$

Compound annual growth rate

\text{CAGR} = \left(\dfrac{X_{\text{end}}}{X_{\text{start}}}\right)^{1/n} - 1

$X_{\text{start}},\ X_{\text{end}}$: The amounts in the first and last year$
$n$: The number of years between them (gaps, not columns)years

Assumes. Both amounts are positive and cover periods of the same length.

Annualising a part-year flow

X_{\text{annual}} = X_{m} \times \dfrac{12}{m}

$X_{m}$: A flow (revenue, an expense, a cash flow) for a period of m months$
$m$: The number of months the period coversmonths

Assumes. The missing months are like the average of the months reported. Never annualise balance sheet amounts.

2. Common-size statements

Common-size income statement line

\text{Line, \% of revenue} = \dfrac{\text{Line amount}}{\text{Net revenue}} \times 100

$\text{Line amount}$: Any line of the income statement for the period (revenue, cost, subtotal)$
$\text{Net revenue}$: Revenue after returns, allowances and discounts, for the same period$

Common-size balance sheet line

\text{Line, \% of total assets} = \dfrac{\text{Line amount}}{\text{Total assets}} \times 100

$\text{Line amount}$: Any asset, liability or equity line at the balance sheet date$
$\text{Total assets}$: Total assets at the same date (equal to total liabilities and equity)$

Gross margin

\text{Gross margin} = \dfrac{\text{Net revenue} - \text{Cost of sales}}{\text{Net revenue}}

$\text{Net revenue}$: Revenue after returns, allowances and discounts$
$\text{Cost of sales}$: Cost of goods sold plus cost of services, as the business classifies them$

Assumes. Depends on what the business puts in cost of sales; compare only like definitions.

Operating margin

\text{Operating margin} = \dfrac{\text{Operating income}}{\text{Net revenue}}

$\text{Operating income}$: Gross profit less operating expenses (EBIT when there is no other income or expense)$
$\text{Net revenue}$: Revenue after returns, allowances and discounts$

EBITDA margin

\text{EBITDA margin} = \dfrac{\text{EBIT} + \text{D\&A}}{\text{Net revenue}}

$\text{EBIT}$: Earnings before interest and taxes; for Lantern, operating income$
$\text{D\&A}$: Depreciation and amortisation for the period (all of it, wherever it is presented)$
$\text{Net revenue}$: Revenue after returns, allowances and discounts$

Assumes. A non-GAAP measure: under US GAAP operating lease cost stays inside EBITDA; under IFRS 16 it does not.

Net margin

\text{Net margin} = \dfrac{\text{Net income}}{\text{Net revenue}}

$\text{Net income}$: Net income for the period (attributable to the parent’s owners when there are noncontrolling interests)$
$\text{Net revenue}$: Revenue after returns, allowances and discounts$

3. Liquidity

Working capital

\text{Working capital} = \text{Current assets} - \text{Current liabilities}

$\text{Current assets}$: Cash and the assets expected to become cash or be used up within a year (or the operating cycle, if longer)$
$\text{Current liabilities}$: Obligations due within a year, including the current portions of long-term debt and of lease liabilities$

Assumes. Both totals from the same balance sheet date.

Current ratio

\text{Current ratio} = \dfrac{\text{Current assets}}{\text{Current liabilities}}

$\text{Current assets}$: Total current assets$
$\text{Current liabilities}$: Total current liabilities$

Assumes. Every current asset will become cash in time to meet the current liabilities.

Quick (acid-test) ratio

\text{Quick ratio} = \dfrac{\text{Cash} + \text{Short-term investments} + \text{Receivables (net)}}{\text{Current liabilities}}

$\text{Cash}$: Cash and cash equivalents$
$\text{Short-term investments}$: Marketable securities that can be sold quickly at close to their value$
$\text{Receivables (net)}$: Accounts receivable less the allowance for doubtful accounts; not a contract asset$
$\text{Current liabilities}$: Total current liabilities$

Assumes. Inventory, prepayments, supplies and contract assets are left out.

Cash ratio

\text{Cash ratio} = \dfrac{\text{Cash} + \text{Short-term investments}}{\text{Current liabilities}}

$\text{Cash} + \text{Short-term investments}$: Money on hand and investments that can be sold at once$
$\text{Current liabilities}$: Total current liabilities$

Operating cash flow ratio

\text{Operating cash flow ratio} = \dfrac{\text{CFO}}{\text{Current liabilities}}

$\text{CFO}$: Net cash from operating activities for the period$
$\text{Current liabilities}$: Total current liabilities at the end of the period$

Assumes. A twelve-month period; annualise a part-year CFO (× 12 ÷ months) before comparing, and say that you did.

Working capital from the long-term side of the balance sheet

\Delta\text{Working capital} = \Delta\text{Equity} + \Delta\text{Noncurrent liabilities} - \Delta\text{Noncurrent assets}

$\Delta$: Closing balance minus opening balance$
$\text{Equity}$: Total shareholders’ equity$
$\text{Noncurrent liabilities}$: Long-term debt, noncurrent lease liabilities and other long-term obligations$
$\text{Noncurrent assets}$: Property and equipment (net), right-of-use assets and other long-term assets$

4. Solvency and leverage

Debt ratio (total liabilities to total assets)

\text{Debt ratio} = \dfrac{\text{Total liabilities}}{\text{Total assets}}

$\text{Total liabilities}$: Everything the business owes, current and noncurrent, including lease liabilities$
$\text{Total assets}$: Total assets on the same balance sheet$

Liabilities to equity

\text{Liabilities to equity} = \dfrac{\text{Total liabilities}}{\text{Equity}}

$\text{Total liabilities}$: Everything the business owes$
$\text{Equity}$: Total shareholders’ equity$

Debt to equity (interest-bearing debt)

\text{Debt to equity} = \dfrac{\text{Debt}}{\text{Equity}}

$\text{Debt}$: Interest-bearing debt: notes, loans, instalment notes, drawn credit lines and their current portions (add lease liabilities only as a stated adjustment)$
$\text{Equity}$: Total shareholders’ equity$

Assumes. Equity is positive; with negative equity the ratio is not meaningful.

Debt to capital

\text{Debt to capital} = \dfrac{\text{Debt}}{\text{Debt} + \text{Equity}}

$\text{Debt}$: Interest-bearing debt, as above$
$\text{Equity}$: Total shareholders’ equity$

Equity multiplier

\text{Equity multiplier} = \dfrac{\text{Total assets}}{\text{Equity}} = 1 + \dfrac{\text{Total liabilities}}{\text{Equity}}

$\text{Total assets}$: Total assets$
$\text{Equity}$: Total shareholders’ equity$

Interest coverage (times interest earned)

\text{Interest coverage} = \dfrac{\text{EBIT}}{\text{Interest expense}}

$\text{EBIT}$: Earnings before interest and taxes; for a business with no non-operating items, operating income$
$\text{Interest expense}$: Interest expense for the same period (it excludes the interest inside an operating lease cost under US GAAP)$

EBITDA coverage

\text{EBITDA coverage} = \dfrac{\text{EBIT} + \text{D\&A}}{\text{Interest expense}}

$\text{EBIT} + \text{D\&A}$: EBITDA: EBIT plus depreciation and amortisation$
$\text{Interest expense}$: Interest expense for the same period$

Net debt to EBITDA

\text{Net debt to EBITDA} = \dfrac{\text{Debt} - \text{Cash}}{\text{EBIT} + \text{D\&A}}

$\text{Debt} - \text{Cash}$: Net debt: interest-bearing debt less cash and short-term investments$
$\text{EBIT} + \text{D\&A}$: EBITDA for a twelve-month period (annualise a part-year)$

Assumes. Debt and EBITDA on the same basis: if lease liabilities are added to debt, add the lease cost back to EBITDA.

Lease-adjusted debt and EBITDA (US GAAP operating leases)

\text{Debt}_{\text{adj}} = \text{Debt} + \text{Lease liabilities}, \quad \text{EBITDA}_{\text{adj}} = \text{EBITDA} + \text{Operating lease cost}

$\text{Lease liabilities}$: Current and noncurrent operating lease liabilities$
$\text{Operating lease cost}$: The straight-line lease expense in operating income: its interest part moves to interest expense, its amortisation part to D&A$

5. Efficiency and working capital

Asset turnover

\text{Asset turnover} = \dfrac{\text{Revenue}}{\overline{\text{Total assets}}}

$\text{Revenue}$: Net revenue for a twelve-month period (annualise a part-year)$
$\overline{\text{Total assets}}$: Average of opening and closing total assets (closing if there is no opening balance sheet)$

Fixed-asset turnover

\text{Fixed-asset turnover} = \dfrac{\text{Revenue}}{\overline{\text{PP\&E, net}}}

$\text{Revenue}$: Net revenue for the period$
$\overline{\text{PP\&E, net}}$: Average property, plant and equipment, net of accumulated depreciation; right-of-use assets excluded unless stated$

Receivables turnover and days sales outstanding

\text{DSO} = \dfrac{\text{Days}}{\text{Receivables turnover}} = \dfrac{\overline{\text{Receivables}}}{\text{Credit sales}} \times \text{Days}

$\overline{\text{Receivables}}$: Average of opening and closing net receivables (not contract assets)$
$\text{Credit sales}$: Sales billed on credit during the period: the sales that passed through receivables$
$\text{Days}$: Days in the period: 365 for a year, 304.17 for ten monthsdays

Assumes. Receivables turnover = credit sales ÷ average receivables. When credit sales are not disclosed, total revenue is used and the result understates the days.

Inventory turnover and days inventory outstanding

\text{DIO} = \dfrac{\overline{\text{Inventory}}}{\text{COGS}} \times \text{Days}

$\overline{\text{Inventory}}$: Average of opening and closing inventory$
$\text{COGS}$: Cost of goods sold for the period (not cost of services)$
$\text{Days}$: Days in the perioddays

Assumes. Inventory turnover = COGS ÷ average inventory.

Payables turnover and days payables outstanding

\text{DPO} = \dfrac{\overline{\text{Payables}}}{\text{Purchases}} \times \text{Days}

$\overline{\text{Payables}}$: Average of opening and closing accounts payable$
$\text{Purchases}$: Purchases on credit for the period; cost of goods sold when purchases are not known (say so)$
$\text{Days}$: Days in the perioddays

Operating cycle and cash conversion cycle

\text{CCC} = \text{DIO} + \text{DSO} - \text{DPO}

$\text{DIO}$: Days inventory outstandingdays
$\text{DSO}$: Days sales outstandingdays
$\text{DPO}$: Days payables outstandingdays

Contract asset in days of revenue

\text{Contract asset days} = \dfrac{\text{Contract asset}}{\text{Revenue}} \times \text{Days}

$\text{Contract asset}$: Revenue earned but not yet billable, at the year end$
$\text{Revenue}$: Total revenue for the period, or the revenue of the contract concerned$
$\text{Days}$: Days in the perioddays

6. Return on assets and equity

Return on assets

\text{ROA} = \dfrac{\text{Net income}}{\overline{\text{Total assets}}}

$\text{Net income}$: Net income for the period (after interest and tax)$
$\overline{\text{Total assets}}$: Total assets, average of opening and closing (closing when there is no opening balance sheet)$
$\text{ROA}$: Return on assets%

Assumes. The period is a year; for a part-year, say whether the profit is annualised.

Return on equity

\text{ROE} = \dfrac{\text{Net income} - \text{Preferred dividends}}{\overline{\text{Common equity}}}

$\text{Net income}$: Net income for the period$
$\text{Preferred dividends}$: Dividends on preferred stock for the period (zero for Lantern)$
$\overline{\text{Common equity}}$: Common shareholders’ equity, average of opening and closing (closing when there is no opening balance sheet)$
$\text{ROE}$: Return on equity%

Assumes. Average equity is positive and not tiny relative to total assets; otherwise report ROE as not meaningful.

Return on equity from return on assets

\text{ROE} = \text{ROA} \times \dfrac{\overline{\text{Total assets}}}{\overline{\text{Equity}}}

$\text{ROA}$: Return on assets, on average total assets%
$\overline{\text{Total assets}}$: Total assets, average of opening and closing$
$\overline{\text{Equity}}$: Equity, average of opening and closing$
$\text{ROE}$: Return on equity, on average equity%

Assumes. Both ratios use the same numerator and the same basis (average or closing).

Return on equity and the leverage spread

\text{ROE} = \text{ROIC} + \bigl(\text{ROIC} - r(1-t)\bigr)\dfrac{D}{E}

$\text{ROIC}$: Return on invested capital: EBIT × (1 − t) ÷ (D + E)%
$r$: Interest rate on the debt: interest expense ÷ D%
$t$: Tax rate%
$D$: Interest-bearing debt$
$E$: Equity$
$\text{ROE}$: Return on equity%

Assumes. An identity when invested capital is D + E, there are no non-operating items, and tax is t of income before tax (a loss earns a tax credit at t).

Return on invested capital (outline)

\text{ROIC} = \dfrac{\text{EBIT} \times (1 - t)}{\overline{D + E}}

$\text{EBIT}$: Earnings before interest and taxes (Lantern: operating income)$
$t$: Tax rate%
$\overline{D + E}$: Interest-bearing debt plus equity, average of opening and closing$
$\text{ROIC}$: Return on invested capital%

Assumes. No adjustment for cash or leases; F05 develops the choices.

7. DuPont analysis

DuPont decomposition: three steps

\text{ROE} = \frac{\text{Net income}}{\text{Revenue}} \times \frac{\text{Revenue}}{\overline{\text{Total assets}}} \times \frac{\overline{\text{Total assets}}}{\overline{\text{Equity}}}

$\text{Net income}$: Net income for the period (less preferred dividends, if any)$
$\text{Revenue}$: Net revenue for the period$
$\overline{\text{Total assets}}$: Total assets, average of opening and closing (closing when there is no opening balance sheet)$
$\overline{\text{Equity}}$: Equity, average of opening and closing (closing when there is no opening balance sheet)$
$\text{ROE}$: Return on equity%

Assumes. The same average total assets in the turnover and the multiplier, so the product equals ROE exactly. Not meaningful when equity is negative.

DuPont decomposition: five steps

\text{ROE} = \frac{\text{NI}}{\text{EBT}} \times \frac{\text{EBT}}{\text{EBIT}} \times \frac{\text{EBIT}}{\text{Revenue}} \times \frac{\text{Revenue}}{\overline{\text{Total assets}}} \times \frac{\overline{\text{Total assets}}}{\overline{\text{Equity}}}

$\text{NI}$: Net income$
$\text{EBT}$: Earnings (income) before tax$
$\text{EBIT}$: Earnings before interest and taxes (Lantern: operating income)$
$\text{Revenue}$: Net revenue$
$\overline{\text{Total assets}}$: Total assets, average of opening and closing$
$\overline{\text{Equity}}$: Equity, average of opening and closing$
$\text{ROE}$: Return on equity%

Assumes. EBIT and EBT positive; with a loss the burdens lose their meaning. Interest inside operating lease cost stays in EBIT.

A change in ROE as a product of factor changes

\frac{\text{ROE}_1}{\text{ROE}_0} = \frac{m_1}{m_0} \times \frac{a_1}{a_0} \times \frac{e_1}{e_0}

$\text{ROE}_0,\ \text{ROE}_1$: Return on equity in the earlier and later period%
$m_0,\ m_1$: Net margin in each period%
$a_0,\ a_1$: Asset turnover in each period
$e_0,\ e_1$: Equity multiplier in each period

Assumes. Both periods decomposed on a stated basis; the result doesn’t depend on the order of the factors.

8. Cash flow and earnings quality

Cash from operations to net income

\text{CFO-to-NI} = \dfrac{\text{CFO}}{\text{NI}}

$\text{CFO}$: Net cash from operating activities for the period$
$\text{NI}$: Net income for the same period$

Assumes. Both figures cover the same period, so part-year periods need no annualising. Not meaningful when net income is close to zero or negative.

Total accruals (cash-flow measure)

\text{Accruals} = \text{NI} - \text{CFO}

$\text{NI}$: Net income$
$\text{CFO}$: Net cash from operating activities$

Accruals ratio

\text{Accruals ratio} = \dfrac{\text{NI} - \text{CFO}}{\overline{\text{Total assets}}}

$\text{NI} - \text{CFO}$: Total accruals for the period$
$\overline{\text{Total assets}}$: Average of opening and closing total assets (closing only when there is no opening balance sheet)$

Assumes. Scaling by assets makes the measure comparable across years and companies. Lease right-of-use assets enlarge the denominator for lessees under ASC 842 and IFRS 16.

Free cash flow

\text{FCF} = \text{CFO} - \text{Capex}

$\text{FCF}$: Free cash flow: operating cash left after investment in long-lived assets$
$\text{CFO}$: Net cash from operating activities$
$\text{Capex}$: Capital expenditure: cash paid for property, plant, equipment and similar assets, as a positive number$

Assumes. A non-GAAP measure: definitions vary (some subtract dividends, some only maintenance capex, Amazon later netted proceeds and incentives). State the one you use.

Capital expenditure to depreciation

\text{Capex-to-D\&A} = \dfrac{\text{Capex}}{\text{D\&A}}

$\text{Capex}$: Capital expenditure for the period$
$\text{D\&A}$: Depreciation and amortisation for the period$

9. Distress scores

Altman’s Z (1968): public manufacturers

Z = 1.2X_1 + 1.4X_2 + 3.3X_3 + 0.6X_4 + 1.0X_5

$X_1$: Working capital (current assets − current liabilities) ÷ total assets
$X_2$: Retained earnings ÷ total assets
$X_3$: EBIT ÷ total assets
$X_4$: Market value of equity ÷ total liabilities (book value)
$X_5$: Sales ÷ total assets
$Z$: The score: below 1.81 distress, 1.81–2.99 grey, above 2.99 safe

Assumes. Ratios as decimals, balances at the balance-sheet date. Fitted to US public manufacturers.

Altman’s Z′: private manufacturers

Z' = 0.717X_1 + 0.847X_2 + 3.107X_3 + 0.420X_4 + 0.998X_5

$X_1,\ X_2,\ X_3,\ X_5$: As in Z
$X_4$: Book value of equity ÷ total liabilities
$Z'$: The score: below 1.23 distress, 1.23–2.90 grey, above 2.90 safe

Altman’s Z″: non-manufacturers and private firms

Z'' = 6.56X_1 + 3.26X_2 + 6.72X_3 + 1.05X_4

$X_1$: Working capital ÷ total assets
$X_2$: Retained earnings ÷ total assets
$X_3$: EBIT ÷ total assets
$X_4$: Book value of equity ÷ total liabilities
$Z''$: The score: below 1.10 distress, 1.10–2.60 grey, above 2.60 safe

Assumes. Sales ÷ total assets is left out to reduce industry effects. The zones are Altman’s 4.35 and 5.85 for the version with the constant, less 3.25.

Z″ with its constant (the EM score)

\text{EM score} = 3.25 + Z''

$Z''$: Altman’s Z″ as above
$\text{EM score}$: The score on the scale calibrated to bond ratings: below 4.35 distress, 4.35–5.85 grey, above 5.85 safe

10. Benchmarking and annual reports

Position of a quartile in a sorted list (inclusive method)

\text{Position}_q = (n - 1) \times \dfrac{q}{4}

$n$: Number of values (peers)
$q$: Which quartile: 1, 2 (the median) or 3
$\text{Position}_q$: Position in the sorted list counting the smallest value as 0; interpolate between neighbours when it is not a whole number

Assumes. Excel’s QUARTILE.INC and PERCENTILE.INC use this rule; QUARTILE.EXC uses (n + 1) × q/4 counting from 1. With few peers the methods can differ, so state which you used.

B01 Introduction to Business Analytics

1. The four kinds of analytics

Expected value of a decision option

\text{EV} = \sum_{s} p_s \, V_s

$\text{EV}$: Expected value of the option: its probability-weighted average outcome$
$p_s$: Probability of scenario s; the probabilities of all the scenarios add up to 1
$V_s$: Outcome of the option, such as profit, if scenario s happens$
$s$: A scenario: one possible future, such as low, medium or high demand

Assumes. The scenarios cover the outcomes that matter and their probabilities are reasonable estimates. Expected value ignores risk: two options with the same expected value can differ greatly in how bad their worst case is.

Forecast error and percentage error

e = A - F \qquad \text{Percentage error} = \dfrac{A - F}{A} \times 100

$e$: Forecast errorunits of the forecast
$A$: Actual value, once it is knownunits of the forecast
$F$: Forecast made in advanceunits of the forecast

Assumes. The actual is measured in exactly the same way as the forecast was defined. Percentage error cannot be calculated when the actual is zero.

2. Business questions and measurable objectives

Rate per day, for periods of different length

\text{Daily rate} = \dfrac{\text{Count in the period}}{\text{Days in the period}}

$\text{Count in the period}$: Number of events in the period, such as orders or visitsevents
$\text{Days in the period}$: Length of the perioddays

Assumes. Every day is treated as comparable; differences between weekdays and weekends, and holidays, are ignored.

3. Problem-solving frameworks

Growth of a product of drivers

1 + g_R = (1 + g_V)(1 + g_c)(1 + g_A)

$g_R$: Growth rate of revenue between the two periods, as a decimal (−0.05 for a 5% fall)
$g_V$: Growth rate of visits
$g_c$: Growth rate of the conversion rate (a relative change, not a change in percentage points)
$g_A$: Growth rate of average order value

Assumes. Revenue is exactly visits × conversion rate × average order value, which is an identity when the three are calculated from the same visits, orders and revenue.

Step-by-step (sequential) contributions to a change in revenue

\Delta_V = (V_1 - V_0)\,c_0 A_0 \qquad \Delta_c = V_1 (c_1 - c_0) A_0 \qquad \Delta_A = V_1 c_1 (A_1 - A_0)

$V_0,\ V_1$: Visits in the earlier and the later periodvisits
$c_0,\ c_1$: Conversion rate in the earlier and the later period, as a decimal
$A_0,\ A_1$: Average order value in the earlier and the later period$ per order
$\Delta_V,\ \Delta_c,\ \Delta_A$: Change in revenue attributed to visits, conversion and order value, changed in that order$

Assumes. The drivers are changed one at a time in a stated order: visits, then conversion, then order value. Each step keeps the drivers already changed at their new values, which is why the three contributions add up exactly to the change in revenue, V₁c₁A₁ − V₀c₀A₀. Changing each driver alone from the old values gives contributions that do not add up.

4. Types of data

Position of the median

\text{Position} = \dfrac{n + 1}{2}

$n$: Number of observations, sorted from lowest to highestcount

Assumes. The values can be put in order, so the variable is at least ordinal.

Mean of a rating scale from its counts

\bar{x} = \dfrac{\sum_{s} s \times f_s}{\sum_{s} f_s}

$\bar{x}$: Mean ratingstars
$s$: A score on the scale, such as 1 to 5 starsstars
$f_s$: Number of responses with score sresponses

Assumes. Treats the steps between scores as equal, which an ordinal scale doesn’t guarantee.

5. Collecting data: sources, samples and bias

Response rate

\text{Response rate} = \dfrac{r}{N} \times 100\%

$r$: Completed responsespeople
$N$: People invited who could have respondedpeople

Assumes. Say what N includes. Organisations differ on whether invitations that never arrived (bounced e-mails, wrong numbers) count.

Range allowed by non-response

\text{Lowest} = \dfrac{y}{N} \times 100\% \qquad \text{Highest} = \dfrac{y + (N - r)}{N} \times 100\%

$y$: Respondents giving the answer of interest (for example, “satisfied”)people
$r$: Respondentspeople
$N$: People invitedpeople

Assumes. No assumption at all about the non-respondents: the lowest figure supposes none of them gives the answer, the highest that all do.

Combining respondents and a follow-up of non-respondents

\hat{p} = \dfrac{r \times p_r + (N - r) \times p_n}{N}

$\hat{p}$: Estimated share of everyone invited giving the answer%
$p_r$: Share among respondents%
$p_n$: Share among non-respondents, from a follow-up of a random sample of them%
$r$: Respondentspeople
$N$: People invitedpeople

Assumes. The follow-up sample is random and its members answer like the other non-respondents.

6. Data quality: is the data fit for the question?

Completeness of a required field

\text{Completeness} = \dfrac{\text{Values present}}{\text{Values required}} \times 100\%

$\text{Values present}$: Non-blank values on the rows that require onecount
$\text{Values required}$: Rows on which a value is requiredcount

Assumes. Only required values count. For an optional field, such as a rating, report the share filled in and don’t call the blanks defects.

Overstatement caused by duplicates

\text{Overstatement} = \dfrac{\text{Rows counted} - \text{True records}}{\text{True records}} \times 100\%

$\text{Rows counted}$: Rows in the file, duplicates includedrows
$\text{True records}$: Distinct real-world things (orders, bookings) the rows describecount

Assumes. Applies to counts. A total is overstated by the value of the duplicated rows divided by the true total.

Effect of one wrong value on a mean

\text{Shift in the mean} = \dfrac{x_{\text{wrong}} - x_{\text{right}}}{n}

$x_{\text{wrong}}$: The value recordedunits of the variable
$x_{\text{right}}$: The value that should have been recordedunits of the variable
$n$: Number of values in the meancount

Assumes. The number of values stays the same: the wrong value is corrected, not removed.

7. KPIs: measuring what matters

Conversion rate

\text{CR} = \dfrac{O}{V} \times 100

$\text{CR}$: Conversion rate: orders per 100 visits%
$O$: Orders placed in the period, as the KPI dictionary defines an orderorders
$V$: Visits (sessions) in the same period and channelvisits

Assumes. O and V cover the same period and the same channel.

Average order value

\text{AOV} = \dfrac{R}{O}

$\text{AOV}$: Average order value$ per order
$R$: Revenue from the orders in the period$
$O$: Orders in the periodorders

Revenue from its drivers

R = O \times \text{AOV} = V \times \dfrac{\text{CR}}{100} \times \text{AOV}

$R$: Revenue$
$O$: Ordersorders
$V$: Visitsvisits
$\text{CR}$: Conversion rate%
$\text{AOV}$: Average order value$ per order

Returning-customer share of orders

\text{RS} = \dfrac{O - N}{O} \times 100

$\text{RS}$: Share of the period’s orders placed by returning customers%
$O$: Orders in the periodorders
$N$: Orders that were a customer’s first ever orderorders

Assumes. Every order that is not a first order counts as returning.

Rating response rate

\text{RR} = \dfrac{O_r}{O} \times 100

$\text{RR}$: Rating response rate%
$O_r$: Orders placed in the period that were ratedorders
$O$: Orders placed in the periodorders

Gap to target

\text{Gap} = A - T, \qquad \text{Share of target} = \dfrac{A}{T} \times 100

$A$: Actual value of the KPIunits of the KPI
$T$: Target valueunits of the KPI

8. Summarising data: changes, averages and spread

Absolute and percentage change

\Delta = x_{\text{new}} - x_{\text{old}}, \qquad \%\Delta = \dfrac{x_{\text{new}} - x_{\text{old}}}{x_{\text{old}}} \times 100

$x_{\text{old}}$: The starting value: the baseunits of x
$x_{\text{new}}$: The later valueunits of x
$\Delta$: Absolute changeunits of x
$\%\Delta$: Percentage change%

Change in percentage points

\Delta_{\text{pp}} = p_{\text{new}} - p_{\text{old}}

$p_{\text{old}},\ p_{\text{new}}$: Two percentages, such as conversion rates in two months%
$\Delta_{\text{pp}}$: Change in percentage pointspercentage points

Compound growth rate

g = \left(\dfrac{x_{\text{end}}}{x_{\text{start}}}\right)^{1/n} - 1

$g$: Constant growth rate per period that links start and endper period
$x_{\text{start}},\ x_{\text{end}}$: Values at the start and endunits of x
$n$: Number of periods (steps) between themperiods

Assumes. Both values positive.

Mean and median

\bar{x} = \dfrac{1}{n}\sum_{i=1}^{n} x_i, \qquad \text{median position} = \dfrac{n + 1}{2}

$x_i$: The i-th valueunits of x
$n$: Number of values
$\bar{x}$: Meanunits of x

Quartiles and interquartile range

\text{position of } Q_p = 1 + (n - 1)p, \qquad \text{IQR} = Q_3 - Q_1

$Q_p$: Quartile: the value with a share p of the sorted data below itunits of x
$p$: The share below the quartile: 0.25 for Q1, 0.75 for Q3
$n$: Number of values
$\text{IQR}$: Interquartile rangeunits of x

Assumes. Quartiles by linear interpolation: values sorted from smallest to largest, interpolating between neighbours when the position is not a whole number (as Excel’s QUARTILE.INC does).

Sample standard deviation

s = \sqrt{\dfrac{\sum_{i=1}^{n} (x_i - \bar{x})^2}{n - 1}}

$s$: Sample standard deviationunits of x
$x_i$: The i-th valueunits of x
$\bar{x}$: Mean of the valuesunits of x
$n$: Number of values

Weighted average

\bar{x}_w = \dfrac{\sum_{i} w_i x_i}{\sum_{i} w_i}

$x_i$: The values being averaged, such as each channel’s conversion rateunits of x
$w_i$: Their weights, such as each channel’s visitsunits of the weight
$\bar{x}_w$: Weighted averageunits of x

9. Interpreting results: charts, comparisons and causes

Rate per day

\text{Per day} = \dfrac{X}{d}

$X$: A count for the period, such as orders in a monthunits of X
$d$: Days in the perioddays

Overall rate as a mix of group rates

r = \sum_{i} s_i\, r_i, \qquad s_i = \dfrac{V_i}{\sum_j V_j}

$r$: Overall rate, such as overall conversion%
$r_i$: Rate in group i, such as a channel’s conversion%
$s_i$: Group i’s share of the denominator; the shares add to 1
$V_i$: Group i’s denominator, such as its visitsvisits

Mix-adjusted rate

r_B^{\,\text{at A’s mix}} = \sum_i s_{A,i}\, r_{B,i}

$r_{B,i}$: Rate in group i in period B%
$s_{A,i}$: Group i’s share of the denominator in period A

Difference in differences

\text{Effect} \approx (T_{\text{after}} - T_{\text{before}}) - (C_{\text{after}} - C_{\text{before}})

$T_{\text{before}},\ T_{\text{after}}$: The measure in the group that got the change, before and after itunits of the measure
$C_{\text{before}},\ C_{\text{after}}$: The same measure in a comparison group that did notunits of the measure

Assumes. Without the change, the two groups would have moved in parallel.

B02 Data Cleaning and Preparation

1. Datasets: rows, columns, records and variables

Average per unit

\bar{x}_{\text{unit}} = \dfrac{\text{Sum of the values over all rows}}{\text{Number of distinct units}}

$\bar{x}_{\text{unit}}$: The average per unit you are asking about: per order, per customer, per bookingunit of the values
$\text{Sum of the values over all rows}$: The column total, for example of line_totalunit of the values
$\text{Number of distinct units}$: The number of distinct values of the unit’s identifier, such as distinct order_idunits

Assumes. The values can be added within a unit: they are ratio amounts such as dollars or packs.

Primary key test

\text{Distinct key values} = n \quad \text{and} \quad \text{Rows with a blank key} = 0

$n$: Number of rows in the tablerows
$\text{Distinct key values}$: Distinct values of the candidate key, or distinct combinations for a composite keyrows

Assumes. Blank means empty or null; placeholder codes such as “unknown” count as values and would also need checking.

2. Profiling a dataset and writing validation rules

Completeness of a column

\text{Completeness} = \dfrac{\text{Values present}}{\text{Values required}} \times 100\%

$\text{Values present}$: Non-blank values on the rows that require one; placeholder codes known to mean “missing” count as blankcount
$\text{Values required}$: Rows on which the data dictionary requires a valuecount

Assumes. The data dictionary says which rows require a value.

Failure rate of a validation rule

\text{Failure rate} = \dfrac{\text{Rows that fail the rule}}{\text{Rows the rule checks}} \times 100\%

$\text{Rows the rule checks}$: Rows the rule applies to; blanks in an optional column are left outrows

Uniqueness of a key

\text{Uniqueness} = \dfrac{\text{Distinct key values}}{\text{Rows}} \times 100\%

$\text{Distinct key values}$: Distinct values of the primary keycount
$\text{Rows}$: Rows in the tablecount

Assumes. The key identifies the real-world thing. Records of the same thing under different keys are not detected; the lesson on duplicates deals with them.

3. Duplicate records

Surplus (duplicate) rows

\text{Surplus rows} = n - n_{\text{distinct}}

$n$: Number of rows in the tablerows
$n_{\text{distinct}}$: Number of distinct values of the business key (or of whole rows, when counting exact copies)rows

Assumes. One row per distinct key should survive.

Duplicate rate

\text{Duplicate rate} = \dfrac{n - n_{\text{distinct}}}{n}

$n,\ n_{\text{distinct}}$: As aboverows

Mean reported when duplicates are left in

\bar{x}_{\text{reported}} = \dfrac{n\,\bar{x} + k\,\bar{d}}{n + k}

$n$: Number of genuine recordsrecords
$\bar{x}$: True mean of the genuine recordsunits of the variable
$k$: Number of surplus duplicate rowsrows
$\bar{d}$: Mean of the variable in the surplus rowsunits of the variable

Edit (Levenshtein) distance, filled in as a table

d_{i,j} = \min\begin{cases} d_{i-1,j} + 1 \\ d_{i,j-1} + 1 \\ d_{i-1,j-1} + c_{ij} \end{cases} \qquad d_{i,0} = i,\quad d_{0,j} = j

$d_{i,j}$: Distance between the first i characters of string a and the first j characters of string bedits
$c_{ij}$: Substitution cost: 0 if the i-th character of a equals the j-th character of b, otherwise 1edits
$d_{i,0},\ d_{0,j}$: Turning i (or j) characters into an empty string takes one edit per characteredits
$i,\ j$: Number of characters taken from the start of a and of bcharacters

Assumes. Each insertion, deletion and substitution costs one edit.

Normalised edit similarity

\text{Similarity} = 1 - \dfrac{d(a,b)}{\max(|a|,\,|b|)}

$d(a,b)$: Edit distance between strings a and bedits
$|a|,\ |b|$: Lengths of the two stringscharacters

4. Incorrect data types and inconsistent dates

Spreadsheet serial number (1900 date system)

S_{1900} = \text{date} - \text{30 December 1899}

$S_{1900}$: The serial number Excel’s 1900 date system stores for the datedays
$\text{date}$: The calendar date, on or after 1 March 1900date

Assumes. Valid from 1 March 1900. Excel counts a non-existent 29 February 1900 (serial 60), so earlier dates are one day out.

Converting between the 1904 and 1900 date systems

S_{1900} = S_{1904} + 1{,}462

$S_{1904}$: Serial number in the 1904 date system, which counts 1 January 1904 as day 0days
$1{,}462$: Days between the two starting points: four years and one leap daydays

5. Text cleaning and consistent categories

Mean of a consolidated category

\bar{x}_{\text{category}} = \dfrac{\sum_{v} n_v\, \bar{x}_v}{\sum_{v} n_v}

$v$: Each variant that maps to the category (for example “North”, “north”, “N.”)
$n_v$: Number of rows with variant vrows
$\bar{x}_v$: Mean of the variable among rows with variant vunits of the variable

Vocabulary coverage

\text{Coverage} = \dfrac{\text{rows whose cleaned value is in the controlled vocabulary}}{\text{all rows}}

$\text{Coverage}$: Share of rows the cleaning rules have placed in an allowed categoryproportion (0–1)

6. Missing values

Standard deviation after mean imputation

s_{\text{after}} = s\sqrt{\dfrac{n - 1}{n + m - 1}}

$s$: Sample standard deviation of the observed values (dividing by n − 1)units of the variable
$n$: Number of observed values
$m$: Number of missing values filled with the observed mean

Assumes. Every gap is filled with the mean of the observed values; the mean itself is unchanged.

Group-weighted mean (group-wise imputation)

\hat{\mu} = \dfrac{\sum_g N_g\,\bar{y}_g}{\sum_g N_g}

$N_g$: Number of rows in group g, including those with a missing valuerows
$\bar{y}_g$: Mean of the observed values in group gunits of the variable

Assumes. Within each group, the rows with a missing value are like the rows without (MAR given the grouping variable).

Pooling multiple imputations (Rubin’s rules)

\bar{Q} = \dfrac{1}{m}\sum_{j=1}^{m} Q_j, \qquad T = \bar{U} + \left(1 + \dfrac{1}{m}\right)B

$Q_j$: The estimate (for example a mean or a coefficient) from completed dataset junits of the estimate
$m$: Number of completed datasets
$\bar{U}$: Average of the squared standard errors across the m analyses (within-imputation variance)squared units
$B$: Sample variance of the m estimates (between-imputation variance)squared units
$T$: Total variance of the pooled estimate; its square root is the standard errorsquared units

Assumes. The imputations are drawn from a model that reflects the uncertainty about the missing values; valid under MAR when the model includes the variables that drive missingness.

7. Outliers and impossible values

Quantile by linear interpolation

\text{position} = 1 + (n - 1)p, \qquad Q_p = x_{(k)} + f\,\bigl(x_{(k+1)} - x_{(k)}\bigr)

$n$: Number of values
$p$: The proportion wanted: 0.25 for Q1, 0.5 for the median, 0.75 for Q3
$x_{(k)}$: The k-th smallest value, where k is the whole-number part of the positionunits of the variable
$f$: The fractional part of the position
$Q_p$: The p-th quantile of the sorted valuesunits of the variable

Assumes. Values sorted from smallest to largest; the same method as Excel’s QUARTILE.INC and NumPy’s default.

Interquartile range and Tukey fences

\text{IQR} = Q_3 - Q_1, \qquad Q_1 - 1.5\,\text{IQR}, \quad Q_3 + 1.5\,\text{IQR}

$Q_1,\ Q_3$: First and third quartilesunits of the variable
$\text{IQR}$: Interquartile range: the width of the middle half of the dataunits of the variable

Assumes. The multiplier 1.5 is Tukey’s convention; outer fences use 3.

Z-score

z_i = \dfrac{x_i - \bar{x}}{s}

$x_i$: The value being scoredunits of the variable
$\bar{x}$: Mean of all values, including x_iunits of the variable
$s$: Sample standard deviation (dividing by n − 1)units of the variable
$z_i$: Z-score: standard deviations x_i lies above (+) or below (−) the mean

Assumes. Roughly normal data with few outliers; |z| > 3 is a common convention.

Modified z-score

M_i = \dfrac{0.6745\,(x_i - \tilde{x})}{\text{MAD}}

$x_i$: The value being scoredunits of the variable
$\tilde{x}$: Median of the valuesunits of the variable
$\text{MAD}$: Median absolute deviation: the median of |x_i − median|units of the variable
$0.6745$: Makes M comparable with a z-score for normal data
$M_i$: Modified z-score: distance from the median in robust standard-deviation units

Assumes. Flag |M| > 3.5 (Iglewicz and Hoaglin). Undefined when the MAD is 0.

8. Skewed data and transformations

Sample skewness (adjusted Fisher–Pearson coefficient)

G_1 = \dfrac{\sqrt{n(n-1)}}{n-2}\cdot\dfrac{m_3}{m_2^{3/2}}

$G_1$: Sample skewness: positive for a long right tail, negative for a long left tail
$n$: Number of values (at least 3)
$m_2$: Mean squared deviation from the mean, dividing by nsquared units
$m_3$: Mean cubed deviation from the mean, dividing by ncubed units

Assumes. The same definition as Excel’s SKEW and pandas’ skew(). Near 0 for symmetric data.

Log and log(1 + x) transformations

y = \ln x \quad (x > 0), \qquad y = \ln(1 + x) \quad (x > -1)

$x$: The original valueunits of the variable
$y$: The transformed valuelog units

Box–Cox transformation

x^{(\lambda)} = \dfrac{x^{\lambda} - 1}{\lambda}\ (\lambda \neq 0), \qquad x^{(0)} = \ln x

$x$: The original value, strictly positiveunits of the variable
$\lambda$: The power, estimated from the (training) data

Assumes. All values positive; for zeros or negatives use Yeo–Johnson.

Geometric mean

\text{GM} = \left(\prod_{i=1}^{n} x_i\right)^{1/n} = \exp\!\left(\dfrac{1}{n}\sum_{i=1}^{n}\ln x_i\right)

$x_i$: Positive valuesunits of the variable
$\text{GM}$: Geometric mean: the back-transformed mean of the logsunits of the variable
$n$: Number of values

Log difference and percentage change

d = \ln\dfrac{x_{\text{new}}}{x_{\text{old}}}, \qquad \text{percentage change} = (e^{d} - 1) \times 100\%

$x_{\text{new}},\ x_{\text{old}}$: The new and old valuesunits of the variable
$d$: Difference between the logs of the new and old valueslog units

9. Normalisation and standardisation

Euclidean distance between two observations

d(\mathbf{p}, \mathbf{q}) = \sqrt{\sum_{j=1}^{k} (p_j - q_j)^2}

$d(\mathbf{p}, \mathbf{q})$: Straight-line distance between observations p and q
$p_j,\ q_j$: The two observations’ values on variable junits of variable j
$k$: The number of variables

Assumes. Every variable is measured on a comparable scale; otherwise the variable with the widest numeric spread dominates.

Min–max normalisation

x' = \dfrac{x - x_{\min}}{x_{\max} - x_{\min}}

$x$: The value to scaleoriginal units
$x_{\min},\ x_{\max}$: Smallest and largest values in the training dataoriginal units
$x'$: The scaled value: 0 at the training minimum, 1 at the training maximum

Assumes. The training range is representative; new values outside it scale below 0 or above 1.

Standardisation (z-score)

z = \dfrac{x - \bar{x}}{s}

$\bar{x}$: Mean of the training dataoriginal units
$s$: Standard deviation of the training data (sample formula, n − 1, in this course)original units
$z$: Standard deviations above (+) or below (−) the training mean

Assumes. The mean and standard deviation describe the bulk of the data; extreme values inflate both.

Robust scaling

r = \dfrac{x - \tilde{x}}{Q_3 - Q_1}

$\tilde{x}$: Median of the training dataoriginal units
$Q_1,\ Q_3$: First and third quartiles of the training data (linear interpolation in this course)original units
$r$: Interquartile ranges above (+) or below (−) the training median

Assumes. The IQR is greater than zero.

Converting scaled values back

x = x_{\min} + x'(x_{\max} - x_{\min}), \qquad x = \bar{x} + z\,s, \qquad x = \tilde{x} + r\,(Q_3 - Q_1)

$x$: The value in the original unitsoriginal units
$x',\ z,\ r$: A min–max, standardised or robust-scaled value, with the parameters it was fitted with
$x_{\min},\ x_{\max}$: Training minimum and maximum (min–max)original units
$\bar{x},\ s$: Training mean and standard deviation (standardisation)original units
$\tilde{x},\ Q_1,\ Q_3$: Training median and first and third quartiles (robust scaling)original units

Coefficient per original unit from a standardised coefficient

b_j = \dfrac{b_j^{(z)}}{s_j}

$b_j^{(z)}$: Coefficient on the standardised predictor j: change in the prediction per standard deviation of the predictorunits of y
$s_j$: Standard deviation of predictor j used for the scalingunits of predictor j
$b_j$: Change in the prediction per one-unit increase in predictor junits of y per unit of predictor j

Assumes. Only the predictors were standardised, not the target.

10. Encoding categorical variables

Columns created by one-hot and dummy coding

p_{\text{one-hot}} = \sum_{j} k_j, \qquad p_{\text{dummy}} = \sum_{j} (k_j - 1)

$k_j$: Number of categories of categorical variable j (after cleaning)categories
$p$: Number of columns the encoded variables occupycolumns

Assumes. A two-category variable needs only one 0/1 column, which is what both formulas give for it when k − 1 = 1.

Dummy coefficients with one categorical predictor

\hat{y} = b_0 + \sum_{c \neq \text{ref}} b_c D_c, \qquad b_0 = \bar{y}_{\text{ref}}, \qquad b_c = \bar{y}_c - \bar{y}_{\text{ref}}

$D_c$: Dummy variable: 1 if the row is in category c, 0 otherwise
$\bar{y}_{\text{ref}},\ \bar{y}_c$: Mean of the target in the reference category and in category cunits of y
$b_0,\ b_c$: Intercept and the coefficient of category cunits of y

Assumes. Least squares with an intercept and this categorical variable as the only predictor; with other predictors, each coefficient is the difference from the reference holding the others constant.

Frequency encoding

f_c = \dfrac{n_c}{n}

$n_c$: Training rows in category crows
$n$: All training rowsrows
$f_c$: Frequency encoding of category c: its share of the training rows

Target encoding (category mean)

\bar{y}_c = \dfrac{1}{n_c}\sum_{i \in c} y_i

$y_i$: Target value of training row i (1 or 0 for a yes/no target)units of y
$n_c$: Training rows in category crows

Assumes. Computed out-of-fold for training rows; test rows use the full training averages.

Smoothed target encoding

S_c = \lambda_c\,\bar{y}_c + (1 - \lambda_c)\,\bar{y}, \qquad \lambda_c = \dfrac{n_c}{n_c + m}

$S_c$: Smoothed target encoding of category cunits of y
$\bar{y}_c$: Mean of the target in category cunits of y
$n_c$: Training rows in category crows
$\bar{y}$: Overall mean of the target in the training dataunits of y
$\lambda_c$: Weight on the category’s own mean, between 0 and 1
$m$: Smoothing strength: the larger it is, the more small categories are pulled towards the overall meanrows

Assumes. m is chosen by the analyst or estimated from the data (scikit-learn’s default estimates it).

11. Transforming and reshaping data

Expected order total

\text{expected total} = q \times p - d

$q$: Quantity: number of packs orderedpacks
$p$: Unit price of one pack$
$d$: Discount on the whole order$

Assumes. One product per order row, and the discount recorded per order rather than per item.

Nights between two dates

\text{nights} = \text{check-out date} - \text{check-in date}

$\text{check-in date}$: Date of arrival, stored as a true datedate
$\text{check-out date}$: Date of departure, stored as a true datedate
$\text{nights}$: Number of nights stayed; the check-out day is not countednights

Assumes. Both columns are converted to dates first; subtracting day-of-month numbers fails across month ends.

Width of equal-width bins

w = \dfrac{x_{\max} - x_{\min}}{k}

$w$: Width of every binsame unit as x
$x_{\max},\ x_{\min}$: Largest and smallest valuesame unit as x
$k$: Number of bins

Assumes. Edges are $x_{\min},\ x_{\min} + w,\ \dots,\ x_{\max}$ ; a stated rule decides which bin gets a value that sits exactly on an edge.

Overall mean from group means

\bar{x} = \dfrac{\sum_g n_g\,\bar{x}_g}{\sum_g n_g}

$\bar{x}_g$: Mean of group gunit of x
$n_g$: Number of rows in group grows
$\bar{x}$: Mean of all the rowsunit of x

Assumes. Each group mean was computed over the same kind of row (orders, nights, students) that the overall mean is about.

Rows produced by inner and left joins

N_{\text{inner}} = \sum_k a_k\, b_k \qquad N_{\text{left}} = \sum_k a_k \max(b_k,\, 1)

$a_k$: Rows with key value k in the left tablerows
$b_k$: Rows with key value k in the right tablerows
$N$: Rows in the joined tablerows

Assumes. The join matches on equal keys; the sums run over every key value that occurs in either table.

Rows produced by a full outer join

N_{\text{full}} = N_{\text{inner}} + U_L + U_R

$N_{\text{full}},\ N_{\text{inner}}$: Rows in the full outer join and in the inner joinrows
$U_L$: Left rows whose key has no match in the right tablerows
$U_R$: Right rows whose key has no match in the left tablerows

12. Data quality reporting and a reproducible workflow

Share of rows retained

\text{retained \%} = \dfrac{n_{\text{after}}}{n_{\text{before}}} \times 100

$n_{\text{before}}$: Rows in the raw datarows
$n_{\text{after}}$: Rows in the cleaned datarows

Change in percentage points and relative change

\Delta_{\text{pp}} = p_2 - p_1 \qquad \Delta_{\%} = \dfrac{p_2 - p_1}{p_1} \times 100

$p_1,\ p_2$: A percentage before and after%
$\Delta_{\text{pp}}$: Change in percentage pointspp
$\Delta_{\%}$: Relative (percentage) change%

B03 Excel for Business Analytics

1. Cells, references and formulas

Order line total

T = q \times p - d

$T$: The order’s value after its discount$
$q$: Quantity: the number of items ordereditems
$p$: Unit price: the shop price of one item$ per item
$d$: Discount on the whole order$

Assumes. The discount is recorded in dollars for the whole order, as in Lantern’s export.

Price after a percentage rise

p_{\text{new}} = p \times (1 + r)

$p_{\text{new}}$: The price after the rise$
$p$: The current price$
$r$: The rise as a decimal (6% is 0.06)

Assumes. One rise applies to every product.

2. Functions, logic, text and dates

Expected subscription discount

d = \operatorname{round}(r \times q \times p,\ 2) \text{ if a subscription order, otherwise } 0

$d$: The discount the order should carry$
$r$: The subscription discount rate (10% at Lantern)
$q$: Quantityitems
$p$: Unit price$ per item

Whole units needed

n = \left\lceil \dfrac{g}{k} \right\rceil

$n$: Units to buy, rounded up to a whole numberunits
$g$: What has to be covered, such as guestsguests
$k$: How much one unit covers, such as guests per pastry boxguests per unit

Lantern’s catering quote (one-day event)

Q = \max\left(M,\ S + a\,g + b\,h + P\,x\right)

$Q$: The quoted price$
$M$: The minimum invoice$
$S$: The setup charge for the day$
$a$: The price per guest$ per guest
$g$: Number of guestsguests
$b$: The charge per hour of service$ per hour
$h$: Hours of servicehours
$P$: The pastry platter charge$
$x$: 1 if the client wants pastries, 0 if not

Assumes. Ana’s quote sheet (fictional). Invoices can differ from the quote by bespoke changes.

3. Conditional sums, counts and averages

Conditional sum (SUMIFS)

S = \sum_{i=1}^{n} x_i\, c_i, \qquad c_i = \begin{cases} 1 & \text{if row } i \text{ meets every criterion} \\ 0 & \text{otherwise} \end{cases}

$S$: The conditional sumas x
$n$: Number of rows in the datarows
$i$: The row number, from 1 to n
$x_i$: The value in row i of the sum rangeas recorded
$c_i$: Whether row i passes all the criteria

Weighted average price

\bar{p}_w = \dfrac{\sum_i q_i\, p_i}{\sum_i q_i}

$\bar{p}_w$: Average price per item, weighted by quantity$ per item
$q_i$: Quantity in order iitems
$p_i$: Unit price in order i$ per item

4. Lookups: VLOOKUP, INDEX and MATCH, XLOOKUP

Approximate-match (band) lookup

\text{result} = r_k, \quad k = \max\{\, i : f_i \le v \,\}

$v$: The lookup value, such as an order valueas the table
$f_i$: The lower bound (“from”) of band i; the bounds are sorted so that f₁ < f₂ < …as v
$r_i$: The result for band i, such as its delivery charge or its labelany
$i$: A band’s position in the table, counting from the top
$k$: The band the value falls in: the last band whose lower bound is not above v

Assumes. VLOOKUP with TRUE and MATCH with match_type 1 need the “from” column sorted from smallest to largest; XLOOKUP’s default search reads every item, so its match_mode −1 also works on an unsorted table (keep it sorted anyway, so people can check it). If v is below the first lower bound, no band qualifies and the lookup returns #N/A.

5. Excel tables and cleaning data

The order-total check (a cross-field rule)

\text{order\_total} = q \times p - d

$q$: Quantity: the number of items in the orderitems
$p$: Unit price: Lantern’s shop price per item$ per item
$d$: Discount on the whole order (10% for subscriptions, $5 for a promotion code)$

Assumes. Delivery and tax are not part of order_total in Lantern’s export. A row where the two sides differ by more than a cent fails the check.

6. PivotTables

% of Grand Total

s_{ij} = \dfrac{v_{ij}}{V}

$v_{ij}$: The value in row i and column j of the PivotTableas the data
$V$: The grand total of all the valuesas the data
$s_{ij}$: The cell’s share of the whole table%

Assumes. Every cell is divided by the same number, so all the cells add up to 100%.

% of Row Total

s_{ij} = \dfrac{v_{ij}}{R_i}

$v_{ij}$: The value in row i and column jas the data
$R_i$: The total of row ias the data
$s_{ij}$: The cell’s share of its row%

Assumes. Each row adds up to 100%. Use it when the question is a share of a row item.

% of Column Total

s_{ij} = \dfrac{v_{ij}}{C_j}

$v_{ij}$: The value in row i and column jas the data
$C_j$: The total of column jas the data
$s_{ij}$: The cell’s share of its column%

Assumes. Each column adds up to 100%. Use it when the question is a share of a column item.

7. Charts and conditional formatting

Apparent ratio of two bars on a truncated axis

\text{apparent ratio} = \dfrac{v_1 - b}{v_2 - b} \quad \text{against the true ratio} \quad \dfrac{v_1}{v_2}

$v_1, v_2$: The two values the bars showunits of v
$b$: The value at which the axis starts (its minimum)units of v

Assumes. Both values are above the baseline.

WCAG contrast ratio

\text{contrast ratio} = \dfrac{L_1 + 0.05}{L_2 + 0.05}

$L_1$: Relative luminance of the lighter colour (0 for black to 1 for white)
$L_2$: Relative luminance of the darker colour

Assumes. Relative luminance as WCAG defines it: 0.2126 R + 0.7152 G + 0.0722 B, after converting each sRGB channel to linear light.

8. Descriptive statistics and regression in Excel

Sample and population standard deviation (STDEV.S, STDEV.P)

s = \sqrt{\dfrac{\sum_{i=1}^{n} (x_i - \bar{x})^2}{n - 1}}, \qquad \sigma = \sqrt{\dfrac{\sum_{i=1}^{n} (x_i - \bar{x})^2}{n}}

$x_i$: The i-th valueunits of x
$\bar{x}$: The mean of the valuesunits of x
$n$: Number of values
$s$: Sample standard deviation (STDEV.S)units of x
$\sigma$: Population standard deviation (STDEV.P)units of x

Standard error of the mean

\text{SE} = \dfrac{s}{\sqrt{n}}

$s$: Sample standard deviationunits of x
$n$: Number of values
$\text{SE}$: Standard error of the meanunits of x

Excess kurtosis (Excel’s KURT)

\text{KURT} = \dfrac{n(n+1)}{(n-1)(n-2)(n-3)} \sum_{i=1}^{n} \left(\dfrac{x_i - \bar{x}}{s}\right)^{4} - \dfrac{3(n-1)^2}{(n-2)(n-3)}

$x_i$: The i-th valueunits of x
$\bar{x}$: Meanunits of x
$s$: Sample standard deviationunits of x
$n$: Number of values (at least 4)

Assumes. Reproduces Microsoft’s example: KURT of 3, 4, 5, 2, 3, 4, 5, 6, 4, 7 is −0.151800.

Correlation coefficient (CORREL)

r = \dfrac{\sum (x - \bar{x})(y - \bar{y})}{\sqrt{\sum (x - \bar{x})^2 \, \sum (y - \bar{y})^2}}

$x, y$: Paired values of the two variablestheir units
$\bar{x}, \bar{y}$: Their meanstheir units
$r$: Pearson’s correlation coefficient, from −1 to +1

Least-squares line (SLOPE, INTERCEPT, FORECAST.LINEAR)

\hat{y} = b_0 + b_1 x, \qquad b_1 = \dfrac{\sum (x - \bar{x})(y - \bar{y})}{\sum (x - \bar{x})^2}, \qquad b_0 = \bar{y} - b_1 \bar{x}

$x$: The explanatory variable (guests)units of x
$\hat{y}$: The predicted value of y (invoice)units of y
$b_1$: Slope: change in predicted y per unit of xunits of y per unit of x
$b_0$: Intercept: predicted y when x = 0units of y
$\bar{x}, \bar{y}$: Means of x and ytheir units

Assumes. A straight line describes the relationship over the range of the data.

R² and the standard error of the regression

R^2 = \dfrac{\text{SS}_{\text{reg}}}{\text{SS}_{\text{tot}}} = 1 - \dfrac{\text{SS}_{\text{res}}}{\text{SS}_{\text{tot}}}, \qquad s_e = \sqrt{\dfrac{\text{SS}_{\text{res}}}{n - k - 1}}

$R^2$: Share of the variation in y around its mean that the regression accounts for
$\text{SS}_{\text{tot}}$: Total sum of squares: Σ(y − ȳ)²units of y squared
$\text{SS}_{\text{res}}$: Residual sum of squares: Σ(y − ŷ)²units of y squared
$\text{SS}_{\text{reg}}$: Regression sum of squares: SS_tot − SS_resunits of y squared
$n$: Observations
$k$: Number of X variables (1 here)
$s_e$: Standard error of the regression (the ToolPak’s Standard Error)units of y

t Stat and the 95% limits of a coefficient

t = \dfrac{b}{\text{SE}_b}, \qquad b \pm t_{0.025,\,n-k-1} \times \text{SE}_b

$b$: A coefficient (slope or intercept)its units
$\text{SE}_b$: Its standard errorits units
$t_{0.025,\,n-k-1}$: The t value with 2.5% in each tail and n − k − 1 degrees of freedom

9. What-if analysis: data tables, Goal Seek, scenarios and Solver

Baristas for an event

B = \left\lceil \dfrac{G}{g} \right\rceil

$B$: Baristas sent to the eventpeople
$G$: Guestsguests
$g$: Guests per barista (Ana’s rule: 40)guests per barista

Assumes. The ceiling brackets mean “round up to a whole number”: a part-group of guests still needs a barista.

Invoice from the quote sheet (one event day)

I = \max\!\left(M,\; S + pG + hH + kP\right)

$I$: Invoice$
$M$: Minimum invoice$
$S$: Setup charge per event day$
$p$: Price per guest$ per guest
$G$: Guestsguests
$h$: Charge per hour of service$ per hour
$H$: Hours of servicehours
$k$: Pastry platter price$
$P$: 1 if a platter is ordered, 0 if not

Contribution and contribution margin ratio of an event

C = I - \left[\,B(H + u)w + cG + qP + v\,\right], \qquad r = \dfrac{C}{I}

$C$: Contribution of the event$
$I$: Invoice$
$B$: Baristaspeople
$H$: Hours of servicehours
$u$: Paid set-up hours per baristahours
$w$: Barista wage$ per hour
$c$: Coffee, milk and cups per guest$ per guest
$G$: Guestsguests
$q$: Pastry platter cost$
$P$: 1 if a platter is ordered, 0 if not
$v$: Van cost per event day$
$r$: Contribution margin ratio

Assumes. Only direct costs are subtracted; fixed costs are covered by the contributions of all jobs together.

Price per guest for a target contribution margin ratio

p^{*} = \dfrac{\dfrac{D}{1 - r^{*}} - S - hH - kP}{G}

$p^{*}$: Price per guest that gives the target ratio$ per guest
$D$: Direct costs of the event$
$r^{*}$: Target contribution margin ratio
$S$: Setup charge$
$h$: Charge per hour$ per hour
$H$: Hours of servicehours
$k$: Platter price$
$P$: 1 if a platter is ordered, 0 if not
$G$: Guestsguests

Assumes. The invoice needed, D ÷ (1 − r*), is above the minimum invoice; the direct costs don’t depend on the price.

A linear programme (maximisation)

\max \sum_{j} c_j x_j \quad \text{subject to} \quad \sum_{j} a_{ij} x_j \le b_i \ \text{for each } i, \qquad x_j \ge 0

$i,\ j$: Indexes: i counts the constraints (resources), j the decision variables
$x_j$: Decision variable j (such as the number of events)units chosen
$c_j$: Contribution of one unit of variable j$ per unit
$a_{ij}$: Amount of resource i that one unit of variable j usesresource units per unit
$b_i$: Amount of resource i available (the limit)resource units

Assumes. Proportionality and additivity: doubling a variable doubles its contribution and its use of each resource, and the effects add up. Constraints can also be ≥ or =; integer constraints are added when the variables must be whole.

Slack in a constraint

s_i = b_i - \sum_{j} a_{ij} x_j

$s_i$: Slack in constraint i at a solutionresource units
$b_i$: Limit of constraint iresource units
$a_{ij}$: Use of resource i per unit of variable jresource units per unit
$x_j$: Value of decision variable j in the solutionunits

10. Dashboards that answer questions

Gap to plan and share of plan

\text{Gap} = A - P, \qquad \text{Share of plan} = \dfrac{A}{P} \times 100

$A$: Actual value for the periodunits of the measure
$P$: Planned (target) value for the same periodunits of the measure

Change on the previous period

\%\Delta = \dfrac{A_t - A_{t-1}}{A_{t-1}} \times 100

$\%\Delta$: Percentage change on the previous period%
$A_t$: Value this periodunits of the measure
$A_{t-1}$: Value in the previous periodunits of the measure

Average invoice from totals

\bar{I} = \dfrac{\sum_{m} R_m}{\sum_{m} N_m}

$\bar{I}$: Average invoice over the months$ per event
$R_m$: Revenue in month m$
$N_m$: Events in month mevents

Assumes. Each month counts in proportion to its number of events.

Top-k client share

C_k = \dfrac{\sum_{c \in \text{top } k} R_c}{R} \times 100

$C_k$: Share of revenue from the k largest clients%
$k$: Number of clients counted (Ana uses 4)clients
$R_c$: Revenue from client c over the period$
$R$: Total revenue over the period$

B04 Descriptive and Inferential Statistics

1. Describing distributions

Population mean and sample mean

\mu = \dfrac{\sum_{i=1}^{N} x_i}{N}, \qquad \bar{x} = \dfrac{\sum_{i=1}^{n} x_i}{n}

$x_i$: The i-th valueunits of x
$N$: Number of values in the population
$n$: Number of values in the sample
$\mu$: Population mean (a parameter)units of x
$\bar{x}$: Sample mean (a statistic): AVERAGEunits of x

Population and sample variance and standard deviation

\sigma^2 = \dfrac{\sum (x_i - \mu)^2}{N}, \qquad s^2 = \dfrac{\sum (x_i - \bar{x})^2}{n - 1}, \qquad s = \sqrt{s^2}

$x_i$: The i-th valueunits of x
$\mu, \bar{x}$: Population and sample meansunits of x
$N, n$: Population and sample sizes
$\sigma^2, s^2$: Population variance (VAR.P) and sample variance (VAR.S)units of x squared
$s$: Sample standard deviation (STDEV.S); σ is STDEV.Punits of x

Assumes. The numerator is the sum of squares, DEVSQ in Excel.

Coefficient of variation

\text{CV} = \dfrac{s}{\bar{x}} \times 100\%

$s$: Sample standard deviationunits of x
$\bar{x}$: Sample mean (must be positive)units of x
$\text{CV}$: Spread relative to the mean%

Assumes. A ratio-scale variable (a true zero) with a positive mean.

z-score

z = \dfrac{x - \bar{x}}{s} \quad \text{(sample)}, \qquad z = \dfrac{x - \mu}{\sigma} \quad \text{(population)}

$x$: The value being locatedunits of x
$\bar{x}, s$: Sample mean and standard deviationunits of x
$\mu, \sigma$: Population mean and standard deviationunits of x
$z$: Standard deviations above (+) or below (−) the mean

Adding a constant and multiplying

y = a + bx \;\Rightarrow\; \bar{y} = a + b\bar{x}, \qquad s_y = |b|\, s_x, \qquad s_y^2 = b^2 s_x^2

$x$: The original valuesunits of x
$y$: The transformed valuesunits of y
$a$: The constant addedunits of y
$b$: The multiplier (a change of units)units of y per unit of x
$s_x, s_y$: Standard deviations before and aftertheir units

Chebyshev’s inequality

\text{share within } k \text{ SDs of the mean} \;\geq\; 1 - \dfrac{1}{k^2}, \qquad k > 1

$k$: Distance from the mean in standard deviations

Assumes. None about shape: it holds for every data set and every distribution.

Percentile rank

\text{percentile rank} = \dfrac{B + 0.5E}{n} \times 100, \qquad \text{PERCENTRANK.INC} = \dfrac{B}{n - 1}

$B$: Number of values below the one being ranked
$E$: Number of values equal to it (itself included)
$n$: Number of values

2. Probability

Complement rule

P(A') = 1 - P(A)

$A$: An event
$A'$: Its complement: every outcome not in A
$P(\cdot)$: Probability, from 0 to 1

Addition rule

P(A \text{ or } B) = P(A) + P(B) - P(A \text{ and } B)

$A, B$: Two events
$P(A \text{ and } B)$: Their joint probability (the overlap)

Conditional probability and the multiplication rule

P(A \mid B) = \dfrac{P(A \text{ and } B)}{P(B)}, \qquad P(A \text{ and } B) = P(B)\,P(A \mid B)

$P(A \mid B)$: Probability of A given that B has happened
$P(B)$: Probability of the condition (must be more than 0)

Independence

A, B \text{ independent} \iff P(A \mid B) = P(A) \iff P(A \text{ and } B) = P(A)\,P(B)

$A, B$: Two events with positive probability
$P(\cdot)$: Probability of the event in brackets

Bayes’ rule

P(A \mid B) = \dfrac{P(A)\,P(B \mid A)}{P(A)\,P(B \mid A) + P(A')\,P(B \mid A')}

$P(A)$: Prior probability (the base rate) of A
$P(B \mid A)$: Probability of the evidence B when A is true (a screen’s sensitivity)
$P(B \mid A')$: Probability of the evidence when A is false (the false-positive rate)
$P(A \mid B)$: Probability of A after seeing B

Assumes. The denominator is P(B), by the law of total probability.

Combinations and permutations (COMBIN, PERMUT)

\binom{n}{k} = \dfrac{n!}{k!\,(n - k)!}, \qquad P(n, k) = \dfrac{n!}{(n - k)!}

$n$: Number of items to choose from
$k$: Number chosen
$n!$: n factorial: n × (n − 1) × … × 1, with 0! = 1
$P(n, k)$: Number of ordered selections (permutations)

3. Discrete distributions

Expected value, variance and standard deviation of a discrete random variable

\mu = E(X) = \sum x\,P(x), \qquad \sigma^2 = \sum (x - \mu)^2 P(x), \qquad \sigma = \sqrt{\sigma^2}

$X$: The random variableits units
$E(X)$: Its expected valueits units
$x$: A value it can takeits units
$P(x)$: The probability that X = x
$\mu$: Expected value (mean)its units
$\sigma$: Standard deviationits units

Assumes. The probabilities cover every value and add to 1.

Binomial distribution (BINOM.DIST)

P(X = k) = \binom{n}{k} p^k (1 - p)^{n - k}, \qquad \mu = np, \qquad \sigma = \sqrt{np(1 - p)}

$P(X = k)$: Probability of exactly k successes
$n$: Number of independent trials
$p$: Probability of success on each trial
$k$: Number of successes, 0 to n
$\binom{n}{k}$: Number of ways to place k successes among n trials: COMBIN(n,k)
$X$: The number of successes

Assumes. Fixed n, two outcomes, constant p, independent trials.

Poisson distribution (POISSON.DIST)

P(X = k) = \dfrac{e^{-\lambda} \lambda^k}{k!}, \qquad \mu = \lambda, \qquad \sigma^2 = \lambda

$P(X = k)$: Probability of exactly k events
$\lambda$: Average number of events in the intervalevents per interval
$k$: Number of events, 0, 1, 2, …
$X$: The number of events in the interval
$k!$: k factorial

Assumes. Events occur independently, at a constant average rate, one at a time.

Exponential distribution (EXPON.DIST)

P(T \leq t) = 1 - e^{-\lambda t}, \qquad E(T) = \dfrac{1}{\lambda}

$P(T \leq t)$: Probability that the gap is at most t
$E(T)$: Expected (mean) gaptime
$T$: Time between consecutive eventstime
$t$: A length of timetime
$\lambda$: Event rate per unit of timeevents per unit time

Assumes. Events follow a Poisson process with a constant rate.

4. The normal distribution

Normal density

f(x) = \dfrac{1}{\sigma\sqrt{2\pi}} \, e^{-\frac{(x - \mu)^2}{2\sigma^2}}

$x$: A value of the variableunits of x
$\mu$: The mean of the distributionunits of x
$\sigma$: The standard deviation of the distributionunits of x
$f(x)$: The height of the curve at x (not a probability)probability per unit of x
$e,\ \pi$: The constants 2.71828… and 3.14159…

Assumes. Areas under the curve, not heights, are probabilities; the total area is 1.

Standardising: the z-score

z = \dfrac{x - \mu}{\sigma}

$x$: A value of a normal variableunits of x
$\mu$: Its meanunits of x
$\sigma$: Its standard deviationunits of x
$z$: How many standard deviations x lies above (+) or below (−) the mean

Assumes. If x comes from N(μ, σ), z comes from the standard normal N(0, 1).

From a probability back to a value

x = \mu + z\sigma, \qquad z = \Phi^{-1}(p)

$p$: The area wanted below the value
$\Phi^{-1}(p)$: The z with area p below it (NORM.S.INV(p))
$z$: That z-score
$\mu$: Meanunits of x
$\sigma$: Standard deviationunits of x
$x$: The cut-off in the data’s unitsunits of x

Normal approximation to the binomial, with a continuity correction

\mu = np, \quad \sigma = \sqrt{np(1 - p)}, \quad P(X \le k) \approx P(Y < k + 0.5)

$X$: The binomial countsuccesses
$n$: Number of trials
$p$: Probability of success on each trial
$k$: A whole-number countsuccesses
$Y$: A normal variable with mean μ and standard deviation σsuccesses
$P$: Probability
$\mu,\ \sigma$: Mean and standard deviation of the countsuccesses

Assumes. np and n(1 − p) both large: more than 5 in OpenStax’s rule of thumb, at least 10 in many texts.

5. Sampling distributions

Standard error of the sample mean

\sigma_{\bar{x}} = \dfrac{\sigma}{\sqrt{n}}

$\sigma_{\bar{x}}$: The standard deviation of the sampling distribution of x̄units of x
$\sigma$: The population standard deviationunits of x
$n$: The sample size

Assumes. Independent draws: a random sample with replacement, or a small share of a large population.

z for a sample mean

z = \dfrac{\bar{x} - \mu}{\sigma / \sqrt{n}}

$\bar{x}$: A sample meanunits of x
$\mu$: The population meanunits of x
$\sigma$: The population standard deviationunits of x
$n$: The sample size
$z$: How many standard errors x̄ lies from μ

Assumes. The sampling distribution of x̄ is close to normal: a normal population, or n large enough for the population’s skewness.

Standard error of a sample proportion

\sigma_{\hat{p}} = \sqrt{\dfrac{p(1 - p)}{n}}, \qquad z = \dfrac{\hat{p} - p}{\sigma_{\hat{p}}}

$p$: The population proportion
$\hat{p}$: A sample proportion
$n$: The sample size
$\sigma_{\hat{p}}$: The standard deviation of the sampling distribution of p̂
$z$: How many standard errors p̂ lies from p

Assumes. Independent draws, and enough of each outcome for the normal model (at least 10 of each is the usual check).

Finite population correction

\text{SE} = \dfrac{\sigma}{\sqrt{n}} \times \sqrt{\dfrac{N - n}{N - 1}}

$N$: The population size
$n$: The sample size, drawn without replacement
$\sigma$: The population standard deviationunits of x
$\text{SE}$: The corrected standard error of the meanunits of x

Assumes. Use it when the sample is more than about 5% of a known population.

6. Confidence intervals

Confidence interval for a mean, σ known (z interval)

\bar{x} \pm z_{\alpha/2} \, \dfrac{\sigma}{\sqrt{n}}

$\bar{x}$: The sample mean (the point estimate)units of x
$z_{\alpha/2}$: The z with α ÷ 2 above it: 1.645, 1.96, 2.576 for 90%, 95%, 99%
$\sigma$: The known population standard deviationunits of x
$n$: The sample size
$\alpha$: 1 − the confidence level

Assumes. A random sample; the sample mean close to normal (a normal population, or n large enough for its skew).

The t ratio

t = \dfrac{\bar{x} - \mu}{s / \sqrt{n}}, \qquad \text{df} = n - 1

$\bar{x}$: The sample meanunits of x
$\mu$: The population meanunits of x
$s$: The sample standard deviation (STDEV.S)units of x
$n$: The sample size
$t$: The ratio, which follows the t distribution with n − 1 degrees of freedom
$\text{df}$: Degrees of freedom

Assumes. A random sample from a roughly normal population.

Confidence interval for a mean, σ unknown (t interval)

\bar{x} \pm t_{\alpha/2,\,n-1} \, \dfrac{s}{\sqrt{n}}

$t_{\alpha/2,\,n-1}$: The t with α ÷ 2 in each tail and n − 1 degrees of freedom: T.INV.2T(α, n − 1)
$s$: The sample standard deviationunits of x
$\bar{x}$: The sample meanunits of x
$n$: The sample size
$\alpha$: 1 − the confidence level

Assumes. A random sample of independent values from a roughly normal population (robust to mild skew, more so as n grows).

Confidence interval for a proportion (Wald)

\hat{p} \pm z_{\alpha/2} \sqrt{\dfrac{\hat{p}(1 - \hat{p})}{n}}

$\hat{p}$: The sample proportion, successes ÷ n
$n$: The sample size
$z_{\alpha/2}$: The z multiplier
$\alpha$: 1 − the confidence level

Assumes. A random sample with at least 10 successes and 10 failures.

Wilson interval for a proportion

\dfrac{\hat{p} + \dfrac{z^2}{2n} \pm z\sqrt{\dfrac{\hat{p}(1 - \hat{p})}{n} + \dfrac{z^2}{4n^2}}}{1 + \dfrac{z^2}{n}}

$\hat{p}$: The sample proportion
$n$: The sample size
$z$: The z multiplier (1.96 for 95%)

Assumes. A random sample; works well even with few successes or failures.

Sample size for a mean

n = \left(\dfrac{z_{\alpha/2}\,\sigma}{E}\right)^2

$E$: The margin of error wantedunits of x
$\sigma$: A planning value for the standard deviationunits of x
$z_{\alpha/2}$: The z multiplier
$n$: The sample size, rounded up

Sample size for a proportion

n = \dfrac{z_{\alpha/2}^2 \, p(1 - p)}{E^2}

$E$: The margin of error wanted, as a proportion (0.05 for ±5 points)
$p$: A planning value for the proportion; 0.5 is the cautious choice
$z_{\alpha/2}$: The z multiplier
$n$: The number of usable replies, rounded up

7. Hypothesis testing

z test for a mean (σ known)

z = \dfrac{\bar{x} - \mu_0}{\sigma / \sqrt{n}}

$\bar{x}$: Sample meanunits of x
$\mu_0$: The population mean under the null hypothesisunits of x
$\sigma$: Known population standard deviationunits of x
$n$: Sample size
$z$: Test statistic, compared with the standard normal distribution

Assumes. A random sample of independent values; σ known; the population roughly normal, or n large enough for the central limit theorem.

One-sample t test for a mean

t = \dfrac{\bar{x} - \mu_0}{s / \sqrt{n}}, \qquad \text{df} = n - 1

$\bar{x}$: Sample meanunits of x
$\mu_0$: The population mean under the null hypothesisunits of x
$s$: Sample standard deviation (STDEV.S)units of x
$n$: Sample size
$t$: Test statistic, compared with the t distribution
$\text{df}$: Degrees of freedom

Assumes. A random sample of independent values from a roughly normal population; for larger n, moderate skew is tolerable.

One-proportion z test

z = \dfrac{\hat{p} - p_0}{\sqrt{p_0 (1 - p_0) / n}}

$\hat{p}$: Sample proportion: successes ÷ trials
$p_0$: The proportion under the null hypothesis
$n$: Number of trials
$z$: Test statistic, compared with the standard normal distribution

Assumes. Independent trials with the same probability; np₀ and n(1 − p₀) at least 10.

The p-value for each alternative

\text{lower: } \Pr(T \le t), \qquad \text{upper: } \Pr(T \ge t), \qquad \text{two-sided: } 2\Pr(T \ge |t|)

$T$: The statistic’s distribution if the null hypothesis is true (t with n − 1 df, or the standard normal)
$t$: The statistic calculated from the sample

Power of a one-sided z test

1 - \beta = \Phi\!\left(\dfrac{|\delta|}{\sigma/\sqrt{n}} - z_{1-\alpha}\right)

$1 - \beta$: Power: the probability of rejecting the null when the true difference is δ
$\delta$: True mean minus the null valueunits of x
$\sigma$: Population standard deviationunits of x
$n$: Sample size
$z_{1-\alpha}$: Standard normal value with α above it (1.645 for α = 0.05)
$\Phi$: Standard normal cumulative probability, NORM.S.DIST(…, TRUE)

Assumes. The effect lies in the direction tested.

Sample size for a chosen power

n = \left(\dfrac{(z_{1-\alpha} + z_{1-\beta})\,\sigma}{\delta}\right)^{2}

$n$: Sample size needed, rounded up
$z_{1-\alpha}$: Standard normal value for the significance level (z₁₋α/₂ if two-sided)
$z_{1-\beta}$: Standard normal value for the power (0.842 for 80%)
$\sigma$: Planning value of the standard deviationunits of x
$\delta$: Smallest difference worth detectingunits of x

Chance of at least one false alarm in k tests

\Pr(\text{at least one false alarm}) = 1 - (1 - \alpha)^{k}

$\alpha$: Significance level of each test
$k$: Number of independent tests, each of a true null hypothesis

8. Comparing two means

Welch’s two-sample t statistic

t = \dfrac{(\bar{x}_1 - \bar{x}_2) - \Delta_0}{\sqrt{s_1^2/n_1 + s_2^2/n_2}}

$\bar{x}_1, \bar{x}_2$: The two sample meansunits of x
$s_1, s_2$: The two sample standard deviationsunits of x
$n_1, n_2$: The two sample sizes
$\Delta_0$: The difference under the null hypothesis, usually 0units of x
$t$: Test statistic

Assumes. Two independent random samples from populations that are roughly normal, or samples large enough for the central limit theorem; the variances need not be equal.

Welch–Satterthwaite degrees of freedom

\text{df} = \dfrac{\left(s_1^2/n_1 + s_2^2/n_2\right)^2}{\dfrac{(s_1^2/n_1)^2}{n_1 - 1} + \dfrac{(s_2^2/n_2)^2}{n_2 - 1}}

$s_1^2, s_2^2$: The two sample variances (VAR.S)units of x squared
$n_1, n_2$: The two sample sizes
$\text{df}$: Degrees of freedom, usually not a whole number

Pooled variance and the pooled t statistic

s_p^2 = \dfrac{(n_1 - 1)s_1^2 + (n_2 - 1)s_2^2}{n_1 + n_2 - 2}, \qquad t = \dfrac{(\bar{x}_1 - \bar{x}_2) - \Delta_0}{s_p\sqrt{1/n_1 + 1/n_2}}

$s_p^2$: Pooled variance (the ToolPak’s Pooled Variance row)units of x squared
$s_1^2, s_2^2$: The two sample variancesunits of x squared
$n_1, n_2$: The two sample sizes
$\bar{x}_1, \bar{x}_2$: The two sample meansunits of x
$\Delta_0$: The difference under the null hypothesisunits of x
$t$: Test statistic, with n₁ + n₂ − 2 degrees of freedom

Assumes. As for Welch’s test, plus equal population variances.

Confidence interval for a difference in means

(\bar{x}_1 - \bar{x}_2) \pm t^{*} \sqrt{s_1^2/n_1 + s_2^2/n_2}

$\bar{x}_1 - \bar{x}_2$: Difference in sample meansunits of x
$t^{*}$: The t value with α/2 in each tail and Welch’s degrees of freedom (2.11 for 95% and 16.7 df)
$s_1, s_2$: Sample standard deviationsunits of x
$n_1, n_2$: Sample sizes

Paired t test and interval

t = \dfrac{\bar{d} - \Delta_0}{s_d/\sqrt{n}}, \qquad \bar{d} \pm t^{*}\,\dfrac{s_d}{\sqrt{n}}

$\bar{d}$: Mean of the differences (after minus before)units of x
$s_d$: Standard deviation of the differencesunits of x
$n$: Number of pairs
$\Delta_0$: Mean difference under the null hypothesis, usually 0units of x
$t, t^{*}$: Test statistic, and the t value for the interval, with n − 1 degrees of freedom

Assumes. Independent pairs; the differences roughly normal, or many pairs.

Why pairing helps: the variance of a difference

s_d^2 = s_1^2 + s_2^2 - 2\,r\,s_1 s_2

$s_d^2$: Variance of the paired differencesunits of x squared
$s_1, s_2$: Standard deviations of the two sets of measurementsunits of x
$r$: Correlation between the paired measurements (the ToolPak’s Pearson Correlation)

Cohen’s d

d = \dfrac{\bar{x}_1 - \bar{x}_2}{s_p}

$\bar{x}_1 - \bar{x}_2$: Difference in meansunits of x
$s_p$: Pooled standard deviation, the square root of the pooled varianceunits of x
$d$: Effect size in standard deviations

F statistic for two variances (F.TEST)

F = \dfrac{s_1^2}{s_2^2}

$s_1^2, s_2^2$: The two sample variancesunits of x squared
$F$: Ratio, compared with the F distribution with n₁ − 1 and n₂ − 1 degrees of freedom

Assumes. Both populations normal: the test is very sensitive to departures from normality.

9. Proportions and chi-square tests

Two-proportion z test (pooled standard error)

\bar{p} = \dfrac{x_1 + x_2}{n_1 + n_2}, \qquad z = \dfrac{\hat{p}_1 - \hat{p}_2}{\sqrt{\bar{p}(1 - \bar{p})\left(\dfrac{1}{n_1} + \dfrac{1}{n_2}\right)}}

$x_1, x_2$: Successes in samples 1 and 2counts
$n_1, n_2$: Sample sizescounts
$\hat{p}_1, \hat{p}_2$: Sample proportions, x ÷ n
$\bar{p}$: Pooled proportion: all successes over all observations
$z$: Test statistic, compared with the standard normal distribution

Assumes. Independent samples (or independent trials); at least 10 successes and 10 failures in each sample.

Confidence interval for a difference in two proportions

(\hat{p}_1 - \hat{p}_2) \pm z^* \sqrt{\dfrac{\hat{p}_1(1 - \hat{p}_1)}{n_1} + \dfrac{\hat{p}_2(1 - \hat{p}_2)}{n_2}}

$\hat{p}_1, \hat{p}_2$: Sample proportions
$n_1, n_2$: Sample sizescounts
$z^*$: Standard normal critical value: NORM.S.INV(0.975) ≈ 1.96 for 95%

Assumes. As for the test, with at least 10 successes and 10 failures in each sample.

Chi-square statistic

\chi^2 = \sum \dfrac{(O - E)^2}{E}

$O$: Observed count in a category or cellcount
$E$: Expected count under the null hypothesiscount
$\chi^2$: Chi-square statistic, summed over every category or cell

Assumes. Counts of independent individuals; every expected count at least 5.

Goodness of fit: expected counts and degrees of freedom

E_i = n\,p_i, \qquad \text{df} = k - 1 - m

$n$: Total number of observationscount
$p_i$: Probability of category i under the null hypothesis
$k$: Number of categories (after any merging)
$m$: Number of the model’s parameters estimated from the same data
$E_i$: Expected count in category icount

Test of independence: expected counts and degrees of freedom

E_{ij} = \dfrac{R_i \, C_j}{n}, \qquad \text{df} = (r - 1)(c - 1)

$R_i$: Total of row icount
$C_j$: Total of column jcount
$n$: Grand totalcount
$r, c$: Numbers of rows and columns
$E_{ij}$: Expected count in row i, column j, if the variables are independentcount

Cramér’s V

V = \sqrt{\dfrac{\chi^2}{n \times \min(r - 1,\; c - 1)}}

$\chi^2$: Chi-square statistic of the test of independence
$n$: Grand totalcount
$r, c$: Numbers of rows and columns
$V$: Strength of association, from 0 (none) to 1 (complete)

10. One-way analysis of variance (ANOVA)

Family-wise error rate for independent tests

\text{FWER} = 1 - (1 - \alpha)^m, \qquad m = \dfrac{k(k - 1)}{2} \text{ pairs}

$\alpha$: Significance level of each test
$m$: Number of tests
$k$: Number of groups
$\text{FWER}$: Probability of at least one false positive among the m tests, if every null hypothesis is true

Assumes. The tests are independent. Pairwise tests share data, so this is a guide to the size of the problem rather than an exact rate.

Sums of squares for one-way ANOVA

\text{SSB} = \sum_{i=1}^{k} n_i (\bar{x}_i - \bar{x})^2, \qquad \text{SSW} = \sum_{i=1}^{k} (n_i - 1) s_i^2, \qquad \text{SST} = \text{SSB} + \text{SSW}

$k$: Number of groups
$n_i$: Number of observations in group i
$\bar{x}_i$: Mean of group iunits of x
$\bar{x}$: Grand mean of all N observationsunits of x
$s_i^2$: Sample variance of group i (VAR.S)units of x squared

Mean squares and the F statistic

\text{MSB} = \dfrac{\text{SSB}}{k - 1}, \qquad \text{MSW} = \dfrac{\text{SSW}}{N - k}, \qquad F = \dfrac{\text{MSB}}{\text{MSW}}

$\text{SSB}, \text{SSW}$: Sums of squares between and within groupsunits of x squared
$k$: Number of groups
$N$: Total number of observations
$F$: Test statistic, compared with the F distribution with k − 1 and N − k degrees of freedom

Assumes. Independent observations; each group normal; equal population variances.

Eta squared

\eta^2 = \dfrac{\text{SSB}}{\text{SST}}

$\text{SSB}$: Sum of squares between groupsunits of x squared
$\text{SST}$: Total sum of squaresunits of x squared
$\eta^2$: Share of the total variation that lies between the groups, from 0 to 1

Bonferroni-adjusted significance level

\alpha_{\text{each}} = \dfrac{\alpha}{m}

$\alpha$: Family-wise significance level wanted
$m$: Number of comparisons made
$\alpha_{\text{each}}$: Level at which each comparison is tested

B05 Correlation Analysis

1. Scatterplots

Correlation is symmetric

r_{xy} = r_{yx}

$r_{xy}$: Pearson’s correlation of x with y
$r_{yx}$: Pearson’s correlation of y with x

Jitter for drawing

x_i^{\text{plot}} = x_i + u_i, \qquad u_i \sim \text{Uniform}(-h, h)

$x_i$: The recorded value of point iunits of x
$u_i$: A random nudge drawn afresh for each pointunits of x
$h$: The largest nudge: less than half the step between recorded valuesunits of x
$x_i^{\text{plot}}$: Where the point is drawnunits of x

Assumes. Used only to draw the plot; every statistic uses the recorded values.

2. Covariance and Pearson’s r

Sample covariance

s_{xy} = \dfrac{\sum_{i=1}^{n} (x_i - \bar{x})(y_i - \bar{y})}{n - 1}

$s_{xy}$: Sample covariance of x and yunits of x × units of y
$x_i, y_i$: The paired values of unit itheir units
$\bar{x}, \bar{y}$: The means of x and ytheir units
$n$: Number of pairs

Assumes. Paired numeric data; rows with a missing value are left out (pairwise deletion).

Pearson’s correlation coefficient

r = \dfrac{s_{xy}}{s_x\, s_y} = \dfrac{\sum (x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum (x_i - \bar{x})^2 \sum (y_i - \bar{y})^2}}

$r$: Pearson’s correlation coefficient, from −1 to +1
$s_{xy}$: Sample covarianceunits of x × units of y
$s_x, s_y$: Sample standard deviations of x and y (STDEV.S)their units
$x_i, y_i$: The paired valuestheir units
$\bar{x}, \bar{y}$: The meanstheir units

Assumes. Measures straight-line association only; read it with a scatterplot.

r as the average product of z-scores

r = \dfrac{1}{n - 1} \sum_{i=1}^{n} \left(\dfrac{x_i - \bar{x}}{s_x}\right) \left(\dfrac{y_i - \bar{y}}{s_y}\right)

$r$: Pearson’s correlation coefficient
$n$: Number of pairs
$x_i, y_i$: The paired valuestheir units
$\bar{x}, \bar{y}$: The meanstheir units
$s_x, s_y$: Sample standard deviationstheir units

r under a change of units

r(a + b\,x,\; c + d\,y) = \operatorname{sign}(b\,d)\; r(x, y)

$r(x, y)$: Pearson’s correlation of x and y
$x, y$: The original variablestheir units
$a, c$: Constants added to x and y (shifts)units of x, y
$b, d$: Constants multiplying x and y (rescaling), not zero
$\operatorname{sign}(b\,d)$: +1 if b and d have the same sign, −1 if not

Coefficient of determination for one x

r^2 = 1 - \dfrac{\text{SS}_\text{res}}{\text{SS}_\text{tot}}

$r^2$: Share of the variance of y that the least-squares line on x accounts for, from 0 to 1
$\text{SS}_\text{res}$: Sum of squared residuals from the least-squares lineunits of y squared
$\text{SS}_\text{tot}$: Sum of squared deviations of y from its meanunits of y squared

3. Rank correlation

Spearman’s rank correlation (definition)

r_s = \text{Pearson’s } r \text{ of } \big(\text{rank}(x_i),\ \text{rank}(y_i)\big)

$r_s$: Spearman’s rank correlation, also written Spearman’s ρ
$\text{rank}(x_i)$: The rank of observation i’s x value among all the x values, ties given their average rank
$\text{rank}(y_i)$: The rank of observation i’s y value among all the y values, ranked in the same direction

Assumes. Paired observations of two variables that can be put in order.

Spearman’s ρ from rank differences (no ties only)

r_s = 1 - \dfrac{6\sum d_i^2}{n(n^2 - 1)}

$d_i$: Observation i’s rank on x minus its rank on y
$n$: Number of paired observations
$r_s$: Spearman’s rank correlation

Assumes. No tied values in either variable; with ties it gives a wrong answer.

Average rank of a group of tied values

\text{average rank} = \dfrac{\text{first position} + \text{last position}}{2}

$\text{first position}$: The position the lowest member of the tied group would take in the sorted list
$\text{last position}$: The position the highest member would take

Kendall’s τ-b

\tau_b = \dfrac{n_c - n_d}{\sqrt{(n_0 - n_1)(n_0 - n_2)}}, \qquad n_0 = \dfrac{n(n-1)}{2}

$n_c$: Number of concordant pairs: the observation higher on x is also higher on y
$n_d$: Number of discordant pairs: the observation higher on x is lower on y
$n_0$: Number of pairs of observations
$n_1$: Number of pairs tied on x
$n_2$: Number of pairs tied on y
$n$: Number of observations

Assumes. Paired observations that can be put in order on each variable.

4. Testing a correlation

t test of a correlation (H₀: ρ = 0)

t = \dfrac{r\sqrt{n - 2}}{\sqrt{1 - r^2}}, \qquad \text{df} = n - 2

$r$: Sample Pearson correlation (CORREL)
$n$: Number of pairs
$t$: Test statistic, compared with the t distribution
$\text{df}$: Degrees of freedom

Assumes. Independent pairs from a random sample; a linear relationship; y roughly normal around the line with constant spread.

Critical value of r

r_{\text{crit}} = \dfrac{t_{\text{crit}}}{\sqrt{n - 2 + t_{\text{crit}}^2}}

$r_{\text{crit}}$: Smallest |r| significant at level α
$t_{\text{crit}}$: Critical t with n − 2 degrees of freedom: T.INV.2T(α, n − 2) for a two-sided test
$n$: Number of pairs

Confidence interval for ρ by Fisher’s z

\tanh\!\left(\operatorname{atanh} r \pm \dfrac{z^*}{\sqrt{n - 3}}\right)

$\operatorname{atanh} r$: Fisher’s z of the sample correlation, ½ ln((1 + r) ÷ (1 − r)); Excel’s FISHER
$z^*$: Standard normal critical value (1.96 for 95%)
$n$: Number of pairs
$\tanh$: The inverse transformation; Excel’s FISHERINV

Assumes. Independent pairs; the two variables roughly bivariate normal.

Test of H₀: ρ = ρ₀ by Fisher’s z

z = \left(\operatorname{atanh} r - \operatorname{atanh}\rho_0\right)\sqrt{n - 3}

$\rho_0$: The population correlation under the null hypothesis
$r$: Sample correlation
$n$: Number of pairs
$z$: Test statistic, compared with the standard normal

Comparing two independent correlations

z = \dfrac{\operatorname{atanh} r_1 - \operatorname{atanh} r_2}{\sqrt{\dfrac{1}{n_1 - 3} + \dfrac{1}{n_2 - 3}}}

$r_1, r_2$: The two sample correlations
$n_1, n_2$: Their numbers of pairs
$z$: Test statistic, compared with the standard normal

Assumes. The two samples are independent of each other.

Pairs needed to detect a correlation

n = \left(\dfrac{z_{1-\alpha/2} + z_{1-\beta}}{\operatorname{atanh}\rho}\right)^{2} + 3

$n$: Pairs needed, rounded up
$z_{1-\alpha/2}$: Standard normal value for a two-sided test at α (1.96 for 0.05)
$z_{1-\beta}$: Standard normal value for the power (0.842 for 80%)
$\rho$: The smallest true correlation worth detecting

5. Outliers and influence

A point’s share of Pearson’s r

r = \frac{1}{n-1}\sum_{i=1}^{n} z_{x,i}\,z_{y,i}, \qquad z_{x,i} = \frac{x_i - \bar{x}}{s_x}, \quad z_{y,i} = \frac{y_i - \bar{y}}{s_y}

$r$: Pearson’s correlation coefficient
$n$: Number of pairs
$z_{x,i}, z_{y,i}$: Point i’s z-scores on x and on y
$x_i, y_i$: Point i’s valuesunits of x, y
$\bar{x}, \bar{y}$: Sample meansunits of x, y
$s_x, s_y$: Sample standard deviationsunits of x, y

Assumes. An identity, true for any data with non-zero standard deviations.

Leave-one-out change in r

\Delta r_i = r_{(-i)} - r

$r$: The correlation of all n points
$r_{(-i)}$: The correlation of the n − 1 points left when point i is removed
$\Delta r_i$: How much removing point i changes r (positive: r rises without it)

The largest possible z-score in a sample

|z_i| \le \frac{n-1}{\sqrt{n}}

$z_i$: Any value’s z-score, using the sample mean and sample standard deviation
$n$: Number of values

Assumes. z-scores calculated with the sample standard deviation (n − 1), as STANDARDIZE with AVERAGE and STDEV.S does.

6. Curves, thresholds and restricted ranges

Hinge at a threshold

h = \max(0,\; x - c)

$h$: The hinge: zero below the threshold, the distance above itunits of x
$x$: The original variable (here the peak load)transactions per barista hour
$c$: The threshold, read from the plot or set in advanceunits of x

Assumes. y is roughly flat below c and changes with x above it. A threshold chosen from the same data flatters the fit.

Correlation over the full range from a restricted one

r_u = \dfrac{k\, r_r}{\sqrt{1 - r_r^2 + k^2 r_r^2}}, \qquad k = \dfrac{S}{s}

$r_u$: Estimated correlation over the full range
$r_r$: Correlation in the restricted data
$S$: Standard deviation of x over the full rangeunits of x
$s$: Standard deviation of x in the restricted dataunits of x
$k$: How many times wider the full range is

Assumes. A straight-line relationship with even scatter, and selection on x alone.

Attenuation by unreliable measurement

r_{\text{obs}} \approx r_{\text{true}}\sqrt{\text{rel}_x\,\text{rel}_y}, \qquad r_{\text{true}} \approx \dfrac{r_{\text{obs}}}{\sqrt{\text{rel}_x\,\text{rel}_y}}

$r_{\text{obs}}$: Correlation between the measured values
$r_{\text{true}}$: Correlation between the true values
$\text{rel}_x, \text{rel}_y$: Reliability of each measure: the share of its variance that is true variation, for example a test–retest correlation

Assumes. Random, independent measurement errors; reliabilities known.

7. Groups and Simpson’s paradox

Within-group and between-group sums of products

\sum_i (x_i - \bar{x})(y_i - \bar{y}) = \sum_g \sum_{i \in g} (x_i - \bar{x}_g)(y_i - \bar{y}_g) + \sum_g n_g (\bar{x}_g - \bar{x})(\bar{y}_g - \bar{y})

$x_i, y_i$: Observation i’s valuesunits of x, y
$\bar{x}, \bar{y}$: Overall means of all observationsunits of x, y
$\bar{x}_g, \bar{y}_g$: Means of group gunits of x, y
$n_g$: Number of observations in group g
$g$: The groups (cafés, stores, regions …)

Assumes. An identity: it holds for any data split into groups.

Pooled within-group correlation

r_W = \dfrac{\sum_g \sum_{i \in g} (x_i - \bar{x}_g)(y_i - \bar{y}_g)}{\sqrt{\sum_g \sum_{i \in g} (x_i - \bar{x}_g)^2 \;\sum_g \sum_{i \in g} (y_i - \bar{y}_g)^2}}

$r_W$: Correlation within groups, with each group’s own means removed
$x_i, y_i$: Observation i’s valuesunits of x, y
$\bar{x}_g, \bar{y}_g$: Means of the group observation i belongs tounits of x, y
$g$: The groups

Ecological correlation

r_{\text{eco}} = r\left(\bar{x}_g,\; \bar{y}_g\right)

$r_{\text{eco}}$: Pearson’s r computed across groups
$\bar{x}_g, \bar{y}_g$: Each group’s mean of x and of yunits of x, y

Assumes. Describes groups. It need not equal, or even share a sign with, the correlation between individuals.

8. Correlation matrices

Number of distinct pairs in a matrix

\text{pairs} = \dfrac{m(m-1)}{2}

$m$: Number of variables in the matrix
$\text{pairs}$: Distinct off-diagonal cells, one test each

The range a third correlation must lie in

r_{xy}\,r_{yz} - \sqrt{(1 - r_{xy}^2)(1 - r_{yz}^2)} \;\le\; r_{xz} \;\le\; r_{xy}\,r_{yz} + \sqrt{(1 - r_{xy}^2)(1 - r_{yz}^2)}

$r_{xy}, r_{yz}$: The correlations of x with y and of y with z
$r_{xz}$: The correlation of x with z

Assumes. All three correlations are worked out from the same rows.

Bonferroni-adjusted p-value

\tilde{p}_i = \min(1,\; m\,p_i)

$p_i$: The unadjusted p-value of test i
$m$: Number of tests in the family
$\tilde{p}_i$: Adjusted p-value, compared with α

Assumes. None about dependence between the tests.

Holm’s step-down thresholds

\text{reject } H_{(1)}, \dots, H_{(k)} \text{ while } p_{(k)} < \dfrac{\alpha}{m - k + 1}

$p_{(k)}$: The k-th smallest p-value
$H_{(k)}$: Its null hypothesis
$k$: Rank, 1 for the smallest p-value
$m$: Number of tests
$\alpha$: Family-wise error rate to control

Assumes. None about dependence between the tests.

Benjamini–Hochberg step-up rule

k^{*} = \max\left\{k : p_{(k)} \le \dfrac{k}{m}\,q\right\}; \quad \text{reject } H_{(1)}, \dots, H_{(k^{*})}

$p_{(k)}$: The k-th smallest p-value
$H_{(k)}$: Its null hypothesis
$k^{*}$: The largest rank meeting the rule
$k$: Rank, 1 for the smallest p-value
$m$: Number of tests
$q$: False discovery rate to control

Assumes. Proved for independent tests; robust to the positive dependence usual in a matrix.

9. Partial correlation

First-order partial correlation

r_{xy\cdot z} = \dfrac{r_{xy} - r_{xz}\,r_{yz}}{\sqrt{(1 - r_{xz}^2)(1 - r_{yz}^2)}}

$r_{xy\cdot z}$: Correlation of x and y holding z fixed
$r_{xy}$: Simple correlation of x and y
$r_{xz}, r_{yz}$: Correlations of x and of y with the control z

Assumes. All three correlations from the same rows; z’s links with x and y are roughly straight lines.

Partial correlation as a correlation of residuals

r_{xy\cdot z} = \operatorname{corr}(e_x, e_y), \quad e_x = x - (a_x + b_x z), \quad e_y = y - (a_y + b_y z)

$r_{xy\cdot z}$: Partial correlation of x and y holding z fixed
$e_x, e_y$: Residuals of x and of y after a least-squares line on zunits of x, of y
$a_x, b_x$: Intercept and slope of the line of x on z (INTERCEPT, SLOPE)units of x; of x per unit of z
$a_y, b_y$: Intercept and slope of the line of y on zunits of y; of y per unit of z
$x, y, z$: The two variables of interest and the controltheir units

Test of a partial correlation

t = r_{xy\cdot z}\sqrt{\dfrac{n - 2 - k}{1 - r_{xy\cdot z}^2}}, \qquad \text{df} = n - 2 - k

$r_{xy\cdot z}$: The partial correlation
$n$: Number of rows complete on every variable
$k$: Number of variables held fixed
$t$: Test statistic for H₀: the partial correlation is 0
$\text{df}$: Degrees of freedom

Assumes. As for the test of a simple correlation, applied to the residuals.

10. Correlation and causation

Change (first difference) of a series

\Delta y_t = y_t - y_{t-1}

$y_t$: The series’ value in period t (members at the end of day t)units of y
$y_{t-1}$: Its value in the period beforeunits of y
$\Delta y_t$: The change in period t (the day’s new members)units of y per period

Assumes. Periods are equally spaced and in order.

Expected chance findings among many tests

E[\text{false alarms}] = m\,\alpha, \qquad P(\text{at least one}) = 1 - (1 - \alpha)^m

$m$: Number of tests run when no relationship is real
$P(\text{at least one})$: Probability that at least one test is significant by chance
$\alpha$: Significance level of each test
$E[\text{false alarms}]$: Average number of “significant” results by chance

Assumes. Every null hypothesis true; the second formula also assumes independent tests.

B06 Regression Analysis

1. The least-squares line

The fitted line and residual

\hat{y} = b_0 + b_1 x, \qquad e = y - \hat{y}

$\hat{y}$: Fitted value of the response for a given xy’s units
$b_0$: Intercept: the fitted value at x = 0y’s units
$b_1$: Slope: the change in ŷ per unit of xy’s units per x’s unit
$x$: Explanatory variableits units
$y$: Observed responseits units
$e$: Residual: observed minus fittedy’s units

The least-squares criterion

\text{SSE} = \sum_{i=1}^{n} (y_i - b_0 - b_1 x_i)^2 \;\text{ is minimised}

$\text{SSE}$: Sum of squared residuals (error sum of squares)y’s units squared
$n$: Number of observations
$x_i, y_i$: The i-th observationtheir units
$b_0, b_1$: Intercept and slope being chosenas above

Assumes. None to fit the line; the line is a description. Inference about it (module 2) needs conditions.

Least-squares slope

b_1 = \frac{\sum (x - \bar{x})(y - \bar{y})}{\sum (x - \bar{x})^2} = r\,\frac{s_y}{s_x}

$b_1$: Slopey’s units per x’s unit
$x, y$: The two variablestheir units
$\bar{x}, \bar{y}$: Means of x and ytheir units
$r$: Pearson correlation of x and y
$s_x, s_y$: Sample standard deviations of x and ytheir units

Least-squares intercept

b_0 = \bar{y} - b_1\,\bar{x}

$b_0$: Intercepty’s units
$b_1$: Slopey’s units per x’s unit
$\bar{x}, \bar{y}$: Means of x and ytheir units

The regressions of y on x and of x on y

b_{y\cdot x} = r\,\frac{s_y}{s_x}, \qquad b_{x\cdot y} = r\,\frac{s_x}{s_y}, \qquad b_{y\cdot x}\,b_{x\cdot y} = r^2

$b_{y\cdot x}$: Slope for predicting y from xy’s units per x’s unit
$b_{x\cdot y}$: Slope for predicting x from yx’s units per y’s unit
$r$: Correlation of x and y
$s_x, s_y$: Standard deviationstheir units

The line in standard units

\hat{z}_y = r\,z_x, \qquad z_x = \frac{x - \bar{x}}{s_x}

$z_x$: x in standard deviations from its mean
$x, \bar{x}$: A value of x and the mean of xx’s units
$s_x$: Standard deviation of xx’s units
$\hat{z}_y$: Fitted y in standard deviations from its mean
$r$: Correlation of x and y

2. Residuals and R²

Residual

e = y - \hat{y}

$e$: Residualy’s units
$y$: Observed valuey’s units
$\hat{y}$: Fitted value from the liney’s units

Sums of squares

\text{SST} = \sum (y - \bar{y})^2 = \underbrace{\sum (\hat{y} - \bar{y})^2}_{\text{SSR}} + \underbrace{\sum (y - \hat{y})^2}_{\text{SSE}}

$\text{SST}$: Total sum of squares: variation of y around its meany’s units squared
$\text{SSR}$: Regression sum of squares: variation of the fitted valuesy’s units squared
$\text{SSE}$: Residual (error) sum of squaresy’s units squared
$y, \hat{y}, \bar{y}$: Observed, fitted and mean values of the responsey’s units

Assumes. Least squares with an intercept.

R² (coefficient of determination)

R^2 = \frac{\text{SSR}}{\text{SST}} = 1 - \frac{\text{SSE}}{\text{SST}} = r^2 \;\text{(one explanatory variable)}

$R^2$: Share of the variation of y around its mean that the line accounts for
$\text{SSR}, \text{SSE}, \text{SST}$: Regression, residual and total sums of squaresy’s units squared
$r$: Correlation of x and y

Residual standard error

s = \sqrt{\frac{\text{SSE}}{n - 2}}

$s$: Typical size of a residual (also s_e)y’s units
$\text{SSE}$: Residual sum of squaresy’s units squared
$n$: Number of observations

3. Inference for coefficients

Population simple regression model

y = \beta_0 + \beta_1 x + \varepsilon

$\beta_0, \beta_1$: Population intercept and slope, estimated by b₀ and b₁units of y; units of y per unit of x
$x$: Predictorits units
$y$: Responseits units
$\varepsilon$: Error: how far one case falls from the population lineunits of y

Assumes. Linearity, independent errors, roughly normal errors, equal error variance at every x (LINE).

Standard error of the slope

SE(b_1) = \dfrac{s}{\sqrt{S_{xx}}}, \qquad S_{xx} = \sum (x_i - \bar{x})^2

$s$: Residual standard error, √(SSE ÷ (n − 2)); Excel’s STEYXunits of y
$S_{xx}$: Sum of squared deviations of x from its mean; Excel’s DEVSQunits of x squared
$x_i, \bar{x}$: Each value of x and their meanunits of x
$SE(b_1)$: Estimated standard deviation of the slope’s sampling distributionunits of y per unit of x

Assumes. Independent errors with equal variance.

t test for a slope

t = \dfrac{b_1 - \beta_{1,0}}{SE(b_1)}, \qquad \text{df} = n - 2

$b_1$: Estimated slopeunits of y per unit of x
$\beta_{1,0}$: Slope under the null hypothesis, usually 0units of y per unit of x
$SE(b_1)$: Standard error of the slopeunits of y per unit of x
$n$: Number of rows
$t$: Test statistic, compared with the t distribution
$\text{df}$: Degrees of freedom

Assumes. The LINE conditions; the t distribution is exact for normal errors and approximate otherwise.

Confidence interval for a slope

b_1 \pm t^{*}_{n-2}\, SE(b_1)

$t^{*}_{n-2}$: Critical t for the confidence level; for 95%, T.INV.2T(0.05, n − 2)
$b_1$: Estimated slopeunits of y per unit of x
$SE(b_1)$: Standard error of the slopeunits of y per unit of x

Standard error of the intercept

SE(b_0) = s\sqrt{\dfrac{1}{n} + \dfrac{\bar{x}^2}{S_{xx}}}

$SE(b_0)$: Standard error of the intercept b₀units of y
$s$: Residual standard errorunits of y
$n$: Number of rows
$\bar{x}$: Mean of xunits of x
$S_{xx}$: Sum of squared deviations of xunits of x squared

F test for a simple regression

F = \dfrac{\text{SSR}/1}{\text{SSE}/(n - 2)} = t^2

$\text{SSR}$: Regression sum of squares, SST − SSEunits of y squared
$\text{SSE}$: Residual sum of squaresunits of y squared
$n$: Number of rows
$F$: F statistic: the regression mean square over the residual mean square
$t$: The slope’s t statistic

Assumes. As for the t test; compared with the F distribution on 1 and n − 2 df.

4. Prediction and intervals

Point prediction

\hat{y}_0 = b_0 + b_1 x_0

$\hat{y}_0$: Predicted y at the new x: the estimated mean response, and the best single guess for one new caseunits of y
$b_0, b_1$: Fitted intercept and slopeunits of y; per unit of x
$x_0$: The new value of xunits of x

Confidence interval for the mean response

\hat{y}_0 \pm t^{*}_{n-2}\, s\sqrt{\dfrac{1}{n} + \dfrac{(x_0 - \bar{x})^2}{S_{xx}}}

$\hat{y}_0$: Point prediction at x₀units of y
$t^{*}_{n-2}$: Critical t; for 95%, T.INV.2T(0.05, n − 2)
$s$: Residual standard error (STEYX)units of y
$n$: Number of rows
$x_0, \bar{x}$: The new x and the mean of the data’s xunits of x
$S_{xx}$: Sum of squared deviations of x (DEVSQ)units of x squared

Assumes. The LINE conditions; x₀ inside the range and conditions of the data.

Prediction interval for one new case

\hat{y}_0 \pm t^{*}_{n-2}\, s\sqrt{1 + \dfrac{1}{n} + \dfrac{(x_0 - \bar{x})^2}{S_{xx}}}

$\hat{y}_0$: Point prediction at x₀units of y
$t^{*}_{n-2}$: Critical t on n − 2 df
$s$: Residual standard errorunits of y
$1$: The new case’s own error variance, in units of s²
$n$: Number of rows
$x_0, \bar{x}$: The new x and the mean of xunits of x
$S_{xx}$: Sum of squared deviations of xunits of x squared

Assumes. As for the mean, and more strongly: constant spread at every x and roughly normal errors, at any sample size.

One-sided upper prediction bound

\hat{y}_0 + t_{1-\alpha,\,n-2}\; s\sqrt{1 + \dfrac{1}{n} + \dfrac{(x_0 - \bar{x})^2}{S_{xx}}}

$t_{1-\alpha,\,n-2}$: One-sided critical t: T.INV(1 − α, n − 2), e.g. T.INV(0.9, n − 2) for a bound exceeded about 10% of the time
$\hat{y}_0$: Point predictionunits of y
$s$: Residual standard errorunits of y
$n$: Number of rows
$x_0, \bar{x}$: The new x and the mean of xunits of x
$S_{xx}$: Sum of squared deviations of xunits of x squared

5. Multiple regression

Fitted multiple regression

\hat y = b_0 + b_1x_1 + b_2x_2 + \dots + b_kx_k

$\hat y$: Fitted value of y for a rowunits of y
$b_0$: Intercept: the fitted value when every x is 0units of y
$b_j$: Partial slope: change in the fitted value per unit of x_j, the other x’s held fixedunits of y per unit of x_j
$x_j$: The j-th predictorits units
$k$: Number of predictors

Assumes. The coefficients minimise the sum of squared residuals; each predictor’s effect is the same at every value of the others (additive).

Adjusted R²

\bar R^2 = 1 - (1 - R^2)\,\dfrac{n - 1}{n - p}

$\bar R^2$: Adjusted R²
$R^2$: Share of the variation in y around its mean accounted for, 1 − SSE/SST
$n$: Number of rows
$p$: Number of coefficients, counting the intercept (k + 1)

Overall F test of a multiple regression

F = \dfrac{\text{SSR}/k}{\text{SSE}/(n - p)} = \dfrac{R^2/k}{(1 - R^2)/(n - p)}, \qquad \text{df} = (k,\; n - p)

$F$: Test statistic for H₀: every slope is 0
$\text{SSR}$: Regression sum of squares, Σ(ŷ − ȳ)²units of y squared
$\text{SSE}$: Residual sum of squares, Σ(y − ŷ)²units of y squared
$R^2$: SSR ÷ SST
$k$: Number of predictors
$n$: Number of rows
$p$: Number of coefficients, k + 1

Assumes. The model’s conditions (independent rows, constant spread, roughly normal errors).

Omitted-variable formula

\tilde b_1 = b_1 + b_2\,\delta

$\tilde b_1$: Slope on x₁ when x₂ is left outunits of y per unit of x₁
$b_1, b_2$: Slopes on x₁ and x₂ when both are inunits of y per unit of x₁; of x₂
$\delta$: Slope of the least-squares line of x₂ on x₁units of x₂ per unit of x₁

Assumes. Exact for least-squares fits on the same rows (with any other predictors in both models, δ comes from regressing x₂ on all the included predictors).

Standardised slope

b_j^{*} = b_j\,\dfrac{s_{x_j}}{s_y}

$b_j^{*}$: Standardised slope: SDs of y per SD of x_j
$b_j$: The slope in its own unitsunits of y per unit of x_j
$s_{x_j}, s_y$: Sample standard deviations of x_j and of yunits of x_j; of y

6. Categorical predictors and interactions

Model with a dummy and an interaction

\hat y = b_0 + b_1x + b_2D + b_3(x \times D)

$\hat y$: Fitted valueunits of y
$D$: Dummy: 1 for the group, 0 for the reference group
$x$: A numeric predictor (or another dummy)its units
$b_0, b_1$: Intercept and slope on x for the reference groupunits of y; per unit of x
$b_2$: Difference between the groups when x = 0units of y
$b_3$: Difference between the groups’ slopes on xunits of y per unit of x

Assumes. The model is additive apart from the interaction included.

Number of dummies for a categorical predictor

\text{dummies} = k - 1

$k$: Number of levels (categories) of the predictor
$\text{dummies}$: Number of 0/1 columns entered with an intercept

Two dummies and their interaction: the four group means

\bar y_{00} = b_0,\quad \bar y_{10} = b_0 + b_1,\quad \bar y_{01} = b_0 + b_2,\quad \bar y_{11} = b_0 + b_1 + b_2 + b_3

$\bar y_{ab}$: Mean of y in the group with D₁ = a and D₂ = bunits of y
$b_0$: Intercept: the mean of the reference group (both dummies 0)units of y
$b_1, b_2$: Coefficients of the two dummies D₁ and D₂units of y
$b_3$: Coefficient of the product D₁ × D₂units of y

Assumes. No other predictors in the model; then the fit reproduces the four means exactly.

7. Multicollinearity and model choice

Variance inflation factor

\text{VIF}_j = \dfrac{1}{1 - R_j^2}

$\text{VIF}_j$: Factor by which collinearity multiplies the variance of the slope on x_j
$R_j^2$: R² of the regression of x_j on all the other predictors

Standard error of a slope in a multiple regression

\text{SE}(b_j) = \dfrac{s}{\sqrt{\sum (x_j - \bar x_j)^2}}\,\sqrt{\text{VIF}_j}

$\text{SE}(b_j)$: Standard error of the slope on x_junits of y per unit of x_j
$s$: Residual standard error of the modelunits of y
$\sum (x_j - \bar x_j)^2$: Sum of squared deviations of x_junits of x_j squared
$\text{VIF}_j$: Variance inflation factor of x_j

Nested (general linear) F test

F = \dfrac{(\text{SSE}_R - \text{SSE}_F)/q}{\text{SSE}_F/(n - p_F)}, \qquad \text{df} = (q,\; n - p_F)

$F$: Test statistic for H₀: every removed coefficient is 0
$\text{SSE}_R, \text{SSE}_F$: Residual sums of squares of the reduced and full modelsunits of y squared
$q$: Number of coefficients removed from the full model to get the reduced one
$n$: Number of rows (the same for both fits)
$p_F$: Number of coefficients in the full model, counting the intercept

Assumes. The reduced model is the full model with some terms removed; both fitted to the same rows; the full model’s conditions hold.

AIC and BIC for least squares

\text{AIC} = n\ln\!\left(\tfrac{\text{SSE}}{n}\right) + 2p, \qquad \text{BIC} = n\ln\!\left(\tfrac{\text{SSE}}{n}\right) + p\ln n

$n$: Number of rows
$\text{SSE}$: Residual sum of squaresunits of y squared
$p$: Number of coefficients, counting the intercept

Assumes. Models fitted to the same rows and the same y; up to a constant n(ln 2π + 1), which software adds (statsmodels and the B06 engine do) and which cancels in differences.

Root mean squared error on a test set

\text{RMSE} = \sqrt{\tfrac{1}{m}\textstyle\sum_{\text{test}} (y - \hat y)^2}

$m$: Number of test rows
$y, \hat y$: Actual value and the prediction of a model fitted without the test rowsunits of y

8. Assumptions and diagnostics

Durbin–Watson statistic

DW = \dfrac{\sum_{t=2}^{n}(e_t - e_{t-1})^2}{\sum_{t=1}^{n} e_t^2} \approx 2(1 - r_1)

$e_t$: The residual for period t, with the rows in time orderunits of y
$n$: Number of periods
$r_1$: Lag-1 autocorrelation of the residuals
$DW$: Between 0 and 4; near 2 means no first-order autocorrelation

Assumes. Rows in time order within one series; a model with an intercept.

Lag-1 autocorrelation of residuals

r_1 = \dfrac{\sum_{t=2}^{n}(e_t - \bar e)(e_{t-1} - \bar e)}{\sum_{t=1}^{n}(e_t - \bar e)^2}

$e_t$: The residual for period t, in time orderunits of y
$\bar e$: The mean residual (0 for a model with an intercept)units of y
$r_1$: How alike neighbouring residuals are, from −1 to 1

Breusch–Pagan test (Koenker’s form)

LM = n\,R^2_{\text{aux}}, \qquad \text{df} = k

$R^2_{\text{aux}}$: R² of the regression of the squared residuals on the model’s predictors
$n$: Number of rows
$k$: Number of predictors (not counting the intercept)
$LM$: Test statistic, compared with the chi-square distribution on k df

Assumes. H₀: the error variance is the same for every row.

HC3 robust covariance of the coefficients

\widehat{V}_{\text{HC3}} = (X^{\top}X)^{-1} X^{\top}\,\operatorname{diag}\!\left(\dfrac{e_i^2}{(1 - h_i)^2}\right) X\,(X^{\top}X)^{-1}

$X$: The matrix of predictors, with a column of 1s for the intercept
$e_i$: Row i’s residualunits of y
$h_i$: Row i’s leverage, between 0 and 1
$\widehat{V}_{\text{HC3}}$: Estimated covariance matrix; the square roots of its diagonal are the robust SEsunits of the coefficients, squared

Assumes. The model’s form is right; the errors are independent but may have unequal variances.

Normal scores for a Q–Q plot (Blom)

q_i = \Phi^{-1}\!\left(\dfrac{i - 3/8}{n + 1/4}\right)

$i$: Rank of the residual, 1 for the smallest
$n$: Number of residuals
$\Phi^{-1}$: The standard normal inverse; Excel’s NORM.S.INV
$q_i$: The value a normal sample would put at that rank, in SD units

9. Transformations and influence

Percentage change from a coefficient in a model for ln y

\%\Delta y = 100\,(e^{b} - 1) \approx 100\,b \quad \text{(small } b\text{)}

$b$: The slope for x in the model for ln y, holding the other predictors fixedper unit of x
$\%\Delta y$: Percentage change in y for a one-unit rise in x%

Assumes. The model for ln y is right; the change is in the median of y (and in the mean, if the error spread doesn’t change with x).

Back-transforming a log prediction

\tilde y = e^{\widehat{\ln y}}, \qquad \hat{\bar y} = \tilde y \times \frac{1}{n}\sum_{i=1}^{n} e^{e_i}

$\widehat{\ln y}$: The log model’s predictionlog units
$\tilde y$: Estimated median of yunits of y
$e_i$: The log model’s residualslog units
$\hat{\bar y}$: Estimated mean of y (Duan’s smearing estimate)units of y
$n$: Number of rows in the log model

Assumes. The errors in logs have the same distribution for every row.

A squared hinge term

\hat y = b_0 + b_1 x + b_2 \max(0,\, x - c)^2

$c$: The threshold, chosen before fitting (from earlier work or the business)units of x
$b_1$: The slope below the thresholdunits of y per unit of x
$b_2$: How fast the slope grows above the thresholdunits of y per (unit of x)²
$x$: The predictorunits of x
$\hat y$: Fitted valueunits of y

Leverage with one predictor

h_i = \dfrac{1}{n} + \dfrac{(x_i - \bar x)^2}{\sum_{j=1}^{n}(x_j - \bar x)^2}

$h_i$: Row i’s leverage, between 1/n and 1
$x_i$: Row i’s predictor valueunits of x
$\bar x$: Mean of the predictorunits of x
$n$: Number of rows

Studentized residuals

r_i = \dfrac{e_i}{s\sqrt{1 - h_i}}, \qquad t_i = r_i\sqrt{\dfrac{n - p - 1}{n - p - r_i^2}}

$e_i$: Row i’s residualunits of y
$s$: Residual standard error of the fit with every rowunits of y
$h_i$: Row i’s leverage
$p$: Number of coefficients, counting the intercept
$r_i$: Internally studentized residual
$t_i$: Externally studentized residual, which uses s from the fit without row i
$n$: Number of rows

Cook’s distance

D_i = \dfrac{r_i^2}{p}\cdot\dfrac{h_i}{1 - h_i}

$r_i$: Internally studentized residual
$h_i$: Leverage
$p$: Number of coefficients, counting the intercept
$D_i$: How far all the fitted values move when row i is left out, in units of the model’s precision

10. Logistic regression

Odds and log-odds

\text{odds} = \dfrac{p}{1 - p}, \qquad \operatorname{logit}(p) = \ln\dfrac{p}{1 - p}, \qquad p = \dfrac{\text{odds}}{1 + \text{odds}}

$p$: Probability of the event
$\text{odds}$: Odds of the event, from 0 to infinity
$\operatorname{logit}(p)$: Log-odds, from minus to plus infinity

The logistic regression model

\ln\dfrac{p}{1 - p} = \beta_0 + \beta_1 x_1 + \dots + \beta_k x_k, \qquad p = \dfrac{1}{1 + e^{-(\beta_0 + \beta_1 x_1 + \dots + \beta_k x_k)}}

$p$: Probability that y = 1 for a case with these predictor values
$\beta_0$: Intercept: the log-odds when every predictor is 0
$\beta_j$: Change in log-odds for a one-unit rise in xⱼ, holding the others fixedper unit of xⱼ
$x_j$: Predictor jits own units
$k$: Number of predictors

Assumes. Independent cases; each predictor’s effect is linear on the log-odds scale.

Odds ratio and its interval

\text{OR} = e^{b}, \qquad e^{\,b \pm 1.96\,\text{SE}(b)}

$b$: A fitted coefficientlog-odds per unit of x
$\text{SE}(b)$: Its standard errorlog-odds per unit of x
$\text{OR}$: Factor by which the odds are multiplied for a one-unit rise in x

Wald test of a coefficient

z = \dfrac{b}{\text{SE}(b)}

$b$: A fitted coefficientlog-odds per unit of x
$\text{SE}(b)$: Its standard errorlog-odds per unit of x
$z$: Compared with the standard normal distribution

Likelihood-ratio test

G = 2\,(\ell_{\text{full}} - \ell_{\text{reduced}}), \qquad \text{df} = p_{\text{full}} - p_{\text{reduced}}

$\ell$: Maximised log-likelihood of each model
$p$: Number of coefficients in each model, counting the intercept
$G$: Test statistic, compared with the chi-square distribution on df degrees of freedom

Assumes. The reduced model is nested in the full one and both are fitted to the same rows.

Classification measures at a threshold

\text{accuracy} = \dfrac{TP + TN}{n}, \quad \text{sensitivity} = \dfrac{TP}{TP + FN}, \quad \text{specificity} = \dfrac{TN}{TN + FP}

$TP, FN$: Cases with y = 1 predicted 1 (true positives) and predicted 0 (false negatives)
$TN, FP$: Cases with y = 0 predicted 0 (true negatives) and predicted 1 (false positives)
$n$: Number of cases

M01 Marketing Fundamentals

1. What marketing is

Value created by an exchange, and how the price divides it

V = W - C \qquad \text{Gain to buyer} = W - P \qquad \text{Gain to seller} = P - C

$V$: Value created by the exchange: the total gain to both sides$
$W$: Willingness to pay: the most the buyer would pay for the offer rather than go without it$
$P$: Price paid$
$C$: Seller’s cost of providing this one sale (only the costs the sale causes)$

Assumes. The buyer buys only if P ≤ W and no alternative would leave her more (its own W − P); the seller can keep selling only if P ≥ C. W is the buyer’s own judgement and is rarely known exactly.

2. Customer value, satisfaction and lifetime value

Net value of an offer to a customer (when benefits and costs can be put in money)

\text{Net value} = W - (P + N)

$W$: Willingness to pay: the most this customer would pay for the offer rather than go without it (the money value of its benefits to her)$
$P$: Price, including any money extras such as delivery$
$N$: Non-money costs put in money: time at the customer’s own value of time, effort, and the expected cost of risk (probability × cost if it goes wrong)$

Assumes. The customer can put a money value on the benefits and on her time and risk. She compares the net value of each alternative, including doing nothing (net value $0), and chooses the highest.

Customer lifetime value (margins at year end; retention constant)

\text{CLV} = \sum_{t=1}^{T} \frac{m\, r^{t-1}}{(1+d)^{t}} \qquad \text{every future year: } \text{CLV} = \frac{m}{1 + d - r}

$\text{CLV}$: Customer lifetime value: the present value of the margins the customer is expected to bring in$
$m$: Margin per year from the customer: revenue minus the costs that serving them causes (not revenue)$ per year
$r$: Retention rate: the probability that a customer in one year is still a customer the nextdecimal (0.70 = 70%)
$d$: Discount rate per yeardecimal (0.10 = 10%)
$t$: Year of the relationship, 1 for the first yearyears
$T$: Number of years counted (the horizon)years

Assumes. Each year’s margin arrives at the end of the year; the customer is certain to be there for year 1 and stays from one year to the next with the same probability r; m, r and d don’t change; acquisition cost is not included. The short form adds every future year and needs r < 1 + d.

Customer acquisition cost, and the net value of winning a customer

\text{CAC} = \frac{S}{n} \qquad \text{Net value of a new customer} = \text{CLV} - \text{CAC}

$\text{CAC}$: Customer acquisition cost: the average cost of winning one new customer$ per customer
$S$: Marketing and sales spending on winning new customers in the period, including free samples and introductory discounts$
$n$: Number of new customers won in the periodcustomers
$\text{CLV}$: Customer lifetime value of the customers won, measured from margin$ per customer

Assumes. The spending caused the new customers (those who would have come anyway shouldn’t be counted as won by it), and CLV is estimated for the kind of customer the spending wins.

3. Market orientation: putting customers at the centre

4. How consumers make buying decisions

Weighted-attribute score of an option

S_j = \sum_{i} w_i \, s_{ij}

$S_j$: Weighted score of option jpoints (same scale as the scores)
$w_i$: Importance weight of attribute i; the weights add up to 1
$s_{ij}$: Score of option j on attribute i, as the buyer judges it (here on a 1–10 scale)points
$i,\ j$: Labels: i runs over the attributes, j identifies the option

Assumes. The buyer is willing to trade attributes off against each other (the model is compensatory), and the weights and scores describe that buyer or a group of similar buyers. Scores must be on the same scale, with higher always better: a cheap option gets a high price score.

Improvement needed to draw level with the leader

\Delta s_{kj} = \dfrac{S_{\text{lead}} - S_j}{w_k}

$\Delta s_{kj}$: Rise in option j’s score on attribute k that would bring its weighted score level with the leader’spoints
$S_{\text{lead}}$: Weighted score of the option currently in the leadpoints
$S_j$: Weighted score of option jpoints
$w_k$: Importance weight of attribute k

Assumes. Only attribute k changes and every other score and weight stays the same. To overtake rather than tie, the rise must be larger than this.

5. What influences buying

Customers including referrals (referral multiplier)

T_n = N \, \dfrac{1 - r^{\,n+1}}{1 - r} \qquad T_{\infty} = \dfrac{N}{1 - r}

$T_n$: Total customers after n rounds of referrals, including the N won directlycustomers
$T_{\infty}$: Long-run total if referrals carried on for evercustomers
$N$: Customers won directly (by advertising, say) at the startcustomers
$r$: Average number of new customers each new customer refers, from 0 up to but not including 1customers per customer
$n$: Number of rounds of referrals countedrounds

Assumes. Each customer refers r others once, soon after joining; r stays the same in every round; the market never runs out of people to refer; and every referred customer is extra. In practice r falls as a market fills up, and some referred customers would have joined anyway.

6. How organisations buy

Equipment orders under derived demand (acceleration effect)

O_t = \max\bigl(0,\; K_t - (1 - d)\,F_{t-1}\bigr) \qquad F_t = \max\bigl(K_t,\; (1 - d)\,F_{t-1}\bigr)

$O_t$: Units of equipment ordered in year t (for example, vans)units
$K_t$: Units needed in year t, in proportion to the customer’s own demandunits
$F_t$: Units in service at the end of year t, including any spare units kept when demand fallsunits
$F_{t-1}$: Units in service at the end of the previous yearunits
$d$: Share of the units in service that wear out and are retired each yeardecimal (0.1 = 10%)
$t$: Year

Assumes. The equipment needed is proportional to the customer’s demand; a fixed share wears out each year; spare equipment is kept rather than sold, so orders can’t fall below zero.

7. Situation analysis

Weighted competitive-strength score

S_j = \sum_{i} w_i \, s_{ij}, \qquad \sum_i w_i = 1

$S_j$: Weighted strength score of competitor jpoints on the rating scale
$w_i$: Weight of factor i: its importance to customers, as a share of the total
$s_{ij}$: Rating of competitor j on factor i, for example from 1 (poor) to 5 (excellent)points

Assumes. The factors are the ones customers use to choose, the weights reflect the customers you want to win, and the ratings are made on the same scale for every competitor.

8. Marketing objectives

Customers needed for a revenue target

N_{\text{req}} = \dfrac{R_{\text{target}}}{v}

$N_{\text{req}}$: Customers the business must have during the periodcustomers
$R_{\text{target}}$: Revenue target for the period$ per year
$v$: Average revenue per customer over the period$ per customer per year

Assumes. Every customer brings in the average revenue and is a customer for the whole period.

New customers needed, allowing for retention

N_{\text{new}} = N_{\text{req}} - r\,N_0 \qquad \text{and, if } v \text{ is unchanged,} \qquad N_{\text{new}} = N_0\,(1 + g - r)

$N_{\text{new}}$: New customers the business must win during the periodcustomers
$N_{\text{req}}$: Customers needed for the revenue targetcustomers
$N_0$: Customers at the start of the periodcustomers
$r$: Retention rate: share of starting customers still customers at the end of the perioddecimal (0.85 = 85%)
$g$: Growth target for revenue (or for customers, when revenue per customer is unchanged)decimal (0.15 = 15%)
$v$: Average revenue per customer per period$ per customer

Assumes. Customers join and leave at the start of the period; new customers stay until its end; the retention rate is not changed by the plan itself.

Top-of-funnel volume needed

V = \dfrac{N_{\text{new}}}{c_1 \times c_2 \times \cdots \times c_k}

$V$: Leads, enquiries or visits needed at the top of the funnelleads
$N_{\text{new}}$: New customers neededcustomers
$c_i$: Conversion rate from each stage to the next, for stages 1 to kdecimal

Assumes. The conversion rates are stable and don’t fall as volume rises (in practice, extra leads are often of lower quality), and leads convert within the period. In business markets a lead can take months to become a client, so leads that arrive late in the year count towards next year’s wins, and this year’s target needs leads earlier than an even monthly spread suggests.

9. Competitive advantage

Value created, and how the price shares it

V = W - C = \underbrace{(W - P)}_{\text{customer}} + \underbrace{(P - C)}_{\text{business}}

$V$: Value created by serving one customer$ per customer (or per unit)
$W$: The customer’s willingness to pay for this offer: the most they would pay rather than go without it$ per customer
$P$: The price the customer pays$ per customer
$C$: The cost to the business of serving that customer$ per customer

Assumes. W is the value of this offer on its own; rivals enter through the comparison of W − P across offers. In practice W differs between customers and has to be estimated.

Price premium

\text{Premium} = \left(\dfrac{P_f}{P_r} - 1\right) \times 100

$P_f$: The firm’s price$ per unit
$P_r$: The rival’s price for a comparable offer$ per unit

Assumes. The two prices are for comparable quantities and terms (per head, per shirt, per month).

Highest price that still wins the customer

P_{\max} = P_r + (W_f - W_r)

$P_{\max}$: Highest price at which the customer is at least as well off choosing the firm$ per unit
$P_r$: The rival’s price$ per unit
$W_f$: The customer’s willingness to pay for the firm’s offer$ per unit
$W_r$: The customer’s willingness to pay for the rival’s offer$ per unit

Assumes. The customer chooses the option that leaves them the most value (W − P), and the rival’s price stays where it is.

M02 The 4Ps Marketing Mix

1. The marketing mix and customer value

Customer value (a way of thinking)

\text{Customer value} = \dfrac{\text{Perceived benefits}}{\text{Perceived total cost}}

$\text{Perceived benefits}$: The functional, emotional and social benefits of the offer, as the customer judges them
$\text{Perceived total cost}$: The price plus the time, effort and risk of buying and using the offer

Assumes. Both are judged by the customer and compared with the next-best alternative, which may be buying nothing.

Gross margin per unit sold through a retailer

g = P \times (1 - m) - c

$g$: Gross margin per unit: what the producer keeps after the trade’s share and the cost of the unit$ per unit
$P$: Shelf price paid by the shopper$ per unit
$m$: Share of the shelf price kept by the distributor and retailer togetherdecimal (0.32 = 32%)
$c$: Cost of goods per unit (for Lantern: liquid, can, co-packing and freight to the distributor)$ per unit

Assumes. The trade keeps a fixed share of the shelf price. For Lantern’s own cafés and online shop, m = 0 and the cost of fulfilment ($0.45 a can) is subtracted instead.

2. Product decisions

Extra cost of goods from a better tier

\Delta C = (c_{\text{high}} - c_{\text{low}}) \times Q

$\Delta C$: Extra cost of goods for the volume sold$
$c_{\text{high}},\ c_{\text{low}}$: Cost of goods per unit of the two tiers$ per unit
$Q$: Units soldunits

Assumes. The cost per unit of each tier doesn’t change with volume.

Extra fixed cost of a wider range

\Delta F = (n_{\text{wide}} - n_{\text{narrow}}) \times \sum_{k} L_k + F_{\text{range}}

$\Delta F$: Extra fixed cost per year of the wider range$ per year
$n_{\text{wide}},\ n_{\text{narrow}}$: Number of SKUs (flavours) in each rangeSKUs
$L_k$: Listing fee per SKU per year in retail channel k$ per SKU per year
$F_{\text{range}}$: Other fixed costs of the wider range: production runs, labels, printing plates$ per year

Assumes. Every flavour is listed in every retail channel chosen; channels without listing fees add nothing.

Extra units needed to pay for a fixed cost

Q_{\text{extra}} = \dfrac{\Delta F}{g}

$Q_{\text{extra}}$: Extra units the decision must sell to cover its own fixed costunits
$\Delta F$: The extra fixed cost of the decision$
$g$: Gross margin per unit: what the producer keeps after the trade’s share and the cost of goods$ per unit

Assumes. The extra units are genuinely extra: sales taken from the company’s own existing items (cannibalisation) don’t count.

3. The product life cycle

Year-on-year growth

g = \dfrac{S_t - S_{t-1}}{S_{t-1}} \times 100

$g$: Growth rate over a year%
$S_t$: Sales in the current year, or in a quarterunits or $
$S_{t-1}$: Sales in the year before, or in the same quarter a year earlierunits or $

Assumes. Compare like with like: whole years, or the same quarter in consecutive years, so that seasonal swings cancel out.

Seasonally adjusted sales

S^{\text{adj}}_q = \dfrac{S_q}{s_q}

$S^{\text{adj}}_q$: Sales in quarter q, adjusted to an average quarterunits
$S_q$: Actual sales in quarter qunits
$s_q$: Seasonal index for quarter q: its sales relative to an average quarter (Harbour City: 0.85, 1.05, 1.25, 0.85)

Assumes. The seasonal pattern is stable from year to year, and the four indexes sum to 4.

4. Pricing approaches

Contribution margin per unit

\text{CM} = P - VC

$P$: Price the seller receives per unit, after any trade margin, commission or fulfilment cost deducted from it$ per unit
$VC$: Variable cost per unit$ per unit

Assumes. Variable cost per unit is the same at every volume.

Contribution margin ratio

\text{CM ratio} = \dfrac{P - VC}{P}

$\text{CM ratio}$: Share of each dollar of price left after variable costs%
$P$: Price the seller receives per unit$ per unit
$VC$: Variable cost per unit$ per unit

Markup on cost and the cost-plus price

k = \dfrac{P - C}{C} \qquad P = C\,(1 + k)

$k$: Markup: profit per unit as a share of the cost%
$C$: Unit cost the markup is applied to$ per unit
$P$: Selling price$ per unit

Margin on price

m = \dfrac{P - C}{P}

$m$: Margin: profit per unit as a share of the price%
$P$: Selling price$ per unit
$C$: Unit cost$ per unit

Converting between markup and margin

m = \dfrac{k}{1 + k} \qquad k = \dfrac{m}{1 - m}

$k$: Markup on costdecimal
$m$: Margin on pricedecimal

Assumes. Both are measured on the same cost and price.

Price that gives a target margin

P = \dfrac{C}{1 - m}

$C$: Unit cost$ per unit
$m$: Target margin on pricedecimal
$P$: Price that gives the target margin$ per unit

Break-even volume

Q_{\text{BE}} = \dfrac{FC}{P - VC}

$Q_{\text{BE}}$: Units that must be sold in the period for total contribution to equal fixed costsunits
$FC$: Fixed costs for the period$
$P - VC$: Contribution margin per unit$ per unit

Assumes. Price, variable cost per unit and fixed costs are constant over the range considered; with several products or channels, the mix is constant.

Break-even revenue

R_{\text{BE}} = \dfrac{FC}{\text{CM ratio}}

$R_{\text{BE}}$: Sales value at which total contribution equals fixed costs$

Volume for a target profit

Q_{\text{target}} = \dfrac{FC + \pi^{*}}{P - VC}

$Q_{\text{target}}$: Units that must be sold in the period to earn the target profitunits
$FC$: Fixed costs for the period$
$\pi^{*}$: Target profit for the period$
$P$: Price the seller receives per unit$ per unit
$VC$: Variable cost per unit$ per unit

Target-return price

P = \text{unit cost} + \dfrac{r \times I}{Q}

$P$: Target-return price$ per unit
$\text{unit cost}$: Full cost per unit at the forecast volume: variable cost plus fixed costs ÷ Q$ per unit
$r$: Target rate of return on the capital invested, per period%
$I$: Capital invested$
$Q$: Forecast unit sales for the periodunits

Assumes. The forecast volume is achieved at this price.

Economic value to the customer

\text{EV} = \text{reference value} + \text{differentiation value}

$\text{reference value}$: Price of the customer’s next-best alternative$
$\text{differentiation value}$: Value to the customer of every difference from that alternative, positive or negative$

5. Price, demand and pricing tactics

Price elasticity of demand

E = \dfrac{\%\,\Delta Q}{\%\,\Delta P}

$\%\,\Delta Q$: Percentage change in quantity demanded%
$\%\,\Delta P$: Percentage change in price%
$E$: Elasticity; usually negative. This course keeps the sign; many texts report the absolute value

Assumes. Only the price changes; everything else that affects demand is held constant.

Point elasticity (from a starting point)

E = \dfrac{(Q_2 - Q_1)/Q_1}{(P_2 - P_1)/P_1}

$E$: Price elasticity of demand; usually negative
$P_1,\ Q_1$: Starting price and quantity$, units
$P_2,\ Q_2$: New price and quantity$, units

Midpoint (arc) elasticity

E = \dfrac{(Q_2 - Q_1) \big/ \tfrac{1}{2}(Q_1 + Q_2)}{(P_2 - P_1) \big/ \tfrac{1}{2}(P_1 + P_2)}

$E$: Price elasticity of demand; usually negative
$P_1,\ Q_1$: First observed price and quantity$, units
$P_2,\ Q_2$: Second observed price and quantity$, units
$\tfrac{1}{2}(Q_1 + Q_2)$: Average of the two quantitiesunits
$\tfrac{1}{2}(P_1 + P_2)$: Average of the two prices$

Revenue after a price change

\dfrac{R_2}{R_1} = \dfrac{P_2}{P_1} \times \dfrac{Q_2}{Q_1}

$R$: Revenue, price × quantity$
$P_1,\ P_2$: Price before and after the change$
$Q_1,\ Q_2$: Quantity before and after the changeunits

Volume change that keeps contribution unchanged

\%\,\Delta Q_{\text{needed}} = \dfrac{-\Delta P}{CM + \Delta P}

$\%\,\Delta Q_{\text{needed}}$: Percentage change in volume that keeps total contribution unchanged%
$\Delta P$: Change in the price the seller receives; negative for a cut$ per unit or % of price
$CM$: Contribution margin per unit at the current price (or the contribution margin ratio, if ΔP is a percentage)$ per unit or % of price

Assumes. Variable cost per unit and fixed costs do not change; ΔP and CM are in the same units.

6. Distribution channels and decisions

Net price through an indirect channel

N = P \times (1 - m)

$N$: Net price: what the producer receives per unit$
$P$: Shelf price paid by the final customer$
$m$: Trade margin: the share of the shelf price kept by all intermediaries together

Assumes. The margin is quoted as a share of the shelf price. Sales tax is ignored, as it is throughout this course’s fictional market.

Net price through a direct channel

N = P - f

$N$: Net price: what the producer receives per unit$
$P$: Price paid by the final customer$
$f$: Fulfilment cost per unit: storing, picking, packing and delivering$

Gross margin per unit

g = N - c

$g$: Gross margin per unit, before fixed fees, promotion and overhead$
$N$: Net price per unit: what the producer receives after the trade’s margin (and, selling direct, after fulfilment)$
$c$: Cost of goods per unit$

Combined margin of a multi-level channel

m = 1 - (1 - m_r)(1 - m_d)

$m_r$: Retailer’s margin, as a share of the shelf price
$m_d$: Distributor’s margin, as a share of the price it sells to the retailer at

Assumes. Each level quotes its margin on its own selling price.

Units needed to cover a fixed channel cost

Q = \dfrac{F}{g}

$Q$: Units that must be sold in a year to cover the fee (round up)units
$F$: Fixed yearly cost of the channel: listing fees × SKUs, or the running cost of a direct channel$
$g$: Gross margin per unit in the channel$

7. The promotion mix and integrated marketing communications

Percentage-of-sales budget

B = s \times R

$B$: Promotion budget for the period$
$s$: Chosen percentage of sales, as a decimal
$R$: Sales revenue: last period’s actual or this period’s forecast$

Assumes. The percentage is a policy choice, often taken from the company’s history or industry habit; it is not derived from what promotion achieves.

Competitive-parity budget (average of rivals)

B = \dfrac{1}{n}\sum_{i=1}^{n} B_i

$B_i$: Estimated promotion spending of rival i$
$n$: Number of rivals used as the benchmark

Objective-and-task budget

B = \sum_{k} q_k \times c_k

$B$: Promotion budget for the period$
$q_k$: Quantity of task k needed to reach the objectives (demo days, thousands of impressions, events…)units
$c_k$: Cost per unit of task k$ per unit

Assumes. The quantities rest on estimates of how much activity is needed to reach each objective; those estimates should be checked against results.

8. Measuring promotion and bringing the mix together

Average frequency

\bar{f} = \dfrac{I}{R}

$\bar{f}$: Average frequency: times each reached person was exposed
$I$: Impressions delivered to the target audienceimpressions
$R$: Reach: different people exposed at least oncepeople

Gross rating points

\text{GRPs} = r \times \bar{f} = \dfrac{I}{N} \times 100

$r$: Reach as a percentage of the target audience%
$\bar{f}$: Average frequency
$I$: Impressions delivered to the target audienceimpressions
$N$: Size of the target audiencepeople

Cost per thousand impressions

\text{CPM} = \dfrac{\text{Spend}}{I} \times 1{,}000

$\text{Spend}$: Cost of the media$
$I$: Impressions boughtimpressions

Click-through rate

\text{CTR} = \dfrac{\text{Clicks}}{I}

$\text{Clicks}$: Clicks on the adclicks
$I$: Impressions of the adimpressions

Conversion rate

\text{CR} = \dfrac{\text{Conversions}}{\text{Clicks}}

$\text{Conversions}$: Completed target actions, such as ordersconversions

Cost per acquisition

\text{CPA} = \dfrac{\text{Spend}}{\text{Conversions}}

$\text{CPA}$: Cost of each conversion$

Return on ad spend

\text{ROAS} = \dfrac{\text{Attributed revenue}}{\text{Ad spend}}

$\text{Attributed revenue}$: Revenue credited to the campaign by the attribution rule in use$

Assumes. Says nothing about costs or about whether the revenue was caused by the ads.

Marketing ROI

\text{ROI} = \dfrac{\Delta C - M}{M}

$\Delta C$: Incremental contribution: extra revenue caused by the marketing minus its variable costs$
$M$: Marketing cost$

Break-even ROAS

\text{ROAS}_{\text{BE}} = \dfrac{1}{m_c}

$m_c$: Contribution margin ratio on the revenue counted in ROAS

Assumes. All attributed revenue is incremental; if it isn’t, the true return is lower.

M04 Market Research

1. The research process

Expected value of an option

E[V(a)] = \sum_{s} p_s \, V(a, s)

$E[V(a)]$: Expected value of choosing option a$
$a$: An option the decision maker could choose, such as launching or waiting
$s$: A possible state of the market, such as strong or weak demand
$p_s$: Probability given to state s now; the probabilities add up to 1
$V(a, s)$: Payoff of option a if state s occurs (negative for a loss)$

Assumes. The states cover every outcome, and the probabilities and payoffs are the decision maker’s best estimates. Choosing by expected value ignores attitudes to risk.

Expected value of perfect information

\text{EVPI} = \sum_{s} p_s \max_{a} V(a, s) \; - \; \max_{a} \sum_{s} p_s \, V(a, s)

$\text{EVPI}$: The most that information removing all uncertainty about the state could be worth to the decision$
$a$: An option
$s$: A state of the market
$p_s$: Probability of state s before any research
$V(a, s)$: Payoff of option a in state s$

Assumes. The first term chooses the best option after learning the state; the second chooses the best option on expected value without knowing it. The payoffs and probabilities are estimates.

2. Secondary research

Scaling a survey rate to a population total

T = N \times r \times k

$T$: Total for the whole population over a yearunits a year
$N$: Number of units in the population, such as householdshouseholds
$r$: Average quantity per unit per period, from a surveyunits per household per period
$k$: Number of periods in a year (12 for a monthly rate, 52 for a weekly one)

Assumes. The survey’s average applies to the whole population (its sample is representative and its answers accurate), and its definition of the quantity is the one you need.

Grossing up for coverage

T = \dfrac{T_c}{c}

$T$: Estimated total for the whole marketunits
$T_c$: Total reported for the part the source covers, such as an association’s membersunits
$c$: Share of the market the covered part accounts forproportion (0 to 1)

Assumes. The share c is known from a trustworthy source, and the uncovered part behaves like the covered part.

3. Qualitative research

Percent agreement between two coders

p_o = \dfrac{a}{n}

$p_o$: Observed agreement: the share of units given the same code by both codersproportion (0 to 1)
$a$: Units both coders gave the same codeunits
$n$: Units coded by both codersunits

Assumes. Each unit (a quotation, a passage, a review) gets one code from each coder, working independently.

Cohen’s kappa

\kappa = \dfrac{p_o - p_e}{1 - p_e}, \qquad p_e = \sum_{k} q_{1k} \, q_{2k}

$\kappa$: Agreement beyond chance, as a share of the agreement possible beyond chance
$p_o$: Observed agreement (the percent-agreement formula)proportion
$p_e$: Agreement expected by chanceproportion
$q_{1k}$: Share of units coder 1 gave code kproportion
$q_{2k}$: Share of units coder 2 gave code kproportion

Assumes. Two coders, the same units, one code per unit from a fixed list. Kappa is 1 for perfect agreement and 0 for agreement no better than chance.

4. Questionnaire design

Likert scale score with reverse-coded items

x'_j = (L + H) - x_j \qquad \text{Score} = \dfrac{1}{k}\sum_{j=1}^{k} y_j, \quad y_j = \begin{cases} x'_j & \text{item } j \text{ reverse-worded} \\ x_j & \text{otherwise} \end{cases}

$x_j$: The code a respondent gave item j (for example 1 = strongly disagree … 5 = strongly agree)
$x'_j$: The reverse-coded value of item j
$L,\ H$: The lowest and highest codes on the scale (1 and 5 on a five-point scale, so a reversed code is 6 − x)
$y_j$: The value used for item j: reversed for reverse-worded items, as given otherwise
$k$: The number of items in the scale
$j$: The item number, from 1 to k
$\text{Score}$: The respondent’s scale score, on the same range as the items

Assumes. The items measure one attitude and every item is coded so that a higher code means more of it. Averaging ordinal codes treats the steps as equal, which is a convention, not a fact about the scale.

Cronbach’s alpha (internal consistency of a scale)

\alpha = \dfrac{k}{k - 1}\left(1 - \dfrac{\sum_{i=1}^{k} s_i^2}{s_T^2}\right)

$k$: The number of items in the scale
$s_i^2$: The variance of item i across respondents, after any reverse-codingsquared scale points
$s_T^2$: The variance of respondents’ totals (the sum of their k item codes)squared scale points
$i$: The item number, from 1 to k
$\alpha$: Cronbach’s alpha: up to 1; around 0.70 to 0.90 is the usual target

Assumes. The items measure one thing and are coded in the same direction. Use the same kind of variance throughout: sample variances (dividing by n − 1, Excel’s VAR.S) here; dividing every variance by n instead gives the same α.

5. Sampling methods

Proportional allocation

n_h = n \times \dfrac{N_h}{N}

$n_h$: The sample (or invitations) given to stratum hunits
$n$: The total sampleunits
$N_h$: The number of units in stratum h on the frameunits
$N$: The number of units on the whole frameunits

Assumes. Round so that the parts add to n (largest remainder).

Neyman allocation

n_h = n \times \dfrac{N_h S_h}{\sum_k N_k S_k}

$S_h$: The standard deviation of the measure in stratum h; for a proportion, √(p_h(1 − p_h)) from a guess or a past surveyunits of the measure
$N_h$: The size of stratum hunits
$N_k,\ S_k$: The size and standard deviation of each stratum k, summed over all strata
$n,\ n_h$: The total sample and stratum h’s part of itunits

Assumes. Sampling costs the same in every stratum. It minimises the standard error of the overall estimate for one measure; other measures may favour other allocations.

Stratified estimate of a proportion

\hat{p}_{st} = \sum_h W_h \hat{p}_h, \qquad W_h = \dfrac{N_h}{N}

$\hat{p}_h$: The sample proportion in stratum h
$W_h$: Stratum h’s share of the population
$N_h,\ N$: Stratum and population sizesunits

Assumes. A random sample within each stratum.

Standard error of a stratified proportion

\text{SE}(\hat{p}_{st}) = \sqrt{\sum_h W_h^2 \left(1 - \dfrac{n_h}{N_h}\right) \dfrac{\hat{p}_h(1 - \hat{p}_h)}{n_h - 1}}

$W_h$: Stratum h’s share of the population, N_h ÷ N
$n_h,\ N_h$: Sample and population sizes in stratum hunits
$\hat{p}_h$: The sample proportion in stratum h
$1 - n_h/N_h$: The finite population correction for stratum h

Assumes. Simple random sampling without replacement within each stratum; at least two units sampled per stratum. Some texts divide by n_h instead of n_h − 1, which matters only in small strata.

Design effect and effective sample size (clusters of equal size)

\text{deff} = \dfrac{\text{Var}_{\text{design}}}{\text{Var}_{\text{SRS}}} \approx 1 + (b - 1)\rho, \qquad n_{\text{eff}} = \dfrac{n}{\text{deff}}

$\text{Var}_{\text{design}},\ \text{Var}_{\text{SRS}}$: The variance of the estimate under the design, and under a simple random sample of the same size
$b$: The number of units taken from each clusterunits
$\rho$: The intraclass correlation: how alike units in the same cluster are
$n$: The number of units in the sampleunits
$n_{\text{eff}}$: The effective sample sizeunits
$\text{deff}$: The design effect

Assumes. The approximation 1 + (b − 1)ρ is for clusters of equal size with a common ρ.

Completes and invitations for a margin of error

n_0 = \dfrac{z^2 p(1 - p)}{E^2}, \qquad n = \dfrac{n_0}{1 + (n_0 - 1)/N}, \qquad \text{Invitations} = \dfrac{n}{r}

$n_0$: The completes needed from a large population (B04)units
$z$: The z multiplier for the confidence level (1.96 for 95%)
$p$: A planning value for the proportion (0.5 when unknown)
$E$: The margin of error wanted, as a proportion
$N$: The size of the population or stratumunits
$n$: The completes needed with the finite population correctionunits
$r$: The expected response rate

Assumes. Round each result up. Work it out separately for every subgroup that must be read on its own.

6. Survey error

AAPOR response rates RR1 and RR2

\text{RR1} = \dfrac{I}{(I + P) + (R + NC + O) + U} \qquad \text{RR2} = \dfrac{I + P}{(I + P) + (R + NC + O) + U}

$I$: Complete interviewscases
$P$: Partial interviewscases
$R$: Refusals and break-offscases
$NC$: Non-contacts (for an e-mail survey, invitations that bounced)cases
$O$: Other eligible non-interviewscases
$U$: Cases of unknown eligibility. On a list where everyone is known to be eligible there are none: AAPOR (10th edition, section 1) counts unanswered invitations as eligible non-respondents, in the denominator either waycases

Assumes. Cases found to be ineligible are excluded from the denominator. The definitions of complete and partial are set before fieldwork.

Non-response bias of a respondent mean or share

\bar{y}_r - \bar{y} = (1 - \text{RR})\,(\bar{y}_r - \bar{y}_{nr})

$\bar{y}_r$: The mean or share among respondents
$\bar{y}_{nr}$: The mean or share among non-respondents
$\bar{y}$: The mean or share among everyone sampled
$\text{RR}$: The response rate

Assumes. Treats each sampled person as a fixed respondent or non-respondent; it is B01’s weighted average of the two groups, rearranged.

Post-stratification weights and a weighted estimate

w_h = \dfrac{N_h}{n_h}, \qquad \bar{y}_w = \dfrac{\sum_i w_i y_i}{\sum_i w_i} = \sum_h \dfrac{N_h}{N}\,\bar{y}_h

$w_h$: The weight of each respondent in group hpeople per respondent
$N_h$: The number in group h in the population (on the frame)people
$n_h$: The number of respondents in group hpeople
$w_i,\ y_i$: Respondent i’s weight and answer (1 or 0 for a share)
$\bar{y}_h$: The mean or share among group h’s respondents
$N$: The population sizepeople
$\bar{y}_w$: The weighted estimate

Assumes. Population counts N_h are known. The estimate is unbiased only if respondents answer like non-respondents within each group.

Kish effective sample size and the design effect of weighting

n_{\text{eff}} = \dfrac{\left(\sum_i w_i\right)^2}{\sum_i w_i^2}, \qquad \text{deff}_w = \dfrac{n}{n_{\text{eff}}} = 1 + \text{CV}^2(w)

$w_i$: Respondent i’s weight
$n$: The number of respondentspeople
$n_{\text{eff}}$: The effective sample sizepeople
$\text{CV}(w)$: The coefficient of variation of the weights (their standard deviation, dividing by n, ÷ their mean)
$\text{deff}_w$: The design effect of the unequal weights

Assumes. A rule of thumb: it assumes equal variances across weighting groups and ignores any gain from stratification (Zipf and Valliant).

Mean squared error

\text{MSE} = \text{bias}^2 + \text{variance}

$\text{bias}$: The average amount by which the estimate misses the true value, over repetitions of the surveyunits of the estimate
$\text{variance}$: The spread of the estimate over repetitionsunits squared
$\text{MSE}$: The average squared distance between the estimate and the truthunits squared

Assumes. The margin of error reflects the sampling part of the variance only.

7. Market sizing

Market value from volume

V = Q \times \bar{p}

$V$: Market value over the period$
$Q$: Market volume over the periodunits
$\bar{p}$: Average price per unit sold, at a stated level: the shelf price, or what producers receive$ per unit

Assumes. The average is weighted by volume (the price of the average unit sold). A simple average of shelf prices is only a stand-in.

Top-down sizing (a chain of shares)

Q_{\text{SAM}} = a \times Q_{\text{TAM}}, \qquad Q_{\text{SOM}} = m \times Q_{\text{SAM}}

$Q_{\text{TAM}}$: Total addressable market: the category’s sales, all suppliers and channelsunits or $
$a$: Share of the TAM the business’s offer and routes to market could serve
$Q_{\text{SAM}}$: Serviceable available marketunits or $
$m$: Share of the SAM the business expects to win in the period
$Q_{\text{SOM}}$: Serviceable obtainable market: the estimate for the businessunits or $

Assumes. Each share applies to the figure before it, for the same products, place and period.

Bottom-up sizing from outlets

Q = W \sum_{c} L_c \, v_c

$Q$: Units sold in the periodunits
$W$: Weeks in the periodweeks
$L_c$: Outlets in channel c that stock the productoutlets
$v_c$: Rate of sale in channel c: units an outlet sells a weekunits per outlet per week
$c$: A channel

Assumes. Every listing outlet in a channel sells at the channel’s average rate, every week of the period.

Bottom-up sizing from customers

Q = N \times r \times f \times u

$Q$: Units sold in the periodunits
$N$: Customers in the target grouppeople or firms
$r$: Share of them who buy in the period
$f$: Purchases per buyer in the periodpurchases
$u$: Units per purchaseunits

Assumes. The share who buy and their frequency apply to the whole target group; segments that differ are summed separately (SUMPRODUCT).

Buyers from stated intent

B = N \sum_{k} c_k \, p_k

$B$: Expected buyerspeople
$N$: People the survey representspeople
$p_k$: Share giving answer k (weighted to the population)
$c_k$: Conversion assumption: share of those giving answer k who will actually buy
$k$: An answer on the intent scale (definitely, probably …)

Assumes. The c_k are assumptions, ideally from comparable launches or past sales; they are not measured by the survey.

Rate of sale needed to reach a goal

v^{*} = \dfrac{G - Q_{\text{other}}}{L \times W}

$v^{*}$: Rate of sale needed in one channelunits per outlet per week
$G$: The goal for the periodunits
$Q_{\text{other}}$: Units expected from every other routeunits
$L$: Outlets listing the product in that channeloutlets
$W$: Weeks in the periodweeks

Assumes. The other routes deliver what the plan assumes.

8. Competitor research

Price premium over a rival

\text{Premium} = \dfrac{P_{\text{own}} - P_{\text{rival}}}{P_{\text{rival}}} \times 100

$\text{Premium}$: Price premium (negative: a discount)%
$P_{\text{own}}$: Your price for a comparable offer$
$P_{\text{rival}}$: The rival’s price for the same offer, in the same channel$

Assumes. The two offers are comparable: same pack size or unit, same channel, both regular or both promoted prices.

Price per unit of volume

p_{100} = \dfrac{P}{S} \times 100

$p_{100}$: Price per 100 ml or per 100 g$ per 100 ml or g
$P$: Shelf price of the pack$
$S$: Pack sizeml or g

Assumes. The products are otherwise comparable; a multipack’s size is the total of its packs.

Promotion depth

d = \dfrac{P_{\text{reg}} - P_{\text{promo}}}{P_{\text{reg}}} \times 100

$d$: Depth of the price cut%
$P_{\text{reg}}$: Regular shelf price$
$P_{\text{promo}}$: Promoted shelf price$

Assumes. The regular price is the one shown on the shelf label at the same store, or a known usual price.

Average shelf price of a brand in a channel

\bar{p}_{bc} = \dfrac{1}{n_{bc}} \sum_{i \in (b,\,c)} p_i

$\bar{p}_{bc}$: Average shelf price of brand b in channel c$
$n_{bc}$: Number of stores in channel c where brand b was recordedstores
$p_i$: A recorded shelf price$
$b$: A brand
$c$: A channel

Assumes. Each store counts once, whatever it sells; promoted and regular prices are averaged together unless separated.

9. Analysing survey data

Top-box and top-two-box scores

\text{Top-}k\text{ box} = \dfrac{x_k}{n} \times 100

$x_k$: Respondents choosing one of the k most favourable answers (k = 1 for top box, 2 for top two)respondents
$n$: The base: respondents who answered the question (with “don’t know” in or out, as stated)respondents
$k$: How many boxes, counted from the favourable end

Assumes. The scale is ordered, with the favourable end clear.

Net Promoter Score

\text{NPS} = 100 \times (p_P - p_D), \qquad p_P = \dfrac{P}{n}, \quad p_D = \dfrac{D}{n}

$P$: Promoters: answers of 9 or 10 to the likelihood-to-recommend questionrespondents
$D$: Detractors: answers of 0 to 6respondents
$n$: All who answered the question, passives (7 or 8) includedrespondents
$p_P,\ p_D$: Shares of promoters and detractors
$\text{NPS}$: Net Promoter Score, from −100 to +100points

Standard error and margin of error of the NPS

\text{SE} = 100\sqrt{\dfrac{p_P + p_D - (p_P - p_D)^2}{n}}, \qquad \text{margin} = z^* \times \text{SE}

$p_P,\ p_D$: Shares of promoters and detractors
$n$: Baserespondents
$z^*$: Standard normal critical value: 1.96 for 95%
$\text{SE}$: Standard error of the NPSpoints

Assumes. A simple random sample (or one stratum of a stratified sample) large enough for the normal approximation.

Comparing two independent scores

\text{SE}_{\text{diff}} = \sqrt{\text{SE}_1^2 + \text{SE}_2^2}, \qquad z = \dfrac{\text{NPS}_1 - \text{NPS}_2}{\text{SE}_{\text{diff}}}

$\text{SE}_1,\ \text{SE}_2$: Standard errors of the two scorespoints
$\text{NPS}_1,\ \text{NPS}_2$: The two scorespoints
$z$: Test statistic, compared with the standard normal distribution

Assumes. The two groups are independent samples: two segments, or two waves of a survey with fresh samples.

M05 Market Segmentation and Positioning

1. Why segment?

Contribution from a set of offers, and the gain from segmenting

C = \sum_{s} N_s\, b_s\, m_s, \qquad \Delta = C_{\text{tailored}} - C_{\text{one offer}} - F

$N_s$: Potential buyers in segment speople
$b_s$: Share of segment s who buy the offer made to them (the purchase rate)
$m_s$: Contribution per buyer in segment s: price less variable cost$ per buyer
$C$: Contribution from the market under one strategy$
$F$: Extra fixed cost of running the tailored offers rather than one (variants, campaigns, training)$
$\Delta$: Gain from segmenting: positive means tailored offers pay$

Assumes. The purchase rates are estimates, from research or a test, for the same period as the costs; the tailored offers don’t change the total number of potential buyers.

2. Demographic and geographic segmentation

Penetration rate of an area or neighbourhood type

\text{Penetration} = \dfrac{C_g}{H_g} \times 1{,}000

$C_g$: Customers (or buyers) living in area or neighbourhood type gcustomers
$H_g$: Households (or people) in area or type ghouseholds
$\text{Penetration}$: Customers per 1,000 households in gper 1,000

Assumes. Customers and households are counted for the same areas and the same period; customers’ addresses are matched to the classification correctly.

3. Psychographic and behavioural segmentation

4. Cluster analysis

Squared Euclidean distance between two respondents

d^2(\mathbf{z}_i, \mathbf{z}_h) = \sum_{j=1}^{p} (z_{ij} - z_{hj})^2

$d^2(\mathbf{z}_i, \mathbf{z}_h)$: Squared distance between respondents (or centres) i and hsquared standard deviations
$z_{ij},\ z_{hj}$: Their standardised ratings (z-scores) on segmentation variable jstandard deviations
$p$: Number of segmentation variables

Assumes. Every variable is standardised first, so each counts equally.

Segment centre (centroid)

c_{gj} = \dfrac{1}{n_g} \sum_{i \in g} z_{ij}

$c_{gj}$: Centre of segment g on variable j: its members’ meanstandard deviations
$n_g$: Number of respondents in segment grespondents
$z_{ij}$: Standardised rating of respondent i on variable jstandard deviations

Within-cluster sum of squares (WCSS)

W_k = \sum_{g=1}^{k} \sum_{i \in g} d^2(\mathbf{z}_i, \mathbf{c}_g), \qquad T = (n - 1)\,p

$W_k$: Within-cluster sum of squares of a k-segment solutionsquared standard deviations
$d^2(\mathbf{z}_i, \mathbf{c}_g)$: Squared distance from respondent i to the centre of their own segment gsquared standard deviations
$k$: Number of segments
$T$: Total sum of squares: W with one segment; (n − 1)p for p standardised variablessquared standard deviations
$n$: Number of respondentsrespondents
$p$: Number of segmentation variables

Assumes. At its best, W falls (or stays level) as k rises, and is 0 when every respondent is a segment of one; k-means may stop above the best.

Silhouette of a respondent, and the mean silhouette

s(i) = \dfrac{b(i) - a(i)}{\max\{a(i),\ b(i)\}}, \qquad \bar{s} = \dfrac{1}{n} \sum_{i=1}^{n} s(i)

$a(i)$: Average Euclidean distance from respondent i to the other members of their own segmentstandard deviations
$b(i)$: Lowest average distance from i to the members of another segment (the nearest other segment)standard deviations
$s(i)$: Silhouette of respondent i, from −1 to 1
$\bar{s}$: Mean silhouette of the solution
$n$: Number of respondentsrespondents

Assumes. A respondent alone in a segment gets s = 0 by convention.

Ward’s merge cost

\Delta(A, B) = \dfrac{n_A\, n_B}{n_A + n_B}\, d^2(\mathbf{c}_A, \mathbf{c}_B)

$\Delta(A, B)$: Increase in the within-cluster sum of squares if groups A and B mergesquared standard deviations
$n_A,\ n_B$: Numbers of respondents in A and Brespondents
$d^2(\mathbf{c}_A, \mathbf{c}_B)$: Squared distance between their centressquared standard deviations

Assumes. Squared Euclidean distances on standardised variables.

5. Profiling segments

Index against the average

I_{gj} = \dfrac{\bar{x}_{gj}}{\bar{x}_{j}} \times 100

$I_{gj}$: Index of segment g on variable j: 100 = the average respondent
$\bar{x}_{gj}$: Mean of segment g on variable j (a rating, spend, a 0/1 share)units of j
$\bar{x}_{j}$: Mean of all respondents on variable junits of j

Assumes. Most meaningful for quantities with a true zero (spend, counts, shares); on rating scales, read beside the means.

Typing a new respondent (nearest centre)

z_{j} = \dfrac{x_{j} - \bar{x}_{j}}{s_{j}}, \qquad g^{*} = \arg\min_{g} \sum_{j \in Q} (z_{j} - c_{gj})^2

$x_{j}$: The new respondent’s answer to golden question jscale points
$\bar{x}_{j},\ s_{j}$: Mean and standard deviation of j in the original study (stored with the tool)scale points
$z_{j}$: The answer standardised with the study’s mean and standard deviationstandard deviations
$c_{gj}$: Centre of segment g on variable j, in standardised unitsstandard deviations
$Q$: The golden questions
$g^{*}$: The segment the respondent is assigned to: the nearest centre

Assumes. New respondents answer the golden questions in the same wording and scale as the study.

6. Evaluating segments

Margin pool of a segment

\Pi_s = n_s \times \bar{x}_s \times m

$\Pi_s$: Margin pool: the margin the segment’s whole spending would earn at the firm’s margin$ per period
$n_s$: Number of customers in segment s (or its share times the market’s size)people
$\bar{x}_s$: Spend per head in the segment over the period$ per person per period
$m$: The firm’s margin on what the segment buys, after the direct costs of making and serving it, as a share of sales

Assumes. The firm could sell the segment what it now buys at the margin m. The pool is a ceiling for comparing segments, not a sales forecast.

A segment’s size after t years of growth

n_{s,t} = n_{s,0} \times (1 + g_s)^{t}

$n_{s,t}$: Size of segment s after t years (people, spend or margin pool)as n_{s,0}
$n_{s,0}$: Size nowpeople or $
$g_s$: Growth rate a year, as a decimal (6% = 0.06)
$t$: Years aheadyears

Assumes. Growth is the same every year (compound growth, as in a CAGR).

Factor score: ratio to the best segment

s_{f,j} = 100 \times \dfrac{x_{f,j}}{\max_k x_{f,k}}

$s_{f,j}$: Score of segment j on factor f0–100
$x_{f,j}$: Segment j’s measure on factor f (share, spend, growth, room), higher is betterthe factor’s units
$\max_k x_{f,k}$: The best segment’s measure on that factorthe factor’s units

Assumes. The measure is positive and more is better. For a measure where less is better, use 100 × (smallest value ÷ x).

Needs fit

\text{fit}_j = \dfrac{\sum_i z_{j,i}\, k_i}{\sum_i |k_i|}

$\text{fit}_j$: Average match between what segment j wants and what the company is good at; 0 = no better than the average respondentz (standard deviations)
$z_{j,i}$: Segment j’s mean on need i in standardised units (its centre)z
$k_i$: The company’s mark for need i: +1 strong, 0 average, −1 weak

Assumes. The marks are the company’s honest view of its strengths; check them against customers’ ratings.

Fit score (centred on 50)

s_{\text{fit},j} = 50 + 50 \times \dfrac{\text{fit}_j}{\max_l |\text{fit}_l|}

$s_{\text{fit},j}$: Fit score of segment j0–100
$\text{fit}_j$: Segment j’s needs fitz
$\max_l |\text{fit}_l|$: The largest fit in either direction among the segments comparedz

Assumes. Fit can be negative, so the ratio to the best won’t do; 50 means no better than average.

Weighted segment score

S_j = \dfrac{\sum_f w_f\, s_{f,j}}{\sum_f w_f}

$S_j$: Weighted score of segment j0–100
$w_f$: Weight of factor f, set from the objectives before scoringrelative (any scale)
$s_{f,j}$: Segment j’s score on factor f0–100

Assumes. The factors can compensate for one another; must-haves have been screened out first.

Threshold weight: the change that ties two segments

\Delta w_f = \dfrac{T_A - T_B}{s_{f,B} - s_{f,A}}

$\Delta w_f$: Rise in factor f’s weight, others fixed, at which segment B draws level with segment Aweight units
$T_A,\ T_B$: Weighted sums Σ w s of the leader A and the challenger B, before dividing by the total weightweight × score
$s_{f,A},\ s_{f,B}$: The two segments’ scores on factor f0–100

Assumes. B scores higher than A on factor f; otherwise raising the weight never closes the gap. Only one weight changes at a time.

Room from the nearest rival

r_j = \min_{b \ne \text{own}} d(\text{ideal}_j, b)

$r_j$: Distance on the perceptual map from segment j’s ideal point to the nearest rival brandmap units
$d(\text{ideal}_j, b)$: Straight-line distance between the ideal point and brand b on the map’s two dimensionsmap units
$b$: Each brand on the map other than the company’s own

Assumes. The map’s two dimensions capture most of how customers see the brands and the ideal points, and each brand’s position rests on enough ratings. A brand’s position is everyone’s average view of it, not the segment’s own.

7. Targeting strategies

Incremental contribution of a new offer after cannibalisation

\Delta C = Q \times m_{\text{new}} - c \times Q \times m_{\text{old}} - F

$\Delta C$: Incremental contribution: what the new offer adds to the company’s total contribution$ per period
$Q$: Units (or customers) of the new offer sold in the periodunits
$m_{\text{new}}$: Contribution per unit of the new offer$ per unit
$c$: Cannibalisation rate: the share of the new offer’s sales that would otherwise have been sales of the existing offer
$m_{\text{old}}$: Contribution per unit of the existing offer that is lost$ per unit
$F$: Extra fixed costs of running the new offer in the period$ per period

Assumes. Each cannibalised unit of the new offer replaces one unit of the old one. If one new unit replaces a different number of old units, multiply by that number.

Break-even cannibalisation rate

c^{*} = \dfrac{Q \times m_{\text{new}} - F}{Q \times m_{\text{old}}}

$c^{*}$: The cannibalisation rate at which the new offer adds nothing; above it, the offer loses money for the company as a whole
$Q$: Units of the new offer soldunits
$m_{\text{new}},\ m_{\text{old}}$: Contribution per unit of the new and the existing offer$ per unit
$F$: Extra fixed costs of the new offer$ per period

Assumes. As for the incremental contribution. With no extra fixed costs, c* = m_new ÷ m_old.

8. Perceptual maps

Distance on the map between an ideal point and a brand

d = \sqrt{(x_b - x_i)^2 + (y_b - y_i)^2}

$d$: The straight-line distance on the map between the brand and the ideal pointmap units
$x_b,\ y_b$: The brand’s coordinates on Dimensions 1 and 2map units
$x_i,\ y_i$: The ideal point’s coordinates on Dimensions 1 and 2map units

Assumes. Both points are on the same map, built from the same brands and attributes. On a two-attribute map the coordinates are mean ratings and the distance is in rating points.

Share of an attribute shown on a two-dimensional map

h^2_a = \ell_{a1}^2 + \ell_{a2}^2

$h^2_a$: The communality: the share of attribute a’s variance across the brands that the two dimensions represent (0 to 1)
$\ell_{a1},\ \ell_{a2}$: Attribute a’s loadings on Dimensions 1 and 2: its correlations with the brands’ scores

Assumes. Loadings are correlations, as in a map of standardised attributes.

10. Differentiation and repositioning

How a change in one mean rating moves a brand on a fixed map

\Delta x_k = \dfrac{w_{ak}}{s_a}\,\Delta r_a

$\Delta x_k$: The change in the brand’s score on dimension kmap units
$w_{ak}$: The weight of attribute a on dimension k: its loading divided by √(the dimension’s eigenvalue)
$s_a$: The standard deviation of attribute a’s mean ratings across the brands that built the maprating points
$\Delta r_a$: The planned change in the brand’s mean rating on attribute arating points

Assumes. The axes are held fixed: the weights, means and standard deviations come from the current map, and no other brand moves.