How FAST is your Excel?

Standards - making life easier

Excel is a remarkably versatile program.   It, or one of the alternatives to it, will undoubtedly be found in just about every business which has a computer.  Businesses use it in many different ways, from simple lists, through reporting to simulations and financial modelling.  However, Excel's flexibility means that for any task, the effectiveness, reliability and results produced will be dependent on the skill of the user who develops the workbook.

What this means is that the chances are really high that for any given task, the excel workbook in one business won't be structured, formatted or use the same formulas as a the same task in another business (except when one person moves to another place and takes the workbook with them).

The fundamental result of this is that huge amounts of time are wasted in business, either re-developing a workbook to do the same task, or trying to troubleshoot (and rebuild) existing workbooks.

Wouldn't it be great if there was a Standard governing how to structure excel workbooks?

Creating Practical Excel Reports Part 3 : Data Flows in the Excel Report Process

Not your data flows.....hopefully!

In Part 2 we looked in more detail at the three parts of the report process.  If you haven't read Part 2, then it is worthwhile doing that now, because otherwise a lot of what follows here won't make sense.

To recap VERY briefly, one of the keys to creating a practical excel report is to separate the parts of the process.  So, you separate the DATA from the ADJUSTMENTS from the REPORT.  Each of these sections has a specific purpose.  The key question now is "How do you connect these sections?".

Creating Practical Excel Reports Part 2 : Import, Adjust and Report

Follow the Process!!

In Part 1 of this series, we looked at some objectives to try and achieve with any Excel spreadsheet plus some basic principles to help achieve them.

One of the key principles was to SEPARATE, specifically to separate your workbook into at least three main parts.  The parts were :

• Data Import; 
• Adjust; 
• Report;

In many workbooks, the Adjust (and in some cases the Data Import) is mixed up with the Report part - this is quite common when the base data you have (some form of excel output from your system usually) needs only a couple of adjustments and some formatting to get to the report you want the users to see.

Well?  Keanu has spoken!

Creating Practical Excel Reports - Part 1 : The Basics

Getting the basics right!

Soooo true.

At work, you will use excel for something.  You'll have an excel spreadsheet that you use to keep track of something and probably you'll feed that data into a report, or even produce a report from it.  It doesn't matter if your spreadsheet is tracking project delivery actions, environmental results, safety, training or financial performance.

At some point, you've had to change this spreadsheet.  Someone has asked for an extra column, a new status, a different format etc........and your heart sank.  It sank because you just KNEW that change was going to be a LOT of work.

Wouldn't it be great if you could be confident that changes to your spreadsheet weren't going to be hugely time consuming?  That you could be confident in making changes and know that the new report was easy to check and reliable?

Are your reports useful?

Soooo tempting to try this, isn't it?

As a preparer of reports I was often convinced that I was wasting my time.   This was because most of the recipients of the report either didn't read the report or were only looking for a couple of key numbers.

Over time, as I started each new job, one of the first things I'd do was to review the reports issued by the finance team.  As a rule,  this review cut out or modified many of the reports produced and freed up significant amounts of team time. (I'll write about the process I used in a separate article).

The key to this process was making sure that the report was useful to the recipients, and then consolidating, modifying or dropping reports as required.

So, what makes a report useful?

The most important part of a system implementation! (SYS)

Remember that the basic processes have to work!

Not your desired action.

In every system implementation I've been involved with, one of the most time consuming processes has been in determining how the system should handle basic processes.

From an ERP perspective, basic processes includes the paying your people, processing customer invoices, the raising of purchase orders and dealing with supplier invoices.    If you analyse your transactions, you'll find only a few types.....but there will be a large number of transactions!

However, too much of the time spent was devoted to working out what to do with the exceptions (warranties, returns, partial credits, service exchange items) and (with the absolute benefit of hindsight!) not enough time was spent making sure that the hundreds (or thousands) of purchase orders, customer invoices/supplier invoices were able to be processed smoothly.

Excel Formulas you need to know! (XLS)

"Never was so much owed by so many to so few"

Winston Churchill

I'm not sure that Microsoft fully realised the impact Excel would have on business reporting.  In my experience, every company I have worked in relied on Excel to produce key reports (even those with business intelligence systems).

