Add a touch of Hemingway to your next finance report

What did the authors Raymond Chandler, John Steinbeck and Ernest Hemingway have in common? Well, aside from being American, they all had a writing style that was direct; they did not waste words.

Sometimes I like to read a book with a lyrical style but for the most part I prefer the direct style of Chandler and co.

When it comes to writing financial reports I encourage you to adopt a plain, direct style. If you do not then, at best, your reports will be over-written and, at worst, you will obfuscate the message you are trying to communicate. (I suppose I should, on that basis, permit over-writing if your intention is to obfuscate!)

It is easy to fall into the trap of over-writing. When I first qualified as an accountant I wrote letters that included phrases like “I would be grateful to receive your remittance at your earliest convenience.” Now I would write, “please pay promptly,” or, better, replace promptly by a deadline date. This is much easier to understand, and takes fewer words, too.

None of us are in the league of Nobel prize-winning authors but we can improve through practice. When we write something of any length we can always read over it with a view to simplify it. These days we can also use technology to help us. Recently I came across a web service called Hemingway. All you need to do is paste the text you have written into Hemingway and it will assess the reading age needed to understand it and highlight sentences that are long and complicated, and words that can be replaced by simpler words.

If you’re interested, the first draft of this post had a reading age of Grade 10, 5 of its 17 sentences were hard to read, and 3 sentences were very hard to read. Using the app I fixed four of those problem sentences and got down to Grade 8.

If you want some help to improve your writing it is worth checking out Hemingwayapp.com. As well as the free web version, you can pay for a desktop version for Windows or macOS.

3 quick tips for formatting spreadsheets

Good design helps people to use products and this applies to spreadsheets and financial models as much as it does to smartphones and websites. When you create a spreadsheet you should consider how it is formatted as part of its overall design. I have made a short video with three tips for formatting your spreadsheets to ensure they are useable.

The video is part of an online course, Financial Modelling in the Public Sector, which you can find here.

If you want more of these tips about writing and making presentations then join my mailing list.

Book recommendation: Getting to Yes by Roger Fisher and William Ury

Last week I saw this story about how Northamptonshire County Council hopes to deal with the financial difficulty it is in by renegotiating its supplier contracts. Taken on face value, if 70 per cent of spending is through contracts with external suppliers it makes sense to seek to reduce expenditure by renegotiating some or all of those contracts. The expert cited in the article is, rightly in my view, concerned about how difficult this task will be, especially given that the suppliers know how perilous the Council’s financial position is.

There is a risk that some of the suppliers might not want to renegotiate, and that others might walk away from the negotiating table, but I think the Council’s team need to try. Step one might be to identify a priority order for dealing with the contracts, taking into account factors such as size, duration, the historical performance of the supplier, etc. But what next? How can the Council negotiate itself new deals it can afford?

This is where my book recommendation comes in. Over the years I’ve attended several courses on negotiation skills but none of them have been as helpful as reading Getting to Yes by Roger Fisher and William Ury. This is a relatively short book, written in a friendly, easy style and it can set you up for any kind of negotiation—in your home life as well as at work. The authors are part of the Program on Negotiation (PoN) at Harvard Business School so they know what they are writing about.

There’s no getting away from the fact that successful negotiations rely on preparation. The better you are prepared the better the outcome will be. And, if I boil it down, the preparations require two main things. First you need to understand your BATNA (the best alternative to a negotiated settlement you have) and the other party’s BATNA. Understanding both of these helps you to evaluate whether the offer on the table is acceptable or not to either or both of you.

The second key message in the book is to focus on each party’s interests and not to take fixed positions. The authors believe that if the parties focus on their interests and think creatively they can often find solutions that both are happy with. This is a way of thinking about negotiations in a way that is not so conflictual and more prone to result in win-win solutions than win-lose solutions.

Connecting this back to Northamptonshire Council’s predicament, they are clearly going to have to do a lot of work to understand the BATNAs relating to so many contracts but that will be an essential step. And although it may be a difficult situation, if the Council focuses on the interests of both parties it may work out agreeable solutions. Clearly the Council is desperately in need of reducing its financial commitments but there are choices. What I don’t think will work very well is the Council making an aggressive opening statement along the lines of “As one of our contractors we need you to reduce your prices by 50%.” The chances are that this will make the contractor defensive and likely to take a position of pushing back with an explanation of why that can’t and won’t happen.

