Improved Data Structure

Tables rule

I ran a webinar in January 2019 where I presented and explained a budget challenge file I had submitted in November 2018. I mentioned during the session that I didn’t like the layout of the Data tab. Well someone asked how should it look? So here is how I would have arranged it.

Click here to watch the webinar recording and download the materials.

Below is the Data sheet layout.

Now the above layout is easy to print and it was for a challenge where people might need to print out the sheet to become familiar with it.

In the real world being able to print out a Data sheet shouldn’t drive a layout for your budget.

I would rearrange the Data sheet like this. The left side and right side are shown separately below.

As you can see the tables are separated by blank columns and no table is under another. As tables expand we shouldn’t have to worry about ”bumping into” another table.

Having a blank column between them makes it easy to select each table individually using Ctrl + a.

The sheet ends up being wide.

This structure is consistent. I have transposed some tables to be consistent.

All the sectors go across the sheet. If we need to add a new sector we just insert whole columns in each of the tables.

All the regions and products go down the sheet.

If we need to expand the tables there is nothing below the tables that will be affected.

The tables are positioned by order of importance. To me the most important table is the Demand calculations.

Then the demand is split up into regions and sectors. Then the sectors are split up into months. Then the sales are split up by products.

Then comes the costs data. Fixed costs are the least important.

Note is all the tables the sequences were consistent (this was also the case in the original sheet). In general the sequences of the dimensions you use should be consistent throughout the file.

I added two tables on the right that relate to my solution which relied on using codes for Sectors and Regions.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.