The ability to manipulate and calculate data as well as finely format the resulting report is an awesome combination.

Unfortunately, this is true for excel

At the same time, poorly used and set up excel spreadsheets are responsible for thousands of lost hours spent trying to balance, or change them. (I know I've spent hours trying to follow formulas or make changes to a report format).

For me, if you are going to do any sort of reporting in excel, you need to know the following excel formulas.

Creating Reporting Identifiers (IDT)

Tips for creating a Reporting Identifier!

Welcome back to the next instalment of tips on Reporting Identifiers.

In this instalment, I'm going to share some experience earned while trying to create reporting identifiers - that is to actually create a detailed list of items, which together represent a view and which individually are a specific subset of that view.

But first, a clarification!  Don't confuse a reporting identifier with the name of the field it is stored in, and remember that you could have more than one identifier in a single system field.

For example, in simple systems, it is common to combine department and nature views into the GL (because these systems don't have a costing module for example).

Tip 1 : Reporting Identifiers should be in separate fields.

When referring to a reporting identifier note that multiple identifiers can be represented in a single system field.

Now ideally, you will have enough system identifier fields to be able to have all your required reporting identifiers in separate fields.  If they don't, you'll have no choice but to combine them.  

Combining them is not ideal, because it increases the chance of misposting (as people select the wrong account) as well as significantly increasing the length of your item list!

Let's look at an example - we have one identifier representing nature ("what we bought") and using the GL Code field (this is really common) plus another responsibility view using a field called department code.

separate fields are the way to go!

Now, the same identifiers, but combined into 1 field, the GL Code field.

Longer, harder to understand, and easier to make entry errors.

As you can see, combining identifiers in a single field leads to a much longer list...imagine what happens when you have several hundred GL accounts?  

The other issue is that adding to  or changing your nature identifier (ie adding a new nature account, say Wages - monthly) means you have to add it to every department, instead of just doing it once.

So, always separate.  Always.  

Tip 2 : Explain the identifier

When you have a view/perspective, it is a REALLY good idea to document what the view/perspective represents.  That really helps when putting the item descriptions together, and also makes it easier for report users to understand what the view represents.

Not the best approach for reporting identifiers

I've found that the easiest way to keep identifiers on point and understandable is to come up with a phrase which is short, but catchy.

Example:

"Nature" views are common and most GL's are based on this view.  In this case, you can use a description like "What we buy or sell" which tends to work well.  It applies really well to P&L items - the balance sheet is a bit shakier, but generally the finance team knows what to include there anyway.

Some examples:

• Nature = "What We Buy" (eg salaries, consultants, cleaning, rental)
• Activity = "What We Do" (mining, processing, administration)
• Responsibility = "Who does it"
• Location = "Where we do it"

Having a short description which is easily understood can really simplify the process of creating good identifiers.

Tip 3 : Keep it Pure

A pure identifier is true to its description, so doesn't have any item descriptions which are outside the view.

Common fails in this area include:

Activity descriptions in a Nature hierarchy

In this case, "Admin" is not a "nature description".  Generally you can't buy "Admin".  The main issue with not having pure identifiers is it leads to confusion (or laziness!) when people actually code transactions.  

In the above example, where do office rentals go?  To 6140 Rentals?  Or to 6130 Admin?

Keep it pure and you are less likely to have miscodings and weird variances.

Location descriptions in Activity Hierarchy 

Here again, you can see that the example isn't pure, because we have location and activity combinations.  This often happens when the system is set up when the company only has one mine, but then it grows and adds another one.

Tip 4 : All numbers or all letters for your codes

What about the numbering system for your items?  From the examples above, you can see I prefer simple numbers.  Alternatively, I can live with all letters.

Why?

From a data processing perspective.  Having to switch between numbers and letters really slows down processing time.  

The other reason to stick with one or the other is that the codes sort more reliably if they are all one or all the other.

Oh, whether you use numbers or letter, NEVER start a code with a 0 or an O.  That creates huge issues importing into excel.  

In fact, don't use O because it is easily confused with 0 - and minimising confusion is a good thing!

Tip 5 : Simple logic in the code

In the system implementations we have done there has been a lot of debate about whether the numbers should have logic built into them, where the position of a number or letter means something.

The argument is usually that if people can work out the code, they are more likely to get the postings correct.

What happens when the logic breaks down?

In practice, that doesn't work.  People tend to memorise the codes they use often, so it is much better to have a shorter code.  

Generally, I find that really simple logic (eg for a Nature hierarchy, 1 is Assets, 2 is Liabilities etc) can work, eliminates a lot of really silly mis-postings but still allows freedom to add new codes as necessary.

The key thing is that too much logic reduces your ability to deal with new codes when the business grows (or, you end up with really long codes with lots of 0 or xx in them).  It also makes setting up the codes a nightmare - as you are bound to get a couple wrong.

* * * * *

I hope that you find these tips useful!  I can't stress enough how much easier reporting is when your codes are separated into separate fields, reflect views used in the business are are pure!

What do you do with your identifiers?  Please share your tips in the comments!

Process for determining what Reporting Identifiers you need (IDT)

A process for successfully determining your reporting identifiers!

In the first post on identifiers I talked about what reporting identifiers are (ways to represent a view/perspective on your data) and some initial dos and don'ts.  The question remains though, what is a process for determining what views/perspectives are needed?

Not this process, hopefully

Putting in a new system (either an ERP or a specialised tool - eg for managing safety, environment or maintenance) is generally a highly stressful and usually expensive experience.

I think the main issues are:

• Businesses don't devote the time necessary at the planning stages (ie BEFORE the request for tender goes out) to work out what they really want;
• There is a general sense of fatalism ("well, this is going to be a disaster - I used this system at xx and it was terrible") that there is no way to avoid a highly painful experience;
• The people directly liaising with the implementation consultants aren't sufficiently expert in the way YOUR business process works/is structured;
• The expense leads to a "buffet/smorgasbord" mentality - where the cost encourages businesses to try and implement waaaay too much functionality.

Against this background, most of the attention goes to business processes and how the system will impact that.  This is not a bad thing, but at the same time, getting the identifiers wrong can stuff up the best process in all sorts of hidden ways (just ask AP/purchasing teams processing large numbers of invoices or POs how much they like identifiers with alphabetical characters in them).

Further, if you leave the finalisation of identifiers to the end, it often gets rushed and you'll discover that you can't easily report information that the business wants or needs.  Also, remember that if you have historical data, you need to get that into your reports at some point!

A process that worked

It suddenly occurred to me that over the last 7 years, I have been involved in 4 ERP implementations and 3 reporting system implementations.

There was a lot of pizza involved so it didn't feel like a punishment!
Well, not ALL the time!

For what it's worth, here's the process that I've gravitated to - it seems to work well for getting the necessary business teams involved and producing identifiers.

1. Establish what each module in the system can do and the primary business area that will use it.
2. Look at ALL the reports the business produces to determine the views/perspectives used.
3. Map those views and perspectives against the business area that GENERATES the data necessary.  This will effectively allocate the reporting identifiers to system modules (assuming you actually did step 1).  Note, this can be tricky as some identifiers are system wide.  In that case, simplest thing is to look at the primary owner of the relevant report to work out who "owns" the identifiers.
4. For each system module develop the identifiers, making sure to keep the identifiers "pure" (don't mix views in a single identifier)
5. Test your new business reporting AS YOU GO.  It doesn't matter if there are only 3 transactions in "test" - if you can't get those to report properly you won't get production data out either.

It is important to start out with the understanding that you'll probably re-write your identifiers a couple of times.  Also, someone is going to need the patience of a saint to coordinate the identifiers across all the modules (and therefore business areas) so that they are "pure" and don't double up.  It is here that testing reporting as you go REALLY helps, because it is a lot easier to discuss something you can see, rather than theoretically writing stuff on a whiteboard. (Been there, done that!)

Just one more thing

I was rereading the steps, and thought a diagram would be helpful, so here it is!

If only it was always that simple!

Ok, here we go.  

Let's assume a company with one current operating mine is implementing an ERP.  The ERP has three modules, a general ledger module, HR/Payroll and a costing/projects module.

Step 1 : Establish what modules there are and who will primarily use them.

In this example, it is really easy, because the GL produces balance sheets and profit and loss accounts (ideal for Finance).  The costing module allows breakdown of cost groups, so Operations owns that one and HR gets the payroll module.

Step 2 : Look at all reports and determine the view/perspectives

The reports produced by this company are shown in green.  Against each is a dark orange scroll which shows the views used.  In this case a "Nature"view refers to "What we bought", so would contain headings like consultants, salaries, wages, rental, contractors, postage etc.  An "Activity" view refers to "Why we bought it", so would be headings like mining, processing, recruiting etc.

Step 3:  Map those views and perspectives against the business area that GENERATES the data necessary. 

The Financial Statements are clearly GL and nature related, so nature was mapped to Finance ownership.  In this case Finance is considered to Generate the data because of the transactions done re the balance sheet.

The Management Reports were primarily Activity focussed (ie reported mining costs by nature) and so Operations picked up the ownership of the Activity view.

HR obviously ended up with the Payroll reports.  Note that there is no "view" shown here - this is only for simplicity.  In practice HR often owns a primary view (that of Responsibility) and the interaction between this (represented by the org chart) and an Activity view is often the most confusing to sort out.

Step 4: For each system module develop the identifiers, making sure to keep the identifiers "pure" (don't mix views in a single identifier)

In this case, the GL codes represent the Nature view, and would be developed by finance.  Operations would come up with the Activity view, represented by cost centre codes.

PRACTICAL TIPS 

DO :

• Follow a process, ensuring that the identifiers are clearly mapped to reports AND an appropriate module.
• Test your reporting as you go.  At the start you can test your proposed identifiers by trying to map your existing identifiers to your new ones - that will generally tell you if you have missed anything important.  Then you can produce existing reports using data from the old identifiers.
• Make sure that all the business areas understand that although they own the identifier, it is SHARED.  In particular, be careful of the impact of convoluted Cost centres on purchasing data entry times.
• Review the reporting to make sure it is used, useful and relevant.
• Keep it simple.

DON'T

• Leave the identifiers and reporting to last; 
• Try and do reporting identifiers without business involvement.  It may seem faster, but it isn't.
• Forget about historical data!

What do you think?  Will the process work for you?  Let me know what has worked for you in the comments!

Getting reporting identifiers right!! (IDT)

Reporting Identifiers - spend the time to get them right!

What's the problem?

In my experience, the difficulty of preparing any report is directly linked to how good the identifiers for the underlying data are.  

As a simple example, consider any report you have to produce where you have to summarise some detail into groups.  Simple examples are grouping detailed info into a summary (eg all your individual cash accounts into the "Cash at bank heading", safety performance by location (or type of incident), environmental surveys by type etc etc).  

If you are grouping, having all the detailed info in group order makes it easy (so all the detailed info is neatly lined up together).  If you have a long list and it is all mixed up (think a list of safety incidents which you have to sort by site), then it is not so easy.  

If we want to get total interest for example?

Worse - think about where you have had to split a single account into several categories (airfares by department anyone?).  That can take hours!

Having the right identifiers is CRITICAL to making the reporting process easier.

The thing is, it is really tricky to predict all the future reports that someone is going to ask for (think what happens when there is a change to a manager, GM or CEO, or a takeover, or cash squeeze....)

What are identifiers?

The best way I've found is to think of an identifier as a view(perspective) of your business data. 

So, from a financial perspective, if you want to see your financial data from different perspectives, you need to have different identifiers.

Let's look at producing information for the financial statements P&L as an example.

Simple, 2 perspective example

In the above diagram, you have your business data in the green box - in this case, financial transactions, but it could equally be environmental survey data, or safety incidents.

The orange boxes represent your view/perspective of the data.  In this case a "nature" view of the transactions (so, looking at the data in terms of "what did we buy") and a "functional" view ("why did we buy it?").  

These perspectives are identified by two identifiers - in this case I've called them "GL" and "Cost Centre".  So, to produce the financial statements, I prepare two different sub reports -one for Nature and one for Function- with a different perspective, each one based on a different identifier.  I then combine them into my target report, the Financial Statements.

All good, because I have identifiers which specifically represent that view or perspective. Assuming I haven't fallen into any of the traps (see below!) I should be able to run a report for each perspective (or directly link my financial statements to that perspective). 

Also, with only two perspectives, life is good, mostly.

It's a Trap!

You just knew that was coming, didn't you.

Identifiers then, represent a perspective or view of the business data.  Having the right identifiers means that you can easily get a report tailored for that particular view.  So, what's the trap?

Not so simple anymore

The main traps with identifiers are:

• Having too many.  It is really, really easy to end up with multiple views that are almost the same thing - I have often seen this with functional vs responsibility views - in many organisations these are very close together (when you start at least!).
• System doesn't have enough identifier fields.  and so, we convert them into multipurpose fields, with multiple identifiers in a single field.
• They aren't "Pure". It is really important that you don't mix and match views inside a single identifier.  For example it is really common to see "admin expenses" in a Gl Chart of Accounts.   Generally, this is a problem because the GL is (usually) a "Nature" analysis, and admin expenses is not something you buy.
• They mean different things to different people.  When you are having discussions about this, are you both on the same page?  I had a discussion recently about "Cost Centres"....and this term is probably one of the worst.  I meant "the activity that the costs were incurred achieving" and the other person meant "the department doing the work".  Historical experience can deeply embed a certain meaning for commonly used terms (like "profit centre" or "cost centre").
  
  
• Having the wrong ones.  It is really, really important that identifiers are considered against the substance of your business and not simply what "the report we currently have" shows.  Remember that these identifiers are what the team on the ground have to work with in managing the day to day activities.  Get this wrong and it makes their life harder....EVERY DAY.
• Putting them in the wrong system or module.  From bitter experience, I can assure you that identifiers do NOT all need to be in the GL or Costing subledgers.  If you have an appropriately rigorous subsystem - have identifiers in there and just a control account in the GL/Costing.

Practical Tips

Ok, there is a lot of traps, so what can you do to manage them?

DO

• Take the time to discuss internally what information you want to get out and how it should be seen (this is your views/perspectives).
• Write down what information you want to keep and how you want it reported.  Make sure the consultant understands the reporting requirements.
• Keep identifiers to a minimum - there really aren't that many ways to view a single business.
• Keep the identifiers pure!  Most reporting systems (especially EXCEL) will let you mix and match later.
• Be very specific with your implementation consultant.  They don't know your business, so don't assume they know what you mean.
• Remember that you will probably be reporting in Excel (because most applications can't format the way you want it)...don't sweat the small stuff in the reports in the system.
• Make sure the identifiers enable the team on the ground to get the information they need.  They generally need more detailed information - it is FAR easier to summarise detail than to break up a summary.

DON'T

• Let the consultant give you your master data.  Sure, take the template and modify it, but don't just upload it!
• Believe the consultant when they say "you can't use that field/module for that".  Computers don't speak English.  They store a series of 1s and 0s.  As long as the module has the functionality you need, use it.
• Create multiple level, highly complicated IDs.  Simple is better.
• Skimp on the time taken to get these right.  You'll regret it later.

So, what do you say?  Let me know in the comments if I've got it wrong or you have a different opinion.

Are your identifiers making life easy for you?

Hello and Welcome

Welcome to Practical Reporting Tips

Hi and welcome to Practical Reporting Tips!

In this blog, I'll be sharing tips and tricks on improving reporting - either improving the usefulness of the report (getting someone to read it is a great start!) or reducing the time or effort taken to produce the report.

There will be a variety of posts, covering a wide range of stuff:

• Setting up to produce the reports that users want
• Reporting processes
• Tips and tricks for specific reporting tools I've used
• Tips and tricks for Excel, the staple of most business reporting!

More than that, I hope that you'll share your reporting tips - leave comments or email me and I'm more than happy to include your tips (all credit to you, obviously!).

The primary rule is that it has to be practical and useful - something that will genuinely save time and effort in the production of reports that are actually read!

A small caveat - I'm not claiming to be the worlds greatest expert in all the topics.  Nope, not at all.  What I'm sharing are practical things that I have done over and over again, which actually work.....and more than that, leaving behind a process and spreadsheets that SOMEONE ELSE can actually follow and amend when things change.

If you think I have it wrong, let me know - the objective here is to have practical things that teams (finance teams, hr teams, environmental teams) can use to make their lives easier.

The motto for this blog!

If you have a pet peeve or something you've been wanting to fix (but haven't had the time) please let me know!

The first posts I'm planning will cover system implementations and targets I've set for reports - so, let's begin!