Dates and Timelines in Excel Financial Models

Description
In this post we will look at the following issues and workarounds:

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.

e) The project may start or end on some date between the beginning and ending date of the model period. We will calculate fractions of such periods during which the project was live to make accurate predictions of interest, dividends, rent etc.

f) Foreign exchange rates are normally forecasted as year-average or year-end figures. The models though are often made on a quarterly or monthly basis, so finding proper beginning, ending or average rates becomes a challenge.

g) Transforming a monthly or quarterly report into annual.

h) If you have a list, database or table with dates, you can group, aggregate or transpose this information by years, months, quarters and weekdays.

i) Finally, I am sharing a collection of elegant formulae dealing with dates.