Risk and probability are factors that we deal with on a daily basis. Our daily behaviour from the moment we get up and make our way to work involves numerous, generally low level, assessments of risks and we adapt our behaviour accordingly. Our reaction to a car moving at speed towards us is entirely different to a report of a meteor heading towards earth. While the end result of both could be disastrous, the probability of the former at the point in time when the headlights are heading towards you is much greater. So we understand simple risk and probability from day to day experience, don’t we?

I recently came across the Monty Hall paradox which suggests otherwise. Monty Hall hosted a game show in the 1950s, where a contestant had to choose one of three doors. Behind two of the doors there was no prize, but behind the third door was a car. After the contestant had chosen a door, Monty Hall would open one of the two remaining doors to reveal nothing. He would then ask the contestant whether they would like to stick with their original choice or change to the other unopened door. What would you do given that choice?

Logic appears to suggest that the contestant has started with a 1 in 3 chance, but that once the losing door has been opened it becomes a 1 in 2 chance. And if it is a 1 in 2 chance, there is no incentive to change.

The reality is however different. By changing your mind when faced with the choice of 2 doors you increase your chance of winning from 1 in 3 to 2 in 3. How can this be?

There are two elements to answering this question. The first is proving that there is an increased chance of winning by changing your mind, and the second is explaining why. The Excel link below looks at proving the benefit of changing your mind.

The spreadsheet randomly makes the contestant’s choice for 500 samples and randomly assigns the door which has the car behind it. The losing door that Monty Hall opens is then shown, and the number of wins for the contestant’s original choice and for the option they could swap to is calculated. The entire 500 samples are recalculated by pressing the F9 key. No matter how many times you recalculate, changing your mind roughly doubles your chance of winning.

The why is not as easy to explain. At the time that the original selection is made, there is a 1 in 3 chance of choosing the car compared to a 1 in 2 chance when the losing door is removed. The question can be reframed as: would you rather choose 1 out of 3 then have a losing door removed, or would you rather chose 1 out of 2? The original 1 in 3 odds remain unchanged if you stick with your original choice because the additional information you have received is that one of the other doors is a loser. You already knew that, and knowing which door number it is would only help you if it was the door you selected. As the odds of that original selection are 1 in 3, the odds of switching are 2 in 3.

Better and more detailed explanation and further proofs can be found at : http://en.wikipedia.org/wiki/Monty_Hall_problem.

How the Spreadsheet Works

The spreadsheet uses the randbetween() function to select the contestant choice and the door that has the car behind it. The losing door to be opened is then calculated by first testing whether the contestant and car doors are different. If so, the switch door is 6 (1+2+3) minus the sum of the two doors. If the contestant and care doors are the same, a further randbetween () function is used to decide the losing door to be opened.  The randbetween function recalculates every time the spreadsheet is calculated.

The spreadsheet is a simple example of Monte Carlo simulation. This is a method that uses repeated random sampling to compute results, and is an example of the innovative and creative approach to financial and operational modelling that Optimal Financial consultancy offers its clients.

Good business modelling puts the power to vary assumptions in the hands of the user. The user will not always know which variables they want to change before the model is developed, sometimes because the model itself prompts the user to extend their anaysis, ansd sometimes due to changes in external factors. Understanding the relationship between events is an important element to creating this flexibility. Where an event has a assumed date, there may be income and expenditure implications before and after that date.

For example, when a company opens a new branch, there may be legal costs and property agents fees prior to the lease start date, and there may be fit out costs after the lease start date. There may be regulatory or licencing issues to deal with before the branch can open, or there may be marketing activities and costs to incur in the period leading up to the opening date. Sales are also likely to be   a period of time after the rental start date.

As is often the case, Excel offers a choice in how this can be achieved. The example being used here is the opening of a new branch. The lease is assumed to start in February 2013, but legal costs will be incurred     two months before, and fit out costs one month after the lease starts.  

The example file can be downloaded at: http://www.optimalfinancial.biz/USERIMAGES/offset&date.xlsx

