# reserving-mack-1: Reserving (12 points)

Original practice question in the SOA ASTAM graded-workbook format. Not an SOA question.

## Check figures
- f_0 = 1.760
- Total reserve = 27,800 (nearest 100)

## Rubric
- (a) 2 points: correct column sums over the accident years that have both columns observed (1); all seven factors correct (1). Using an average of individual factors instead of the volume-weighted ratio loses 1.
- (b) 3 points: correct projection to development year 7 (1); reserves as ultimate minus latest diagonal (1); total (1). Carry-through credit if (a) is wrong but used consistently.
- (c) 2 points: sigma_j^2 with divisor n-j-2 and the individual factors weighted by C(i,j) (1); the extrapolated sigma_6^2 (1). Dividing by n-j-1 loses 1.
- (d) 3 points: process term 1/C-hat(i,k) and estimation term 1/S_k both present (1 each); square root and percentage (1). Using S_k over all accident years instead of the contributing ones loses 1.
- (e) 2 points: BF reserve = ELR x premium x (1 - 1/product of factors) (2). Using the chain-ladder ultimate instead of the a priori ultimate scores 0.

## Excel refresher for this question
- Column sums with SUM over a range that stops at the last accident year with both columns observed; do not include the diagonal cell in the denominator.
- SUMPRODUCT(weights, (ratios - f)^2) computes the weighted sum of squared deviations in one cell; the array arithmetic inside SUMPRODUCT needs no Ctrl+Shift+Enter.
- Build the projected square to the right of the triangle with one formula per cell (previous cell x factor) and fill right; keep the observed cells as references (=N15), not values.
- PRODUCT(range) for the cumulative development factor in the BF formula.
- Put every requested number in its yellow Answer cell as a formula (=cell), never a typed value.

## Time budget
- 12 points at 3 minutes per point = 36 minutes. Stop at the time limit and grade yourself.
# Excel refresher for the ASTAM workbook question

For someone who has not opened Excel in years. Everything the graded question needs, nothing else. Practise each item once on a question workbook before the timed attempts.

## The environment you will get
- One workbook, one tab per question, opened on the Prometric machine. No Solver, no macros, no add-ins. Goal Seek is there.
- Yellow "Answer" cells are what graders read. Work anywhere to the right of or below the tables, never insert rows or columns inside a table.
- Type your Candidate ID into the Candidate ID cell first.
- Put formulas in the Answer cells (=G40), not typed numbers. A live formula earns method marks even when a number upstream is wrong.

## Ten operations to be fluent in
1. Fill down and fill right: select the cell with the formula, drag the small square at the bottom-right corner, or double-click it to fill down a whole column next to data.
2. Absolute references: press F4 while editing a reference to cycle $A$1, A$1, $A1. Anchor parameters (lambda, theta, f_j) with $ before filling.
3. SUM(range), SUMPRODUCT(range1, range2), SUMSQ(range), AVERAGE, COUNT. SUMPRODUCT also accepts array arithmetic inside: SUMPRODUCT(w, (x - m)^2) with no special keystroke.
4. LN, EXP, SQRT, power with ^. GAMMALN(x) for log-factorials and non-integer gamma functions.
5. Distribution functions (type the full name, Excel autocompletes): NORM.S.DIST(z, TRUE), NORM.S.INV(p), POISSON.DIST(k, mean, cumulative), BINOM.DIST, NEGBINOM.DIST(k, r, p, cumulative) with p = 1/(1+beta), GAMMA.DIST(x, alpha, theta, cumulative) where Excel's beta is the scale theta, CHISQ.DIST.RT(x, df), CHISQ.INV.RT(p, df), F.DIST.RT, LOGNORM.DIST.
6. Goal Seek: Data tab, What-If Analysis, Goal Seek. "Set cell" = the cell holding the equation (a derivative, or a difference from a target), "To value" = 0 (or the target), "By changing cell" = the parameter cell. Start the parameter at a sensible value. If it fails, change the starting value and retry.
7. PRODUCT(range) for cumulative development factors; MIN, MAX; ABS.
8. Name your work: type a label in the cell to the left of every block ("f_j", "sigma_j^2", "log-likelihood"). Graders award method marks for legible work.
9. Number formats: right-click, Format Cells, Number, and set decimals; never round with ROUND() inside a chain of calculations, only in the Answer cell if asked.
10. Checking: every question has a "You should find that ..." figure. Match it before building the next part on top.

## Keyboard shortcuts worth the five minutes
- Ctrl+Enter: keep the cursor in the cell after entering.
- Ctrl+Shift+Down / Right: select to the end of a data block.
- Ctrl+` (backtick): show all formulas on the sheet, for a final audit before upload.
- F2: edit the active cell; Escape: cancel.
- F9: recalculate (relevant only if calculation was set to manual; it should be automatic).

## Time discipline
- 3 minutes per point. A 12-point question is 36 minutes, all in.
- If Goal Seek will not converge in two tries, type a reasonable value, label it "assumed", and continue; later parts still earn credit from a consistent value.
- Leave two minutes to fill the Candidate ID, check every Answer cell holds a formula, and save.

## How the trainer works
- Each question folder has `-question.xlsx` (work in a copy), `-solution.xlsx` (every Answer cell is a live formula; study the layout), `-rubric.md` (points, what earns credit, common errors, the Excel functions this question needs), and `-key.json` (numbers with tolerances).
- Grade yourself: compare your Answer cells with the solution, award points by the rubric, log every lost mark in `../error-log.md`.