Hi Guys,

How are you doing? I thought I have been speaking enough about financial modeling (getting started and taking interviews), let me get on to show you a very effective time scale trick and what it can do to our models

Timelines Financial ModelingAccording to changes in the date the revenue and project duration shifts to relevant financial year automatically !

Time Scales and What can they do to our Models!

What I have often seen is that financial analysts often adjust the models manually when the project is delayed or in some cases preponed. In financial modeling the more the manual intervention the more the chaos.. let’s try an automate our projections period with time scales

Consider the following project information

Financial Modeling - Getting Started 3

FY Start Month – 1 means Jan, 2 means Feb and so on..

Setting up the Financial Year Calendar

Now lets make an annual calendar for 25-30 years that will be the reference point for project period. For our calendar to start from the beginning of the financial year write a simple formula

Timelines Financial Modeling 2 For Financial Year Start

Timelines Financial Modeling 3 For Financial Year Ending

  • Start of Next financial year will be previous year end +1 
  • End of Financial year will be =EOMONTH(Financial Year Start Date, 11)
  • Drag the formula to the rest of the cells

Timelines Financial Modeling 4

A Short Note on EOMONTH function

The  EOMONTH function results in month ending date. For example

  • =EOMONTH(“25Jan2014”,11) will result in month ending date 11th month ahead i.e. 31 Dec 2014
  • =EOMONTH(“10mar2014”,-11) will result in month ending date 11th months prior i.e. 30 Apr 2013
  • You can write the date in double quotes or refer to any other cell and specify the months ahead or months back in time with a negative sign

Now comes the counter for Project Duration

We now want to build a counter that will map the project dates (Start & end date) in the calendar and highlight the project period. Let’s get started, its logical but simple 😀

1. Check for the Project Start Date

Building a simple check that, Is my project start date between the financial year start and financial year end ?

Timelines in Financial Modeling 5

In the spreadsheet your formula would look like 

Timelines in Financial Modeling 6

2. Building a Check for Project End Date

Yet another check for finding out if the project end date is between the start & end of the financial year!

Timelines in Financial Modeling 7

 In excel the formula would look like

Timelines Financial Modeling 8

 

3. Final Check – Is the financial year falling in between the project start and end dates

Timelines Financial Modeling 9

The full blown excel formula would look like this

Timelines Financial Modeling 10

Relax – we are almost done here 😀

The formula would result in 1 or 0 (Boolean)

  • Result 1 for financial years that are a part of the project period
  • Result 0 for financial years that fall outside the project period

 

So what now ?

  • Take those ones and zeros and multiply them to your projections
  • When there is a 1 the projections would show up and when there is a 0 the projections would turn off
  • As of now this trick generalized. You can further categorize this into construction period and operations period
  • This trick can easily shift your entire models to the relevant project period
  • You can additionally name the critical cells (project start date and end date etc) [read : cell naming]

 

Download this file and do some practice. I have additionally applied conditional formatting for highlighting project period. Please use comments to share how to do manage project delay adjustments in your financial models ?

Take Care!



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI