I would employ a multi-layered ethnographic and technical archaeology approach combining shadowing, reverse-engineering, and facilitated knowledge extraction sessions. This involves pairing the retiring expert with a shadowing BA who uses the Socratic method to uncover decision trees while simultaneously deploying process mining tools on the Excel files to reconstruct logic flows. The methodology prioritizes preserving tacit knowledge through relationship building rather than documentation demands, using the limited time to map critical path dependencies and revenue-impact logic first.
At a mid-sized logistics firm, the entire freight pricing algorithm lived inside a labyrinthine Excel workbook with 47 interlinked tabs, VBA macros, and hidden pivot tables. Maria, the only employee who understood the conditional pricing rules for different shipping lanes, was retiring in 18 days and had stonewalled previous documentation attempts because she feared being replaced before her pension vested.
Problem description:
The workbook calculated $40M in annual freight quotes, but contained no version control, no comments, and circular references that crashed if cells were edited in the wrong order. Management needed to migrate this to a Salesforce CPQ system within 90 days to support expansion, but every discovery session with Maria ended in vague generalizations. Meanwhile, sales reps were beginning to panic as they noticed quote errors creeping in when Maria took sick days, revealing the system's fragility.
Solution A: Forced Documentation Sprint
We considered mandating Maria complete a standardized template checklist of all formulas and business rules under threat of early termination. This approach promised the fastest theoretical path to written records and would create an immediately auditable paper trail for compliance purposes. However, this strategy carried significant risks to knowledge quality given the adversarial dynamics.
Solution B: Parallel System Build
We evaluated hiring external consultants to reverse-engineer the Excel logic using Python parsing scripts and historical quote data while ignoring Maria entirely. This removed dependency on the unwilling participant and utilized modern technical solutions that could potentially discover optimizations. Unfortunately, this method failed to account for the qualitative reasoning behind pricing exceptions.
Solution C: Embedded Ethnography with Structured Output
We proposed assigning a senior BA to shadow Maria for 4 hours daily as a "knowledge partner" rather than auditor, using screen recording and conversational inquiry. Simultaneously, a technical analyst would extract formula relationships using Power Query and VBA decompilers to reconstruct the logic scaffold. This approach required careful framing to avoid appearing as surveillance but promised comprehensive capture of both explicit and tacit knowledge.
Chosen solution and rationale:
We selected Solution C after securing a private meeting with Maria where we guaranteed her full pension benefits and a three-month post-retirement consulting contract at premium rates in exchange for cooperation. This aligned her incentives with project success. The "knowledge partner" framing positioned the BA as her legacy protector rather than replacement, unlocking candid explanations of why certain carriers received unofficial discounts that were historical relationship credits not in any database.
Result:
Within 16 days, the team mapped 89% of the critical pricing logic, including a hidden IF statement that applied a 3% buffer for fuel volatility that would have cost the company $1.2M annually if omitted in the new system. Maria departed feeling respected, and the Salesforce CPQ implementation launched with only a two-week delay. The three-month consulting retainer proved critical when the system encountered an unexpected seasonal pricing scenario during go-live that only Maria recognized.
Question 1: How do you distinguish between a business rule that must be preserved exactly and a technical artifact that exists only because of Excel's limitations?
Answer: Candidates often treat every formula and macro as a sacred business requirement, failing to recognize that Excel workarounds frequently represent technical debt rather than business intent. The key is to ask "what would you do if Excel allowed you to..." questions to separate the constraint from the objective. For example, a complex nested IF statement might actually represent a tiered discount schedule that should be a lookup table in a proper database, not a multi-layered formula. Always trace back to the business policy document or regulatory source—if the rule isn't written there, it's likely an implementation workaround. You must validate with stakeholders whether the specific calculation sequence matters or just the mathematical outcome.
Question 2: What specific techniques prevent a subject matter expert from feeling interrogated during knowledge extraction sessions?
Answer: The critical error is using "why" questions which trigger defensive justifications; instead use "help me understand how this protects the business" framing which positions the expert as a guardian. Implement the Five Whys technique indirectly through storytelling: "Walk me through what happened during the Q4 rush when the west coast ports were backed up." This narrative approach triggers memory through context rather than abstract recall. Additionally, never record the first session—use it purely for relationship building. Share your notes at the end of each day asking "Did I miss anything important to you?" This gives them editorial control and psychological safety.
Question 3: How do you validate the accuracy of reconstructed business logic when the original system has no audit trail and the expert has left the organization?
Answer: You cannot rely on code comparison since Excel and Salesforce handle floating-point arithmetic differently. Instead, construct a parallel run period using historical data replay: extract 500 random historical quotes from the past year and run them through the new system, then analyze variance. Any deviation greater than 0.01% indicates a logic gap. Create a "tribal knowledge register" documenting every assumption made during reconstruction, tagged with confidence levels. During UAT, specifically test the edge cases marked Low confidence with extreme input values.