In my role as a mining consultant I am often required to review spreadsheet cost models or cashflow models built by others. Some of these spreadsheets can be monsters, incorporating multiple worksheets, cross-linking between worksheet cells, and having hard wired numbers inside cell formulas.
Some of the models I have reviewed will build the entire operating cost (mining, processing, G&A) in one grand file. They will build in the capital cost too and finally provide the economic model… all in one spreadsheet!
This makes the model very complex to audit and it becomes difficult to follow the logic. Sometimes gut feel says there are formula or linkage errors in there somewhere but you just can’t find them. In these types of models my focus is on trying to figure out the formula logic than actually looking at the validity of the inputs and output.
It seems that only the model developer can really work with these spreadsheets and the rest of us can just hope that they have created everything correctly.
Don’t be too clever
Over the years, I have learned that there is an art to creating a clear, concise, and auditable cashflow model (or cost model). Once in awhile you come across one that is well crafted and isn’t an example of someone trying to show how clever they are.
In building the spreadsheet models I have learned to not do too much within the same model, especially if different people are involved in its foundation. My other suggestions are:
-
Color coded input cells differently than formula cells.
-
Carry over values rather than linking to other worksheets.
-
Highlight cells that are carried over from other worksheets.
-
Never hardwire numbers into a formula.
-
Use named cells for key fixed inputs (like exchange rate, fuel price, etc.)
-
Use conditional formatting when possible to help identify errors.
-
Put your “Totals” column along the left side of the worksheet so you can add columns if needed.
I won’t go into detail on good spreadsheet practices, but you can check out the instructional presentations prepared by Peter Card at Economic Evaluations (http://economicevaluation.com.au).
He has some excellent practical recommendations that all financial modellers should consider. It doesn’t take long to review his online courses and it’s worth your time to do it. His recommendations can generally apply to any Excel modelling exercise, whether its costing, scheduling, or economic analysis.
Try to help by building in clarity.
The bottom line is that you must build your spreadsheet models compatible with the way you think. However not everyone thinks the same way so try to keep all aspects easily identifiable and traceable. Be consistent in the model format from worksheet to worksheet. Be consistent in methodologies on all worksheets and with all your models. Your client, colleagues, and reviewers will thank you.
Another aspect of due diligences that can be taxing is figuring out the structure of a data room. Simply throwing all of your files into an unstructured data room helps no one. I have written another blog about this annoyance at “Mining Due Diligence Data Rooms – Help!“
