Nov 21 2019 01:44 PM
Nov 21 2019 01:44 PM
Join Excel MVP Liam Bastick as he attempts to distill 30+ years of industry experience into an hour of favourite tips and tricks on how to improve your modeling and forecasting techniques using modern Excel. Regardless of current experience and expertise, you'll find new ideas that apply to your everyday work.
Liam Bastick, FCA, FCMA, CGMA, D.Phil. (Mathematics), M.Phil., MSc., BA (Hons.) has spent over 30 years in corporate finance, financial modeling, M&A and valuations, making the most out of his favourite tool, Microsoft Excel. Dr. Bastick has been a corporate trainer for over 20 years, and he's currently the Managing Director of SumProduct, providing training and consulting services to a large number of organizations around the world. He's the author of Introduction to Financial Modeling and co-author & editor of Excel Insights: a Microsoft MVP Guide to the Best Parts of Excel and Power BI MVP Book. Dr. Bastick joins us from Melbourne, Australia.
Nov 22 2019 03:57 AM
An entertaining, if somewhat shambolic presentation!
As a word of warning, Liam first tests his audience by cutting the audio. Then, if you survive that test, he sees whether the you can follow some forecasting techniques from the audio stream but with the video showing a static display on the wrong screen.
Despite all that, a refreshing and informative presentation that makes traditional modelling look like a throwback to a previous decade (millennium?).
Nov 23 2019 01:57 AM
During the amusing and informative webinar Liam mentioned that the file would be made available on the Microsoft platform. Do you whereabouts I can find it?
Nov 24 2019 04:04 AM
Thank you for providing the workbook. There are some stylish touches in its construction and presentation.
By way of discussion (ignore if you feel this is not the right place for it), I notice that you use defined names for constants but direct (also relative) referencing for the calculated dynamic arrays. Do you think the traditional practice of direct referencing has much to offer in the dynamic array context? After all, there is little need for filling across because the DA has already achieved that.
An example from your depreciation worksheet might be
= IF( DepnYear# + Economic_Life - 1 < Year#, , IF( DepnYear# <= Year#, TRANSPOSE(CAPEX#)/Economic_Life ) )
Nov 24 2019 04:32 AM
Brilliant! Many thanks.
Learnt from a course with Leila Gharani, a fellow MVP of yours I believe, that if you reference a spilled range with the name manager and use that named range in the data series reference in the chart you can used spilled ranges to update chats dynamically. However, you must include the worksheet name
I've had mixed results but that's probably something I misunderstood when setting up the tables and ranges… Am sure she'll put you right.
Will Petit aka willflash
Nov 24 2019 05:35 AM
here's a example -file dosent quite behave as previously described but I named the ranges in Name Manager the same as the row titles, eg Revenu, COGS and Bénéfice and it worked! Haven't figured out why yet!
Nov 24 2019 06:33 AM
I understand what you are saying, but I am trying to get the idea of dynamic arrays across here and don't want to obfuscate their functionality with range names. That was my primary intention here. Yes, this may appear inconsistent, and would I do this when modelling in real life? Not sure yet - ask me again when they become Generally Available to all.
Nov 24 2019 06:36 AM
@willflash You are right on all counts. I have been here presenting with Leila in Bulgaria this past week and we were talking about this very subject. I don't like using sheet specific range names, which is why I didn't use it. I have tried to keep range names out of dynamic array functionality for this article, but you could definitely do what you are suggesting (most of the time!).
Nov 24 2019 09:51 AM
What surprised me was that the chart series you have introduced do not show the defined names and yet they are still fully dynamic. I have always found that introducing fully-qualified names into the chart series to be a painful process; Excel charts provide no help and is as picky as hell in what is accepted. I will try to remember this strategy for the future.
I must also ask for forgiveness because I have used this workbook to demo the use of defined names in dynamic array formulas. There are two strategies running. For the chart you introduced the '#' as part of the definition of the name. Elsewhere, I have only named the anchor cell and appended '#' to the name to make the range dynamic. I am not completely settled on either strategy.
Nov 24 2019 10:08 AM
I take your point about not wanting to make the concepts any more alien to a traditional spreadsheet developer/user than is essential.
For me, it is rather different; I haven't used a direct cell reference for about 8 years now and frequently used named formulas simply to bypass the implicit intersection that wrecked array relationships on the grid. My workbooks do appear alien though. They are strangely readable and yet do not conform to one's expectations of a spreadsheet.
Nov 24 2019 11:12 AM
Hum, "most of the time"!
Implies dosent always work - certainly my experience!
Nov 30 2019 11:04 AM