Common spreadsheet problems; “Worst Practice”

I read an interesting article on CFO.com about the usual mistakes made when building financial models.

They suggest the following:

1) Poor Segregation of Data

2) Poor Documentation of Assumptions

3) Poor Documentation of Constraints

4) Difficulties in Making Changes

5) “Now It’s Here; Now It’s Not”

6) The Presentation Readiness Problem

They then suggest some ways to improve this:

—Encouraging the separation of data, and documenting the reference
sources.

—Making robust and useful key interim calculations and analyses for
which the data and your assumptions are being used.

—Encouraging a summary design for communication and presentation.

It is exactly these issues that we have spent the last few years building software to solve. We work to build all the important separations between inputs and outputs, we have over 20,000 checksums to provide integrity and then we layer a neat presentation pack on the top.

 As I discussed in a previous post why is it that there is a standard for how to prepare and present historic data but no such standard in the world of forecasting? A well designed standard and training courses to supplement it, with industry body backing would surely take a lot of the pain out of the forecasting process and reduce the possibility of serious errors.

If anyone has any other ideas for how to improve the process throw them into a comment on this blog and lets discuss.

Heres a link to the CFO article http://www.cfo.com/article.cfm/11288290/c_11362095?f=singlepage

Forecasting your carbon footprint 2

Following on from the recent post on this topic I saw one of these energy monitors in use the other day.

Great little tool for geeks, so I loved it!

I am going to get one for the office so people can see what each appliance costs to use and then we can use this to forecast our carbon use over the next financial year

Anyone else use them in their offices??

 

Forecasting your carbon footprint?

Access Accounts were the first to launch a module to allow you to account for your carbon emissions.

It uses the carbon tables from DEFRA and the Carbon Trust to calculate a companys carbon output and the idea is then you can use this knowledge to try to find areas for improvement.

I saw the launch at this years Softworld in Hammersmith and Tim Yeo MP was wheeled in to do the honours. I have to applaud the initiative Access have taken and will watch with interest to see how many of their customers use it and learn from it.

Accountancy Age wrote a good piece about this back in March

I am keen to explore how we may be able to do a similar thing in a forecast. It raises some questions.

Would we actually make business decisions based on the forecasted environmental impact? In the endless and relentless pursuit of profit I have to doubt this.

Breaking it into basics, would you do a video conference with a new prospect rather than fly to speak face to face to save your company several tons of carbon footprint.

Perhaps it might lead to the board insisting on using environmentally friendly aircraft rather than cutting out ”unnecessary flights” all together. That would be a good start.

I would be interested to know if anyone has started seeing pressure from the top to start being more energy efficient…car sharing perhaps, recycling of course but anything further than this? 

Free workshop for mid size businesses

We are co-hosting a workshop in the middle of an event being run by the M-Institute on the 17th June in London. Places are free so please contact cat.corcoran@ruggedlogic.com or call her on 0151 702 8330 to book your place. Its free!!

The focus of our workshop at 9:30 will be:

Forecasting and the management of costs and cash in a constrained business environment and the key imperatives for IT in an M business in 2008 – helping to create a 90 day action plan to drive growth and constrain costs

The second workshop of the day will focus on green issues and specifically ” Accounting for the Green Agenda”.

Some of you may have seen the recent launch of Access Accounts carbon emissions module and I wouldnt be suprised to see some other “mee too” releases from other vendors in the near future.

It should make for an interesting day.

Some of the team from Rugged Logic will be there so drop us a line if you want to meet up for a coffee.

More information on the day is available here

 

Having trouble with Excel 2007 commands?

It seems there is a lot of concern amongst people moving to Excel 2007 that they cant find the commands under the menus they are used to

Microsoft, unsuprisingly, have done lots of work to make sure the upgrade is a smooth as possible. After all they spent over £1bn on R&D for this new ribbon technology so they want you to adopt it!

