Skip to content
All projects
Employer data, method onlyFinancial planningFinancial analysis

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
This analysis was done on employer data. Only the method is described here and no figures are published.

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

  1. 01Restructured the raw data into clean tables and built a Power Pivot model with revenue and quantity measures.
  2. 02Analyzed revenue by year and by category, and compared the categories across the three years.
  3. 03Tested the relationship between the exchange rate and revenue, and between price changes and revenue per category.
  4. 04Compared actual against forecast for the latest year and ranked the best and weakest products.
  5. 05Built a next-year forecast with Excel forecast sheets, including lower and upper confidence bounds.
  6. 06Added a scenario summary with Scenario Manager for price and volume assumptions.
  7. 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.
Chat on WhatsApp