To get the spending reductions it is after the Council needs to have a constructive dialogue focused on “getting to yes”. It has options that can help this dialogue such as reducing the volume of services, reducing performance standards, offering longer contract durations, offering exclusivity, changing the terms of the contract that the supplier feels are onerous or that oblige the supplier to incur higher costs. There could be many more. The important thing will be listening to the other party to each contract on an individual basis, working out what their interests are and then working together to agree a change or set of changes to the contract that give each party what they are looking for.

If this has whetted your appetite about Getting to Yes you can buy the book from Amazon using this link. Also, the PoN’s website is a useful resource and you can sign up for their daily newsletter on the site. Alternatively you can follow PoN on Twitter at @harvardnegoti8 and William Ury is @WilliamUryGTY.

How to Hire a Consultant in the Public Sector — the e-book

Over the last couple of weeks I’ve been working on a small e-book as a learning exercise. This is not learning how to write a book, but how to get a manuscript formatted as an e-book, with a cover, etc and uploaded to a platform for sale. It turns out that it is fairly easy to do but, like many things, the first time you do it takes a lot longer.

I wrote an e-book about procurement in the public sector, using the example of hiring a management consultant or advisor. I did this because it seems like the sort of thing a manager might do in pretty much any part of the public sector. Schools, hospitals, local councils, police forces, etc will all use advisors from time to time and they all have to follow the same process in broad terms. What I mean is, they all need a specification, a tender or quotation process, an evaluation, and an award process even if all of them have their own specific rules. They also all need to manage the consultant once appointed, an aspect often overlooked.

I wrote the manuscript in an app called Ulysses because it allows you to write your text as plain text but export it in many formats. One of the export formats is epub which happens to be one of the formats that Apple’s iBookstore can handle. As an Apple nerd I wanted to make the book available as an iBook even though the sales potential in the iBookstore is very limited. (Let’s face it, first you have to have Apple hardware and then you have to be interested in a niche subject!)

Before I could export the epub from Ulysses I had to create a cover. I could have commissioned an artist to create a cover for me but that would cost potentially more money than I might make from this test book so I made my own. I’m not a great graphic artist but I know a bit about using an app like Pixlmator so that’s how I created the cover you can see below.

Once I had a cover I had to incorporate it into the export function in Ulysses and, bingo, I had an epub file.

Amazon Kindle does not support epub files so I then had to run my epub file through an app called Kindle Previewer in order to convert it into the mobi file type that Kindle does support.

The next part turned out to be the hardest and longest step in the whole process. I thought I would be able to use my accounts with iTunes and Amazon to upload the files and click a few buttons to add prices etc and that would be that. It is sort of like that except the first time you need to get yourself an American tax identification number in order to benefit from the tax treaty between the UK and USA. As American companies Apple an Amazon would hold back 30% tax from any royalties that I earn unless I registered with the IRS and claimed an exemption under the tax treaty. On that basis they would withhold 0% tax and I would have to pay only the UK tax on the income. That took a fair bit of research, the completion of a form, a long phone call to the IRS to explain what I was doing, etc. To be fair, at the end of the call I was given my ID number and could use it directly in the Amazon and iTunes set up pages.

After that is is more or less a case of uploading the file, entering meta data, marketing blurb, pricing information and a publication date and the book will be published.

If you are interested in reading this book about public sector procurement click on the following links to get it from Amazon or the iBookstore.

Five more tips for better spreadsheets

Last week I wrote a post giving you five tips for improving your spreadsheets, and in that post I promised to share five more tips in the future. the first five tips were:

1. Inputs, calculations & outputs go on different worksheets

2. Use an input in only one place (so it is easy to change)

3. Do not hard code numbers into calculations

4. Break up long formulas into component calculations in separate rows/columns

5. Use the exact same formula across all the cells in a row

Read the post to get an explanation of these tips.

Below are five more tips, some of which build on the first five.

6 Do not mix up time periods in a work sheet

In tip 5 last week I recommended that you use a common formula across the whole of a row. One reason that people don’t use the same formula across all rows is because they insert sub-totals. In a spreadsheet with monthly columns they might have annual totals inserted every thirteenth column. There is, I think, a better way by using a consistent time period in a spreadsheet and then creating other spreadsheets that summarise the data in alternative time periods.

