Building apps that generate complicated reports means digging deep into business goals and mathematical models

Our real estate client needed a way to get complex reporting functionality into his web application. The application would help real estate property managers and Homeowner Associations (HOAs) plan their reserve funding over a 30-year period to cover planned and incidental expenses associated with real estate maintenance. 

Two previous vendors failed and hindsight being 20/20, he knew what he needed in this third and final effort: a development team who could formulate the report calculations including defining interdependencies and setting up predictive models.  

The application would take user data inputs and produce funding projection reports. Reports would include expenses like landscaping, plumbing, roofing, pest control, fencing, stucco, and pavement. Myriad variables would be modeled including interest rates, inflation, and monthly fees per unit. For example, a user could back into a stepped increase in monthly fees necessary to plan for a complete roof replacement of all units over a 10-year period, while at the same time maintaining a 100% fully funded reserve throughout a 30-year span.  

The industry needed this tool. This type of funding scenario calculation was currently done via cumbersome Excel spreadsheets and pivot tables. The application would put knowledge and planning power directly into the hands of the property managers who needed it.

Reporting functionality in the hot seat

One of the main problems was unclear and incomplete reporting requirements. While our client knew what he wanted to achieve, he didn’t have complete calculations to hand off to a development team. The previous vendors hadn’t understood the business goals behind the reports, did not have the analysis skills to extract this information, and didn’t have experience building complex financial reports.  

The following details how we addressed the complex reporting requirements of this project.

Understanding the business

We recognized that we would have to dive deep into the business domain in order to understand which reports would be needed and to determine the supporting calculations and formulas.  Both the client and Integrant recognized from the previous failures that though time-consuming, this deep dive would be an important investment to make.  

The Integrant team spent hours each sprint with the client to understand the business goals of the project and the business logic behind the reports, variables, and inter-dependencies. The result was key enhancements, corrections, and additions to reporting functionality.

Re-calculating to improve accuracy

In addition to these challenges, we didn’t have access to the previously built report calculations and formulae. The Integrant team reverse engineered the calculations based upon existing data and corresponding reports. We reconciled previous data inputs and hundreds of report outputs using a predictive modeling approach.  

We found the previous calculations lacked relevance and specificity. For example, when one expense variable was modified, others were not necessarily modified accordingly, and time related adjustments were not applied throughout the expense lifecycle. Integrant improved the calculations and added additional reporting functionality.

Room to play

For instance, in digging into user goals we realized that seemingly straightforward reports that would predict an outcome based upon a change in one variable would be more relevant if they allowed the user to “play” with multiple variables.  

In one report the original idea was to look at reserve financing for a period of expenses over a 10-year period, allowing the user to input various monthly fees per unit and look at how that would affect ending balance over 10 years.  

But in digging deep with the client, we identified that ideally a user would want to play with many variables and should look out 30 years, not just over the span of the expenditure in question. In addition, there should be “smoothing” so that monthly increases would occur gradually, not in fits and starts, and not up and down.  

Previous versions of the report had been marginally customizable. The new version was 100% customizable. This is functionality property managers need to prioritize and make decisions.

Final report variables included:  

  • Independent variable/fixed amounts
    • Projected annual disbursement (how much $$ to replace components)
    • Fully funded reserve 
  • Dependent variables
    • Annual funding amount 
    • Average monthly fee per unit 
    • Beginning cash 
    • Interest earned 
    • Reserve funds 
    • End balance 
    • Percent funded 
  • Other 
    • Inflation rate
    • Interest rate
    • Reserve contribution increase 

Sub-reports included: 

  • Current funding 
  • Threshold funding 
  • 100% funded in 10 years 
  • Recommended funding 

“Form follows function” = more forms required!

The previous attempt included 5 or 6 reports and some changeable fields. Awe asked questions behind the business case for each report and the calculations within it, the client realized that more options and scenarios should be included. The final project included 12 reports, some of which were completely customizable. 

Getting it right required more than code

The key was to learn from what had gone wrong prior. The client recognized he would need a vendor with financial, mathematical, and predictive modeling skills to make sure we built something of value. We built a modern web application using the latest technologies, the user interface was clean and modern, and the client invested time and money to ensure a great user experience. But at its core, the application is a reporting engine and without a team with experience building complex financial reports, the project would never have gotten off the ground.

 


facebook twitter linkdin