SME Risk and Insurance Survey Panel

Nine annual waves of a Singapore SME risk-and-insurance survey (2015 to 2024, about 450 respondents each) reconciled into a single 4,050-row panel, then used to measure the gap between the risks SMEs say they worry about and the risks they actually insure.

Stack: R (dplyr, tidyr, purrr, ggplot2), logistic, linear-probability and Poisson models.

The hard part of this project was not the modelling. It was discovering that the same question does not mean the same thing across waves.

Stack the nine waves naively and the employee-size variable S5 comes back with nine distinct codes:

2  3  4  101  102  103  104  117  118

That looks like one variable with nine size bands. It is three different code spaces stacked on top of each other:

Code 101 appears in two of those rows and means something different in each: "31 staff or fewer" in 2018 to 2022, "10 or fewer" in 2023 to 2024. Revenue is worse: 17 apparent bands across the decade, three real ones. Industry moved column (s6 to S4) and code range (1 to 15 became 1 to 19) at the same time.

None of this raises an error. bind_rows() succeeds, the panel looks complete, and every downstream trend quietly compares incomparable groups. A "small firms are increasingly underinsured" finding means nothing if "small" silently switched from 20 staff or fewer to 10 or fewer halfway through the series.

R/01_harmonise.R maps all three code spaces onto stable bands before anything is measured. That mapping is the real deliverable; the charts come after it.

The study data belongs to the insurer that commissioned it and is not redistributed here. 00_make_synthetic_waves.R writes nine synthetic waves with the same schema and the same defects, so the pipeline is exercised for real:

Rscript R/00_make_synthetic_waves.R      # nine .xlsx waves into data/waves/
Rscript R/01_harmonise.R                 # 4,050 x 108 panel, with diagnostics
Rscript R/02_trends_and_insurance_gap.R  # 13 charts into outputs/
Rscript R/03_regression_models.R         # 9 model summaries into outputs/regression/

Or make all.

Defects the generator reproduces on purpose, because each one is something 01_harmonise.R has to survive:

The synthetic waves also carry a planted latent trait, so the models return non-degenerate coefficients instead of separating. Every number produced by a clean clone of this repo is therefore synthetic. The repo demonstrates the method, not the client's findings.

A few design choices:

• Availability is printed, not assumed. 01_harmonise.R ends by reporting per-wave non-NA rates for every question block, so a block that exists in only four of nine waves is visible before it reaches a model.
• Reverse-scored attitude index. Three of the six C3 attitude items are negatively worded; they are reversed before averaging, otherwise the index measures nothing.
• Logit and LPM side by side. The logistic model is the primary specification; the linear-probability model is reported next to it because coefficients on a probability scale are what a non-technical reader can act on.

Not included, and not obtainable from this repo. The waves are a commercial insurer's commissioned SME research, provided for coursework. No microdata, questionnaire or client report is reproduced here, only analysis code written against that schema and a synthetic generator that matches it.

Originally the group project for NTU BR2211 Financial and Risk Analytics. The harmonisation, trend and regression code here is my own. Three changes were made when preparing it for publication, all documented in the source: local paths and client identifiers were removed, the wave-file lookup was loosened to accept any filename containing the year, and two wave-specific fix-ups were guarded with existence checks so the scripts run against either the raw or the harmonised wave layout. A missing comma in the trends script, which stopped it from running at all, was also fixed.

Herui Dou, MSc Business Analytics, NTU. LinkedIn