Tutorial: Creating Automated PSAK-Standard Financial Reports for Clinics & Hospitals

Monthly book closing in many clinics and hospitals still ends in Excel: three days of reconciliation, cash discrepancies of Rp2–5 million, and reports only reaching the directors on the 25th. Yet PSAK demands consistent presentation—PSAK 1 for report structure, PSAK 72 for revenue recognition, PSAK 71 for impairment of BPJS receivables, PSAK 73 for leases. The root cause is not the standards, but operational transaction data from SIMRS, pharmacy POS, and ERP never being stitched into accounting journals. This article dissects how to build automated financial reports that comply with PSAK, from chart of accounts to CALK printed as PDF. Example stack: PostgreSQL 16, Python 3.12, SQLAlchemy 2.0, and openpyxl 3.1.

Foundation: Chart of Accounts and Double-Entry Aligned with PSAK

COA is not an arbitrary list of accounts. Start with the PSAK 1 structure: current assets, non-current assets, short-term liabilities, long-term liabilities, equity, revenue, and expenses. Use stable 5-digit codes, for example 1-1000 Cash, 1-1100 Patient Receivables, 1-1110 BPJS Receivables, 1-1200 Drug Inventory, 1-2000 Fixed Assets, 1-2010 Accumulated Depreciation, 2-1000 Supplier Payables, 3-1000 Capital, 4-1000 Outpatient Revenue, 4-1100 Inpatient Revenue, and 5-1000 Salary Expense. Store metadata per account: normal balance (debit/credit), report type (balance sheet or income statement), and CALK group.

The ledger must be double-entry with constraints at the database level. Create tables journal_entries (id, entry_date, period_id, ref, source, status, unique idempotency_key) and journal_lines (entry_id, account_code, debit, credit, cost_center). Use NUMERIC(19,4) for monetary values, not FLOAT. Install a trigger that rejects entries if SUM(debit) != SUM(credit) or zero. That way, posting errors are detected before touching reports.

PSAK mapping must be explicit in the COA. Revenue is recognized when services are rendered (PSAK 72), not when cash is received—important for BPJS claims that are settled in 30–60 days. BPJS receivables are subject to expected credit loss (PSAK 71) based on aging and forward-looking. Drug inventory uses FIFO (PSAK 14), fixed assets are depreciated straight-line (PSAK 16), and clinic space leases are recognized as right-of-use assets (PSAK 73). Each account is tagged with PSAK so that CALK can be generated automatically.

Finally, create an accounting_periods table with status open or closed. A trigger rejects insert/update of journals in a closed period, except via approved correction journals. This is what makes the audit trail tidy and prevents staff from “patching” last month's numbers.

Automating Journal Posting from SIMRS, POS, and ERP

The principle is event-driven: each billing, payment, purchase, payroll, and depreciation sends an event to a queue (PostgreSQL LISTEN/NOTIFY or Redis Stream). A worker reads the event and maps it to a journal template in the posting_rules(source, event_type, debit_account, credit_account, amount_field) table. No journals are typed manually for routine transactions.

A real example: an outpatient invoice of Rp750,000 with a 70% BPJS claim results in Debit BPJS Receivables Rp525,000, Debit Patient Receivables Rp225,000, Credit Outpatient Revenue Rp750,000. At the pharmacy POS, a sale of Rp120,000 results in Debit Cash Rp120,000, Credit Revenue Rp120,000, as well as Debit COGS Rp85,000 and Credit Drug Inventory Rp85,000. For a volume of 1,200 transactions per day (36,000 per month), manual posting is clearly unrealistic.

from decimal import Decimal
from sqlalchemy import text

def post_journal(conn, entry_id, lines):
    total_d = sum(Decimal(str(l['debit'])) for l in lines)
    total_k = sum(Decimal(str(l['credit'])) for l in lines)
    if total_d != total_k or total_d == 0:
        raise ValueError(f'Jurnal tidak balance: D={total_d} K={total_k}')
    for l in lines:
        conn.execute(text('INSERT INTO journal_lines(entry_id, account_code, debit, credit, cost_center) '
                          'VALUES (:eid, :acc, :d, :k, :cc)'),
                     dict(eid=entry_id, acc=l['account'], d=l['debit'], k=l['credit'], cc=l.get('cc')))
    return entry_id