So, if you need monthly and yearly figures, first create your spreadsheet as monthly columns. These columns can have several rows of headings as needed, to show the count of the months, the financial year, calendar year, etc. What you then need to do is create a new worksheet for the annual totals. In this worksheet you can use a ‘SUMIF’ formula to sum all the amounts that are in year 1, year 2, etc. 

This is even more flexible because, for example, if your financial year is not a calendar year you can easily create separate worksheets for each way of analysing the data, something you cannot easily do any other way.

7 Do not use named ranges

If you put all your input data into an inputs worksheet it can be tempting to use named ranges to define them, to make it easier to write formulas in the calculation worksheet(s). This would be easier for you as the creator of the spreadsheet but it makes things harder for reviewers, auditors and people trying to update it. This is because it is quite difficult in Excel to work out what range the name defines. It can be done, but the dialogue box will list all the named ranges and the reviewer will have to scroll to find the one they want and probably will have to keep notes of them to avoid repeatedly having to make the check.

If you don’t use named ranges the reviewer can see exactly which cells and ranges are being used in calculations. This is much better.

8 Do not hide rows or columns

When creating a spreadsheet you might want to simplify what is seen by hiding some of rows and/or columns using the Hide function. If you do this it can be difficult for a reviewer to understand what is going on. Essentially they have to look at all the row and column numbers and letters and spot where they are not consecutive. That can be surprisingly difficult to do.

A better way of tidying up rows and columns is to use the outlining function instead. This function can be done automatically (it finds the subtotals and totals for you and assumes that is where you want the outline to be) or you can do it manually. The advantage of this method is that small boxes with + or – signs appears in the top and left margins making it obvious to the review that there are rows/columns to open out or collapse as the case may be.

9 Do not use macros

Macros are computer programs (scripts) embedded within the Excel file

Macros are an advanced technique and perhaps a relatively small proportion of Excel users could create them. Even if you know how to create them, my recommendation is that you do not use them. This is for several reasons:

• like most of my tips, the use of macros make reviewing and updating your spreadsheet difficult (this would be less the case if you created full documentation for the macros but, let’s face it, would you really do that)

• macros are not supported in all versions of Excel so you cannot be sure other users could even use the macros

• if you are emailing the spreadsheet it may be seen as spam because it is an executable file (a programme) and not a data file.

10 Hide the gridlines

View fullsize

My reason for recommending this is because the faint grey cell borders add a lot of visual clutter to spreadsheets which is not needed. I think the final spreadsheets are much cleaner and easier to understand without the cell borders. Apart from anything else, it makes any other formatting that you use (colours, shading, underlining, etc) much clearer.

Gridlines are the default option for all new spreadsheets. To turn them off, on a worksheet by worksheet basis, the easiest way is to select the View tab in the menu bar and uncheck the Gridlines box. This works for Windows and Mac versions.

This last tip causes the most discussion when I teach this subject face to face. I don’t quite know why that is. I know very few people who would change the default printer settings to add back the cell borders when printing their spreadsheets.

So there we go: ten tips for better spreadsheets. For more

Five tips for better spreadsheets

I’m old enough—just—to remember accounting work before the rise of spreadsheets. We had a accounting ledger system on a mainframe compute to record all the transactions and it could produce printed reports but if you wanted to do any analysis you had to do it by hand. We had stationery called analysis paper which was A3 size, pale blue with a grid pattern in darker blue ink. There were wider cells on the left side and heading and footer rows, too. This stationery made it easier to lay out data in rows and columns but all the sums had to be done with a calculator, at least twice to check they were right. There were no graphs and no sorting on the fly.

Excel is ubiquitous now but in the mid-80s we moved from paper to SuperCalc (I knew some organisations used Lotus-123) and these were spreadsheets that pre-dated the graphical user interface and the mouse. They did not have colours: they were white or green on black depending on your monitor. And we used them like analysis paper, basically summing the numbers in our columns and rows.

Modern spreadsheet packages are massively more powerful with all types of arithmetic, financial, scientific, statistical and database functions built in. Indeed, there are more functions than most users are aware of. My point with this blog post, though, is not to explain some esoteric function but to address the fact that accountants (and others) are not trained in financial modelling. Instead of adhering to some basic standards or principles for all their work people lay out each spreadsheet to solve the problem they have right now. This is OK as long as no-one else is collaborating on the spreadsheet or, at some later date, asked to update it. At times like that the lack of standardisation means it is very difficult to understand how the spreadsheet works and it might be easier for the person to build a new spreadsheet from scratch—using their idiosyncratic style, of course—than update the old one.