The   file shows the input cells in yellow. Any changes to the yellow cells will update the output.

The simplest way to deal with these costs is to manually enter them against the relevant dates. You could     input the dates of December 2012 for legal costs and March 2013 for fit out costs. This works for these specific dates, but if you want to sensitize for a delay in the lease date to August 2013, you need to remember to amend the   legal costs and fit out dates as well. In user flexibility terms this method is a total failure.

The spreadsheet shows two more elegant solutions using the offset function and using the date function.

1. Offset Function

The offset function is not commonly used, but enables you to reference a cell a specified number of rows and/or columns away from a named cell. The syntax is Offset(Reference, Rows, Columns). In the example attached, offset(D4,3,1) would return the contents of cell E7 which is 3 rows down and one column to the right of cell D4   i.e. 2. Cell E7 tells us that legal fees will be paid two months before the reference date.This cell is used in row 15, where the formula used is: Offset(F14,0,$E$7). The 0 tells us   that we are referencing row 14 and $E$7 two column to the right i.e. H14. Row 14 identifies whether the   rent start date has been triggered. The same logic applies to fit out costs. Cell E8 shows that the cost is incurred one month after the start date. This cell is used in row 16 where the formula is: Offset(F14,0,-$E$8). In this case the column reference (-$E$8) is negative because the event is after the start date.

The cost section (rows 19-23)   calculates the monthly rent recognising that fit out and legal costs are one off costs. It does this by finding the month where the trigger date changes from 0 to 1.

2. Date Function

The date function can be used to achieve the same result. The UK date function syntax is Date(Year,Month,Day). This method works by adding or subtracting the number of months in cells E7 and E8 from the starting date. Cell I7 has the formula: Date(Year($D$4),Month($D$4)-E7,Day($D$4)). This   formula   splits the start date into its year, month and day components and subtracting two months. The functions Year, Month and Day will return the relevant component of a date. Therefore if cell A1 contained 31-Mar-12, then Year(A1) would give 2012, Month(A1) would return 3, and Day (A1) would return 31.

A particuarly clever part of the Date   formula is that it recognises the need to change the year when the month is less than 0 or more than 12.

Changing the start date will recalculate the legal and fit out dates.

Both of these methods enable "what ifs" to be answered immediately. The example here is relatively simple, but a real life example would also have an income linkage, and may be part of a cashflow projection for a branch opening programme, These methods would enable the cashflow to be calculated, and scenarios to be run to fit the programme within funding constraints.

I have tended to use the offset method, but the date function has the advantage that it shows you the calculated date beside the input file. Let me know if you use a different method, or if you have any views on the two methods above.

It’s an old interview trick to ask a candidate to identify their greatest strengths in order to identify potential weaknesses. Being focussed is great unless it really means being too single minded to be a team player, being flexible and open minded is also good unless it translates into indecisiveness, and being creative is also a positive attribute unless it results in an inability to deal with day to day tasks.

So it is with Excel. It would turn up at interview supremely confident about its flexibility, its capacity for analysis, its reporting skills, its ability to perform routine functions as well as medium and long term strategic planning. It has a CV that includes every blue chip company. It’s undoubtedly a formidable skill set. Excel relaxes in its seat waiting to hear the words “You’re hired”.  The interviewer clears his throat. “That’s great, Excel, but I don’t see anything on your CV about these horror stories that European Spreadsheet Risks Interest Group have collated  www.eusprig.org/horror-stories.htm .  In the last four months alone, you have released confidential internal information on AstraZeneca to analysts, cost a local authority $400,000 in interest, and worst of all you have deprived 10,000 people of the opportunity to watch synchronised swimming at the Olympics.”

Excel squirms awkwardly then composes itself. “I appreciate that the outcome of each of these cases was poor, but I did absolutely everything I was asked to do. I am the ultimate team player. I won’t be beaten on calculation speed even on complex logic and high data volumes. But I rely on other team members to understand the business and to provide quality control. Each of these cases involved situations where the quality wasn’t in place. If you look at my work where there are standards in place across the organisation, and where checks and controls are routine, you won’t find these issues.”

