# Creating excellent spreadsheets using the SAFER principles

The Safer Principles are summarised in the following chart.

AS an accountant I also like to link this to key accounting principles (as described in the 2017 draft update of the IASB Conceptual Framework Update):

For more detail see Using SAFER to create good spreadsheets

Posted in Uncategorized

# Weighted Average Using SumProduct()

A business will frequently want to know whether its Profits, Sales etc are growing or declining. This can be a difficult question to answer when results are variable and sometimes even a good chart doesn’t help.  Additionally,  it is useful to based a forecast on  quantified understanding of the growth of the item that you are interested in.

In modelling terminology one way to quantify growth is using current trends.  I’ll cover other methods in another article on another day.   The rest on this blog focuses on calculating a trend, particularly where results are variable.

Moving Average

One of the most common ways to smooth out variable results is to use a moving average. For example if you have monthly results then the average will be typically based on the last 12 months i.e. if your latest result are for February 2017 then you take the average of Mar 16 to Feb 17 results (in January the average would have been based on Feb 16 to Jan 17). NB taking a full year has the advantage of not being subject to seasonal variations.

However, there is often a desire to place more emphasis on the more recent results. In this case a weighted moving average should be used.  Commonly the months are weighted as follows:To get the weighted average you take the following steps

• Multiply Sales for Mar-16 by 1, sales for Apr-16  by 2, May-16 by 3 through to Feb-17 by 12.
• Add the above together and divide by 78 (the sum of the numbers 1 to 12

A shorthand way of achieving the same result is to use the sum product formula.  If your sales values are in cells C3:N3 and your weightings are in cells C6:N6 then using the  sumproduct formula you would enter Sumproduct(C3:N3,\$C\$6:\$N\$6)/78.  NB the reference to the weightings needs to be an absolute reference incl the \$ signs so that you can copy the formula to subsequent months.  An full example is shown below:

One thing to not3: if the data is extremely variable then I would normally create a moving average first and then weight the moving average using the Sumproduct() technique explained above. One slight drawback is that it requires nearly 2 years worth of data as the first weighted number would be the moving average for Mar-16 and to create this as a 12 month moving average would be Apr-15!

Growth

Both the Moving average and the weighted average (WAv) can be used to determine the current growth trend (which can then be used for forecast purposes.  There are two options (i) straight line growth and (ii) exponential growth.

Straight line growth is calculated as the difference between the WAv for the current month and the WAv for 12 months ago. This is then divided by 12 (the number of months). The formula is written as =+(AD39-AD27)/(AB39-AB27) and the result is in units  or £. A result of 1.25 means that sales grow by 1.25 units each month.  Therefore, to forecast we would add 1.25 units each month starting from the latest moving average (or WAv).

Exponential growth is calculated as the WAv for the current month divided by the WAv 12 for months ago multiplied by the power 1/12 (as we have had 12 months of growth. The formula is written as ‘=(AD39/AD26)^(1/12)-1 and the result is expressed as a percentage.  A result of 0.88% means that on average sales increase by 0.88% each month. Therefore, to forecast we would add 0.88% units each month starting from the latest moving average (or WAv).

An example:

Posted in Uncategorized

# Charts: Tips to Create Excellent Charts

A long standing saying is that pictures paint a thousand words.  A chart is one form of a picture and in Excel charts are one of the best tools for communicating information, particularly as a key component of a dashboard.   They can get a message across simply and clearly and at the same time give a lot of information.

A good business chart should be understandable to the reader without any further explanation.  It should focus on a communicating a key business message in sufficient detail but without becoming overly complicated.

• Keep the colouring simple and follow some standard conventions – e.g. green for good news and red for bad. A good use of colours makes it easy on the eye whereas lots of different vibrant colours can jar and confuse
• Use your titles to add information. Instead just saying ‘Profit’ let the reader know a key fact e.g. in the waterfall chart below the main heading is ‘profit for 2016 is £260m’ which immediately lets the user know which time period the chart relates and the
• Colour subtitles differently to the main title – black and blue work well together
• Adding a couple of notes in the main body of the chart can provide further information but be careful to make these short statements. take care not to add too many notes as this will make the chart cluttered
• Round numbers for data values and axes appropriately (e.g. to thousands or millions) to keep them small
• Where text descriptions are used on the horizontal axis, keep them brief.
• Remember to include a vertical axis title which describes the scale of your values

This is the first in a series of posts on charting in excel.  Future posts will look at selecting the correct chart for your purpose and some specific chart types.

Posted in Uncategorized

# Getting the best out of excel pivot tables

Pivot tables are one of the most powerful features in Excel.  They can quickly summarise a vast amount of data and present it in a clear and concise way.  Adding slicers will provide users with tools to slice and dice the data. In addition there are many simple tricks that will let you get the best out of your pivot tables.  See my pivot table page for a list of tips and trick

Tagged with: ,
Posted in Excel Pivots, Excel tips

# Linking files – some do’s and don’ts

Linking files is a useful feature in Excel but needs to be used with care.

I recently had to develop a model for a client where data held in a single workbook and used to feed many other workbooks for single users. The files were initially held on a network drive location but were subsequently moved to sharepoint. The obvious solution was to link excel files in some way. However, with Excel some things work better than others. Here are some pointers if you find your self with a similar requirement:

1. Some formulas require both linked files to be opened at the same time (in my case the data file and the user file). In my case, I wanted the user have their own file open but not the source data file. My original plan was to use SUMIFS and COUNTIFS linked to the data file to extract the relevant data but these don’t work unless you have both files open.  Some internet searches revealed that Microsoft states this is ‘by design’. There are other formulas that fall into this category including SUMIF, COUNTIF, COUNTBLANK, OFFSET, INDIRECT and many of the database functions. Therefore, don’t use these  formulas for linking files unless you are happy that both files can always be opened at the same time.
2. Pivot tables linked to a source in another file will work on a network but not when using SharePoint.  Pivot tables work extremely well as a linking device when you have the files on your own hard drive or on a network. You only need to have one of the files open at any point in time.  The drawback is that the pivot table will extract the full data set rather than just an extract (even if you set the pivot table to just display a subset of the data). Thus in my example above, any of the users would who had a little Pivot Table knowledge would be able to see all of the data relating to other users as well as their own.  I also found that Pivot table links do not work when the files are located on SharePoint. Therefore, don’t use these Pivot tables for linking files located if user should not be able to access all of the source data or if the file are located on SharePoint
3. Basic formulas such as SUM (or even simply =[external workbook.xlsx]!P16) will work for linking in all situations that I have tried to use them including SharePoint.  These can be used to restrict the user files by linking to a subset of the overall data (which could possibly have been created using pivot tables) in the master data file. Therefore, my recommendation is to use simple formulas to link files as this provides the safest and most robust to do it.
4. Some other points to note on linked files
• Keep your links to one or two levels only.  Any more than this creates a risk that the one of the links will fall over and be difficult to find. Long chains of links are something that should be avioded
• If you are copying a worksheet in from another workbook (whether its your own or another users) then check that its not bringing links with in or that if it is then these links will be manageable and will not form long chains.
• Links can not only come in the form of formulas. Charts, data validation, hyperlinks and VBA can all create links. These are the sort of things that can crate phantom links which can be very difficult to clear (simply going to edit links and breaking them will not always suffice).  A post on clearing phantom links is a subject in its own right and is for another day!
• Avoid network drive names in links such as N:\sales folder\file.xls. better to use use UNC names, such as \\myserver\salesfolder\file.xls, instead of drive letters. UNC names can make links easier to update when several people will use a linking workbook, because Excel can update links that use UNC names even if users don’t have a drive letter mapped to the network share.
• Avoid long paths and file names as these can confuse links
• Links can only return the first 255 characters in a cell
Tagged with: , , , , ,
Posted in Excel Formulas, Excel tips, Uncategorized

# Useful Excel Hacks

I keep a examples of useful excel hacks that I have created or discovered.  These are all things that I have actually used.  They generally require advanced or expert knowledge to create but are easy enough to understand if explained.  These are being added to a separate section on my website ExcelMonkey.org.

Today I have included:

The full list can be found on my Useful Excel Hacks page

Posted in Uncategorized

Use to the SAFER method to create excellent spreadsheet models:

S – Structured i.e. constructed in a logical and orderly manner

A – Appropriate i.e. fit for purpose as specified by client/user

F – Flexible i.e. capable of handling reasonably foreseeable changes in circumstances

E – Easy to understand i.e. the client/user can follow the flow from data to outputs and is able to amend

R – Robust i.e. the model includes check totals and completeness checks

Using SAFER principles to design excellent spreadsheets

Posted in Uncategorized

# Excel – Ten Useful Tips

These are ten useful tips at varying knowledge levels (I’ve indicated the knowledge level in italics in brackets for each point: basic, intermediate, advanced and expert).  These are all things I have recently used and/or explained in response to training questions and are in no particular order. I hope that you find these useful.

• These are range names which will vary as your data size varies. They are particularly useful where you are downloading data on a regular basis (e.g. sales orders) and the number of entries in the list will vary on each download occasion
• To make a range name dynamic you replace the standard range (e.g. =Sheet2!\$G\$5:\$G\$16) replace this with an offset formula such as =OFFSET(Sheet2!\$G\$5,,,COUNTA(Sheet2!\$G\$5:\$G\$50))
• As with fixed range names, dynamic range names can be used in formulas

2. Pivot table slicers (intermediate)

• Slicers add the ability to filter a pivot table.  You can add one or many Slicers to your pivot table and they can be linked to several pivot tables if they come from the same data source
• Once your pivot table is created you can set up slicers from the icon on the PIVOT-ANALYZE ribbon

3. Index with match and indirect (advanced)

• INDEX is a lookup type formula.  It comes in two forms. The use below relates to the INDEX(Array, Row number, Column number) form
• On its own INDEX has limited use but is very powerful when combined with MATCH() and INDIRECT()
• MATCH can be used in place of the row and/or column part of the formula. It becomes powerful when you are matching  a selectable item from your spreadsheet(e.g. through a data validation list or a combo box)
• INDIRECT can be used in place of the array part of the formula and I use this when I want to pull data from different worksheets in the workbook. It converts a text string into a range that excel can use. It is at its best when used in conjunction with range names. E.g. I may have data for budgets, actuals and forecasts on different sheets. Having set up range names for each of these data sets, I then use these in a data validation list to select which set of data I require
• A full INDEX formula would look like =INDEX(INDIRECT(CustName), Match(costtype,\$C\$3:\$C\$30,0), Match(month,\$B\$2:\$B\$11,0))

4. Using autofill instead of dragging formulas down (basic)

• Double click on the small black square at the bottom right of the cell (or cells) that you have selected (within the red circle in the screenshot below). This square is called the ‘fill handle’ and will copy the contents of your cell(s) down as far as the data in the adjacent column on the left (in this case Column B).
• A couple of points to note on autofill: 1. If there is a gap in the adjacent column then the auto fill will only go to this point – 2. If you want to auto fill with exactly the same number then you need to highlight a block of two cells with that number.

5. Using NA() in charts (intermediate)

• Useful in line charts where no data exists. Without using =NA() the line would drop to the horizontal axis. If you use the +NA() formula in the chart data (e.g. =IF(C18=”Act”,C4,NA()) then the line is not drawn where the NA is produced. The example below plots 5 months data for where we have actuals for Jan to May:

6. SUMIFS  – dispense with SUMIF (intermediate)

• Now that the SUMIFS has been created in Excel you do not need to use the older SUMIF formula any longer. This is because SUMIFS is designed to work with multiple criteria it works equally well with a single criteria.  Additionally, the parts of the SUMIFS and SUMIF formulas work in a different order which can be confusing when you move from one to another.  Therefore, if you are new to these formulas then use SUMIFS and don’t bother with SUMIF

7. Make your check totals equal zero if everything is correct.

• g. Subtract the total in your summarised report from the total of your input data.
• This will give zero if the values match and a value if they do not match (positive if your report total is less than the total of your data, negative if your report total is greater than the total of your data)
• Formatting zeros not to appear (with a number format) makes it even easier to spot where errors are indicated as these will be4 the only cells with values showing.

8. Check data completeness using MATCH and COUNTIF (intermediate)

• After downloading detailed information from a system there is often a need to check if your spreadsheet is up to date e.g. if you are reporting sales product and new products are constantly coming on line. Using COUNTIF or MATCH are two good ways to check if there are new products to be included in your spreadsheet.
• COUNTIF method:
• in a spare column to the right of your data (never insert a column in your data as it makes it much harder to refresh) use =COUNTIF(array, item) where array equals existing list of products in your spreadsheet and Item references the product name in the download.
• This will return 1 if the product exists or zero if it doesn’t. A zero means that there is a new item in the download that doesn’t exist in your data.
• Use conditional formatting to highlight zeros –especially if the list is long.
• MATCH method:
• in a spare column to the right of your data (never insert a column in your data as it makes it much harder to refresh) use =Match(item, array, 0) where item references the product name in the download, array equals existing list of products in your spreadsheet and the zero will look for exact matches only.
• This will return a number representing the row the product is on within your existing data or #N/A which means that there is a new item in the download that doesn’t exist in your data.
• NB for the array part of the formula you could select the whole column that contains existing products. The MATCH formula will then return the row in the spreadsheet where the product is contained making it easy to find should you need to do this

9. Data validation – List option (intermediate)

• Data validation is a tool to control manual inputs into a cell. There are many options for this such as whole number, date etc but I find that the most useful is the list option. This restricts the input to a specific set of items such as names and is especially useful where the user wishes to ensure that something is spelled in a particular way so that it can be used elsewhere in the spreadsheet. In particular it will help protect against typos which we all make at some time or another!
• The source can be defined as a standard range or as a range name  – see examples below.
•  I have experienced many examples where the main systems of a business spell a particular item in different ways (e.g. region, product, division) as do other spreadsheets that you may be using as a source of data. Almost always your spreadsheet will have a particular spelling to identify an item that you need to use consistently throughout the workbook – the Data Validation-List option is the way to guarantee this for manual entries.

10. Using Camera tool to create a dashboard (basic)

• The camera tool creates a linked picture of a set of cells in your workbook and allows you to paste this picture into another place in the work book such as a dashboard page. As you update the original cells then the picture in the dashboard is also updated. This is useful when you have 2 or more tables of numbers that you want to show in a dashboard with different numbers of columns as these can be shown beside or underneath each other without having to force fit columns. You can format these linked pictures and other objects such as charts to be the same size.  One note of caution: be careful not to create some bizarrely stretched fonts.
Posted in Uncategorized

# My Latest Excel Top Tips

This  website is about getting the best from excel so here are some top tips for this week:

1. Use data validation to control entries in cells  – the dialog box is found on the data ribbon.  The ‘list’ option is particularly useful – see my data validation video
2. Using Vlookup is a good way to retrieve data from along list – see the video here
3. Good structure is essential for anything other than very simple spreadsheets – keep your inputs, calculations and outputs on separate sheets
4. The magic camera tool is a simple way of combining different tables and charts into a fantastic looking report
5. Use conditional formatting to find duplicate data
6. Use the match formula to find out if something exists in a long list
Posted in Uncategorized

# Hello world!

This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!

Posted in Uncategorized
In Archive