Across the company, the key functions involved in a real estate development project were convinced that the financial model they had been working from for the past four years was correct. An examination of the model by the new director showed that, in fact, they were headed for a liquidity crisis. The team's proactive approach preserved liquidity and strengthened cross-functional alignment, establishing a standard of excellence that elevated the visibility of FP&A.

The AFP FP&A Case Study series is designed to help you build up key FP&A capabilities and skills by sharing examples of how leading practitioners have tackled challenges in their work and the lessons learned.

Insight: It is critical to validate financial models by examining the structure, logic, calculations, inputs and outputs.

Background

A landmark real estate project in this real estate development company’s portfolio was taking shape after four years. About 80% of the apartments had been sold, and momentum for the project was strong.

At the same time, a new Director of Investment and Business Planning (IBP) had joined the team from a sister company. He was tasked with shaping investment strategy, developing business models, and ensuring that financial planning translated into clear, actionable decisions.

What he inherited was a financial model stakeholders had grown comfortable with. They believed it was correct. Unfortunately, they were wrong. The presenter of this case study is the Director of Investment and Business Planning (IBP).

The Challenge

Upon moving into the role, the new Director immediately had a critical decision to make: validate the existing financial model or build a new one from scratch. Given the time constraints the IBP team was under, and understanding that the stakeholders were already familiar with the existing model, he opted to continue with the current model.

As the Director delved into the model, he discovered an inconsistency in its output, in which 20% of inventory was expected to generate 45% of revenues; that implied an unrealistic price uplift on a product with similar specs, construction cost and launch timeline.

From a technical standpoint, everything appeared to be functioning correctly; however, when he looked more closely, the Director discovered a serious flaw in the collections tab: The payment plan never changed, regardless of the product type or delivery date. This led to a systematic exaggeration of cash collections across the entire project, and ultimately, a liquidity gap.

How did it happen? Simple human error. The formula in the collections tab had been fixed to both rows and columns, rather than just columns. The consequences from this small error were significant. On paper, the project looked like it was performing well — in reality, there was a critical finance gap that would have occurred in a year.

The Approach

The Director approached the fix in two phases. The first was technical: validating the business model. The second required a different set of skills as he had to then win over the hearts and minds of the stakeholders who believed wholeheartedly that the model, and project inputs, calculations and output were correct.

Phase 1: Validate the Financial Model

There were five steps to this phase:

1. Check the physical structure of the model. The Director reviewed the documentation and created a flow chart to validate the integrity of the input, output and calculations across the sections. Everything appeared to be functioning correctly.
2. Examine the system’s relationships and information flow. This step included looking at the formulas and decision rules that connected the inputs (e.g., costs, revenues and assumptions) to outputs (e.g., financial projections, ratios or valuations).
3. Check the integrity of the inputs. This step is performed to ensure the assumptions, variables and parameters are accurate, complete and validated. What the Director found was unrealistic revenue projections in the assumptions. In response, he initiated a full repopulation of the financial model assumptions from raw data, involving all key functions to reevaluate the validity of the assumptions.
4. Analyze the calculations. This is when the Director found the critical flaw. When testing the collections tab, he discovered that the formula had been fixed to both rows and columns rather than just the columns, leading to overstated cash collections. They corrected this error and validated the model again.
5. Ensure the integrity of the output. In this step, the results are tested to ensure they are reliable, auditable and aligned with the model’s purpose. The output should be explainable relative to history, benchmarks and common sense.

Phase 2: Shifting the Mindset

The technical phase might be considered the easy part, as the Director now had to convince all the stakeholders who had been running this model for four years, and believing it to be 100% correct, that, in fact, there was a critical error that had to be addressed. He met this challenge in three steps:

1. Framing the issue. Issuing blame was not the way to win people over. The Director knew he had to frame the issue in a diplomatic way, so he decided to frame it as risk exposure. All key functions needed to understand that if the model was not corrected, there was a high probability of a liquidity crisis occurring within a year.
2. Get buy-in from the company’s key functions. The Director initiated a full repopulation of the financial model’s assumptions from raw data, and then brought all key functions together in one room, including the commercial department, technical planning, technical construction, sales team and development team. They worked together to reevaluate the validity of the assumptions, including selling prices, technical rollout, cost rollout, collection curves and development curves. He made sure everyone understood how unrealistic the revenue projections were, and that they were able to acknowledge the errors in the model and assumptions.
3. Present the solution and action plan. The Director presented the CFO with three tactical options and one core solution. The core solution involved negotiating extended payment terms with contractors while the other options included freezing sales on unsold inventory, raising external debt and accelerating collections from current buyers.

With approval from the CFO, the IBP team implemented the core solution, which included arranging short-term interest-bearing facilities, rolling out targeted cash collection offers and aligning cash flow timing with all units. The entire operation was framed as a “proactive optimization” to an existing project.

The Outcomes

The outcomes were significant. The project's liquidity gap was closed within six months. All contractual obligations were met on time, and no distress signals were flagged to the market, thereby maintaining the company's credibility and avoiding any rumors of a liquidity crisis.

The experience also underscored the importance of communication and collaborative problem-solving. By fostering an open dialogue and encouraging cross-functional engagement, they were able to remedy the model’s deficiencies and strengthen trust among stakeholders. It also served as a powerful illustration of how, when addressing complex financial challenges, you need both technical expertise and the ability to effectively manage team dynamics.

For FP&A, it became a model for their ability to drive insights and ensure financial stability. As a result, FP&A’s role was elevated in the company, and their input is now regularly requested when evaluating strategic and investment decisions.

In order to prevent future issues, the following model controls were implemented:

• Validate and stress test all aspects of an inherited financial model. Never accept a financial model at face value; this is an opportunity to check and improve the company’s work.
• Develop a comprehensive policy and procedure manual. The manual includes detailed diagrams and visuals to guide users in the proper operation of the financial model. It was designed to be so clear that even a new hire could understand the model with minimal questions.
• Institutionalize peer review and business partnering with key stakeholders for ongoing validation and improvement. This includes review of inputs, logic and outputs.
• Validate all data. This was applied to all input fields to ensure that only valid data could be entered.
• Lock and protect cells. Cells that shouldn’t be modified were locked and protected to prevent accidental changes.
• Embed error checks. Error checks and conditional formatting were embedded across all sheets to highlight any discrepancies or issues immediately.
• Establish interrelationships within the model. These were established to verify that the outputs and calculations were correct. Stress testing is an effective way to explore the cause-and-effect relationship.
• Regularly update models. Conduct periodic reviews to ensure alignment with reality; convenient opportunities include when new “actuals” are fed into the model, or during a set period such as annual planning.

Rethinking Surcharging: Compliance and Customer Trust in Today's Payment Landscape

Read More

8/1/2025