What is the hidden risk in your company?

What is the hidden risk in your company?

I was recently talking with a friend who was presenting on “Risks within Your Business” to company directors. This topic made me think of all the companies that I had worked with over the years and what was the most significant unrealized threat they faced within the organization. Upon reflection of all of 2 minutes, I concluded that it was Excel!

Yes, it is Excel! Excel has taken over our corporate environments where it is used by finance, marketing, sales, HR, etc. to do everything from planning, budgeting, forecasting, pricing, reconciliation, and analysis. While Excel is a marvelous tool, which undoubtedly made many of these tasks much more manageable, the problem is that anyone can use it and change a model without anyone else realizing it.

There are three areas of concern:

1. Incorrect models.
Many people within organizations develop Excel models to solve problems that they are currently facing. However, many of these models are built using the improper practices that I’ve highlighted in previous blogs. As a result of these practices, other users of the models are not always aware of the model’s shortcomings but still rely on the output to make significant decisions.

In one case, the model’s author had linked all but two of the assumptions to the assumptions page, thus changing the assumptions all but two of them change within the model. As a result, no one was aware that these two assumptions when “off-line” for the model and were making decisions based on incorrect conclusions. While appearing to be an insignificant problem, the lack of connection of these assumptions led to the approval of some projects because they had positive NPVs and good IRRs, but in reality, were terrible projects with negative returns.

2. Hard-Coded Models.
Many models are built using the corrupt practices I have mentioned before. While I have come across many of these, I cannot meet the example given by my friend Rob Brown in his blog post. To have an organization making decisions with an error in their spreadsheet of the order of magnitude of $200 million that no one was aware of is scary. Do you have one of these in your company? Before you answer “No,” are you sure!

3. “Fixed Models.”
More worrying are those models where there is a good model, but then hard-coded change is made to prevent a #N/A or #DIV/0! result. Unfortunately, no one tells anyone about this change, and it left unnoticed for ages. As a consequence, massive errors can arise.
Recently I was working with a company whose budgeting model had some hardcoded adjustments in formulas to adjust sales, compensation, and other budget items. When reviewing this model with the company’s finance department, there was no explanation of why they made these changes. When asked how good the design was for budgeting, the answer I received was, “Last year was a horrible surprise!” As a result, I wrote CEOs Beware: Problems with Financial Modeling. At last check, the company was still using the model!

Recently I was working with a Fortune 500 company that had a model to drive dashboards for the executive team. Somewhere the data had not all been supplied, and so there was a #DIV/0! result. Rather than fix this, someone had changed some formulae to exclude the offending cells and their related data. However, when entering proper results into the offending cells, the dashboard didn’t update to reflect this. The dashboard continued to exclude these outcomes in the actual and historical averages. Who knows how many ad campaigns and how much marketing spend resulted in improving the results when the results were probably already exceeded expectations.

So what to do?
Companies, departments, etc. should review all their models regularly. This review involves someone checking all the calculations, cell references, and links to ensure that the model is performing as expected. A painful task, but undoubtedly better than to keep walking off the cliff, hoping you can survive the landing.

 

© 2015 Marc Borrelli All Rights Reserved

Recent Posts

Do You Truly Know Your Core Customer?

Do You Truly Know Your Core Customer?

Knowing the profit of your core customers is key to building a growth model. Many companies have identified core customers that are generating a sub-optimal profit and so they cannot realize the profits they seek. Identifying the correct core customer allows you to generate profits and often operate in “Blue Ocean.”

The Greatest Own Goal or the Greatest Collapse

The Greatest Own Goal or the Greatest Collapse

The European Super League collapsed within days of launch due to hubris and the founder forgetting the key parts of their business model, value creation, sales, and value delivery. The collapse might bring a high price.

Does Your Financial Model Drive Growth?

Does Your Financial Model Drive Growth?

Working with many companies looking to grow, I am always surprised how many have not built a financial model that drives growth. I have mentioned before a financial model that drives growth? Here I am basing on Jim Collin's Profit/X, which he laid out in Good to...

COVID = Caught Inside

COVID = Caught Inside

As we emerge from COVID, the current employment environment makes me think of a surfing concept: “Being Caught Inside When a Big Set Comes Through.” Basically, the phrase refers to when you paddle like crazy to escape the crash of one wave, only to find that the next wave in the set is even bigger—and you’re exhausted. 2020 was the first wave, leaving us tired and low. But looking forward, there are major challenges looming on the horizon as business picks up in 2021. You are already asking a lot of your employees, who are working flat out and dealing with stress until you are able to hire more. But everyone is looking for employees right now, and hiring and retention for your organization is growing more difficult.

Why is there not MORE common sense

Why is there not MORE common sense

“Why don’t they use common sense?!” You may have said this phrase yourself, or heard it with your managers, when discussing an employee’s actions. However, the frustrated appeal to “common sense” doesn’t actually make any meaningful change in your organization. We all make decisions based on the information we have and the guides we have to use. So if the wrong decisions are being made in your organization, it’s time to examine the tools you give decision-makers.

Do You Understand Your Costs to Ensure Profitability?

Do You Understand Your Costs to Ensure Profitability?

You can only determine profitability when you know your costs. I’ve discussed before that you should price according to value, not hours. However, you still need to know your costs to understand the minimum pricing and how it is performing. Do you consider each jobs’ profitability when you price new jobs? Do you know what you should be charging to ensure you hit your profit targets? These discussions about a company’s profitability, and what measure drives profit, are critical for your organization.

Sunk Costs Are Just That, Sunk!

Sunk Costs Are Just That, Sunk!

If you were starting your business today, what would you do differently? This thought-provoking question is a valuable exercise, especially when it brings up the idea of “sunk costs” and how they limit us. A sunk cost is a payment or investment that has already been made. Since it is unrecoverable no matter what, a sunk cost shouldn’t be factored into any future decisions. However, we’re all familiar with the sunk cost fallacy: behavior driven by a past expenditure that isn’t recoupable, regardless of future actions.

Do You REALLY Know Your Business Model?

Do You REALLY Know Your Business Model?

Bringing clarity to your organization is a common theme on The Disruption! blog. Defining your business model is a worthwhile exercise for any leadership team. But how do you even begin to bring clarity into your operations? If you’re looking for a place to start, Josh Kaufman’s “Five Parts of Every Business” offers an excellent framework. Kaufman defines five parts of every business model that all flow into the next, breaking it down into Value Creation, Marketing, Sales, Value Delivery, and Finance.

CFOs Beware: Problems with Financial Modeling

CFOs Beware: Problems with Financial Modeling

Through my work, I have dealt with thousands of Excel, and for those that remember Lotus, financial models, and I have to say that most of them are wrong. I want to summarize five of my least favorite things about the “bad” models I have had the unfortunate experience to deal with during my career. All of these issues, except the last one, are sufficient for me to walk away from the work or opportunity as they indicate that I am likely to find more problems in the finance area or business.

Excessive precision.

When I see models with numbers in the millions and show accuracy to the dollar, or even worse, the cent, red flags fly. Just because Excel can calculate to ten plus decimal places, you don’t need to use it. That much precision is a distraction and is usually wrong, especially if it is a forecasting model. In the words of Niels Bohr, “Prediction is very difficult, especially about the future.” Thus models that predict amounts to the dollar a year away are wrong!

Ask yourself what level of accuracy is needed. If you are dealing in tens of millions and show numbers to the thousand, the error level is 0.01%, which is more than enough precision. Another test is, would you be willing to bet on the outcome being right to that level of accuracy, and if not, ask why you are showing it. Remember the adage, “I would rather be 90% right and imprecise than precise and 100% wrong.”

Hardcoded numbers.

Models that have hardcoded numbers in them also raise red flags. Users forget they are there, and they remain forever with no rhyme or reason, leading to wrong results. If things change, it is hard to find them and correct them in all the cells that need changing. Another issue with hard-coded numbers is where a correct model produces #N/A or #DIV0 results due to the values entered, and the model cannot adjust. To fix these, instead of using Excel logic statements, users change the formulas to exclude the offensive input. However, when the correct data is obtained and entered, everyone has forgotten about these changes. As a result, I have seen large companies use fatally flawed models in planning, but no one has realized it. Finally, requiring hardcoded numbers to change a model to get it to produce correct results could mean the model is fatally flawed in design and operation – time to start again.

No tracking of results.

Recently I met a firm that had an Excel model it had been using for all its forecasting for years. I inquired about how accurate it was, and the answer was that the last month had been a huge surprise, but no one had ever tracked its results against actual. If the model’s results are not measured, its effectiveness is unknown, and no improvements are made. Thus one could be relying on something wrong for years and not realize it. No model is perfect, and they are like an iterative process, use them, measure the results, and then adjust them to improve the results.

Understand the logic of the model.

Understand what the model is trying to accomplish. It is often good to diagram out how all the parts will fit together and where the different components will reside in the model. Also, think through all the pieces carefully about how they work and what they do. It is my experience that usually 6 – 10 items contribute to the majority of the values, and so they need the most precision; the rest will not change things much, and we don’t need to focus on those as much. Also, beware of some essential items, i.e., exchange rates.

Another Excel model I recently reviewed had no exchange rate assumptions even though the company had significant European operations – this oversite would lead to incorrect forecasting. Finally, remember all the parts! The complex financial model used business forecasting mentioned above showed P&L, Balance Sheet, and Cash Flow statements; however, the model didn’t distinguish between book and tax depreciation, leading to incorrect cash flow statements well as errors elsewhere.

Layout.

