When we discuss a financial model’s design, layout, and structure, we address these aspects during the early stages of model construction. Once you’ve established your spreadsheet’s layout and design, you can proceed to the building stage. This means you no longer need to focus on design and can apply the rules outlined in your financial model user manual. This is the topic I cover in my free mini course on spreadsheet design. You can sign up for it here:

However, I’ve recently realized that considering the design is an ongoing process. I reevaluate my design, especially when optimizing calculation blocks and macro codes, at various stages of the appraisal process—even if the model’s basic structure is already built and the model is up and running.

Step 1: Gathering Project Information

• What is the nature of the project?
• Who are the project sponsors?
• Who are the potential lenders involved?
• What constitutes the financing structure?
• What is the current stage of the project’s development?
• Are there any noteworthy aspects of the project’s location or technology?
• Are there any relevant signed contracts?
• What incentives are in place?
• …

This process includes reading and asking questions

Step 2: Defining the Layout and Design of the Financial Model

I offer a free mini-course dedicated to this particular subject. You can register for the course by clicking the following link:

https://hedieh-s-school.thinkific.com/courses/Projectfinancespreadsheetdesign

Step 3: Constructing the Model’s Key Modules

During this phase, I focus on constructing the model’s essential modules. I follow established best practices when creating sections such as Inputs, Primary Sheets, Calculations, Outputs, Dashboards, Checks, and more. This ongoing process aims to include all existing elements, tools, agreements, and anticipated future components.

For example, you might engage with just one lending institution at the project’s initiation. However, you’re aware that additional lenders might get involved down the line. To address this potential, you could design the model to accommodate multiple debt issues right from the start. Similarly, even if you don’t possess a detailed cost breakdown during the initial stages of the project, you could expect more comprehensive cost data to emerge as the due diligence process unfolds. Thus, you insert placeholder cost items into your model, preparing for more precise cost details in the future.

This phase involves the technical aspects of spreadsheet creation, considerable analysis, and forward-looking considerations.

Step 4: Stress Testing

This phase ensures the functionality of the model. Here, you begin to interact with your model, like playing a musical instrument to ensure it’s in tune. For this purpose, you modify inputs and observe the model’s responses. If the resulting change in direction and magnitude is reasonable, the mechanics function correctly. This includes implementing various error checks and confirming that all these checks hold true even during different stress tests.

The upcoming step is the one I want to emphasize in this post or draw your attention to.

Step 5: Returning to Design: Removing Parts or Processes

This step can be challenging, as you’ve invested significant effort into building various components. You’ve likely spent so much time with it that going through numerous rows and columns or your code feels burdensome. However, trust me; it’s a one-time effort. While the process might not be entirely satisfactory, the end result will be worth it.

At this stage, I focus on sizeable calculation blocks that span multiple rows. For instance, when I review my calculation sheet, I scrutinize blocks that contain hundreds of rows. I deliberately designed them this way to align with the principle of simplicity. Rather than crafting lengthy and complex formulas, I break down the calculations, making them easier to read and audit. Nonetheless, I continually contemplate condensing these calculation blocks while maintaining their simplicity. 

Let me give a concrete example of calculation block optimization: 

When engaging in negotiations regarding drawdown schedules, it’s advisable to consider modeling various drawdown scenarios:

• Pro-rata Drawdown Across All Facilities
• Full Up-Front Equity Drawdown
• Partial Up-Front Equity Drawdown, Followed by Pro-rata Debt Drawdown
• Prioritizing Debt Drawdown

You can construct all four disbursement schedules within your calculation sheet. Then, implement an input switch that allows seamless navigation between the introduced drawdown alternatives. This entails creating an extensive calculation block to reach the ultimate objective: the debt and equity drawdown schedules.

However, consider an alternative approach for simplicity. Condense all the options mentioned above into a single input. You can name it “Percentage of Up-Front Equity Requirement.” By using this input:

– When the input is set to 0, you achieve a 100% up-front debt drawdown.

– When the input is 100%, you accomplish a 100% up-front equity drawdown.

– You also maintain flexibility by inputting varying percentages for up-front equity, such as 30% or 50%.

So, you’ve substituted four inputs with a single input and streamlined five calculation blocks into just one. This approach not only simplifies the process but also enhances efficiency.

You can access my recommended manual and the corresponding spreadsheet for disbursement schedules by following this link:

The same principle applies to macro codes. If your VBA code consists of multiple lines, it’s beneficial to revisit it. Consider removing unnecessary lines and replacing them with optimized code that maintains simplicity and, most importantly, speeds up execution time.

I invite you to join the conversation and share your experiences as well. Have you encountered similar situations where streamlining inputs and calculations led to enhanced efficiency? Feel free to share your stories on how you’ve optimized your models, especially if you’ve discovered innovative approaches that differ from the traditional methods used by others. Your insights could inspire the community and help others explore new ways of achieving efficient and effective financial modeling.