GF585 Unit 7 Seven Input EBITDA Sensitivity Table Example

Steepness Times Reach: A Unit 7 Sensitivity Table for a Commercial Linen Service

[Student Name]

Purdue University Global

GF585: Strategic Financial Planning and Forecasting Analytics

Unit 7 Sensitivity Table

[Instructor Name]

[Date]

The linen service, its inputs, historical ranges and results are composite and illustrative; the articles and book cited are real.

Two Questions Kept Apart

A sensitivity table often merges two questions. The first is how steeply EBITDA responds when an input moves. The second is how far that input actually moves in a normal year. An input can be steep but tightly held, or shallow but volatile. This table answers each question in its own column and multiplies them only at the end (Pannell, 1997).

How Each Column Was Built

Elasticity. Each input was raised 1 percent with every other input at its base value, and the percent change in EBITDA was recorded. Signs are dropped in the ranking because the question is size.

Plausible move. The size of a normal year's move comes from five years of the firm's own records. Contract terms bound prices, the posted wage scale bounds pay, utility bills show the range of gas prices and route sheets show how far hotel pounds have varied.

EBITDA at risk. The product of the two, expressed in dollars, is the amount of profit a normal adverse move in that input would remove.

The Table

1. Hotel pounds per account. Elasticity 1.77. Plausible move 7 percent, from route sheets across five summers. EBITDA at risk $339,600, or 12.4 percent of the base.

2. Plant hours per thousand pounds. Elasticity 2.12. Plausible move 4 percent, from production logs. EBITDA at risk $232,800.

3. Hourly wage. Elasticity 2.12. Plausible move 3 percent, the largest single step in the wage scale in five years. EBITDA at risk $174,600.

4. Linen replacement rate. Elasticity 0.69. Plausible move one point of revenue, from 11 to 12 percent, seen in a year of heavy loss at two hospitals. EBITDA at risk $171,600.

5. Gas price. Elasticity 0.16. Plausible move 35 percent, from utility bills. EBITDA at risk $155,400.

6. April hotel renewal price. Elasticity 3.03. Plausible move 1.5 percent, the widest gap between asked and agreed renewals. EBITDA at risk $124,800.

7. Healthcare price. Elasticity 1.69. Plausible move 1 percent, since escalator clauses fix most of it. EBITDA at risk $46,300.

Gas is the most volatile input in the business and still ranks only fifth, because the plant spends about $444,000 a year on it out of $14.4 million in costs; volatility on a small base removes less profit than a modest move in a large driver.

Why the Steepest Input Ranks Sixth

The April hotel renewal price has the highest elasticity in the table. A 1 percent change adds almost 3 percent to EBITDA because the extra revenue carries no extra pounds, labor or gas. Yet it ranks sixth, because renewal contracts hold the price inside a narrow band. In five years the gap between the price asked and the price agreed has never exceeded 1.5 percent. Steepness without reach produces a modest risk. A tornado chart drawn from elasticities alone would have placed price at the top and sent management after the wrong figure (Eschenbach, 1992).

Why Hotel Pounds Rank First

Hotel pounds per account combine moderate steepness with real reach. When occupancy falls, every hotel sends fewer sheets and towels, and the plant cannot shed its fixed and route costs in the same week. A 7 percent fall, which happened in one of the last five summers, removes about $340,000. The sensitivity also explains a finding from the scenario work: the downside's biggest single driver is the one that management watches least closely, because no contract governs it.

A Two-Way Table for Inputs That Interact

Hotel pounds and plant hours interact on the busiest days, when a full plant runs more slowly per pound. The second table therefore crosses the two.

1. Hotel pounds 7 percent lower: EBITDA of $2.18 million if plant hours rise 4 percent, $2.41 million at base hours and $2.63 million if hours fall 4 percent.

2. Hotel pounds at base: $2.51 million, $2.74 million and $2.98 million.

3. Hotel pounds 7 percent higher: $2.84 million, $3.08 million and $3.33 million.

The worst corner, $2.18 million, is about $560,000 below the base. The best corner, $3.33 million, is about $585,000 above it. Neither corner is a forecast; both show the range when the two most important inputs move together.

Limits of the Table

Each elasticity is measured at the base and holds only for modest moves. Larger moves, such as a hotel volume collapse that triggers route cuts, change the cost structure and need scenarios rather than elasticities. The table also treats inputs one at a time, so it understates risk when inputs move together, which the scenario set addresses. Methods that vary all inputs at once over their ranges would give a fuller picture, at the cost of a simulation the planning team does not yet run (Saltelli et al., 2008).

The Figure to Watch

Management should track hotel pounds per account every month from route sheets, beside occupancy reports, rather than watching the gas bill.

References

Eschenbach, T. G. (1992). Spiderplots versus tornado diagrams for sensitivity analysis. Interfaces, 22(6), 40-46. https://doi.org/10.1287/inte.22.6.40

Pannell, D. J. (1997). Sensitivity analysis of normative economic models: Theoretical framework and practical strategies. Agricultural Economics, 16(2), 139-152. https://doi.org/10.1016/S0169-5150(96)01217-0

Saltelli, A., Ratto, M., Andres, T., Campolongo, F., Cariboni, J., Gatelli, D., Saisana, M., & Tarantola, S. (2008). Global sensitivity analysis: The primer. Wiley.