Is Excel Cost Modeling a Path to Failure?

It's hard to imagine a company these days that doesn't use Excel. Not only are we very accustomed to using it, but it's also a powerful tool that has gained more and more functionalities over the years. So, if we are so familiar with it and it's so powerful, why not develop the company's cost model in Excel and save the cost of a specialized tool? Sounds logical, right?... well, experience says not so much.

Reasons why an Excel cost model is destined to fail

1- Mixed-up data and formulas

In Excel, data and formulas are on the same sheet. If, for example, I have a table with 1000 records where calculations are performed in the same row, I must ensure that when adding more records, the formulas are also copied for the new rows. Another headache occurs when data is deleted and the cell references are lost in the formulas, the dreaded #REF!, which forces us to review the formulas again and find out where the error is coming from. In a costing tool, all the calculation logic is encapsulated so that my only concern is updating the new data without having to worry about "the formulas".

2- Lack of referential integrity

The term referential integrity comes from databases and refers to the validation of codes that are not in the master data. I know it sounds complicated, but you'll understand it with an example. In an Excel model, data is shared across multiple sheets or tabs of the workbook. If in my item catalog I have one with the code AEDG01 and I enter it on another sheet as AEDGO1, Excel won't give me any alert, but a system with referential integrity won't let me enter it. Did you notice? Look at it again! The zero changed to an

uppercase O. Silly error like these waste time and cause a lot of frustration trying to find out "where is the discrepancy?" or "Why isn't the cost allocated 100%?" With a system with referential integrity, you don't even have to worry about this error.

>> How to maximize the Return on Investment (ROI) in your Projects <<

3- If the drivers are dynamic, it adds a difficult complexity to manage

Drivers are what allow us to assign costs, distributing them proportionally to the driver values. Let me explain, suppose I want to allocate the cost of the IT department to three departments using the driver "Number of users per department". By doing so, the department with more users will be assigned more IT cost and vice versa. Now, if I wanted to change that driver and assign it by "Number of licenses per department", in Excel, I would have to do this manually by adding each new driver value and replacing the previous value. This change could be made dynamically by adding formulas like VLOOKUP and HLOOKUP, but it would add unnecessary complexity to Excel and we would go back to what was mentioned in point 1, as well as performance issues, leading us to the next point.

4- Poor performance

While it's true that computers have greatly increased their memory and processing power, it's also true that very large Excel files with sheets with many rows become heavy to calculate. Just to give you an idea, a typical model where we have a catalog of 90 account codes assigned to 100 cost centers, which are assigned to 20 departments, and these in turn are assigned to at least 3 processes each (90 x 100 x 20 x 3 = 540,000 assignments) and each assignment involves drivers where, apart from their value, we also have to calculate the percentage of each one. Well, there are many calculations even in a small model. This is where Excel starts to have performance issues, and this large volume of data exacerbates the problems mentioned in the previous points.

5- Limitations for running scenarios

As I mentioned before, changing drivers is a manual task, and moreover, an Excel cost model is distributed across several sheets of a workbook, which means that if we want to try different scenarios, there are many points where changes need to be made, and they are all manual! Another option, the most common one, is to make a copy of the Excel workbook and make changes for the new scenario. The problem here is that in the following period when we want to replicate that same scenario, now we have two files to update instead of one, where the task is not only updating but also ensuring that both files are consistent. And if there are more scenarios, I don't even want to think about it.

>> How to optimize my cost structure from a B2B approach <<

6- Limitation in reporting

Closely related to the previous point, having information on so many spreadsheets in the same workbook makes it very difficult to generate reports because the information is not only scattered but also not consolidated to the level of detail required. In these cases, the typical solution is to copy the information into additional sheets and process it manually to obtain the data required for the report. Despite being the same model, there are "many versions of the truth" because in this "copy-paste" and filtering, it's easy to leave out data along the way.

7- Data security

The confidential expense data of the company will end up in an Excel sheet that anyone with a copy of the file can access. Sure, you can put a password on the file. It's also true that on YouTube, in about 5 minutes, they explain how to "hack" an Excel file. If security is important, there's no better option than using a costing tool that provides adequate access control.

8- Costly model maintenance

Costly! But if the Excel license is very cheap. I wanted to leave this point for last because if there's a reason why a cost model fails, it's because of its high maintenance cost (measured in effort in hours). Let's say you can live with the previous points by being very careful with data entry and formulas, maintaining an adequate level of security, and dedicating hours and hours to generating and updating reports. However, there's one point that you will never get rid of, and that's the enormous effort required to update an

Excel cost model. You might not believe it, but it's almost like doing it all over again. Don't believe me? A huge amount of time is spent loading data, updating formulas, but where most time is consumed is in locating discrepancies. Is it possible that not all data has been loaded? Do we have a typo in the codes? Are we using the wrong driver? Did the driver have no data for this period? Or is it that the formulas are pointing to the wrong cell? Or all of the above?

>> How to create a business model canvas? <<

Conclusion

As you may have noticed throughout the article, creating a cost model in Excel may sound very appealing at first, but believe me, it will end up being a nightmare. I've been there already. An Excel cost model works very well as an academic exercise to understand the methodology, but it's fatal as the definitive model. With a longer-term vision and aiming to have a permanent model that reflects what happens in the organization, there's nothing like having a dedicated software tool for it, of course, linked to an adequate implementation of costing methodology. At ICX, we'd be happy to help you implement your cost model.