The problem
A handful of extreme values can drag a mean far away from what is typical. The median barely moves. Winsorizing picks a low and a high percentile, caps every value at those two cutoffs, then recomputes.
In the Compustat data you will use this week, some firms have tiny revenue and much larger costs, so their gross margins are wildly negative. Health Care is the clearest case. On the filtered HW 4 rows (about 89,020), its average margin is about -2.84, while its median is about 0.45. Capping at the 1st and 99th percentiles, computed across all firms, lifts Health Care's average to about -0.86. It is a big move, but the average stays negative, because about one Health Care firm-year in four has a negative margin. Winsorizing limits the damage. It does not replace reporting the median.
Live demo: set the cutoffs yourself
Type a lower and an upper percentile, or pick a preset. Then watch the mean and the median. Start with the room, then try one real sector.
Forty people are in a room: 28 students, 6 staff, 5 executives and Jeff Bezos. Net worth is in US dollars. Student loans make some of it negative. This data is illustrative. Jeff Bezos is shown at $200 billion (illustrative, rounded).
Whole numbers from 0 to 100. 0 means no floor. 100 means no cap.
Cap moves each value outside the cutoffs to the nearest cutoff and keeps every row. Homework 4 does this. Remove drops those rows.
Each person's net worth, sorted from lowest to highest
Real Compustat data: every Materials firm-year in the Homework 4 rows, 6,193 of them. Gross margin is (sale - cogs) / sale. A margin of 0.40 means the firm keeps 40 cents of each sales dollar after the cost of goods sold.
The floor and cap here are computed within Materials only. Homework 4 computes them on the whole gross_margin column, all sectors together, so its cutoffs are different. This tab uses Materials rather than Health Care. Health Care's left tail is so long that even its 5th-percentile floor sits near -15, far off this chart.
Whole numbers from 0 to 100. 0 means no floor. 100 means no cap.
Cap moves each value outside the cutoffs to the nearest cutoff and keeps every row. Homework 4 does this. Remove drops those rows.
How many Materials firm-years fall in each gross margin range
Drill 1: the cutoff
The 99th-percentile margin is the value below which 99% of the margins fall. If your margins are the range
named margins, write the formula that returns the 99th percentile.
PERCENTILEIFS, and you do not need one here.
99th-percentile cap:
=PERCENTILE.INC(margins, 0.99)
1st-percentile floor: use 0.01 in place of 0.99,
=PERCENTILE.INC(margins, 0.01)Drill 2: the cap
The 1st-percentile floor is in $P$1 and the 99th-percentile cap is in $P$2. Write the
formula that caps the margin in B2 at both tails (no lower than the floor, no higher
than the cap).
=MAX(MIN(B2, $P$2), $P$1)
MIN pulls anything above the cap down to it. MAX lifts anything below the floor up to it.
One catch.
MIN and MAX ignore text in a referenced cell, so if B2 holds
the "" from the sale guard, this returns the cap. The HW 4 filters remove those rows. On other data,
guard it:
=IF(B2="","",MAX(MIN(B2,$P$2),$P$1))