Budget Import & Dashboard Comparison

Single-snapshot budget storage and per-line P&L comparison powered by the BP Excel workbook. Built around BudgetImporter, BudgetComparisonService, and a Filament Budget page that lives under the Dashboard in the admin nav.

What it does

• Imports the BP Excel (For-PL sheet) into the budget_entries table, one row per category/subcategory × month × year × region.
• Keeps a single snapshot. Re-uploading replaces the previous data and the previous file inside one transaction. There is no version history.
• Persists the original file on the project’s filesystem disk (configurable, defaults to local in dev and s3 in production) so it can be re-downloaded.
• Compares actuals vs budget on the Revenue dashboard: KPI cards show variance, the Revenue chart overlays the monthly budget as a second series, and the P&L Waterfall gains Budget FY and Var % columns.

Data model

budget_entries

Column	Type	Notes
period_year	unsignedSmallInteger	Fiscal year (e.g. 2026)
period_month	unsignedTinyInteger	1–12; never null (monthly granularity only for v1)
region	string(8)	ESP, USA, ALL, etc.
section	string(32)	revenue, cogs, personnel, operating, other, da, financial, tax
category	string(128)	CC label from the Excel (e.g. Airline, Marketing Performance)
subcategory	string(128) (nullable)	SubCC label (e.g. Paid Media) when present
cost_center	string(64) (nullable)	Parent CC name on SubCC rows
amount_eur	decimal(18, 2)	Signed — revenue positive, costs negative
source_row	unsignedInteger	For audit traceability back to the xlsx
source_sheet	string(32)	Always For-PL today

Indexes: (period_year, period_month) and section.

budget_meta

Single-row table holding metadata for the current snapshot:

Column	Type	Notes
fiscal_year	int?	Year with the highest revenue magnitude in the import
source_filename	string?	Client-side filename (BP2026_V1__Volare.xlsx)
source_path	string?	Relative path on the storage disk
source_disk	string(32)?	Disk used at import time (local, s3, …)
imported_at	datetime?	When the import ran
imported_by_id	foreignId? → users	Operator who triggered the import

BudgetMeta::current() returns the latest row.

Excel layout assumptions (For-PL sheet)

The importer reads BudgetImporter::TYPE_TO_SECTION and YEAR_FIRST_COLUMN_INDEX:

Cell / range	Meaning
I6	Region label — Spain → ESP, USA → USA, anything else → ALL
Col F (row 11→)	Type code: R (revenue), C (cogs), P (personnel), O (operating), OR (other), DA, FR, TX, GP/GM (derived — skipped)
Col G (row 11→)	Level: Total (skipped — recomputed), CC, SubCC
Col H (row 11→)	Line item label
Monthly cells	12 consecutive columns per year, starting at: Q for 2024, AC for 2025, AO for 2026, BA for 2027, BM for 2028, BY for 2029

Cached formula values are read via getOldCalculatedValue() to avoid PhpSpreadsheet’s calculation engine, which trips on the workbook’s named ranges.

Services

App\Services\Budget\BudgetImporter

Public surface:

public function import(
    string $filePath,
    ?int $userId = null,
    ?string $originalFilename = null,
    ?string $storedPath = null,
    ?string $storedDisk = null,
): array;
// returns ['rows_imported' => int, 'fiscal_year' => int|null]

Behaviour:

• Wraps everything in a DB::transaction().
• truncates budget_entries, bulk-inserts the new entries with created_at/updated_at set.
• truncates budget_meta and writes a fresh row with the storage path + disk.
• Skips Total rows and derived rows (GP/GM) — totals are recomputed from CC/SubCC sums in the dashboard so we never double-count.

App\Services\Budget\BudgetComparisonService

Aggregates the snapshot for dashboard widgets:

public function getFiscalYearSummary(?string $market = null): ?array;
public function getMonthlyRevenue(int $year, ?string $market = null): ?array;