Heres a great resource to use to find those function. Simply start the interactive guide and perform the function as you would in 2003. Then the interface will spin and show you where the function exists in 2007.

This is what happened when I used it to find how to hide and unhide sheets.

It
 
And then the demo moves to the 2007 interface and repeats your move…

 

Very neat.

Here is a link to the interactive tool. You need Adobe Flash Player 7.0 or later

http://office.microsoft.com/en-us/help/HA101491511033.aspx

 

 

Article on the “process” managers use when planning

Jyoti Banerjee, a well respected technology specialist, wrote an interesting piece on the processes that managers use and how they are split between “thinking processes” and “doing processes”. This is very relevant when looking at planning since future forecasting requires more of the thinking piece. You can’t rely on what happened in the past to predict the future…as the good old stock brokers disclaimer always reminds us. 

“processes are about analysis and benchmarking. While learned processes are about business “as is,” perceptive processes are about business as-it-will-be.”

The full article is here

Modelling errors and an organisation that is trying to stamp them out

EuSpRIG is the world’s only independent web based source of information describing the current state of the art in Spreadsheet Risk Management.  Their site makes an interesting read, but beware if you are a fainthearted FD!

As well as attempting to describe “best practice” for spreadsheet development they also list the publicly known errors caused through inaccurate spreadsheet design.

This is my personal favorite: (This link will open the Stories section in a new window)

NASA misstated by $644M: undetected spreadsheet errors in “ad hoc” process

With a current budget of $17bn , this is still a significant hole.

Being interested in statistics, this figure is more than the Annual GDP ($) of each of Zambia, Nicaragua and Mozambique. Is it worth it….thats another blog!!

Not a bad place to start…

Since this site is all about financial planning issues it would a good start to discuss the definition of a financial plan.

Wikipedia gives its own view:

In general usage, a financial plan can be a budget, a plan for spending and saving future income. This plan allocates future income to various types of expenses, such as rent or utilities, and also reserves some income for short-term and long-term savings. A financial plan can also be an investment plan, which allocates savings to various assets or projects expected to produce future income, such as a new business or product line, shares in an existing business, or real estate.

In business, a financial plan can refer to the three primary financial statements (balance sheet, income statement, and cash flow statement) created within a business plan. Financial forecast or financial plan can also refer to an annual projection of income and expenses for a company, division or department. A financial plan can also be an estimation of cash needs and a decision on how to raise the cash, such as through borrowing or issuing additional shares in a company.

While a financial plan refers to estimating future income, expenses and assets, a financing plan or finance plan usually refers to the means by which cash will be acquired to cover future expenses, for instance through earning, borrowing or using saved cash.

We’ve all heard the phrase

Turnover is Vanity

Profit is Sanity

Cash is King!!

Never has this held so true in the current economic climate.

This blog is intended to cause debate and raise issues to help anyone who has to produce financial plans, to make them more accurate, get the answer quicker and with less hassle.

 

Question: Have you been trained to build a financial model?

Forecasts get reviewed by management, boards, investors and potential investors. They drive the decisions that those parties take with regards the future direction of the company. Yet amazingly there is no industry standard for how to produce a cashflow forecast.

I find this quite incredible.

When you buy an Accounting System, the software is designed with rules and workflow to make sure you do the right things and do them correctly. It spits out your reports in the IFRS standard, quite probably, and you rely on them as the “Truth”….this is what happened last month, look, the system says it is so.

In addition you are trained to use the software to make sure you are doing it right.

Who shows you how to model a business. Who shows you how best to write formulas, how to linke cells, how to insert checksums. Sure there are courses you can go on and if you train with an accounting firm they will send you on a training course. Very few trained accountants then actually use this training in practice. When an accountant then finds himself in an FD position, he has to recall that training to produce, arguably the most important document that all the stakeholder need to rely on to make decisions.

I would love to know how many FD’s have been professionally trained to produce forecasts. If you have, let me know.