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
According 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
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
For Financial Year Start
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
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 ?
In the spreadsheet your formula would look like
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!
In excel the formula would look like
3. Final Check – Is the financial year falling in between the project start and end dates
The full blown excel formula would look like this
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 from down below 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!
