Blog 1:  Moving on from Excel

According to surveys, Excel is still the dominant tool used by organizations for planning. There are many reasons why this is the case, ranging from the fact that everyone has a copy and so the software and usage price is ‘free’; it’s easy to use (for the initial setup at least); is very flexible in that you can setup whatever you want including the collection of text, dates as well as numbers; and it comes with some great charting and formatting capabilities.

But as organizations that use Excel know, at some point systems built on this end-user productivity tool, start to fail.   However, the concern for many is that the alternatives have their own issues – in particular with reference to ease of use, flexibility of what can be collected, and cost.

So how do organizations decided on what planning tools to use?  At what stage are they forced to exchange their spreadsheet system for an enterprise application, and does that solution have to be complex and expensive?  These are some of the questions we hope to answer in this series of blogs.

Planning Complexity

To get a better understanding of how planning requirements change within an organization, consider the following company, XYZ Incorporated, as it grows into a large, multinational concern.

Planning as a Sole Trader

XYZ Company was founded as a reseller of ballpoint pens.  The owner and workforce of four people buy in a small range of quality pens from different manufacturers and supply them to a number of shops within their local area.  For XYZ to grow they must ensure that the resale price is competitive with other suppliers and that the anticipated sales revenue covers the cost of buying, marketing, and distributing the pens along with paying the wages and other administration expenses.

The planning requirements here are quite simple and because there is only one person involved, they can be easily modelled in Excel.  The model evaluates discount purchases from suppliers as well as what discounts could be given to customers should they want to buy in bulk.  Output from the model includes a target sales price, the volume to be sold each week, an associated budget for each marketing, travel, etc., and a cash flow forecast so that adequate funding is in place.

Planning as a small manufacturer

Business is good.  Pen sales increased and they are now sold through a number of wholesalers across the country as well as to large independent shops.  However XYZ are experiencing supply problems in terms of delivery dates and quality, which they feel is vital if they are to retain customers.

To fix these issues and to be more competitive (and more profitable), XYZ believe they need to manufacture some of the pens themselves.  This will require an investment in machinery and warehousing capabilities for both components – each pen typically consists of 26 different parts, some of which are bought in, while others are manufactured from raw materials – and for finished stock.

However, these changes – an increase in the number of outlets in a wider geographic area and a move to manufacturing – greatly increases organization risk.  This is because of the overhead costs to be covered are now much higher; any miscalculation of variable costs (e.g. Price) can cause significant losses due to the high volumes involved; there is a danger of losing customers (and hence revenue) if production doesn’t meet demand; wasting resources (which detract from profits) if more goods are produced than is required; and the capital investment for the machinery has to be funded.

These risks greatly increase the complexity of the planning model(s) required, which now need to cover:

• This will include a sales forecast broken down into each pen type, along with a budget for the promotional program.
• Pricing – this still needs to be competitive as well as generating sufficient margin to cover organizational costs and investment.  It also needs to evaluate promotional pricing to capture market share.
• Optimum production levels – management will need to decide which pens to manufacture, at what time, and the levels of stock to be held.  This should be linked to the sales forecast
• Raw material purchases – what materials do they need to buy, from which suppliers, and the levels of stock to be held.  This will need to be linked to production levels but XYZ may want to take advantage of any ‘special offers’ that suppliers are willing to give.
• Warehousing and logistics – this will need to cover the best way to deliver finished products to customers, which could either be by their own transport capability or via a 3^rd party carrier, depending on location.
• Cash flow and sources of funds.  This will need to show how much cash will be needed and how the operation and investment is be funded.

It’s unlikely that all of the above can be covered by one single model, and it’s likely that multiple people will be involved.

However, as the organization is still relatively small in the number of people and that they are all based in a single location, using a spreadsheet to plan is still possible, although much care needs to be taken when setting up formulae, as an overlooked error could prove catastrophic.

Particular issues related to using a spreadsheet for planning includes linking the different parts of the plan together.  For example, cash flow will need to take into account sales forecasts by customer and the payment for raw material purchases, both of which could come from different planning spreadsheets.  Similarly, the order over how the plan is constructed needs to be carefully managed.  i.e. production planning needs to be re-evaluated each time a sales forecast is received, while marketing promotions need to reflect what can be produced and the lead time required to order/stock materials.

To avoid potential errors as well as to allow the organization to grow, now would be a good time to consider and enterprise planning product.

In the next blog I will look how the complexity of planning increases once an organization becomes a public company and the fundamental reasons why spreadsheets fail.