You might also be interested in
Replies (17)
Please login or register to join the discussion.
Black boxes
As a seasoned software developer, my 'input' would be to make the components of your spreadsheet as self-contained (doing one well-defined job well) and as self-reliant as possible, both to avoid the 'spaghetti links' syndrome described in the article, but also to help you check each 'box' in isolation. This also lets you swap in a replacement or use a bought-in tool or an online service instead, if need be. Plus you can more readily sanity check it, on its own, with test values to put it through its paces.
You can then mock up parts of the sheet with 'empty boxes' that are not ready or not as important, allowing them to return approximate, simplified or fixed results for the time being, without throwing everything else out of place, and let you see the whole model working end-to-end. The empty boxes can be swapped for working ones when ready with nothing else being any the wiser.
Obviously it also allows the system to be much more readily understood since each box is small enough to understand in detail, and then when you put the boxes together you have confidence that they all do their jobs properly.
End of lecture !
Formula reveal
I love [ Ctrl + ¬ ]* Reveals all the formulae and their origins. Always worth quickly checking before beginning.
* note: ¬ is at the top left-hand side of the keyboard beside the '1'
Checking
When I used C++ to develop financial model, I always use Excel to do a parallel development and make sure the results from both C++ and Excel match each other. This is how I audit my financial model. My business users find my Excel model much easier to understand my C++ programs, they can double check my Excel model.
I wonder how someone audit a financial model that consists of 857 workbooks.
Regards,
Andrew Chan.
Horses for Courses
Pleasing to hear someone say that some tasks are better handled in Access.
Excel should not be the automatic, knee-jerk response to everything numerical.
"Is Excel the right tool?"
Good article and I heartily agree that a good question to ask before embarking on any Excel project is "Is Excel the right tool?".
In my experience Excel is the only desktop tool that most people have to handle structured data. This made it a fantastic productivity enhancer for 15 years but has (often) made it a productivity reducer in the last 5 years when users have built unwieldy Excel workbooks instead of embracing other more appropriate tools.
I would now recommend to anyone to do a quick google search for their task before embarking on an Excel 'project'. For so many common tasks there are better tools out there:
e.g. manipulating data - Access (as mentioned above), gathering data - Wufoo, project management - Basecamp, tracking activities - Saleforce (and other CRMs), etc...
Of course Excel is a great tool and will remain a key tool for accountants for some time but there are alternatives!
M
Sum error
The sum errors in excel although occasional really used to frustrate me, not necessarily when I added cells in but just occasionally when we had typed in bank statement details etc or were working on client spreadsheets. We would add a column up but it would refuse to enter a total at all or simply put an obviously wrong total in such as the total of the last few lines in a 700 line spreadsheet. I mentioned this to one of my clients who is something of a whizz kid with computers generally and he suggested I check all formula in the column that I was trying to add. Lo and behold the next time this happened I found that there were different formula in that column, mostly accounting but occasionally numbers etc. when I checked the adds between these erroneous entries they added up to the total excel had given so I now re format every spreadsheet before I add it and I have not experienced any problems since!
Spreadsheets - Using Formulae especially "SUM"
I have been using Spreadsheets since 1982. That was way before Excel and the IBM Personal Computer. The software then was SuperCalc.
The manual had a mere 10 or 12 sections. It was so easy to learn the principles of a spreadsheet.
The KEY solution when "summing a column" was to "sum between text cells". So if one inserts a row of hyphens in a cell, to represent an underlining of figures, then if any Rows are ADDED or DELETED then the formulae need not be updated. I tried to tell a client this tip very recently. Instead of a simple "thank you", I got back "I've been using spreadheets for years...".
Are we all too stubborn to learn new ways - or even to forget the "old" tried, and trusted methods?
Reliance on spreadsheets can be risky
Financial directors may be putting their companies at risk by continuing to rely heavily on Excel spreadsheets, and this especially applies to the complex processes associated with managing the capital assets register.
How an organisation manages its assets has multiple effects on a company’s finances, from tangible costs of heightened insurance premiums through to neglected depreciation. Having an accurate and robust asset register that shows the location, value and condition of assets can also help ensure that resources are available and usable when needed as well as achieving compliance with key industry legislation. And whilst spreadsheets are adequate for collecting basic data, there are several compelling reasons for switching to a bespoke system designed to cope with these intricacies.
Too many spreadsheets contain errors, which is to be expected with information entered by hand. Whether it be the background asset data or the formula itself, there’s little doubt that depreciation calculations, when based on a spreadsheet, are likely to be inaccurate – potentially affecting the overarching balance sheet. Furthermore, with the raw data within a spreadsheet dictating that each report be constructed individually using complex macros, this also wastes valuable time and resources at month-end.
Compliance with the latest legislation including IFRS, Sarbanes-Oxley (SOX) and SORP consists of improved financial management and increasingly detailed reporting. With such a high degree of attention focused on your organisation, why would you risk the integrity of your financial data by relying solely on spreadsheets, which are inherently instable and invariably achieve poor audit results?
Karen Conneely, Group Commercial Manager, Real Asset Management
SUM Formula
A better way to handle the SUM formula blues is to have a blank below the data and let the formula include that row. The blank row is never used - new rows are inserted when needed just above the blank row so the formula always stays intact.
edit: Just noticed MartinLevin saying about the same thing.
Limitations of the SUM function
Another limitation of the SUM function occurs when you stick an auto-filter at the top of your range of data and search individual entries. I always use the subtotal function as it is far more reliable and the blank row idea mentioned above.
Excel
Rather like driving, in the head of the person who builds the model's hands, it's fantastic. It's the other idiots you have to watch out for. What's worrying about this is the number of interested parties who pop their heads up and say "you should use our proprietary system". They would say that wouldn't they?
(Yes, I build complex models with embedded layers of error checking, and no I don't let the untrained loose on them.)
Slander
"[Spreadsheets are] inherently instable and invariably achieve poor audit results"
Speak for yourself, dear.
Use autostart templates
Put a template Book.xlt and Sheet.xlt in xlstart directory with all formatting and print header / footers for consistent look of your sheets.
It is good idea to put your report (regular printout required) on one sheet and data and other unstructured input on another sheet(s). Set the printing to “blank” tall and one page wide as default. Further set the print filename, date and time in the footer.
Just to emphasize already mentioned point; use subtotal and blank row to avoid sum blues.
My rule is that if file size gets bigger than one MB, some thing is wrong with the workbook or choice of Excel is wrong.
Regards
Zubair Edhy
Excel file size
I use many Excel files above 1Mb and don't find a problem. If the Excel file size gets too large, use CTRL-END on all worksheets to see if blank rows / columns are being captured between your data and Excel's last cell.