Layout optimization with spreadsheet — Why (not)?

There is a good chance that spreadsheet tools are among the most widely used tools in your company. What could be more logical, therefore, than to use such tools to optimize the layout of the assembly center, the warehouse, a production hall or even the whole company site? After all, there is a template for every conceivable task. Or so it seems. The following article sheds light on reasons why this ostensibly natural approach is less than perfect in practice.

a business man thinking about layout optimization using spreadsheets

Note: “Excel” is here taken as an example representing the whole spectrum of spreadsheet tools. The arguments are not applicable solely to the product Microsoft Excel.

Spoiler: Even though the article explains why there are more suitable alternatives to Excel for layout optimization, you will still find a collection of links to templates for layout sketching with Excel at the end.

It is very probable that you use Excel several times a week. Perhaps even several times a day. That’s not really surprising. Excel is installed almost everywhere, and everyone is familiar with its use (to a greater or lesser extent). Let’s look first at the positive aspects of a spreadsheet tool. Why is it apparently the tool of choice to solve every problem?

Benefits

Graphic representation

Excel is the absolute master when it comes to the visualization of tabular data. Pie charts, bar charts or line graphs — as an all-round tool, it offers a multitude of more or less appealing presentation options.

Accessibility and costs

Almost everyone has worked with Excel at some time in his or her career. The basic functions are well known, and a few hundred rows and columns are quickly filled with data. The software is present on almost every PC in the company, or is at least accessible in its basic form as a cloud application. There is no need to consult other departments of the company regarding licenses or access rights.

Simple familiarization

Thanks to its exceptionally widespread use, the tool offers a seeming sound basis for communication and collaboration by way of tables and worksheets. If assistance is needed, it is no problem to consult a colleague or the Internet.

Fast results

The sorting and filtering of information is practically instant, and the presentation of complex correlations belongs to the tool’s DNA. Half an hour already suffices to produce a quite respectable table, including the required analyses. If there are any outliers in the data, that is easy to solve with Excel. You can simply delete the inappropriate data, leaving all questions of data integrity aside.

Where there’s light, there’s shadow

Because it is so easy to get started with Excel, you will generally see your first successes very quickly. The workbook improves further with each new version, but also becomes more complex. Formulas are added, cross-references point to other worksheets, parameters are incorporated. In short: You have created a nice-looking Excel tool. You are proud of your achievement, impress your colleagues with sophisticated charts in meetings and presentations, and promote your tool within the company. As time passes, your colleagues also use your file and make their own copies.

You have worked painstakingly over many hours to link up large amounts of data. Perhaps you have even integrated macros, which at last make is easier for your colleagues to perform complex calculations and determine relevant parameters. Unfortunately, many users are not Excel experts.

One misplaced click, a brief interruption or distraction, simple mistakes which can happen at any time, and then suddenly the realization: “Why am I now in this strange formula?” Not everyone needs, or even understands everything in your carefully elaborated Excel document. It is thus only a question of time before “unnecessary rows and columns” are deleted. Formulas which “no-one understands in any case” are also disposed of without further ado. The typical opinion is then that “the missing stuff can no doubt be copied back from some other file.

Practical tip: The worst mistakes made when using Excel can be avoided if the user is aware of them. Common Spreadsheet Errors

You have probably tried to minimize the problems by locking certain cells and adding comments, or else you have created a comprehensive template which your colleagues can fill with data without prolonged pondering and without a deeper understanding of the underlying correlations. But when your Excel tool goes viral, the mistakes do so too.

The slippery slope to Excel hell

When a file in which the formulas and row/column structures have been edited (often accidentally) is passed on as a template, modified by further colleagues, supplemented with new individual formulas and corrected here and there, things start to become risky — even where this all occurred with the best of intentions.

Do you really want to rely on a layout of your overall production site which is based on nebulous formulas, macros and templates? Where you cannot even be sure that the visualizations are properly scaled, or that they provide a true picture of the real-world situation? With such thoughts in the back of your mind, can you honestly go into the next presentation to your colleagues and superiors with the necessary confidence?

At this point, at the latest, even the most sophisticated Excel template does more damage than good. A study conducted by Forbes indicates that almost 90% of all spreadsheets contain errors. You can browse some of the most serious “horror stories” from the business world on the website of the European Spreadsheet Risks Interest Group.

source: https://www.researchgate.net/publication/221525970_Agent-based_modeling_and_simulation_Desktop_ABMS

Excel was simply not designed for this purpose

It must be said that Excel is not per se a poor tool. And the worst outcomes can be avoided if Excel is used in clean processes and with carefully prepared templates, though that will naturally also limit the scope for creativity. This brings us to the disadvantages of Excel:

Susceptible to human errors

When you have access to an Excel file, it is a simple matter to manipulate formulas without noticing. Specification of an incorrect format, the accidental deletion of cells, errors when defining axes — almost every user interaction can lead to errors, many of which then remain undetected.

Time-consuming and too complicated

The aim of a layout optimization project should be to concentrate activities on elementary value creation. Only seldom will it be sufficient simply to enter a number in a table or to highlight data in a particular color. For most users, Excel is not the essence of their daily work, but just a tool which is expected to assist them in their tasks. Consequently, they are interested not in the inner workings of the tool, but rather in intuitive discussion and the solutions to specific questions. Excel, however, was not designed as a tool for the creation and graphical processing of complex layouts, or for testing of the plausibility of amendments suggested by shifting around machines, equipment or production areas.

Unsuitable for collaboration

Projects aimed at layout optimization, in particular, thrive on the collaboration within a team. A common, universally comprehensible view of the areas under analysis is essential. There are other tools which are far better suited for this purpose — Excel is not one of them. At the latest, when you want to view a layout in 3D to gain a realistic impression, even the most sophisticated template will fail to deliver.

“If I only possess a hammer, then every task becomes a nail.”

Layout optimzation using spreadsheets, is this really an option?

The way out of the Excel hell, therefore, is clear. It helps to tackle the challenge of layout optimization with the seriousness which this task deserves. The following frequently presented reasons should be questioned critically before starting creation of a new Excel template — and certainly before using one from an unknown source.

That is the way we have always done it

This killer phrase is encountered in practically every organization where people work together for longer periods. As layout optimization is a brand-new challenge, however, previously established tools are not automatically suitable. Furthermore, the task of layout optimization can only be tackled successfully by adopting the correct procedure. A template supplies at most the technical framework (as graph paper did in times past), but not the know-how as to the best approach.

Lack of time and personnel resources

Layout optimization is not something which can be accomplished in passing. Modern tools support the team, provide access to methods and focus on the essential aspects. Many companies shy the outlay for research, analyses and introduction — what remains is what is already in place, because there are no extra costs.

Alternatives are unknown

In the meantime, there are good overviews of the demands addressed by layout optimization tools, and they ideally include templates to make the process as simple as possible. A cost-benefit analysis may help with evaluation and selection. Established providers will also not leave you alone with any problems after your investment decision. They assist you with practical tips on building up support for the introduction of a suitable software package in the company.

Missing interfaces

At the latest, once you have developed an ideal layout and agreed the proposals with all relevant stakeholders, it will be difficult to actually implement the project using Excel. How are you supposed to convince the decision-makers if 3D visualization is not possible? Do you pass an Excel file on to the colleagues in the CAD department and then expect them to produce a real layout? And how do you reunite all the variants which are spread across dozens of separate files?

Conclusion

If your ideas regarding layout optimization go beyond just mini-projects, it is worth thinking outside your usual box. An Excel template may be adequate for the relatively trivial planning of a kitchen or a small apartment, using a fixed grid based on row and column formatting, but where this is not powerful enough, you should take time to explore alternatives.

Especially in the early phases of planning, a clean database can be the game-changer for analyses of floor space requirements, material flows and transport costs.

Want the latest articles about visTABLE® in your Mailbox?

No problem, simply subscribe to our Blog-News!

Those who cannot wait to try out a few templates directly — and to experience the limitations for themselves — are warmly invited to delve into the following collection of links:

Liked it? Share it:
Share on linkedin
Share on xing
Share on twitter
Share on email
Recent articles
Content
Like this content? Receive more of it straight to your inbox!
Share on linkedin
Share on xing
Share on twitter
Share on email
Receive professional knowledge by e-mail

Liked This Article?

We have a lot more articles! Join the visTABLE® subscribers and stay ahead.

By entering your email, you agree to receive future messages
about visTABLE® and have read the Privacy Policy.

André Heller
André Heller

Hello, I am glad that you are interested in my article.

Do you wish to be informed about new content?