10 mistakes people make in Cash Flow Models

Excel doesn’t make mistakes – people do

Businesses are run on spreadsheets.?But when studies show that nearly 90% have at least one error, this is a problem.?Decisions get made that cost millions and are discovered later.?And much as we’d love to blame the technology, you can be sure it is human error.

Here are some common errors that are made when building or working on a cash flow model for businesses.

1.??External links – a dangerous weapon

External links can be good when used properly.?Maybe they cannot be avoided.?Large file sizes, or different users “championing” different parts of the process are reasons why you may not be able to avoid using them.??

2.??SUM range omitting cells

Usually, this happens when you add additional rows, and then forget to update the sum formula.?It is one of the most common issues.

3.? Not setting out all the inputs and assumptions in one place (or grouped into tabs if there are a lot of them)

Cash Flow models and spreadsheets in general need a flow; starting with inputs, running through calculations, and then outputs.??

4.??Selecting the entire column/row when formatting

Do your Excel models blow up in size and suddenly you find the loading and saving time is really extending??Files over 20MB usually have one thing in common.?Formatting issues.?And the biggest one is when a user copies a format the entire length of rows and columns.??

5.? Adding new lines on one sheet, but forgetting to do so in a Summary Sheet

So, you’ve got a new cost to weren’t previously aware of.?Buggy whips are required for the project… who knows why, but the project manager has figured out you can’t do without them.?

6.?Overwriting formulae with hardcoded numbers

Have a system and stick to it.?If you’re updating actuals for the month and replacing forecasts, it is best to link to another spreadsheet and consistently change the whole column, as the status of that month changes from “forecast” to “historic” or “actual”.

7.??Not applying data validation to cells

Data validation allows the creator of the spreadsheet to restrict the type of data or the values that users enter into a cell. Often this will be a drop-down list.

8.? Not locking the spreadsheet

Of course, someone needs to control the protection passwords, but most users should only be able to adjust very specific cells.?This maintains the integrity of the model over a longer period.

9.? Not doing checks and reconciliation

One way to ensure you don’t make mistakes like omitting cells in the sum range, is to have checks and reconciliations throughout your spreadsheet.?It is also worthwhile having a single tab that checks that there are no errors in the rest of the model for easy monthly auditing.?

10. Not having an audit tab for checking that everything passes the “smell test”

It is always wise to cross-check calculations to make sure they make sense at a high level. This is the best step in avoiding all of the mistakes listed above.

Read the full article here: