Using a semi-automatic log to track model changes

Introduction

Imagine this scenario, finally you have a financial model that you are happy with and is working, using it to do the analysis and projections that you and your team need. You are now starting to make changes to the inputs as new data come in, the tax structure starts to be optimised….so how do you keep track of all the changes?

A common approach is to save a different version and label it like ‘ABC Project (50% Gearing) 20100210.xls’ and another file ‘ABC Project (80% Gearing) 20100211.xls’ to tell the two versions apart. This was still manageable until the filenames need to be more descriptive to distinguish the different nuances of each version.

In this tutorial we will cover the following

1. What is a change log and what does it do?
2. How does a dynamic log of changes work?
3. Step by step instruction to download the sample file

What is a log of changes?

A log worksheet at its simplest updates manually to track who made the changes, when and what the impact on the key output was. But the shortfall with this approach is if the user doesn’t choose to update the log then the changes go unnoticed.

What does it do?

A functional log of changes should update automatically. In the attached model, whenever a change is made that impact key output such as the IRR, LLCR, NPV etc, Excel automatically prompts the user to update the Log worksheet. This removes the need for the user to manually track the changes.

At Navigator the log of changes prompts the workbook to record the following information when key outputs change

• Filename, date saved, and author of the latest update
• Key outputs / ratios (row 7: Column G to K)
• ‘Nature of Change made’

If there is a change in the key outputs of the model, it prompts the user to enter a one line comment describing the change he/she made. In Screenshot 1 you can see that if the user doesn’t annotate the change made then “Change Undocumented’ is recorded against the user name along with the date and time.

Screenshot 1: Log of Changes

Note that column D records the filenames of the previous versions. This is a useful feature allowing the user to cross reference against older files as the model go through various iterations in its development.

How does it work?

Whenever you see a button in an Excel spreadsheet, and in Screenshot 1 it is called ‘Update’ (column D), it means there is a macro behind it. Each time you press ‘Update’ it copies the information relating to the current version (row 7) and paste it to the bottom of the log. It also pastes the values in row 7 into row 8 that is used to calculate ‘Last Total’, range name given to cell L8.

Cell L7, which is below ‘Check Delta’ is a key cell, is given a range name of “Current Total”, it sums up all the key outputs from column G to column k. Through this cell the macro detects a change in the key outputs. If there is a change to the “Last Total” cell L8, a message box as shown in Screenshot 2 appears when you save the model.

 Screenshot 2: Message Box

Quite often the key outputs we track in our project finance models are debt ratios like DSCR and LLCR, so when there is no debt, Excel will naturally return “#DIV/0!”. Current Total and Last Total needs to be a number, so we should prevent the underlying values of the key outputs returning #N/A, #DIV/0 or #VALUE. To get around this we use the formula in Screenshot 3.

Screenshot 3:ISERROR formula

Downloading the sample file

You are welcome to download the log change functionality from attached file “Change Log Sample File.xls”. To do this please follow the steps below:

1. Open your own model in the same Excel application
2. “Move / Copy Sheet” the Log Tab to your workbook

Screenshot 4: Copying Log Sheet to new workbook

Make sure you make a copy of the worksheet to the destination workbook “Book 2”.

1. Check and remove all non blank cells for external links to the original workbook
2. Re-link the key outputs in cells G7 to K7 to cells in the new workbook. Please update the table headings where appropriate.
3. Open up VBA by holding down “ALT” and “F11”
4. Select VBAProject for the destination workbook “Book2” (Screenshot 5)

Screenshot 5: Project Window for Book2

Right mouse click, select insert “Module” (Screenshot 6) 

Screenshot 6: Insert a blank module

Rename the module to “Log” (Screenshot 7)

Screenshot 7: Rename module to “Log”

In the Projects Window, select “Log” Module of the original file (Screenshot 8)

Screenshot 8: Select Log module of original file

Fully copy the code displayed on the right (Screenshot 9)

Screenshot 9: Code for Log Module

Select the newly inserted “Log” module in the destination workbook (Screenshot 10)

Screenshot 10: Select Log module in destination workbook

1. Paste the VBA codes into the blank module
2. Select “This Workbook” in the Projects Window of the original file (Screenshot 8)
3. Copy the code and paste it into “This Workbook” of your new file
4. Exit VBA window by clicking on the top right corner

Final Words

A great financial model should be one that allows people to make changes and automatically track those changes when key outputs are impacted.

In this tutorial we have demonstrated our best practice approach to putting this Log of Changes together through the use of VBA.

To better understand the VBA environment and our best practice approach in applying macros in financial modeling check out our VBA for Cashflow Models Course.

To find out more about our best practice methodology to financial modeling visit our other tutorials or check out our popular Project Finance Modelling (A) course.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...