15.8 Chapter Review
15.8.1 Chapter summary
Technical refreshers are most useful when they solve a specific project need. Across statistics, R, Excel, and Power BI, analysts should preserve the unit of analysis, distinguish rates from counts, separate inputs from calculations, validate relationships and measures, document transformations, and choose tools according to the task and handoff requirements.
15.8.2 Common mistakes
- using a statistical procedure without checking its measurement and data assumptions;
- mixing raw data, manual corrections, calculations, and presentation in one spreadsheet area;
- creating joins or Power BI relationships without validating grain and row counts;
- reporting a calculated value without an independent reasonableness check; and
- choosing software by habit when another tool would produce a clearer or more reproducible result.
15.8.4 Exercises
Exercise 1. Choose the best primary tool for each task: fitting and validating a random forest, maintaining a client-editable scenario model, delivering a monthly interactive operations dashboard, and maintaining a data dictionary.
Check Your Work
R is well suited to fitting and validating the random forest. Excel is suitable for a client-editable scenario model and can maintain a structured data dictionary. Power BI is suitable for the monthly interactive dashboard. The final choices also depend on software access, data size, security, maintenance capacity, and handoff requirements.
Exercise 2. Reproduce the facility retention table in R, Excel, and Power BI. Confirm that all three tools use the same eligible denominator and produce the same rounded rates.
Check Your Work
The expected rates are 73.48 percent for Downtown Centre, 79.93 percent for Lakeside Centre, 69.48 percent for North Ridge Centre, and 75.82 percent for Westbrook Centre. All tools should exclude censored periods from the eligible denominator and agree before rounding.
Exercise 3. In Excel, rebuild one membership-type CLV calculation with visible input cells, one row per year, survival probabilities, discounted margins, CAC, and a final total.
Check Your Work
The workbook should separate inputs from calculations. Year zero should show CAC, later rows should show survival probability, expected contribution margin, discount factor, and present value, and the final CLV should sum discounted margins and subtract CAC. Retention and discounting must use the same period, and changing an input should update every dependent value.
Exercise 4. Use the Power BI guide in powerbi/NVRW_Power_BI_Guide.md to construct the model. Validate total visits, retained memberships, and contribution margin against the CSV files before designing the report page.
Check Your Work
Validate table row counts, relationship cardinality, filter direction, distinct visit identifiers, retention eligibility, and the contribution-margin calculation before building visuals. Totals should match an independent calculation from the clean CSV files. Investigate discrepancies at the row, relationship, filter, and measure level rather than forcing one output to match another.