fbpx
Search
Close this search box.

CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions

Share

Facebook
Twitter
LinkedIn

In the 19th session of Chandoo.org podcast, lets talk about modeling best practices.

CP019 - 6 tips for best practice modeling - Interview with Danielle from Plum Solutions - Chandoo.org Podcast - Become Awesome in Excel

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

 

Links & Resources mentioned in this session:

Download Example Workbook

Please download the example workbook Danielle created to understand these tips.

About Danielle

Learn how to create financial models in Excel

On Modeling Best Practices

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.

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

10 Responses to “CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions”

  1. Akos says:

    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 🙂

    • Danielle says:

      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.

      • Akos says:

        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 :)).

  2. Jmdias says:

    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

  3. Mr Sumit says:

    Such a wonderfull tips..!! This article helps me in my project thanks for sharing this dude..!!

  4. Jesus says:

    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.

  5. Mucio Batista says:

    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!

  6. […] and a download sample Excel file of the tools and techniques that were discussed in the podcast.  Download the podcast.  Read the […]

Leave a Reply