About this lesson
A continuation of how to create a timing sheet to be used in building a Financial Model.
Download this lesson’s related exercise files.Creating a Timing Sheet Part B.xlsm
60.7 KB Creating a Timing Sheet Part B - Solution.xlsm
Creating a Timing Sheet Part B
Discover how to create a Timing Sheet.
When to use
When constructing a basic Financial Model.
- Essentially, three lines are necessarily needed when modelling (the rest may be derived as necessary):
- Start date: This will allow for models where the first period is not a “full” period (often called a ‘stub’ period), e.g. a business may wish to project its profits from now until the end of the calendar year for the first year;
- End date: This will define the end of the period and will often coincide with reporting dates, e.g. end of financial year or quarter ends. By having both the start date and end date defined, a modeller can determine the number of days / weeks / months in the period, which financial year the period pertains too and so forth;
- Counter: Start and end dates are insufficient. Constructing calculations based on consideration of a date is fraught with potential issues in Excel. This is because dates are really serial numbers in Excel which may differ depending upon the underlying operating system (e.g. Day 1 for Microsoft Excel for Windows is 1 January 1900, whilst Day 1 is 1 January 1904 for Microsoft Excel for the Macintosh). Further, if you are building a monthly model you may wish to divide an annual figure evenly instead of based on the number of days. This is also the easiest way to identify the first and last periods in a robust manner.
- The Counter is simply the last period’s number plus one.
- =N(Previous_Cell)+1, where the N() function takes the numerical value in the previous cell, and more importantly, text is ignored so that #VALUE! errors will not arise;
- The Start Date is simply the Model Start Date for the first period and the day following the last period’s end date otherwise. This can simply be written as =IF(Counter=1,Model_Start_Date,Previous_Period_End_Date+1).
Lesson notes are only available for subscribers.