Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
  • Dates and Timelines in Excel Financial Models
Originally published: 27/02/2019 20:38
Last version published: 25/06/2019 06:43
Publication number: ELQ-11221-2
View all versions & Certificate
certified

Dates and Timelines in Excel Financial Models

The tool shows how to work with dates & timelines in financial modelling.

Description
This sections deals with modelling those assumptions which rely on dates and time lines:

a) How to extend or shorten a sales ramp-up schedule for a new business, or a life-cycle plan for a new product, or a rent take-up schedule for an office complex by months, or a construction project with CAPEX budget by quarters, and many else ?

In these instances you may want to see what happens if your plan is delayed (e.g. if instead of one year it will take a year and a half) or accelerated (and will take nine months instead of one year). Therefore, you will need an algorithm to extend or shorten your schedule while preserving the same patterns of your initial schedule curve (with all its spikes, falls and plateaus) and keeping the total amount the same.

b) Calculating interest expense. This seemingly trivial task becomes complicated if repayments occur at non-regular dates, or if the rate is changing, and you need to calculate interest expense per standard calendar periods in the model, with a flexibility to switch these model periods quickly between quarters, years, or months.

c) If you need a certain number of items delivered in June, and delivery takes 20 days, how many items do you need to order in May and June? Or if you will sell a certain number of items in September and you get paid in 10 days, how much cash can you expect to come in in September and October? How can you model an ongoing flow of such things? A complex problem cracked by a simple formula.

d) Calculating rental revenues with rental rates and/or rented areas changing at some date during the month. Rental rates are usually indexed annually, and indexation date might fall on any date during the year (typically this will be every anniversary of the rent start). In addition, there could be a change of rented area at some point, also at a random date. If you have an office building with many tenants having indexation at various dates, calculating rental revenue and allocating it to calendar intervals (months, quarters, years) might become a challenge, and the attached file explains how to tackle it.

This business tool includes
1 Excel File

Andrei Okhlopkov offers you this business tool for free!

download for free

Add to bookmarks

Discuss

Further information

This tool adds flexibility in modelling date and timeline assumptions

Financial models which deal with time schedules, periods, intervals etc.

Reviews


keyboard_arrow_leftkeyboard_arrow_right

More tools from Andrei Okhlopkov

keyboard_arrow_leftkeyboard_arrow_right

Any questions on Dates And Timelines In Excel Financial Models?

The user community and author are here to help. Go ahead!

please wait...