In my current 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, using numerous 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 file. They will build in the capital cost too and finally provide the economic model… all in one!
This makes the model very complex and difficult to follow the logic. Sometimes your gut feel says there must be formula or linkage errors in there somewhere but you just can’t find them. In these types of models more focus is spent 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 they have done everything correctly.
Cleverness is not a virtue
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 is not an example of someone saying “look how clever I am”.
In building the spreadsheet models I have learned to not try to do too much in the same model, especially if several different technical people are involved in its foundation. 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 conditional formatting when possible to help identify errors.
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.