When you release a new version of a financial model, the first thing that receiver

party wants to know is:

● What has been changed?

● And what’s the impact of the changes your applied in the new version on the
key project metrics?

You can always put it in writing but a lazy and efficient way to do the version
comparison is to include a tracker sheet and save the key inputs and outputs of
different versions of the model.

In the accompanying excel sheet, I’ve put together a template that does this. The
template is just the skeleton and you need to link your key assumptions and results
to bring life into it.

I also use this sheet to do scenario analysis. I find it quite helpful when I’m running
simulations live in a meeting and use it to save a scenarios and compare saved
scenarios .

Color codes used in the Excel file are:

Before we get into the steps, I find it important to also let you know about the
importance of having file naming convention.

To avoid chaos and to have better control of different model versions, use a file
naming convention across all model.

● For example, you can start with a project name (use short file name as much
as possible), then you put the model version:

File Name: Kaombo_01a.xls

Second iteration of the mode becomes:

File name: Kaombo_01b.xls

….

Changing to 02a after you reach 01z.

● Another type of name convention can be using dates in the below format:

File name: Project Name_YEARMONTHDAY

Kaombo_20200202.xls

Now let’s get into steps:

Step 1: Preliminaries
● Download and open the “FM Version Tracker” workbook
● Open the financial model where you want to insert the “Tracker” sheet
● Make sure that your financial model is macro enabled.
 Check if the file extension is either .xlsm or .xlsb

To save as macro enabled.

1. Click the File tab and then choose Save As. The Save As dialog box appears.
Or use shortcut F12 for windows/Command + Shift + S in Mac
2. Enter a name and select a location for your workbook.
3. Click the Save as Type drop-down arrow. A list of file types appears.
4. Select Excel Macro-Enabled Workbook.
5. Click Save.

● Display the Developer tab

If the Developer tab is not available, do the following to display it:
 In Mac:
1. Select Excel > Preferences >
Ribbon & Toolbar.
2. Under Customize the Ribbon,
select Main Tabs and then
check Developer.
3. Click Save and then close
Excel Preferences.

In Windows:
1. On the File tab, go to Options >
Customize Ribbon.
2. Under Customize the Ribbon and
under Main Tabs, select the
Developer check box.

Step 2: Insert the Tracker template into your own model

If you already know how to copy a sheet from one excel workbook to another then
skip go directly to excel and copy the “Tracker” sheet into your model. If not I have
listed the steps on how to insert the “Tracker” tool in your own models here:

Once you have both files open:

1 2
go to “Tracker” sheet in “FM right click on the sheet labeled
Version Tracker v01.xlsx” file “Tracker” and click on Move or Copy
and a new window will open.
 3 4
at the bottom of the window, select your filename in the To
check the Create a copy box to only Book drop down list.
copy and not move the sheet.

5
click ok and go back to your
financial model. You should now have
the same sheet sitting in your model.
Now if you go back to your financial model, you should find the “Tracker” sheet in
your workbook.

Step 3: Insert the VBA code into your own model

Go to your financial model and open the VBA tab within your own financial model.
To do this :
Go to the Developer tab and click on the visual basic button or use shortcut Alt+F11
in Windown/fn + Option+F11 in Mac
 Once in the VBA editor, at the top left
corner of the editor window, right-click
on your workbook name in the Project-
VBAProject pane and select
Insert/Module from the menu.

Copy and paste the below code (highlighted) into the new module that you just
created:

Sub SaveActiveCase()
'This is a simple macro to save the active scenario as values

Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Columns("m:m").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Columns("M:M").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Call SetBlue_Click

End Sub

Function LastSaveDate()
LastSaveDate = FileDateTime(ActiveWorkbook.FullName)
End Function

Function LastSavedBy()
LastSavedBy = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Sub SetBlue_Click()

'INSPIRED BY SOURCE: https://software-solutions-online.com/excel-vba-cell-fill-color/

Dim PastedValues As Range

Set PastedValues = Range("M1:M74")

For Each cell In PastedValues

'Check the color of the cell

If cell.Interior.Color = RGB(226, 239, 218) Then

'If it is matching, change it to below

cell.Interior.Color = RGB(0, 255, 255)

End If

Next cell

End Sub
Well done! you have the skeleton in your model, now you have to breath life into it
by making the links. Save your file by pressing Ctrl +S in Windows/ Command + S
in Mac to save the changes we’ve made.

Now we are completely diving into your territory, meaning in your own financial
model. we have to link the “Tracker” sheet to your model.

Step 4: Define the Key inputs and outputs

Take some time to think about what are the key inputs and outputs that you want
to be reflected in the tracker sheet.

The key inputs and outputs of a financial model will differ depending on the type of
projects and who the model is for. However, you want to make sure that you
mention key inputs covering the below areas:
• Operating revenues
• Operating costs
• Construction costs
• Loan drawings and debt service
• Accounting
• taxation
• Macroeconomic assumptions
...

And for the key outputs, it’s really specific to your own project but typically you
might want to have the below key outputs:

• Summary project cost and funding

• Summary of cash flow statement
• Summary of Profit and Loss statement
• Summary of Balance sheet
• Debt metrics
• Equity return metrics
...

Step 5: Now you need to link the key inputs and outputs to the
“Tracker” sheet
In “Tracker” sheet that is now inserted in your own financial model, you should link
both the label (column E), units (column F) and figure (column G).
To facilitate this task, I have color codes the sections that you need to link to your
model as light green background.

Step 6: Assign a macro to the macro button and run tracker

1 2
in the “Tracker” sheet, right click in the new window on top in the
on the button on top and select Assign Macro name, select SaveActiveCase
Macro and then OK. and select OK
 3
Click on the button to save your
active case.

And you're done!

Congratulations on successfully creating a tracker sheet using the template . I hope

you enjoyed the process and that you can use it from now on in your models.

Once you’ve had a chance to use the Tracker template, I would love to hear from

you. please share your thoughts and ideas directly in the comments.

In the meantime, stay $locked$ and happy modelling.

Hedieh

h.kianyfard@finexmod.com