“Okay” replies the interviewer “I don’t think you’ll ever make team leader but I’m confident that you can be an excellent team member with the right support and supervision”.

So what does this mean in the real world. Well the power and flexibility of Excel are undoubtedly its strength as well as its weakness. Excel is used for a massive range of purposes from simple single sheet instances with only basic functions to complex multi scenario long term planning models. Virtually all organisations use Excel to some extent for mission critical functions and for strategic planning. And yet very few organisations have taken the time to put in place standards for spreadsheet development. These would include:

·         The structure of spreadsheets (separating inputs, data, calculations and outputs);

·         Controls used to avoid errors when the spreadsheet is amended;

·         Checks put in place to identify any errors or potential errors;

·         The layout and colour schemes used, the functions used;

·         Identifying responsibilities for maintaining and updating key spreadsheets.

It’s not an onerous task to put this in place, but the investment in doing so will pay itself back within a matter of months. As well as a greater level of confidence in accuracy, there will be a much lower exposure to individual employees who have developed critical spreadsheets. There will be less time spent on employees learning how to use other people’s spreadsheet, and it will raise the company’s skill level in using Excel.

The answer to what standards companies should put in place will vary depending upon the organisation, its use of Excel and current practice. Optimal Financial Consultancy can guide you through that process. We don’t tell you what you to do; we use our expertise to work with you in a workshop setting to involve all stakeholders. The end product is a straight forward manual backed up by a training session. And we’re so confident about the services we provide that we are offering money back guarantee if payback is not achieved within a year. Contact Atholl Craigmyle at atholl.craigmyle@optimalfinancial.biz or on 07947 740287 for a no obligation chat.

My experience of spreadsheets goes way back to the pre Excel days of the mid 1980s. Lotus 123 was new, graphics were monocrome and the mouse was being talked about in almost sci-fi langauage as a future productivity aid. Having heard the buzz about Lotus, I was keen to understand what it could offer.

I was working for a offshore survey company who provided staff and equipment for rig moves and seismic surveys, and had implemented its first computerised accounting system and developed a management accounts pack. Much of the activity behind the accounting sysytem was manual. Quotes, bookings and personnel/asset chargaeability records were maintained manually. The opportunities for improved decision making and better management information were immense. One of these opportunities came in the form of the largest contract that the company had been invited to tender for. Creating my first ever fianncial model for this contract made clear the potential of spreadsheets, and amazed the directors when they saw the ability to vary assumptions. The next development was moving chargaebility records on to spreadsheet which steamlined the billing process and provided management information on staff and equipment utilisation. It was some years later while reviewing my CV that I realised that I had developed KPI reporting before the term had been coined!

The early spreadsheets, though a massive step forward, were basic and limited compared to today. Today's Excel can be viewed as a programming language. The sophistication of the product means that it can be, and is, used for complex high value pricing models across the financial services sector. This brings its own issues and concerns that I will explore in my next blog. However, Excel is still often under utilised, and even more often poorly utilised.

Every organisation should have a business model that shows what the profit and cash flow will be based on current expectations of the short to medium term. But they don't. On one occassion I encountered a publicly quoted company that did have a profit forecast and a cashflow forecast for the year ahead. All fine and good, except that the profit forecast had different assumptions from the cash forecast. And this was a business that was in a tight liquidity position.

But what about the realities of business life? Excel can't cope with seasonality, or in year cost/price changes, or calcualting increased/decreased staffing requirements as circumstances change, or any other situation you might encounter. Wrong every time. Excel can cope with all of these as long as the model is built well, and as long as the developer understands the business and can provide strategic analysis.

While there are some tasks that can be done better in other environments, I have yet to encounter a sitauotion that can't be dealt with in Excel. But I'm open to challenges at blog@optimalfinancial.biz!