Framework and preparations

In the previous part we quickly laid out what we want in this file and how we are going to approach it. In this first part we are setting up the file and adding the basic things. As discussed in the previous part, we will be using the Google suite. If you want to follow along in Windows + Excel you can do so as the same practises apply.

So first open up google drive and make a new folder (wherever you want). As a general rule, I always like to have everything that I use for a given project together. So this means that within the project folder I make the following subfolders:

• Assets: I mainly use this for image files like logo’s, headers etc.
• Forms: this is something we need in a later stage in this project.

At this point we can also make a new Google sheets file (right click and select Google sheets). A new browser window will open and you can name your file in the top left hand corner.  So if all went well we should see the following image.

File structure

With a file that is quite complex it always helps to work structured. In this case we will be using the follow structure within the google sheet file:

FunctionOverview – This is the visible page where we can interact with the information
>Function_Helper – this is the helper sheet where we make calculations or add function specific content
>Function_Form – this is not always needed, but a great way to gather data.

The helper sheet

First we have to start giving Sheets some general knowledge we would like to use. In order to do so we are making a new sheet (the + button in the lower left hand corner) and name it Helper. Think of this sheet as the general options menu in a normal program, so everything we might want to add or change later we can do so here. In later stages we will also build specific helper sheets for different functionality of the file as well.

In this sheet we start by writing down all the names of the months starting in cell A1 (if you type January in A1, February in cell A2, you can then select the 2 and You’ll see a small blue box in the lower right corner. Drag the blue box any number of cells down to auto complete).  For the other columns we write the following information:

• column B we write the corresponding periods / month numbers (January = 1, February = 2 etc).
• column C we write the corresponding Quarter (Q1, Q2 etc)  
• column D quarter + month number in that quarter (Q1-1, Q1-2 etc).
• column F we write the years we want to cover in this file. For me I start at 2017 as I am migrating data from an older file.

We are almost ready setting up the file, at this stage we only need to add TAX information. I put the tax description in column I and the corresponding percentage in column J. If you need to add other forms of taxes (or other fixed percentage variables for that matter) I would add them in this section. At the minimum, we all need to take VAT into consideration. As I am working from Spain I am also obligated to calculate IRPF (SPanish income tax).  If all went well your helper sheet should look the same as mine.

Named Ranges

To make our life a bit easier later on we will use named ranges.  When setting up a named range we can reference a name (rather than a range) in formulas. In some cases we will also use the named ranges to easily create drop down menus. In the helper sheet we make the following named ranges:

* you might have other ranges in your sheet depending on the information you have entered. If you need to make another named range, for this you can think of your own (unique) name.

To enter a named range you select the cell(s), right click and click Define Named Range… A sidebar will pop out and you can name the range.  Do this for all the named ranges we have determined above.

Optional preparations

Before we add more sheets to the file we could make a header that we could use for the different overviews we will make. I like to make one now as it gives me time to think about the layout and colors I want to use. This header can be copied so that we have a uniform experience while switching sheets once everything is completed.

As this step is completely optional (and cosmetic) you are free to make your own design. In my case I started with a new sheet and then change around the height and width of the following rows/columns

After this I make row 3 grey and merge cells B2:E2 and color the cells in light grey. I also add my logo to the top right hand corner. In the end, mine looks like the image below.

With this completed we can now just simply right click on the tab and click duplicate to make a copy in which we can build our overviews in the next steps.

A few notes on this:

• Make sure you do not make the area to tall as it will definitely interfere with your user experience.
• Try to think ahead as we will need to change our column widths
• If you are working in MS Excel try not to use oversized images as it tends to make the file size rather large.