Actuarial studies · 2025

Medical Cost Modelling

Compare the economics behind different care settings.

The same specialty can look very different financially under a different procedure mix or schedule. This application explores those differences using procedure-cost references and an operator’s assumptions about billing, volume and costs. Scenarios can be compared in the app and exported for further work in Excel.

Status
Shipped
Domain
Health economics · Scenario modelling
Role
Model design, application
Year
2025
Medical Cost Modelling · Interactive concept

Explore the workflow with illustrative content.

Stage 03 / 03

Review the difference

The foreground markers show where the selected scenario separates from the reference. Use the layer controls to isolate each part.

Illustrative content · no live data

See which assumptions change the picture

Stack

  • Python
  • SQLite
  • Excel interchange
  • JavaScript
  • Scenario engine

A scenario application for comparing procedure revenue, costs and margins across day-hospital and private-hospital settings.

01

Keep the baseline visible

Reference data and entered assumptions are stored separately.

A scenario can be compared with the reference case or another entered case, making it clear which inputs came from the source dataset and which reflect a proposed operating plan.

02

Model the mix of work

The application supports several specialty contexts together and aggregates them into a practice-level view.

Volume, scheduling, fixed costs and variable costs can be considered in the same comparison, so a change in one procedure mix can be assessed across the wider operation.

03

Find the assumptions that matter

Scenario comparison helps reveal when one care setting becomes more attractive under the chosen inputs.

Reviewers can explore how utilisation, billing or cost changes affect the result and identify which assumptions need closer investigation.

04

Take the analysis into the next conversation

Saved scenarios and versioned spreadsheet exports support finance reviews beyond the application.

The exported snapshot preserves the context of the comparison, giving analysts a basis for further modelling and discussion.

Pipeline

Compare operating scenarios

Stage Can reject

  1. 01

    Reference data

    Market anchor

  2. 02

    Operator inputs

    Billing · volume · mix

  3. 03

    Cost base

    Fixed + variable

  4. 04

    Scenario engine

    Per-procedure margin

  5. 05

    Compare

    Scenario differences

    Billing, volume and cost sensitivity

  6. 06

    Export

    Versioned workbook

The mathematics

A closer look
at the method.

The ideas behind the analysis, with the main terms explained. These are method summaries; fitted parameters and project-specific calculations remain private.

01

Two ways to estimate monthly activity

A scenario can begin with a case volume or with the hours available for a procedure. Both inputs are converted to a monthly activity estimate for comparison.

volume q = v·k · φ hours q = H·ψ t · 4.33 · φ
q
Procedures per month
v, k
Volume per period and its period multiplier
H
Hours per week available
ψ
Share of the week allocated to the procedure
t
Hours per procedure
φ
Work share for the modality

Under the hours-based approach, a shorter procedure increases modelled capacity when the other assumptions stay fixed. Actual activity also depends on utilisation, scheduling and demand, so capacity is only one part of the operating scenario.

02

An illustrative contribution comparison

This simplified comparison applies billing and per-procedure costs to a monthly volume. It makes the effect of those inputs visible before the wider operating scenario is considered.

Π = ( b − c ) · q c = Σj cj supplies, devices, pharmaceuticals, theatre time, gases, personnel Δ = Πday − Πprivate
Π
Monthly contribution in this simplified comparison
b
Billing per procedure
c
Cost per procedure
cj
Cost category j
Δ
Difference between the two scenario contributions

The sign of Δ depends on the inputs. Wider scenarios also need fixed costs, utilisation and work mix; the expression here alone does not establish a break-even volume or a stable ranking between settings.

Behind the work

The skills
that came together.

Financial analysis

  • Procedure-level revenue and cost modelling
  • Fixed and variable cost analysis
  • Volume and work-mix sensitivity

Application development

  • Persisted scenario state
  • Interactive comparisons
  • Versioned spreadsheet exports

Project highlights

  • Side-by-side scenarios

    Compare a plan with reference data or another case

  • A practice-level view

    Several specialties combined in one analysis

  • Useful beyond the app

    Saved scenarios and spreadsheet snapshots