I have worked with many organizations big and small and am always amazed that their number one Business Intelligence tool is Excel! It's used for so many things and one of the most painful things is Company Budgets. I know, you think that BI should look at history and budgeting and forecasting is done by other toolsâ€¦ you're wrong. But that's another blog.
So why does budgeting in Excel make me cry? Here are the three main reasons:
1. Complex Functions
Functions in Excel are excellent, just don't have a 6-level IF statement. Sometimes it seems like the only way to go but it isn't. It makes it really difficult for people to understand that function, even the creator who left a few months ago ..? Then you have that function that links to workbooks that are held on a drive .. the one that doesn't exist any longer â€¦
So how do you make the experience easier for people who are not Excel savvy? Write some macros of course! That's what I did and many others have done before me. Send out a work book with pretty buttons .. then wonder why people aren't happy. Lock down that spreadsheet so people can't edit cells that are calculations or used for links to consolidated reports. But you have to unlock some cells so macros are needed. Then you use a macro to automaticallyâ€ blast out an email of workbooks. And the code is bad, poorly tested and - well, just how many accountants do you know that can write a macro really well ..?
3. Manual copy and paste
Then you get all the spreadsheets back from the field and they are mostlyâ€¦broken! What The? All those macros and functions are gone or changed and none of the workbooks calculate correctly. Your consolidated reports don't work either so you have to fix all 65 workbooks manually. Again. Just like last year.
There is a better way â€“ CALUMO!