Excel provides a rich set of functionality to manage date-sensitive calculations. Yet in spite of this, it is common that issues in models and analyses that revolve around this fundamental area become needlessly complex. One alternative to working with dates in detail is demonstrated in lecture # 10 - Implementing Complex Scenarios in Cash Flows.
In this session the examples deal with implementing models with variable financial year-ends; those with variable reporting periods; and demonstrate techniques to deal with the apportionment of transactions into different reporting periods. The samples presented provide some simple yet very effective techniques that can be re-used in ongoing model construction.
Participants should be familiar with how dates are stored and calculated in Excel, and should be familiar with the functions DATE, YEAR, MONTH, DAY, EDATE and EOMONTH. They should also be familiar with custom formatting of dates. An understanding of array formulas would be an advantage.