How to build effective spreadsheets

July 14, 2008 at 10:46 am | In Business | 1 Comment
Tags:

Spreadsheets have become an important business tool. I first experienced them at university in the mid to late 1980s. I remember thinking, for the first time, that computers could really be useful. Since then some of the things that I have used them have been:

  • Business reports
  • Create financial forecast
  • Run horse race sweeps
  • Develop a betting program (unsuccessful) and
  • Calculate average km times in races

Spreadsheets are very powerful and can be used for a variety of purposes. However, in business, they are prone to error and an incorrect formula or a spreadsheet designed poorly can result in poor business decisions being made.

I have used my own knowledge and an article written by Shahid Ansari and Richard Blockto to provide some rules to develop effective spreadsheets. The following examples help to demonstrate the benefits of the rules.

Option A

Qtr 1 Qtr 2 Qtr 3 Qtr 4
Product 1 Volume 800 =+B2+B2*C3 =+C2+C2*D3 =+D2+D2*E3
Product 1 Volume % increase 0.01 0.02
Product 1 Price 10 =+B4+B4*C5 =+C4+C4*D5 =+D4+D4*E5
Product 1 Price % increase 0.05
Qtr 1 Qtr 2 Qtr 3 Qtr 4
Sales =+B2*B4 =+C2*C4 =+D2*D4 =+E2*E4

Option B

Qtr 1 Qtr 2 Qtr 3 Qtr 4
Sales =800*10 =800*1.01*10 =800*1.01*10*1.05 =800*1.01* 1.02*10*1.05

Both option A and option B produce the same results for the total sales of product 1 by quarter. However, option A will be the more effective spreadsheet in the future for the following reasons.

1. Identification and Segregation of Data: Option A clearly identifies each input. For example 800 is the volume of product 1 in quarter 1 and .01 is the % increase in volume of product 1 in quarter 2.

Each formula only has cell references in option A. Compare that with the formulae in option B that are a series of numbers. In option A it can be determined that the sales of product 1 in quarter 2 is the volume of product 1 by the price of volume 1. Alternatively in option B sales in quarter 2 are 800 by 1.01 by 10. The new user (or the original user a couple of weeks later) will have to guess what these numbers refer to.

2. Ease of making changes: Option A has highlighted the cells that can have direct input by giving them a yellow background. In addition the cells that should not be changed i.e. the cells that have a formula have been locked. The easiest way to unlock cells for input and to lock the remainder of the spreadsheet is to right click the cells that can have input, choose format and protection and uncheck the lock check box. Then choose the tools protection option from the main menu and click on protect the sheet.

3. Document Assumptions: Although it is not shown in the above example it is good practice to document the assumptions that you have made in developing the spreadsheet. For example some of the Option A assumptions were:

a. Product 1 Volume Qtr 1: Based on the prior year sales volume in the first quarter + 3 % which was the increase in the prior quarters sales volumes compared to last year.

b. Product 1 Volume % increase: Based on market research which indicates the total market will increase by 3 % in the next twelve months. The assumption is a 1% increase in quarter 2 and a further 2 % increase in quarter 4. This sales volume increase will be supported by a marketing campaign during which we also plan to increase our price by 5 %

These options should be documented in the workbook, preferably in a separate worksheet.

These are three simple rules to help develop effective spreadsheets and provide a better basis for business decision making.

1. Identify and segregate the data

2. Make making changes easy

3. Document the assumptions

How to win the race to Happiness

July 14, 2008 at 10:39 am | In Life | Leave a Comment
Tags:

Happiness has become a hot scientific topic over the last two decades. Recently the BBC presented a six part series called The Happiness Formula. This series covered topics such as:

  • Think yourself happy
  • The politics of happiness
  • The power of happiness
  • The happiness recipe

The scientific tests supporting the series suffered, to some extent, because happiness relies upon the subjective self assessment by an individual of their own happiness rather than an objective measure. However, that suggests that happiness is something that can be objectively measured and that may not be so. Perhaps each of us have our own definition of happiness that are all equally valid.

The instigator for this article was not the BBC series but a lecture that I heard while waiting to pick up my wife and son from the airport after they had competed in the Gold Coast Marathon Running Festival. The lecture was delivered by a guy called Daniel Gilbert. He is a professor in psychology at Harvard University. He has an entertaining delivery style and uses humour to communicate his messages about what makes us happy. Examples of this included:

  • Stating that heroin is not a misery drug but a happy drug. He claimed that the moment of taking the drug was the happiest time for the addict. However it was the rest of the addict’s life that was a misery.
  • We can sit through hours of a baseball or cricket match where nothing much happens but we can go home happy because we witnessed one or two minutes of magic. Similar to this I remember telling my dad in the 1980s that I could still enjoy seeing my football team lose each week as long as “Roachy” took a specky.
  • We can convince ourselves that we are happier with a $85 pair of socks than a $5 pair of socks that perform the same function.

He questioned the belief that it is children that make us truly happy. My two youngest did not like this part of the lecture. He stated that similar to the sporting matches it is the relatively short magic moments that children provide that help us believe our lives our happier overall. Those moments include such times as when they look in our eyes and tell us they love us or give us the unexpected hug.

His overall point is that happiness can be synthesised or manufactured rather than externally found or bought. The science that he uses to justify this includes:

  • There was no difference in happiness six months after the event between a person who was made a paraplegic and a person who won the lottery.
  • People were asked to rank six Monet prints from their most preferred to their least preferred. They were then told they could choose their 3rd or 4th favourite to keep. Most people chose their 3rd favourite. Some months later they were asked to rank the six Monet prints again. The average results were that the original 3rd favourite print was now the 2nd and the original 4th favourite print was now the 5th favourite.

Another point made both in the BBC series and by Gilbert is that despite being wealthier than prior generations we are not happier. The assertion here was that it is comparative wealth that makes us happier. That is if everybody is as comparatively as wealthy as us we are not likely to be any happier.

Gilbert is saying that we have the brain power to make us happy no matter what our circumstances. It may be easier to be happy while eating chocolate than it would be when eating brussel sprouts but our brain gives us the power to be happy no matter what our taste buds are experiencing. It is therefore, the tortoise who will win the race to happiness as it take it’s time to make the most of what it is experiencing now as it heads towards its goal.

Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.