Tools for Financial Modelling
Danielle Stein Fairhurst · 2012
This chapter discusses the basic, commonly used Excel tools used for financial modelling, such as hiding, grouping, array formulas, goal seeking, pivot tables, user-defined functions (UDFs), and macros are discussed. Being able to hide sections of a model is a very handy tool, especially when tidying up a model to conceal cells that are necessary for the model to work, but not necessary for the user to see. Grouping tool can be used to make hidden data more obvious and avoid it being overlooked. Array formulas are powerful tools, and financial modellers generally advocate its use only when it is not possible to do something any other way. Goal seek is used to adjust the value in a specified cell until a formula dependent on that cell reaches the result specified. It changes the inputs such that the output is set to the exact amount one wants it to be. A common use of goal seeking tool is in break-even analysis. Excel pivot tables are very useful tools, which can be used to summarise, analyse, explore, and present the data. A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the sheet.