Add a unique idempotency_key per event so that retries do not duplicate journals. Run a daily reconciliation job: total cashier deposits vs bank mutations, total BPJS claims vs receivables generated. Discrepancies go into an exception queue and must be closed before the period is locked. Target posting SLA is under 5 minutes—down from previously 3 working days.

Generate Balance Sheet, Income Statement, Cash Flow, and CALK

Start from a trial balance view: aggregation of opening balances plus debit-credit movements per account per period. From this view, the balance sheet is calculated from cumulative balances of asset, liability, and equity accounts; the income statement from movements of revenue and expense accounts; cash flow using the indirect method, i.e., net profit plus depreciation and adjustments for changes in working capital. Example: net profit Rp180 million, depreciation Rp45 million, BPJS receivables increase Rp60 million, then operating cash flow is Rp165 million.

Render reports using Jinja2 3.1 for templates and WeasyPrint 62 for PDF, while openpyxl 3.1.3 produces Excel with check cells. Before publishing, run three automatic assertions: total assets = liabilities + equity; period income statement = change in equity; cash flow = change in cash and bank balances. If any fails, the report is in draft status and an alert is sent to Slack or WhatsApp.

Do not retype CALK every month. Store accounting policies as templates, then inject numbers from queries: BPJS receivables aging per bucket (0–30, 31–60, 61–90, >90 days), ECL allowance, fixed asset movements, and revenue details per service unit. For audits, provide an appendix containing a list of journals (general ledger) that can be filtered by account and by transaction source.

Proven practice: a single clinic entity with 3 branches requires 6–10 weeks of implementation; multi-entity with consolidation takes 3–4 months. The key is not expensive tools, but master data discipline and written posting rules.

Best Practices

1. Make the ledger append-only: corrections via reversing journals, not UPDATE or DELETE.
2. Lock accounting periods and apply tiered approval before reopening.
3. Store idempotency_key in every posting to prevent duplicate journals.
4. Automated daily reconciliation for cashiers, banks, and BPJS claims; differences must enter an exception queue.
5. Test with 12 months of data and accountant review before go-live, not after reports are published.

FAQ

Is it enough to use Excel and pivot tables?

For volumes below 50 transactions per day it is still possible, but not audit-ready. Excel has no double-entry constraint, is prone to overwriting, and is difficult to track who changed numbers. Use a transactional database from the start.

How do you calculate ECL for BPJS receivables according to PSAK 71?

Use aging buckets, determine the probability of default per bucket based on collection history, then multiply by outstanding. Review quarterly and adjust for forward-looking conditions such as changes in tariff regulations or delays in disbursement.

What is the recommended minimal stack?

PostgreSQL 16 as the ledger, Python 3.12 + FastAPI 0.111 for API and worker, SQLAlchemy 2.0 for ORM, plus openpyxl and WeasyPrint for output. If your team uses PHP, Laravel 11 + MySQL 8 is also sufficient as long as constraints and triggers are still installed.

How long until reports can be issued automatically?

For one entity with a SIMRS/POS that already has an API, 6–10 weeks is enough: 2 weeks for COA design and posting rules, 3 weeks integration, 2 weeks UAT, the rest parallel run with manual reports.

Building automated financial statements compliant with PSAK standards is not merely an accounting project, but the integration of operational data, accounting rules, and internal controls. If your clinic or hospital still closes the books with Excel and manual reconciliation, start with COA, double-entry constraints, and one automated posting flow first. Need assistance with architecture design, SIMRS/POS integration to the ledger, or a PSAK 71/72/73 compliance review? Consult your needs with the Nugroho Setiawan team to get a realistic and measurable roadmap.

Related solutions by PT Pilar Inovasi Informatika (Pilar Inovasi IT products): PilarPOS SIMRS (Hospital Information System) BPJS & SatuSehat Integration. Need a free consultation?