A model should be like a book or an essay – an introduction – the characters – the plot – the conclusion. I have seen everything mixed up, and you cannot follow the logic or flow, which makes it hard to read and understand. Make the model easy for the user to follow and read. Thus, I would recommend:

  • A tab that explains what the model does and describes what each tab does as well.

  • Color Coding. Use color-coding to distinguish between input, calculation, and output areas.

  • Indentation. Use the cell indents for indentation, not another column

  • Colors. Use colors to format types of cells. It makes it very easy to see what are inputs, assumptions, outputs, etc. If all input cells are yellow, then the user can quickly know what to change. Excel provides this through cell formatting.

  • Data Validation. Use lists to control user inputs, which prevents mistakes from accidentally happening.

  • Spell check. Excel provides it, so use it.

  • Make it clean and easy to read.

  • Tabs, Use more tabs with a purpose rather than one colossal tab that is difficult to navigate around. Break tabs down into Assumptions, Working, Results. These tabs make it much easier to follow.

  • Assumptions must feed the model. All assumptions should feed into the appropriate parts of the model – just showing them on an Assumptions page is useless. Remove any cells that contain assumptions that don’t feed into anything or do calculations. Users will change them to no avail.

  • Check cells. Use check cells to ensure that things are correct. If you are building a forecasting model, put in a line showing that the balance sheet balances. Thus if it doesn’t, you can easily see it rather than suddenly realize it when it is too late.

I hope that you find this useful. Good luck, forecasting.

 

© 2013 Marc Borrelli All Rights Reserved

Recent Posts

Do You Truly Know Your Core Customer?

Do You Truly Know Your Core Customer?

Knowing the profit of your core customers is key to building a growth model. Many companies have identified core customers that are generating a sub-optimal profit and so they cannot realize the profits they seek. Identifying the correct core customer allows you to generate profits and often operate in “Blue Ocean.”

The Greatest Own Goal or the Greatest Collapse

The Greatest Own Goal or the Greatest Collapse

The European Super League collapsed within days of launch due to hubris and the founder forgetting the key parts of their business model, value creation, sales, and value delivery. The collapse might bring a high price.

Does Your Financial Model Drive Growth?

Does Your Financial Model Drive Growth?

Working with many companies looking to grow, I am always surprised how many have not built a financial model that drives growth. I have mentioned before a financial model that drives growth? Here I am basing on Jim Collin's Profit/X, which he laid out in Good to...

COVID = Caught Inside

COVID = Caught Inside

As we emerge from COVID, the current employment environment makes me think of a surfing concept: “Being Caught Inside When a Big Set Comes Through.” Basically, the phrase refers to when you paddle like crazy to escape the crash of one wave, only to find that the next wave in the set is even bigger—and you’re exhausted. 2020 was the first wave, leaving us tired and low. But looking forward, there are major challenges looming on the horizon as business picks up in 2021. You are already asking a lot of your employees, who are working flat out and dealing with stress until you are able to hire more. But everyone is looking for employees right now, and hiring and retention for your organization is growing more difficult.

Why is there not MORE common sense

Why is there not MORE common sense

“Why don’t they use common sense?!” You may have said this phrase yourself, or heard it with your managers, when discussing an employee’s actions. However, the frustrated appeal to “common sense” doesn’t actually make any meaningful change in your organization. We all make decisions based on the information we have and the guides we have to use. So if the wrong decisions are being made in your organization, it’s time to examine the tools you give decision-makers.

Do You Understand Your Costs to Ensure Profitability?

Do You Understand Your Costs to Ensure Profitability?

You can only determine profitability when you know your costs. I’ve discussed before that you should price according to value, not hours. However, you still need to know your costs to understand the minimum pricing and how it is performing. Do you consider each jobs’ profitability when you price new jobs? Do you know what you should be charging to ensure you hit your profit targets? These discussions about a company’s profitability, and what measure drives profit, are critical for your organization.

Sunk Costs Are Just That, Sunk!

Sunk Costs Are Just That, Sunk!

If you were starting your business today, what would you do differently? This thought-provoking question is a valuable exercise, especially when it brings up the idea of “sunk costs” and how they limit us. A sunk cost is a payment or investment that has already been made. Since it is unrecoverable no matter what, a sunk cost shouldn’t be factored into any future decisions. However, we’re all familiar with the sunk cost fallacy: behavior driven by a past expenditure that isn’t recoupable, regardless of future actions.

Do You REALLY Know Your Business Model?

Do You REALLY Know Your Business Model?

Bringing clarity to your organization is a common theme on The Disruption! blog. Defining your business model is a worthwhile exercise for any leadership team. But how do you even begin to bring clarity into your operations? If you’re looking for a place to start, Josh Kaufman’s “Five Parts of Every Business” offers an excellent framework. Kaufman defines five parts of every business model that all flow into the next, breaking it down into Value Creation, Marketing, Sales, Value Delivery, and Finance.