Casket Inventory Discount and a Broken Reconciliation
Hillcrest sells 96 burial caskets a year out of 120 calls. Its average casket costs $1,150 wholesale and retails for $2,995. Average inventory is 24 units, carried at cost. The controller applies an 18% annual carrying rate covering capital, storage, obsolescence, and insurance. A supplier offers an 8% volume discount if Hillcrest orders in 12-unit lots, which would raise average inventory to 30 units. Separately, the monthly preneed trust reconciliation spreadsheet totals column D with =SUM(D2:D109), but the trust holds 110 contracts, which occupy rows 2 through 111. The report still 'balances' because a $7,300 correcting entry was posted to the wrong contract.
1. Compute casket inventory turnover, gross margin per unit and as a percentage, and annual carrying cost at the current policy.
Read the worked answer
Turnover.
- Annual cost of goods sold = 96 x $1,150 = $110,400
- Average inventory at cost = 24 x $1,150 = $27,600
- Turns = $110,400 / $27,600 = 4.0 times per year (about 91 days of supply)
Gross margin per unit.
- $2,995 - $1,150 = $1,845 per casket
- Margin percentage = $1,845 / $2,995 = 61.6%
- Annual gross margin = 96 x $1,845 = $177,120
Carrying cost. $27,600 x 18% = $4,968 per year
Reading the numbers. Four turns on a high-margin, non-perishable-but-style-sensitive product is reasonable but not tight; the 61.6% margin means the dominant risk is not carrying cost, it is failing to sell the units already owned and being left with a dated finish or interior.
2. Should Hillcrest accept the 8% discount? Quantify the trade-off and note the non-financial factors.
Read the worked answer
New unit cost. $1,150 x 0.92 = $1,058 (a saving of $92 per casket).
Compare total cost of ownership, not just purchase price.
| Current | Discount | |
|---|---|---|
| Annual purchases (96 units) | $110,400 | $101,568 |
| Average inventory at cost | $27,600 | $31,740 |
| Carrying cost (18%) | $4,968 | $5,713.20 |
| Total | $115,368 | $107,281.20 |
Net annual benefit = $8,086.80, driven by $8,832 in purchase savings less $745.20 in added carrying cost.
If retail prices are held, gross margin rises to $2,995 - $1,058 = $1,937 per unit, or $185,952 per year - an $8,832 gain. Turns fall from 4.0 to $101,568 / $31,740 = 3.2, and an extra $4,140 of cash is tied up in stock.
Non-financial factors before signing:
- Obsolescence. Cremation is rising; 30 units of a style that goes out of favor is a write-down, not a saving. Confirm the 12-unit lots can be mixed by model rather than forcing one style.
- Storage and condition. Do you have dry, protected space for six more units? Damaged inventory erases the margin.
- Supplier reliability and lead time. The discount is only worth having if delivery stays dependable.
- Cash position. $4,140 of additional working capital at the firm's borrowing rate should be checked against the $8,087 benefit.
- Pricing ethics and consistency. If the discount is passed to families, ensure the General Price List and casket price list are updated consistently; if it is not passed along, be prepared to justify that internally.
Recommendation: accept, conditional on a mixed-model lot and confirmed storage.
3. Diagnose the spreadsheet error and describe the controls that would have caught it.
Read the worked answer
The error. The formula =SUM(D2:D109) omits row 111, so the last contract's $7,300 balance never enters the total. This is an omission error hidden by a compensating error - a correcting entry posted to the wrong contract makes the grand total agree to the custodian's statement, so the report appears to reconcile while two individual contract balances are wrong. The total is right by coincidence; the underlying records are not.
Why it survived. A hard-coded range that no one re-checked when the contract count grew from 109 to 110, plus a reconciliation performed only at the total level.
Controls to implement:
- Reconcile at the contract level, not the total. Match each contract to the custodian statement; a per-contract variance report makes compensating errors impossible to hide.
- Eliminate hard-coded ranges. Use an Excel Table (structured reference) or SUM over a full column with a separate control total, so added rows are captured automatically.
- Add integrity checks on the sheet: contract count via COUNTA compared to the custodian's contract count; a check cell that flags any difference between the sum of individual balances and the control total; conditional formatting on variances over $0.
- Lock formulas and use change control - one designated preparer, one independent reviewer who signs off monthly.
- Reconcile to an external source (trustee/custodian statement) every month, and require documentation for every correcting entry, including who approved it.
- Test after every structural change: add a dummy contract, confirm the total moves, then delete it.
- Retain the reconciliation and reviewer sign-off for the period required by the state preneed statute and for the firm's records-retention policy.