Menu
CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions
Chandoo.org Podcast Sessions - 10 comments
In the 19th session of Chandoo.org podcast, lets talk about modeling best practices.
What is in this session?
I am very happy to interview my good friend, blogger, author, excel trainer & business-women – Danielle Stein Fairhurst for this session. I first met Danielle when I went to Sydney, Australia in April 2012. Our friendship & collaboration grew a lot in the last 2.5 years. She is a great speaker & trainer. This episode is loaded with her trademark style commentary, explanation & tips for better modeling. I hope you will enjoy it.
In this podcast, you will learn,
- Introduction to Danielle & her work
- 6 Tips for Best Practice Modeling
- Write consistent formulas
- Avoid hard-coding
- Smart referencing
- Ditch the bad habits
- Document assumptions
- Format & label things
- Resources for learning more
Go ahead and listen to the show
Podcast: Play in new window | Download
Subscribe: RSS
Links & Resources mentioned in this session:
Download Example Workbook
Please download the example workbook Danielle created to understand these tips.
About Danielle
- Visit Plum Solutions: her website for more tips, tutorials & articles on modeling, data analysis & Excel
- Get Danielle’s book: its great for both aspiring & working analysts (read my review)
- Join Danielle’s linked group: and network with fellow financial modelers & professionals
Learn how to create financial models in Excel
On Modeling Best Practices
- 5 tips on modeling best practices
- BASE rule for keeping your models simple
- 12 rules for making better Excel models
- 10 tips for better workbooks
- Introduction to Spreadsheet Risk Management
Other topics relevant to the podcast:
Transcript of this session:
Download this podcast transcript [PDF].
What keeps your models sane & sexy?
I use all the tips recommended by Danielle. Apart from these, I also use ideas like named ranges, structural references, separation of input & output to keep my models user friendly.
What about you? Do you apply the tips suggested by Danielle? What else do you use to make your models awesome? Please share your tips & ideas using comments.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Quickly convert numbers stored as text [tip] | Teach me (and rest of our community) something this Teachers’ Day » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
10 Responses to “CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions”
Hello Chandoo,
Thanks to you and Danielle for deliverying this podcast, it is an excellent piece! Listening to it I think we could add one more practice to modelling (to customers) that is to protect the cells with formulas against editing.
This can be controversial, as it could make editing the model cumbersome, but I have seen many times end users by accident modifying the formulas resulting in the collapse of the model... so I think it is better to prepare 🙂
Hi Akos, yes protecting cells is very useful indeed! I don't think it's appropriate though for EVERY model, as it can get quite time consuming and also prone to error (Ever tried to edit a model where they have accidentally protected the cell you need to edit? Very annoying!). Most modellers will simply format the hard-coded values and formulas differently so that you can quickly see which cells you are supposed to edit and which you are not.
If the model is going to be used by people who aren't very skilled though, I would definitely recommend using protection.
You are right, we need to keep the profile and skill level of end users in mind (just remembering my own frustrations with over-protected sheet :)).
Hi Chandoo.
What is the best way to get data from internet?
I want to fill a form with a data from a website that gives me a TXT page like this page: http://www.greens.org/about/software/editor.txt
I know this way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("sheet1").Range("a1").Value = False Then
Range("xxxx").ClearComments
Exit Sub
End If
If Not Intersect(Target, Range("xxxx")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
ActiveWorkbook.Connections("Conexão").Delete
Dim sName As Name
For Each sName In ThisWorkbook.Names
If InStr(1, sName, "xxxx") Then
sName.Delete
End If
Next
Sheets("xxxx").Range("a:a").ClearContents
Dim url As String
url = "URL;http://www.greens.org/about/software/editor.jsp?COD=" & Application.WorksheetFunction.VLookup(Cells(Target.Row, 3).Value, Plan1.Range("L2:M32"), 2, False) & "&CEP=" & Cells(Target.Row, 8).Value
With Worksheets("xxxx").QueryTables.Add(Connection:=url, Destination:=Worksheets("comments").Range("A1"))
.Name = "Conexion"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
a = ""
For i = 2 To Sheets("comments").Range("n1").Value
a = a & Sheets("comments").Cells(i, 15).Value & Chr(10)
Next i
Range("xxxxxxx").ClearComments
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:=a
ActiveCell.Comment.Shape.Width = Len(a) / 2 + 300
ActiveCell.Comment.Shape.Height = Len(a) / 5 + 40
Else
Range("xxxx").ClearComments
End If
End Sub
Such a wonderfull tips..!! This article helps me in my project thanks for sharing this dude..!!
Hi Danielle,
I have already taken up Financial Modelling Course and I am not sure whether your book goes deep into Finance Modeling or it is just a book that cover the foundations.
Thanks so much,
Hi Jesus,
The title of the book is "Using Excel for Business Analysis: a Guide to Financial Modelling Fundamentals", so it does cover the fundamentals of financial modelling, with a heavy emphasis on the Excel techniques critical for financial modellers to know. I hope that answers your question!
Danielle.
Yes it does I have just bought your book and I hope to enjoy it a lot.
Thanks so much for your answer
Thank you and Danielle for sharing all this stuff!!! God bless you guys! You have no idea about how much you re helping people delivering this podcast!
[…] and a download sample Excel file of the tools and techniques that were discussed in the podcast. Download the podcast. Read the […]