Excel Workbook for Controlling Operations
Budget: €30 – €250 EUR
a mid‑sized German company, specialized in design, production and distribution of care beds and medical furniture throughout Europe, annual revenue around 60M euro
Objective
Construct a self‑supporting Excel workbook that simulates monthly Controlling operations: actual vs. budget reporting, KPI dashboards, forecast scenarios, variance analytics, and documented refresh process. No PowerPoint or presentation slides are required.
Deliverables — Excel Workbook Requirements
Tab Name Contents / Functionality
01 RawData Import-ready sample transaction data Jan–Dec 2024 in table format (~650 rows): columns Date (YYYY‑MM), CostCenter, GLAccount, Type (Actual/Budget), Amount (€). Simulate realistic data: monthly revenues around €1.5–2.0 m per cost centre, ±5–8% variance between actual and budget.
02 Assumptions Dropdown selector: Scenario (Worst/Base/Best). Assumption blocks per scenario for key drivers (e.g. revenue growth %, cost inflation %, DSO, DPO, margin targets). Name ranges for each assumption.
03 Model Dynamic, single‑spreadsheet projection of P&L, cash/cash‑flow, Working Capital (WC) days for all three scenarios. Use table-structured formulas for scalability.
04 Variance Month‑by‑month comparison of Actual vs Base forecast. Compute Variance (€ and %) and decompose into Price, Volume, Personnel drivers. Designed for seamless waterfall chart use.
05 Pivots (hidden) One pivot for Actual vs Forecast trend; one pivot for Cost Center drill-down and Price × Volume analysis. Connect both to slicers. Keep hidden to prevent unintended changes.
06 Dashboard (UI‑only) Executive dashboard with KPI cards, slicers (by Month, Cost Center, Scenario), trend charts (Revenue, Contribution Margin, WC Days, Liquidity Ratio), Mini‑in‑cell visuals (sparklines or bullet charts), and conditional formatting (traffic lights). Entire layout fits on one screen.
07 ProcessNotes One‑page process map and instructions: how Power Query → refresh raw data, how to switch scenario, how to interpret dashboards, and stakeholder handoff (e.g. who in finance/COO/CEO reviews what). Ensures the workbook can be taken over long-term.
Sample Data Format (as example)
yaml
Copy
Edit
Date | CostCenter | GLAccount | Type | Amount
2024‑01 | Sales Germany | 4000 Sales Rev | Actual | 1 532 000
2024‑01 | Sales Germany | 4000 Sales Rev | Budget | 1 600 000
2024‑01 | Service | 5300 Freight | Actual | 78 200
...
– Include 6 cost centers: Sales Germany, Sales UK/EU, Service & Delivery, Logistics, Admin, Warranty Repair.
– 12 months × 2 types (Actual/Budget) × approx. 6 CC × 5 aggregated GLAccount groups = ≈720 rows.
Timeline & Payment
Draft Submission: Preliminary workbook (24 hours)
Final Delivery: Completed workbook including hidden Pivot layer and cleanup (48 hours)
Required Skills
Advanced Microsoft Excel (Power Query imports, Data Model, Table‑structured formulas, PivotTables, slicers, advanced charting)
Scenario logic (CHOOSE() or Excel Scenario Manager; scenario-switch dropdown)
Variance analysis mastery (waterfall charts, price/volume decomposition)
Familiarity with financial KPIs: Liquidity ratio, Working Capital days, Contribution margin, Forecast accuracy
Clean, business‑ready dashboard design (10+ years standard: clarity, legibility, scroll-free layout)
Objective
Construct a self‑supporting Excel workbook that simulates monthly Controlling operations: actual vs. budget reporting, KPI dashboards, forecast scenarios, variance analytics, and documented refresh process. No PowerPoint or presentation slides are required.
Deliverables — Excel Workbook Requirements
Tab Name Contents / Functionality
01 RawData Import-ready sample transaction data Jan–Dec 2024 in table format (~650 rows): columns Date (YYYY‑MM), CostCenter, GLAccount, Type (Actual/Budget), Amount (€). Simulate realistic data: monthly revenues around €1.5–2.0 m per cost centre, ±5–8% variance between actual and budget.
02 Assumptions Dropdown selector: Scenario (Worst/Base/Best). Assumption blocks per scenario for key drivers (e.g. revenue growth %, cost inflation %, DSO, DPO, margin targets). Name ranges for each assumption.
03 Model Dynamic, single‑spreadsheet projection of P&L, cash/cash‑flow, Working Capital (WC) days for all three scenarios. Use table-structured formulas for scalability.
04 Variance Month‑by‑month comparison of Actual vs Base forecast. Compute Variance (€ and %) and decompose into Price, Volume, Personnel drivers. Designed for seamless waterfall chart use.
05 Pivots (hidden) One pivot for Actual vs Forecast trend; one pivot for Cost Center drill-down and Price × Volume analysis. Connect both to slicers. Keep hidden to prevent unintended changes.
06 Dashboard (UI‑only) Executive dashboard with KPI cards, slicers (by Month, Cost Center, Scenario), trend charts (Revenue, Contribution Margin, WC Days, Liquidity Ratio), Mini‑in‑cell visuals (sparklines or bullet charts), and conditional formatting (traffic lights). Entire layout fits on one screen.
07 ProcessNotes One‑page process map and instructions: how Power Query → refresh raw data, how to switch scenario, how to interpret dashboards, and stakeholder handoff (e.g. who in finance/COO/CEO reviews what). Ensures the workbook can be taken over long-term.
Sample Data Format (as example)
yaml
Copy
Edit
Date | CostCenter | GLAccount | Type | Amount
2024‑01 | Sales Germany | 4000 Sales Rev | Actual | 1 532 000
2024‑01 | Sales Germany | 4000 Sales Rev | Budget | 1 600 000
2024‑01 | Service | 5300 Freight | Actual | 78 200
...
– Include 6 cost centers: Sales Germany, Sales UK/EU, Service & Delivery, Logistics, Admin, Warranty Repair.
– 12 months × 2 types (Actual/Budget) × approx. 6 CC × 5 aggregated GLAccount groups = ≈720 rows.
Timeline & Payment
Draft Submission: Preliminary workbook (24 hours)
Final Delivery: Completed workbook including hidden Pivot layer and cleanup (48 hours)
Required Skills
Advanced Microsoft Excel (Power Query imports, Data Model, Table‑structured formulas, PivotTables, slicers, advanced charting)
Scenario logic (CHOOSE() or Excel Scenario Manager; scenario-switch dropdown)
Variance analysis mastery (waterfall charts, price/volume decomposition)
Familiarity with financial KPIs: Liquidity ratio, Working Capital days, Contribution margin, Forecast accuracy
Clean, business‑ready dashboard design (10+ years standard: clarity, legibility, scroll-free layout)