Hi Guys, the second and the final post in the Time Scale series got a bit delayed, my sincere apologies. Let’s begin

Recap of our earlier post

In the earlier post we had seen how can we map the project period in a calendar and link that to our financial model. Do a quick review of the earlier post here – Time Scales in Financial Modeling Part 1

The Problem

Sometimes mere mapping of the relevant project period is not enough but we also have to calculate the number of days during the project period. The number of days would help us calculate accurately things like..

  • The interest payments for the relevant number of days
  • Or the revenue or cost calculation as per the number of days
A snapshot of what we are trying to do
Timescales in FM2 - 1

The Solution

Lets continue with our earlier data where we have the following information

Timescales in FM2 - 2

Step 1) Calculate days passed from the project start date

  • This is simple, we can take the difference of the days between the Financial Year End and Project Start Date
  • We encounter negative days when the project has not started (i.e. when project start date is bigger than financial year ending) to settle that we use =MAX(Financial Year End – Project Start Date,0). So if the days are negative the MAX function will pick up a zero

Timescales in FM2 - 3

Step 2) Calculate the number of project days in full financial year

  • A project can run for a full year too, for which we simply take the difference of the financial year ending date and financial year starting date
  • Notice the formula has also adjusted for leap years

Timescales in FM2 - 4

Step 3) Calculate number of days passed from the project end date

  • Just take the difference between the project end date and financial year start
  • Notice you’ll again have negative sign where ever the project period is not falling in the calendar (i.e. project end date is smaller that the financial year start date), for which use =MAX(Project End Date – Financial Year Start,0)

Timescales in FM2 - 5 Click on the picture to enlarge

Step 4) Calculate the project days

The logic is that the minimum number days between the 3 items will be our project days, so just a take minimum between

  • Days passed from the project start date
  • Project days in full financial year
  • Days passed from the project end date

Timescales in FM2 - 6

One consolidated formula

If you want you can consolidate the formula like this

=MIN(MAX(Financial Year End – Project Start Date,0),Financial Year End – Financial year Start+1, MAX(Project End Date – Financial Year Start,0))

Download the file and check it out yourself

 

What lies ahead

You can go further and bring more detail in your model by spliting the project between

  • The construction period and
  • Operations period

and calculate the relevant number of days for each for different cost and revenue calculations. The basic structure of calculating the number of days remains the same

Additionally check out a full video review (+ download) of a Startup Company Financial Model and Investor Presentation

I hope you enjoyed this, please use comments to describe how do you deal with project days calculations ?

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI