Separate Input & Output
When you read a book or a report or a website, we read from top to bottom and from left to right. Remember that setup when you create a spreadsheet and follow this structure, both within a worksheet and between them. We advise to distinguish separate input & output sheets as well as sheets for calculations, documentation and dashboards.
The Rule of Thumb
Did you ever get a visit from an accountant? Did they check that one important spreadsheet containing the investment plans? Maybe they asked questions like: What are the input values? What are the assumptions the calculations are based on? Can you give an example of a calculation? Where are the conclusions?
These are moments you will benefit from a clear Excel model. The rule of thumb is: Separate input & output. That really improves readability. When we zoom into this further, we reach these types that a worksheet can have:
Use 1 table per worksheet in input sheets. Make a difference between dynamic data that is gathered from outside systems, for example imported data from a CMR system, and data within the spreadsheet such as calculations. Place references as close to the data as possible and make them explicit, for example by using a color coding. That makes the accountant happy.
In the calculation worksheets you store information which does not change day to day. These worksheets are sober and purely functional, and use input data to draw conclusions. Remember that a spreadsheet stays in use for about 5 years, so things can change also in calculation worksheets.
In the output sheets, we give an overview of the conclusions that can be drawn from the input data and calculations. Use tabels, charts and nice layouting. Also think about making worksheets printable by changing the margins. This is even more important for higher ranked managers.
There is one special category of worksheets, those meant for documentation. Use them to explain the functionality of calculations and the source of input data. Other sheets like this are version control sheets and index sheets.
Some spreadsheet users love the use of so-called dashboards: worksheets that contain input, output and calculations together. While dashboards disrupt the flow of data, and the clear separation of input and output, they can be very insightful when modeling dynamic processes.
We do advise to minimize them, use maximum 1 or 2 (depending on the size of the spreadsheet) and make it clear this is a dashboard worksheet.
It is important to recognize different roles a worksheet can play, and make deliberate choices when creating a spreadsheet. We often encounter mixed sheets that are messy and confusing.
Do you want to learn more about the flow of your worksheet? Create a free PerfectXL account now to visualize the dependencies between worksheets in no time.
The image shows the visualization of a spreadsheet in PerfectXL. Read more about the spreadsheet visualization feature.