EP-012B: Financial Reports

Parent Epic: EP-012 Analytics, Reporting & Night Audit Split Note: EP-012 has been split into sub-epics EP-012A, EP-012B, EP-012C, EP-012D, EP-012E. This file covers P&L, balance sheet, cash flow, aging receivables, channel commission, F&B cost analysis, and multi-format export.

Field	Value
Epic ID	EP-012B
Epic Name	Financial Reports
Parent Epic	EP-012 Analytics, Reporting & Night Audit
Owner	Product — Revenue Intelligence & Operations
Created	2026-03-27
Last Updated	2026-03-27
Status	Partial
Target Release	R4 (Q3 2026)

1. Business Objective

Provide hotel Finance Managers with the core financial statements they need to run the business and present to owners/investors — without requiring a separate ERP system for 80% of reporting needs. Indian hotels typically generate 3-4 financial reports per month for owner review; automating these saves 10-15 hours/month of manual Excel work. The P&L and aging receivables reports are specifically required by Indian hotels for CA-certified audits.

2. Audience

Role	Interaction
Finance Manager	Generates P&L, balance sheet, cash flow, aging receivables
Revenue Manager	Reviews revenue reports by source and segment
F&B Manager	Monitors outlet revenue, cost of goods, contribution margin
Owner / Investor	Views financial summary and channel commission breakdown
External Auditor	Downloads PDF/Excel for CA certification (read-only)

3. Scope

3.1 In Scope

Revenue report by booking source and market segment (Direct, OTA, Corporate, Walk-in)
Revenue report by room type, floor, building
Profit & Loss statement (Revenue − COGS − Operating Expenses = GOP)
Balance sheet (Assets, Liabilities, Equity) — requires chart of accounts mapping
Cash flow statement (Operating, Investing, Financing activities)
Aging receivables: amounts outstanding by company/city ledger account in buckets (current, 30d, 60d, 90d+)
Aging payables: OTA commissions, vendor bills
Channel commission report: per-OTA gross bookings, commissions, net revenue
F&B cost analysis: outlet revenue, COGS, contribution margin per menu category
Operational reports: daily arrivals/departures, in-house list, no-show, VIP list, group status (carried from EP-012)
Housekeeping productivity report (carried from EP-012)
Multi-format data export: CSV, PDF (hotel letterhead, headers, footers), Excel (.xlsx)
Report scheduling and email delivery (see EP-012D)

3.2 Out of Scope

GST GSTR-1 / GSTR-3B export (EP-012C)
Custom report builder (EP-012D)
Real-time KPI dashboard (EP-012A)
Full ERP / Tally / Zoho Books integration (EP-020)
Statutory audit / CA certification workflows

4. Current Implementation Status

4.1 Backend — `analytics-service`

Controller / Endpoint	Status	Notes
`GET /reports/revenue`	Built	Revenue by source, segment, room type; pivot table
`GET /reports/arrivals`	Built	Daily arrivals report with VIP highlight
`GET /reports/departures`	Built	Daily departures report
`GET /reports/in-house`	Built	In-house guest list
`GET /reports/no-shows`	Built	No-show report with charge amount
`GET /reports/housekeeping`	Built	Attendant productivity, maintenance summary
`GET /reports/fnb`	Built	Outlet revenue, menu item analysis
`GET /reports/pnl`	Partial	Revenue mapped; expense CoA mapping not wired
`GET /reports/balance-sheet`	Not Started	Requires CoA and ledger mapping
`GET /reports/cash-flow`	Not Started	Requires manual expense input or Tally integration
`GET /reports/aging-receivables`	Built	City ledger aging buckets
`GET /reports/channel-commissions`	Not Started	OTA commission parsing not implemented
`GET /reports/fnb-cost`	Partial	Revenue built; COGS requires inventory integration
`POST /reports/export`	Partial	CSV export works; PDF formatting has layout issues

4.2 Dashboard — `02-hmp-web-dashboard`

Component	Status	Notes
Revenue Reports page	Built	Pivot table, charts, date range picker
P&L Statement page	Partial	Revenue rows complete; expense rows placeholder
Arrivals / Departures pages	Built	Phase 5 deliverable
Aging Receivables page	Built	Shows city ledger aging with download
Channel Commission page	Not Started	-
F&B Cost Analysis page	Partial	Revenue chart built; COGS chart missing
Export button (CSV/PDF/Excel)	Partial	CSV works; PDF has formatting issues

5. Gap Analysis

#	Gap	Impact	Effort	Priority
1	P&L expense side requires chart of accounts mapping	High	M	P1
2	Balance sheet and cash flow need manual expense input or Tally	High	L	P1
3	PDF export formatting — missing headers, footers, hotel logo	Medium	S	P2
4	Channel commission report not built (OTA commission % table)	Medium	M	P1
5	F&B COGS requires inventory integration (not yet built)	Medium	L	P2