• Returns null when no snapshot exists.
• Returns null when the dashboard’s market filter selects a region (UK, CA, DE, …) the budget does not yet cover — currently the budget only contains data for ESP.
• getFiscalYearSummary() returns signed totals per section plus derived gross_profit, gross_margin_pct, adjusted_ebitda, ebitda, ebitda_margin_pct, and by_category.
• getMonthlyRevenue() returns [1 => €, …, 12 => €] for the requested year (always 12 keys).

Filament integration

App\Filament\Pages\Budget (/admin/budget)

• Top-level nav item directly under Dashboard.
• Header shows the current snapshot meta (filename, fiscal year, uploaded date/user).
• Upload Excel action — modal with a single FileUpload (preserveFilenames), confirms “overwrite the existing budget” before running the importer. On success the previous file is deleted; on failure the just-uploaded file is removed so nothing orphans.
• Download original action — visible only when a stored file exists. Streams the file back via Storage::disk($meta->source_disk)->download().
• Year selector (#[Url(as: 'year')] selectedYear) — defaults to BudgetMeta::current()->fiscal_year, switches the monthly tables between any imported year.
• KPI stats widget (BudgetKpiStatsWidget): Revenue / Gross Profit / EBITDA for the fiscal year.
• Per-section tables: Revenue, COGS, Personnel costs, Operating costs, Other results, D&A, Financial result, Tax — each with Jan–Dec columns and a year-total column.

Dashboard comparison

App\Filament\Pages\RevenueDashboard uses HasFiltersAction (modal + Apply button, not live). Defaults endDate to end-of-year on mount so a fresh visit shows the whole fiscal year.

Widget	Budget enrichment
RevenueKpiStatsWidget	Each stat shows vs Budget FY{year} with achievement % / pp delta, green/red color and trend icon
RevenueByMarketChart (now monthly)	Two bar series per month: actual revenue + budget for the same months in the selected window
PnlWaterfallWidget (Filament TableWidget)	Extra columns Budget FY{year} and Var %, coloured by direction (positive = good for revenue/margin, bad for costs)
UnitEconomicsWidget (Filament TableWidget)	Average per-booking economics are scoped to the same status/date/market window as the P&L (no longer DB-wide)

When the dashboard filter selects a market outside the budget’s region (anything other than ES/all-markets today), the budget series are hidden and the variance columns show —.

Storage configuration

config/budget.php:

return [
    'disk' => env('BUDGET_DISK', env('FILESYSTEM_DISK', 'local')),
    'directory' => env('BUDGET_DIRECTORY', 'budget-imports'),
];

• Local: file lives at storage/app/private/budget-imports/{original-name}.xlsx.
• Production: set BUDGET_DISK=s3 (or FILESYSTEM_DISK=s3) — the same code path uploads to S3 and the Download action serves it back via the s3 disk.

Per-snapshot the disk used is persisted on budget_meta.source_disk, so historic snapshots still resolve even if the default disk later changes.

Importing a budget

1. Navigate to Budget in the admin sidebar.
2. Click Upload Excel, pick the BP{year}_V*__Volare.xlsx file, confirm the overwrite.
3. The page reloads with the new snapshot and the Revenue dashboard immediately shows the variance columns against the new fiscal year.

A successful import returns { rows_imported, fiscal_year }. Failures surface as a persistent danger notification with the exception message.

Tests

• tests/Feature/Services/Budget/BudgetImporterTest.php — round-trips the real tests/Fixtures/BP2026_V1__Volare.xlsx, asserts month-level totals (Revenue ESP = €6.6M, Airline = -€2.97M, Marketing Performance › Paid Media = -€340k), checks idempotent re-import, and that a missing file throws RuntimeException.
• tests/Feature/Services/Budget/BudgetComparisonServiceTest.php — seeds a minimal snapshot and exercises FY summary, monthly revenue, market scoping, and the no-budget fallback.