Financial Planning Using Excel (eBook)

Chapter 1. Spreadsheet Skills for all Types of Planning

The most popularly claimed pitfall of planning concerns commitment. The assumption is that with the support and participation of the top management, all will be well. But the questions must be asked: well with what and well for whom? For planners? To be sure. But for the organisation?–Henry Mintzberg, The Rise and Fall of Strategic Planning, 1994.

Introduction

Whether a spreadsheet is being developed as a forecasting plan, a profit and loss account or a marketing plan, it is essential that due care and attention be given to the design and structure of the plan. Establishing some rules as to how all the spreadsheets in a department or organisation are developed enables different people to look at different plans and feel familiar with the layout, style, reports, charts, etc. This is similar to the way users feel familiar with software applications that have a common interface such as those in the Microsoft Office suite of products.

The objectives of good design in spreadsheet terms are exactly the same as those required for any other software development:

• To ensure that the spreadsheet is as error free as possible.
• To ensure that the spreadsheet can be used without much training or control.
• To minimise the work required to enhance or change the spreadsheet.

If care is taken to ensure sound structure and good design, a spreadsheet will be straightforward to develop, easy to read, simple to use, not difficult to change and will produce the required results.

The plan developed over a number of developmental stages in this chapter illustrates a variety of aspects of the principles of spreadsheet design and development. The series begins with a plan that has had little or no thought put into its design and layout, and as the chapter proceeds ways of improving and enhancing the plan are identified and explained. These plans can be found on the CD accompanying the book under the names style01 through style10.

Spreadsheet 1: Getting started

The spreadsheet in Figure 1.1 is a simple profit projection that may be of use to the author, but it is unlikely to be helpful to anyone else. This is clearly a quick one-off plan which has been prepared with very little care and which might not even be saved on the disk.

Figure 1.1. Simple profit projection

Problems with this spreadsheet

The immediate obvious problems with this spreadsheet are that it has no title, it is not clear what the columns represent (i.e. they are different periods or perhaps different products) and the author is unknown.

With regard to the data itself, the figures are hard to read as there are varying numbers of decimal places. Whilst perhaps there has been a growth in sales and price, the percentage has not been indicated. The costs line could also be misleading as no indication of where the costs have been derived is supplied.

Positive aspects of this spreadsheet

If the author of the spreadsheet required a quick profit estimation based on known data and growth rates for sales units, prices and costs, then the spreadsheet would have supplied that information quickly and in a more concise form than would have been achievable using a calculator and recording the results on a paper.

Spreadsheet 2: Ownership and version

In Figure 1.2 the three major shortfalls of the first spreadsheet have been remedied. The plan has also been given a title and author details have been included. It is important that every business plan has a clear owner who is responsible for overseeing the accuracy and maintenance of the system. A name plus some form of contact details should always be included.

Figure 1.2. Incorporating some annotation

Problems with this spreadsheet

The construction of the data and results is still unclear and the lack of formatting makes the figures hard to read. The costs remain grouped together.

Positive aspects of this spreadsheet

In addition to the owner details having been added to the plan, the date when the plan was written is a useful feature. The date becomes particularly important when the question of spreadsheet versions arise. Note that the date has been entered here as text. If a date function had been used, it would be continually updated each time the file is retrieved, whereas here it is the date of the last update that is required. The ruling lines above and below specific sections of the spreadsheet are also quite helpful. This can be quickly achieved using the automatic formatting features. These are accessed via the Format Autoformat command (Home Styles).

Spreadsheet 3: Formatting

In Figure 1.3 the data for the four quarters is totalled and reported as an annual figure. The values in the plan have also been formatted with the majority of figures being formatted to zero decimal places and the price line to two decimal places.

Figure 1.3. Formatting the plan

One of the automatic formatting options has been selected to shade and outline the plan.

Problems with this spreadsheet

By looking at the plan in Figure 1.3 it can be seen that the sales and the costs both increase over time. However, the rate of increase of the sales and the costs is not clear because the sales growth factor and the increase in costs have been incorporated into the formulae as absolute references.

The inclusion of absolute values in formulae is not recommended and can lead to GIGO[*]. To change the sales growth factor in Figure 1.3 two processes are required. First, cell c7 is accessed, the growth factor is changed and the enter key is pressed. This has changed the formula in this one cell, but only once the formula has been extrapolated across into cells d7 and e7 is the amendment complete. It is not difficult to see that there is room for error here in a number of different ways.

* GIGO is computer acronym meaning Garbage In Garbage Out.

Positive aspects of this spreadsheet

Having a current date and time indicator displayed on the spreadsheet ensures that a hard copy report will reflect the date, and perhaps more importantly the time it was printed. This is achieved through the now() function, which can be formatted with a range of different display options. Because it is likely that a spreadsheet will be recalculated, even if it is set to manual calculation, before printing, the date and time will always be up to date. It is of course possible to include the date and time in headers and footers, but during the development phase of a system the page layout is of less relevant than printing the section being worked on and so thought should be given to the positioning of the now() function.

The cells in this plan have now been formatted, which makes the data easier to read. When formatting a spreadsheet it is important to consider the entire plan and not just the cells that are currently being worked on. The entire spreadsheet should be formatted to the degree of accuracy the majority of the plan is to be. The cells that need to be different, such as percentages, can be reformatted when necessary. This is quickly achieved by right clicking on the top left corner of the spreadsheet at the intersection between the column letters and row numbers and then by selecting format cells option. Whatever formatting is now applied will affect the entire worksheet.

It is important to understand that formatting cells only changes the display and does not affect the results of calculations that are still performed to the full degree of accuracy, which is usually 16 significant decimal places. This is why a cell containing the sum of a range of cells might display an answer that does not agree with the result of visually adding the values in the range.

The round function is the only safe way to ensure that the results of a calculation are actually rounded to a given number of decimal places. Figure 1.4 shows two tables representing the same extract from a profit and loss account. In both cases all the cells have been formatted to zero decimal places, but in Table B the round function has been incorporated in the formulae for cells f15 through f19.

Figure 1.4. Difference between rounding and formatting cells

The formula entered into cell f15, which can then be copied for the other line items is: