My Convenience, Your Tedium.

Sometimes we try to do too many things in a single spreadsheet or template. When faced with creating a template for data-entry and a table for presentation, many of us tend to take the shortcut, entering data directly into a table formatted for presentation. Sometimes it works, sometimes, it just doesn’t. When it doesn’t, you’ll need to prioritise one over the other, or even split them up, handling data entry and presentation separately.

Credit: Shutterstock

Once a year, I need to fill in a massive spreadsheet for the company’s annual budget. Like many large, international cooperations, we adopt the best practices and have a detailed budget broken down by months and business segments. I have a fictional re-creation of the template below:
Jan Segment A Jan Segment B Jan Total Feb Segment A Feb Segment A Feb Total
The template, protected to prevent unwanted tempering, is clearly designed for presentation: beautifully formatted and laid out for printing on A3 paper in microscopic font. It is also horrendously tedious to update, requiring me to link every one of the hundreds of cells to my workings INDIVIDUALLY, especially when I am only in charge of 1 single business segment!
This is an example of a lazy template design, where data is entered directly into a table designed for output, and the ease of data entry is sacrificed for ease of presentation.
I’m sure that if I think hard enough, I can think of many other examples where my laziness had been the cause of grief for my colleagues.
Not every instance of data entry needs to be separated from data presentation. The general rule is that the more complex the idea you are presenting, or the larger the data that you are managing, the more efficient it is to separate data entry from presentation.
For example, if you are just going to present quarterly sales numbers for your company in a table, entering the figures directly into the presentation is just fine, and in fact, efficient. However, if you are going to present quarterly sales by geographic region, market segment and product type, as well as projections for the future using some complicated model, you’d be better off separating the data entry, projection model and presentation into different sections and handle them separately.
Have you faced poor interface and template design recently at your work? Or have you caused others grief with it recently?

Common Sense Trumps All

It just happened to me last week:

I was preparing payroll summary sheet which was linked to the detailed payroll calculations.  It was a spreadsheet that I had developed and used for quite some time and I had developed checks over time to ensure that I link to the correct cells and the total sums up correctly.

After clearing all the checks, I was about to send it out when I decide to look through one more time.  I noticed the following:

Department 1: $2,000 (It has a headcount of 5)

Department 2: $1,100 (It has a headcount of 70)

It just doesn’t make sense!

So, I retraced my links and found out that indeed, a column has changed resulting in the above.

Moral of the story, Nothing beats Common Sense.

Has it happened to you too?

Always do a “sense check” on your work.  The corporate jargonator has distorted it to “sensitivity check” and “Sanity check”, the latter always makes me rolls my eyes.

Simplicity – Keeping Intelligent Spreadsheets Simple

As I wrote in my previous post, I’ll be expanding on my thoughts on Simplicity in Spreadsheets.  Having both created spreadsheets as well as used those created by others, I have both caused, as well as suffered under others, much grief at using spreadsheets due to what is boils down to bad and over complicated design.  The result of this is wasted time in correcting errors, misunderstandings and frustrations.  The problem is that most finance-trained executives are just not good at design.

simplicity (Photo credit: TheAlieness GiselaGiardino²³)

I started work in a global HQ office as an analyst whose job is to collect and present data from around Asia and Australasia region, thus, I needed to create lots of templates for different country analysts to fill in.  There I had many great teachers and they generally fall within 2 groups: people who taught me the technical side of Excel and people who made me realised the effect of my templates on them and the impact I had on their work.  It’s the latter group who taught me that spreadsheets can be great enablers, but only if you design it well.
Most people use spreadsheets to perform calculations, collate data and even create models, but they miss out the main function of a spreadsheet, and that is to present your ideas, albeit in the form of computations and tables, to enable decision making.
By presenting your idea in a simple manner, you make it easy for others to follow your thought process, which means that they will be more willing to accept your idea.  That way, you overcome mental barriers to achieve your colleagues’ buy-in.
Simplifying spreadsheets will also mean that you can dedicate less time in maintaining and troubleshooting the spreadsheets, freeing up your resources to do more high-value work.
Spreadsheets are also an important part of the company’s knowledge management tools as they often encapsulate the processes of the company and the key indicators that are monitored.  Thus it is essential that they be simple and well-designed.
Simplicity can be achieved by thinking through, before you start working on the spreadsheet, what is the conclusion you want to achieve, what are the inputs and computations you need to perform in order to achieve the desired conclusion?  Only then should you start to create your spreadsheet.
Remember: “More haste, less speed”, more time spent planning and designing will mean less time spent down the road troubleshooting and explaining.

