How to find why revenue dropped using spreadsheet data
To diagnose a revenue drop, verify the metric definition and period completeness, then decompose the change into customer or order volume, price, product mix, retention, acquisition, channel, market, and timing effects. Rank segments by contribution to total change, compare rates with denominators, and treat correlations as hypotheses until causal evidence exists.
Why the obvious method fails
When revenue falls, teams compare two totals and blame a channel or market. Partial periods, currency changes, refunds, duplicates, customer mix, and metric definitions can produce the same headline.
Start with arithmetic before narrative
If revenue fell by $240,000, segment contributions should bridge to the same movement. Any unexplained remainder is a data or definition issue, not a storytelling opportunity.
Contribution and rate answer different questions
A small product can decline 80% while explaining little. A large market can decline 4% and drive most loss. Report both percentage performance and absolute impact.
A useful mental model
- Reconcile the decline to source totals before explaining it.
- Contribution matters more than the largest percentage decline.
- Separate fewer customers, fewer purchases, lower prices, and mix shifts.
- Patterns generate hypotheses; they do not prove cause.
Revenue questions and measures
| Question | Measure | Mistake |
|---|---|---|
| Where did it decline? | Absolute contribution | Ranking only by percentage |
| What factor moved? | Volume, price, mix, retention | Using revenue alone |
| Why did it happen? | Events, experiments, causal evidence | Calling correlation causal |
From the field · Multi-region retail analyst
Explain a revenue decline across markets without blaming the first weak segment
Reported revenue fell 11%. One market changed local exchange rates, a high-value product was unavailable for five days, and the latest store file arrived incomplete. The apparent decline combines business movement with currency and data completeness effects.
Columns used in the example
order_id · order_date · market · product · units · local_price · currency · exchange_rate · customer_id
Follow it with your own file
- Confirm that both periods are complete and use the same revenue definition.
- Separate local-currency performance from the reporting-currency translation effect.
- Decompose the remaining change into units, price, product mix, new/lost customers, and timing.
- Rank contributions to the total change and label unverified explanations as hypotheses.
Check: The driver contributions plus any disclosed remainder must reconcile to the total revenue change. Re-run after the missing store file arrives and compare the explanation, not just the headline percentage.
Patterns that need context
- The latest period is incomplete.
- Revenue mixes currencies or gross and net definitions.
- A tiny segment has the biggest percentage decline.
- Over-grouping makes the output impossible to interpret.
A method you can follow
- Verify metric and comparison: Confirm gross versus net, currency, refunds, tax, recognized date, period completeness, and grain. Reconcile both totals.
- Build a change bridge: Calculate absolute and percentage movement and rank products, customers, markets, and channels by dollar contribution.
- Decompose operating drivers: Separate volume, price, mix, acquisition, retention, frequency, and refund effects with valid denominators.
- Test hypotheses: Connect verified movements to promotions, outages, price changes, seasonality, or pipeline events and state confirming evidence.
Doing these steps in Prepyr
Upload a CSV working copy in Prepyr. Describe the result you need: Find why revenue dropped by separating volume, price, mix, retention, timing, and currency effects. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Verified aggregates · Segment analysis · Charts · Evidence Based reports
What to verify before acting
- Metric fixed
- Currencies handled
- Periods comparable
- Totals reconciled
- Contributions ranked
- Denominators included
- Causal claims labeled
A concise bridge showing where the decline occurred, which components explain it, and which hypotheses deserve investigation.
Questions readers usually ask next
What is contribution to change?
It is a segment’s absolute movement divided by the overall movement, showing how much of the total it explains.
How do I separate price and volume?
Use a consistent decomposition with baseline and current price and quantity, document interaction, and reconcile components.
Can a spreadsheet prove why revenue dropped?
It narrows hypotheses. Proving cause usually needs experiments, process events, or stronger causal design.