All projects
Revenue Analysis and Forecast for an Industrial Gases Business
Three years of revenue transactions analyzed by category and product, linked to exchange rate movements, compared with forecast and extended into a next-year forecast with scenarios.
2023ExcelPower PivotForecast sheetsScenario ManagerDashboards
Revenue forecastIllustration
Actual and forecast (index)
ActualForecast with range
Scenarios
Downside92
Base100
Upside109
Price effect vs volume effect
1Problem
Revenue transactions were complete but gave management no insight: which categories drive growth, how much of the growth is price and how much is volume, and whether the forecast was realistic.
2Data
- Monthly revenue transactions for three actual years plus one forecast year: category, product, quantity, unit price and revenue.
- Monthly USD to EGP exchange rates collected from public sources.
- This is employer data. The method is described here and no figures are published.
3Approach
- 01Restructured the raw data into clean tables and built a Power Pivot model with revenue and quantity measures.
- 02Analyzed revenue by year and by category, and compared the categories across the three years.
- 03Tested the relationship between the exchange rate and revenue, and between price changes and revenue per category.
- 04Compared actual against forecast for the latest year and ranked the best and weakest products.
- 05Built a next-year forecast with Excel forecast sheets, including lower and upper confidence bounds.
- 06Added a scenario summary with Scenario Manager for price and volume assumptions.
- 07Designed three dashboards: financial, and two comparison views.
4Result
- Growth separated into price effect and volume effect for each category.
- A forecast with a confidence range instead of a single number.
- Management could see which products to push and which to review.
