A good knowledge of core functions (SUM, IF etc) is often sufficient to build Excel models in a range of applications. For example and integrated financial statement model or cash flow valuation rarely requires more than these. However, more powerful models – including situations where models are desired to be more flexible – require knowledge of a wider range of functions.
My recently launched service to provide web training and consultations about @RISK™ and Excel modelling has sparked good interest. In particular, it allows for 1:1 consultations on modelling topics with people all around the world. I use a particular web-platform for these which allows for video conferencing, as well as document sharing and control passing. This blog described some of the features and benefits of this approach.
Today sees the launch of my Interactive Web Consultation service. This allows one-to-one consultations using a high-quality internet video conferencing services over the internet in which applications (such as Excel, Excel-add-ins such as @RISK, PowerPoint and Word) can be shared. This provides an opportunity to jointly work on a model or document.
Following the earlier blog about the use of user-defined functions in VBA code to model time delays in Excel, I am here posting a similar example, but which is more general and allows for both lead times, as well as lag time; that is projects or cash flows can brought forward according to some allocation mechanism as well as delayed. The advantage of this extra flexibility is to avoid having to set up models in which the base case is the most optimistic one possible, and in which any deviation is necessarily a delay. Rather, the base case can be more flexibly defined and the used case adapted from that in either time direction (for each variable).
This blog continues the series on self-diagnosis questions about risk modelling covers issues relating to the use of key probability distributions within @RISK. There will be three further postings on this topics priort to Christmas, to complete the series. I’ll also be posting more on modelling leads and lag times through VBA code.
Streamlining a model is important to reduce error, time consumption and its overall complexity. As mentioned in an earlier blog, one key area where models can become large, complex, and inflexible, it in the implementation of time delays, such as where a project start date may need to be changed (or is uncertain), where receivables may be received at a future date that may need to be changed, or where a quantity needs to be allocated across time, such as for depreciation schedules, tax calculations, or in insurance loss triangles. In this blog, we show how such topics can most efficiently be implemented through the use of user-defined functions in VBA.