Yes in this blog I’m going to prove that process automation in your business can improve your sex life. This statement is actually based on a case study that happened several years ago.

The Challenge

My client was an international finance company based in London and Krakow (Poland). They had two directors – one based in each location. The London director looked after sales, whilst the Polish director foused on service delivery.

Once a month, the two directors held a meeting in which they reviewed the business performance. Prior to the meeting, the directors undertook a manual reconciliation process that compared the actual performance with the expected performance. However this was:-

  • an incredibly time consuming process taking 8 hours
  • invariably wrong due to the amount of data entry involved

In addition, as the process was so cumbersome it created considerable conflict between them. Indeed the business was stagnating because of the lack of having clear understanding of the numbers.

How we acheived process automation

Fingertips Intelligence implemented process automation by consolidating the existing spreadsheet systems into a single spreadsheet so that all the data flowed through the workbook. This was in contrast to the original process where the data had to be entered several times – which invariably resulted in errors.

Following the process automation, the updated spreadsheet:-

  • was instantaneous – immiediately saving them 8 hours a month
  • gave them an accurate picture of the business
  • contained graphs as well numbers so they could easily see what was happening on their business
  • allowed them to focus on growing the business rather than arguing with each other.

And Finally …

Having eliminated a very manual and cumbersome process, through process automation the directors were able to see the true state of the business and focus on growing it steadily rather than arguing with each other. Consequently they brought the business back from the brink, and turnover improved.

So why is an automated spreadsheet process benefical for your sex life ?

Well once they automated the most tedious part of their business, they weren’t so stressed. However as the turnover increased, the directors wives were a lot happier ….

Introduction to Dynamic Charts

Most excel users know how to create Excel charts by selecting a group of cells and clicking on one of the chart designs at the top

.

 

In this case, the chart is easy to produce because we only have one row of data covering just 10 months. However, what about if we had the data below :-

We have 17 rows of data and in fact the data goes up to 2019, so there are over 60 columns of data. The initial temptation might be to draw 17 graphs – which would probably result in either “Hell with Excel” or analysis paralysis.

However, Excel offers another solution – dynamic charts – where you can select the data item (e.g actual turnover) from a dropdown, and the chart updates with the values from the relevant field:-

So In the chart above, the user can select up to three options and the chart will pull through the values automatically. This is another big advantage of dynamic charts – it is very easy to do adhoc comparisons of data.

In addition, the solution permits the user to select which year they want to view. Hence if we have  several years of data, and only want to watch a snapshot, the user can select the relevant year from the dropdown. We have set it up so that the user can compare the selected year with the previous year.

How to create a dynamic chart

The key to creating a dynamic chart is the way that the data is set up. In particular it should be a single tranche as per the diagram above. One thing I would recommend is to extend the time period so that it includes say the next 3 years from the outset:-

 

This means that the person doing the data entry won’t have to come back to you each year to update the formulae.

There are three key stages in setting up a dynamic chart:-

  1. Creating the data entry area – where we specify the year and drop downs for the values.
  2. Setting up a mini data table – which uses the values from the drop downs to pull the data from the main tabl
  3. Set up the graphs using the values in the mini data table.These are explained in more detailed below.

Step 1 – Creating the data entry area

In this stage, we set up the interface so that the user can specify the years and create the drop downs. Note for simplicity, we will keep everything on the same sheet.So in cells B28,B29 and B30 enter the values Year, Option 1 and Option 2 as per the screenshot below:-

 

 

For simplicity, we just going to do a dynamic chart that allows the user to compare two values. However, it can easily be extended to have additional values.

The next step is to set up a couple of named ranges called “Graph_Options” and “Years” respectively:-

Named ranges can set up via the formulas option on the main menu. In this case,the named range consists of the values in the range B6:B22 – which are the possible options for the graphs.We also need to set up one for the years. In this case we need a blank area of the sheet – which the user won’t see (I often use a separate sheet) and type in the consecutive values for the years:-

 

Observe that we start the values for the year from 2015 rather than 2014,as in the dynamic chart we are comparing one year with a previous year. So when we select 2015, the previous year is 2014.

Now go back to the left hand side of the sheet, and use data validation to restrict the values that can be entered in cells D28 (for the year) and D29 & D30 for the graph options:-

 

Then use the drop down to select the values. For now we will select the values 2015, Actual Turnover and Forecasted Monthly Turnover:-

 

 

That completes setting up the data entry part.

 

STAGE 2. Setting up a mini data table – which uses the values from the drop downs  to pull the data from the main table.

The first part of the data table is to create the monthly timeframe. Using the date function, we enter the formulae above in cell D34. This sets up a link to the 1st of January of the selected previous year. So if cell D28 has the value of 2015, then the formulae above gives the value 1/1/2014.

We now need the next 23 consecutive months in our formulae. Excel’s EOMONTH formulae can be used to specify the end of a month whose value is stored in another cell. So for example, if we have the date 23/04/2015 in cell C12 then putting the following formulae in D12:-

D12 = EOMonth(C12,0)

Will return the value 30/04/2015 – the end of the current month. And if we want the first of the next month we just add one:-

D12 = EOMonth(D12,0)+1

Which returns 1st May 2015.  This is how we start to create the time line for the graph :-

And we now need to have a slightly different formulae in the adjacent twenty two cells to get the full time line for the dynamic chart:-

 

And now we drag this third formulae to the right to complete the time line – which should be 24 columns showing adjacent months.  Altering the value in D28 will automatically adjust the values in the timeline.

The next step is to incorporate the two axis into the data table. These are just cell references to the values in cells B29 and B30. So in cells b36 and b37 put “=b29” and “=b30” respectively:-

 

The next step is the most challenging – populating the mini data table using the values from the axis and the timeline. It uses a combination of the OFFSET and MATCH functions to do this. The OFFSET function allows to return cell values relative to an “anchor” cell. The MATCH function returns the position of one value within another range.

Enter the following formulae in cell D36 – which will be the intersection of the first month and first axis option in the datatable:-

=OFFSET($C$5,MATCH($B36,$B$6:$B$22,0),MATCH(D$34,$D$5:$DF$5,0),1,1)

And now drag this formulae across both axis of the time line – to complete the values in the datatable:-

 

 

If you use the dropdowns in cells D28, D29 and D30 then the data table should update automatically.

  1. Set up the dynamic charts using the values in the mini data table.

This is the final part of creating the dynamic charts – actually creating the chart. Highlight the first row of the datatable and insert a standard line chart:-

 

This is the first axis in our graph. We can click on the graph, and select the data source to specify the second axis:-

Closing the pop up, we now have a dynamic chart – that allows us to select two rows from the main data table and compare.