6. User Stories

Story ID	Title	Priority	Points	Sprint	Status
US-012-02	Daily Operational Reports	P0	8	1	Built
US-012-03	Revenue Reports by Source & Segment	P0	8	1	Built
US-012-09	Financial P&L Statement	P1	13	4	Partial
US-012-10	Balance Sheet & Cash Flow Reports	P1	13	4	Not Started
US-012-11	Aging Receivables Report	P1	5	3	Built
US-012-12	Housekeeping Productivity Report	P1	5	2	Built
US-012-13	F&B Revenue & Menu Analysis	P1	8	3	Built
US-012-15	Data Export (CSV, PDF, Excel)	P1	8	3	Partial
US-012B-01	Channel Commission Report	P1	8	4	Not Started
US-012B-02	F&B Cost Analysis (COGS + Contribution Margin)	P2	8	5	Partial
US-012B-03	PDF Export Formatting (Hotel Letterhead)	P2	5	4	Partial

7. Detailed User Stories

US-012-03: Revenue Reports by Source & Segment

As a Revenue Manager, I want to analyze room revenue broken down by booking source and market segment, So that I can optimize distribution strategy and identify high-value channels.

Priority: P0 | Points: 8 | Sprint: 1 | Status: Built

Implementation Status

Revenue reports are fully built with pivot capability. Revenue sliced by: booking source (Direct, MakeMyTrip, Goibibo, Booking.com, Walk-in, Corporate, Travel Agent), market segment (Transient, Corporate, Group, Government, Crew), room type, and time period. Bar, line, and pie chart visualizations included.

Acceptance Criteria

Scenario: Revenue by booking source for current month
  Given today is 2026-03-27
  When I generate "Revenue by Source" for March 2026
  Then the report shows revenue contribution per source with percentages
  And includes a comparison to the same month last year (STLY)
  And I can export the data as CSV or PDF

Scenario: Corporate segment drill-down
  When I select "Corporate" segment
  Then the report shows revenue by individual corporate account
  And highlights the top-3 corporate accounts by total spend

US-012-09: Financial P&L Statement

As a Finance Manager, I want to generate a monthly Profit & Loss statement that maps all hotel revenues and operating expenses to standard accounting categories, So that I can present accurate financial performance to the owner and board.

Priority: P1 | Points: 13 | Sprint: 4 | Status: Partial

Implementation Status

Revenue side is mapped from folio charges and POS transactions. Expense side requires a chart of accounts (CoA) configuration. Currently expenses are placeholder values. CoA mapping is a required dependency.

Acceptance Criteria

Scenario: Generate March 2026 P&L
  Given the hotel has CoA mapped with 12 expense categories
  When I generate P&L for March 2026
  Then the report shows:
    | Category            | Amount (INR)  |
    | Room Revenue        | 48,75,000     |
    | F&B Revenue         | 12,30,000     |
    | Ancillary Revenue   | 2,45,000      |
    | Total Revenue       | 63,50,000     |
    | Room Expenses       | 8,25,000      |
    | F&B COGS            | 4,92,000      |
    | Payroll             | 12,00,000     |
    | Utilities           | 3,50,000      |
    | Total Expenses      | 35,40,000     |
    | GOP                 | 28,10,000     |
    | GOP %               | 44.3%         |
  And the report can be exported as PDF with hotel letterhead

Scenario: CoA not configured
  Given no chart of accounts is configured for the property
  When I try to generate P&L
  Then the system shows: "Please configure your Chart of Accounts under Settings → Finance before generating P&L"

US-012B-01: Channel Commission Report

As a Revenue Manager, I want to see the net revenue after OTA commissions for each booking channel, So that I understand the true profitability of each distribution channel and can optimize the channel mix.

Priority: P1 | Points: 8 | Sprint: 4 | Status: Not Started

Implementation Notes

GET /reports/channel-commissions?from=&to=
Commission rates configured per OTA in channel management (EP-009): MakeMyTrip 12%, Booking.com 15%, Goibibo 12%, Expedia 15%
Report: gross revenue, commission %, commission amount, net revenue, ADR, RevPAR per channel
Comparison across date ranges (current month vs. same month last year)

Acceptance Criteria

Scenario: Channel commission report for Q1
  Given OTA commission rates are configured (MakeMyTrip: 12%, Booking.com: 15%)
  When I generate channel commission report for Q1 2026
  Then the report shows per channel:
    | Channel       | Gross Revenue | Commission % | Commission INR | Net Revenue |
    | MakeMyTrip    | 18,50,000     | 12%          | 2,22,000       | 16,28,000   |
    | Booking.com   | 14,25,000     | 15%          | 2,13,750       | 12,11,250   |
    | Direct        | 16,00,000     | 0%           | 0              | 16,00,000   |
  And a "best channel by net revenue" summary is shown

