Skip to content
All projects
Public data setFinancial analysis

Income Statement Analysis from Raw Data (FY16 to FY18)

Three years of raw P&L account balances turned into a multi-year income statement, variance and common-size analysis, and an interactive dashboard.

Jul 2026ExcelFormulasVBADashboards
Open the live dashboard
3
fiscal years
15
statement lines
26.3m
FY18 revenues, EGP
78%
FY18 gross margin
Income Statement Analysis from Raw Data (FY16 to FY18)Real data
EGP m22.5FY16826FY170.526.3FY187.5
This project uses a public or training data set, so the numbers on this page are the real results of the analysis.

1Problem

Three fiscal years of P&L data existed only as raw account balances per partner company, each year in a different layout. There was no consistent structure, so management could not see how revenues, costs and margins moved from year to year.

2Data

  • Three raw sheets (FY16, FY17, FY18) with P&L account, partner company, amount and account number.
  • Around 115 account and partner combinations after cleaning.
  • Revenue stored as negative values and costs as positive, as exported from the accounting system.

3Approach

  1. 01Cleaned each raw sheet with formulas: removed total rows, aligned columns and built a unique code per account and partner.
  2. 02Built one database that brings the three fiscal years side by side.
  3. 03Created a mapping table that assigns about 45 accounts to 15 statement lines.
  4. 04Produced the income statement in EGP millions with EBITDA, EBIT, EBT and net income.
  5. 05Added variance analysis between years and KPI ratios: gross margin, operating margin, profit margin, direct cost % and opex %.
  6. 06Designed the dashboard with a VBA button that switches the fiscal year, plus waterfall and hierarchy charts.

4Key insights

  • Total revenues grew 15.8% in FY17 to EGP 26.0m, then stayed flat in FY18 at EGP 26.3m (+1.0%).
  • Gross margin held between 78% and 80% in all three years, so the swings in profit did not come from direct costs.
  • FY17 turned into a net loss of EGP 6.1m: personnel expenses almost doubled to EGP 12.2m and capitalized costs fell from EGP 4.4m to EGP 0.7m.
  • EBITDA recovered from EGP 0.5m in FY17 to EGP 7.5m in FY18, close to the FY16 level of EGP 8.0m.
  • FY18 net income was only EGP 0.8m (3.0% margin) because extraordinary items rose to EGP 2.3m.

5Result

  • One structured income statement that reconciles back to the raw accounts.
  • Growth and margin movements visible at a glance for each fiscal year.
  • A reusable model: new periods flow through the mapping table with no rework.
Chat on WhatsApp