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
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
- 01Cleaned each raw sheet with formulas: removed total rows, aligned columns and built a unique code per account and partner.
- 02Built one database that brings the three fiscal years side by side.
- 03Created a mapping table that assigns about 45 accounts to 15 statement lines.
- 04Produced the income statement in EGP millions with EBITDA, EBIT, EBT and net income.
- 05Added variance analysis between years and KPI ratios: gross margin, operating margin, profit margin, direct cost % and opex %.
- 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.