Scenario: Export as Excel
  When I click "Export Excel"
  Then an .xlsx file downloads with all channel data and a summary row

US-012-15: Data Export (CSV, PDF, Excel)

As a Finance Manager, I want to export any report in CSV, PDF, or Excel format, So that I can share reports with the owner, CA, or import data into accounting software.

Priority: P1 | Points: 8 | Sprint: 3 | Status: Partial

Implementation Status

CSV export is functional. PDF export has layout issues (missing headers/footers, no hotel letterhead). Excel export not yet implemented.

Acceptance Criteria

Scenario: Export revenue report as PDF
  Given the revenue report for March 2026 is displayed
  When I click "Export PDF"
  Then a PDF generates with:
    - Hotel name and logo in header
    - Report title, date range, and "Generated by HMP on 2026-03-27" in footer
    - Properly formatted INR amounts with comma separators
    - Page numbers
  And the PDF downloads within 10 seconds

Scenario: Export as Excel for Tally import
  When I click "Export Excel"
  Then an .xlsx file downloads with:
    - First row: column headers
    - Data rows with raw INR values (no formatting)
    - A "Summary" tab with totals

8. Dependencies

Dependency	Type	Epic / Service	Status
Folio / billing data	Upstream	EP-005B / frontdesk	Available
POS transaction data	Upstream	EP-007 / pos	Available
City ledger (aging receivables)	Upstream	EP-005B / frontdesk	Available
Channel management (OTA commission %)	Upstream	EP-009 / channel	Partial
Inventory (F&B COGS)	Upstream	EP-007 / pos inventory	Partial
Auth / RBAC (Finance Manager role)	Upstream	EP-001 / auth	Available
Night audit (business date, daily totals)	Peer	EP-005C / frontdesk	Available

9. Acceptance Criteria (Sub-Epic Level)

AC-EP-012B-01: Revenue reports slice correctly by source, segment, room type, and date range with accurate INR totals.
AC-EP-012B-02: P&L statement maps to a configurable chart of accounts and shows GOP% for the period.
AC-EP-012B-03: Aging receivables report shows city ledger balances in correct aging buckets (current, 30d, 60d, 90d+).
AC-EP-012B-04: Channel commission report shows gross and net revenue per OTA with commission deducted.
AC-EP-012B-05: PDF export includes hotel letterhead, report title, date, page numbers, and properly formatted INR amounts.
AC-EP-012B-06: Excel export generates a valid .xlsx file with a summary tab importable into Tally/Zoho Books.
AC-EP-012B-07: All reports respect RBAC — Finance Manager sees financial reports; Housekeeping Manager sees only housekeeping reports.

10. Technical Notes

Analytics controller: 01-hmp-backend/apps/analytics-service/src/reports/reports.controller.ts
Finance controller: 01-hmp-backend/apps/analytics-service/src/finance/
PDF generation: analytics-service uses pdfmake or puppeteer for PDF; formatting fix needed in template
Excel export: implement using exceljs library (already in package.json as dependency? verify)
Chart of accounts: PropertyConfig in property-service already has a chartOfAccounts JSON field (Sprint 5 TenantConfig deliverable); analytics-service needs to read this via inter-service call
Revenue report source data: joins reservations + folio_charges + folio_payments tables in analytics DB schema

1. Business Objective​

2. Audience​

3. Scope​

3.1 In Scope​

3.2 Out of Scope​

4. Current Implementation Status​

4.1 Backend — analytics-service​

4.2 Dashboard — 02-hmp-web-dashboard​

5. Gap Analysis​

6. User Stories​

7. Detailed User Stories​

US-012-03: Revenue Reports by Source & Segment​

Implementation Status​

Acceptance Criteria​

US-012-09: Financial P&L Statement​

Implementation Status​

Acceptance Criteria​

US-012B-01: Channel Commission Report​

Implementation Notes​

Acceptance Criteria​

US-012-15: Data Export (CSV, PDF, Excel)​

Implementation Status​

Acceptance Criteria​

8. Dependencies​

9. Acceptance Criteria (Sub-Epic Level)​

10. Technical Notes​

1. Business Objective

2. Audience

3. Scope

3.1 In Scope

3.2 Out of Scope

4. Current Implementation Status

4.1 Backend — `analytics-service`

4.2 Dashboard — `02-hmp-web-dashboard`

5. Gap Analysis

6. User Stories

7. Detailed User Stories

US-012-03: Revenue Reports by Source & Segment

Implementation Status

Acceptance Criteria

US-012-09: Financial P&L Statement

Implementation Status

Acceptance Criteria

US-012B-01: Channel Commission Report

Implementation Notes

Acceptance Criteria

US-012-15: Data Export (CSV, PDF, Excel)

Implementation Status

Acceptance Criteria

8. Dependencies

9. Acceptance Criteria (Sub-Epic Level)

10. Technical Notes