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

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:

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.

Get the best from your charts by following these tips:

• 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.

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:

• Listing all sheets in a workbook using a small amount of VBA
• Use range names where your list length varies (so called Dynamic Ranges’)
• Summing items that appear of different lines in several worksheet tabs

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

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

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.

 1. Dynamic range names (advanced)

• 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.

• You should always include a check that your output report adds up to the same total as your input data.
• 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.