A New Direction: Keeping Intelligent Spreadsheets Simple

Why wait for the new year to have a new resolution?

Today, I changed the look of this blog.  The reason is that I’m planning to take a new, more active approach to my articles.

Previously, I have only linked to articles that I find useful and interesting, making this more like a personal bookmark.

Now, I’ll be using this blog to outline and develop on my take on KISS.  As you know KISS refers to “Keep It Simple Stupid“, but here, I’m applying it to spreadsheets, thus the header “Keeping Intelligent Spreadsheet Simple”

Why Intelligent Spreadsheets? My late friend, KK Tang, founder and director of Institute of Business Analytics, was a strong advocate of I3 (Integrated, Interactive, Intelligence).  I strongly support and advocate his idea.  You can read more about it at the IBA company website.

When KK Tang passed away suddenly early this year, I felt a strong sense of loss, because the world lost a strong and passionate advocate that not only teaches how to really create spreadsheets but to teach it in a simple, easy to understand way that is absolutely magical.  Still, he left behind a strong team at IBA to continue his work.

I’m not affiliated with IBA, but still being an Excel advocate, I feel that I should do my part to continue to carry the torch.

Building on I3, I’m going to add a new dimension, which will be the focus of my articles: Simplicity.

My next article will touch on why simplicity and what it means for us at work.  I will publish an article every month, which is slower than my Photoblog as this is more time consuming.

Until my next article!

Importing Custom Lists

For those who make extensive use of custom lists in Excel.

Allen Wyatt’s ExcelTips

via Importing Custom Lists.

Does the location of a formula affect its accuracy?

Daily Dose of Excel reported a possible bug in the calculation in Excel documents.

This is the reason why some checks in financial models does not work perfectly.

After reading this, it would seems that it is better to use A1-A2 to check that the two value zeros rather than A1=A2 to check if the two values are identical.

Wonder if there will be a fix for this bug.

Read more in the link below

via Does the location of a formula affect its accuracy?.

Help Wanted: Calculating the First Business Day of the Month

I came across this question on my RSS feed on Excel solutions from
Strangely, when I clicked on it, I can’t find any answers.

When I tried to search online as I’d always done, I can’t find a straight answer or formula to this question. Feeling challenged, I decide to decompose what Excel can do, and see if I can contribute to the Excel community.

This is helpful for calculating deadlines, timesheets etc.

Before constructing a formula, let’s see what Excel offers that can help in this.

Date – This is the basic function for dates, that return a date where you key in a specific day, month and year.

Networkdays – This function calculates the number of working days between 2 dates.  For it to work with your country’s calendar, you need to maintain a list of public holidays.

Weekday – This function returns the day of the week (e.g. 1 for Sunday, 2 for Monday, etc)

Workday – This function which is the one I’ll be using together with the Date function, returns the date after number of working days from a specified date.

The logic of it is simple: the first working day of the month is 1 working day after the end of the prior month. (i.e. April 2011 ends on a Saturday, 1 working day after is 2 May 2011)

My Excel function to solve the above question will be:
=WORKDAY(DATE([current] Year,[current] Month,[0] Day),1,[Public Holidays])
You key in 0 for Day to indicate last day of the prior month
[Public Holidays] is a list of dates of the holidays in your country maintained by yourself

Do drop in the comments if you find anything that is unclear and need extra clarifications