One way to envisage what I mean is to think about a paper notebook. Your notebook is yours and it does not matter how you choose to keep notes at meetings. Your notes could jump around from topic to topic, mix words, numbers and pictures, have asides, scribbles and crossings-out but that does not stop you from being able to remember what you need to remember. In my experience most people create spreadsheets in much the same way. They start at the top left of the sheet and add things as they think of them. They are not thinking of future users as they build the spreadsheet.

But there is a better way. Some organisations do have standards for financial modelling so that there is consistency in the work produced by their staff. This makes good business sense since it saves time, enables quality control to be implemented consistently and, I reckon, reduces errors. (Errors in spreadsheets are legion and can be costly, either in money or reputation.)

In the absence of financial modelling standards in your organisation, here are five tips, or principles, that you can adopt in your spreadsheets. Although I mention Excel throughout they would equally work on Google Sheets, Apple's Numbers, or Open Office spreadsheets.

1 Inputs, calculations & outputs go on different worksheets

Excel opens to a blank spreadsheet and many people will start creating their masterpiece on ‘Sheet 1’ and build it up and up until it’s done. For years it’s been possible to have multiple worksheets within a spreadsheet file and you move between them by selecting the tabs at the bottom of the screen. I suggest as a minimum you use three worksheets, one for your inputs and assumptions, one for your calculations and one for your outputs. If you want a variety of output formats you might want multiple output worksheets, and if your calculations are complicated you might need several worksheets for them so they are easier to follow (and audit).

It’s separating the inputs that I think is most important. By inputs I mean the key assumptions, such as start and end dates, interest rates, inflation, etc and also data specific to your model. It’s here that you would have all the relevant data for the model such as population information, unit costs, prices. If your organisation has specific financial policies such as the discount rate to use or the profit margin to apply you would also include them on this page. This simple tip, combined with tip 2, makes the job of someone asked to update your spreadsheet much easier. Basically they ought to be able to look at the input sheet and make amendments there which ripple through the calculation sheet(s) into the output.

2 Use an input in only one place (so it is easy to change)

This is an important tip for avoiding errors and making updates easier. If the interest rate is in cell C10 on the ‘Inputs’ worksheet then wherever it is needed in the calculations it should be referenced as Inputs!C10.

3 Do not hard code numbers into calculations

The formulas in the cells in the calculations worksheet(s) should not include actual numbers. The reason for this is that the calculations should be drawing on data on the inputs sheet(s). If you are including some data directly into your calculations you are making it very difficult for someone to understand your model and be able to update it. Even if they find the number within your calculations they may not know why it is there or where it is from. The possible exception to this rule is that sometimes it might be useful to include the numbers 0 and 1 in formulas. For example, you might use them as results from a conditional formula because 0 and 1 are the logical values for false and true.

4 Break up long formulas into component calculations in separate rows/columns

It’s possible to have very long formulas in a spreadsheet calculation especially if you are nesting If statements or the like. Developing these formulas can be difficult because you need to get all the arguments right and include the correct number of commas and parentheses; and once created, they can be difficult for someone to understand. It can help, therefore, to break the whole formula into component parts and set them out on the rows (or columns) of your worksheet. This makes it easier for you to create the formulas and makes it easier for someone to follow your logic.

5 Use the exact same formula across all the cells in a row

When using Excel have you ever noticed a small green triangle appear in the top corner of some of the cells. This is an audit warning marker. It appears when the formula in a cell does not match the neighbouring cells. This is a useful feature but only if you create your worksheets so that the same formula is used across a whole row. If that is how you build your spreadsheets then the green triangle actually works to draw our attention to a possible (likely) error. If you create spreadsheets where the formula used in each column is different then the triangle is no help. Creating formulas that are the same across the rows does require a little bit of extra thinking. This could be, for example, because the calculation you want to create in the first and/or last period of the date range are different from the calculations for all the other periods. You might need a calculation that includes an ‘If’ statement to identify whether it is the first or last period. Another example might be to base the calculations on the number of days in the month. For that you might need a row with a formula that calculates the number of days in the period (i.e. the end date of this period minus end date of prior period) and then use the answers in subsequent formulas.

I’m confident these five tips will improve your spreadsheets immensely but there’s more you can do so I’ll post five more tips soon. Meantime, you can always share these tips with colleagues to improve their work and, if you want to be kept informed about my work, please join my mailing list here.