To serve or not to serve ... that is the question

 

Number 1: how to retain a customer

Back last September, the heating system was having a few hiccups so I went to the local Screwfix Limited store (they had late opening) and purchased a fan heater over the counter.   It was fairly late, fairly cold and I still had work to do.

This December the heater broke down.  Being me, I took it apart to find out why.  A connector had not been properly insulated and had burnt through.  Naturally, of course, I couldn’t find the invoice (in my haste to use the heater I think I must have thrown in away in the packaging).  I resigned myself to writing off the cost.  In any event, according to the warranty I had voided that by
opening the casing.

I was concerned however about the manufacturing fault and so emailed Screwfix with photographs of the damaged component.  They replied asking for the purchase order number.  “Hello”, I thought, “this is going to be a bad experience” but I emailed back saying I didn’t have any purchase documentation. I told them them where and when I purchased the heater and I wasn’t trying to claim on the warranty, but I wanted the manufacturer to know that there could be a manufacturing fault.

The next day I got a reply.  Screwfix had traced my purchase, had credited back the purchase cost to my bank account and given me a voucher for a future purchase on top of that.  They had also checked their records of complaints and were satisfied it was one off fault.

Needless to say, I was impressed.  I’ve told others therefore about this and I am writing this blog to expand that reach.  They deserve it.  And I will be back to them again.

Number 2:  How not.

I have a set of headphones I use for video conferencing.  I’ve had them for years.  Not surprisingly their usage has gone up rather a lot recently.  In December, the foam ear-pieces started to fall apart.  The particular headphones were no longer made and it wasn’t clear from the manufacturer’s website which of their current ear-pieces would fit.  They don’t show dimensions on the web-site, of course, that would be too helpful: they only show part-numbers.  Pre COVID I would have called into a shop and asked.  But at present, there was nothing for it but to email the manufacturers.  

“Can you give the model number?” they responded. 
“Yes,”  I replied, “I already did in my original email along with the dimensions of the ear-pieces I need.”

“We don’t make that model any longer” they said.  “I know!” I replied, “I’m asking whether any of the ear-pieces you make for existing models would fit.”
“We’ll get back to you”….

Three days later I got an email:  “Which of the new models would you like ear-pieces for?”
“I DON’T KNOW.  I’m asking you.  Please – ears have not changed that much in shape in ten years, something must fit.”

I’ve still not had a reply.  How difficult is it to look at the dimensions I gave and compare them with those of the models they currently make?  Apparently extremely difficult.

In any case, we’ve made some replacements ourselves which work.  I’m not throwing away a perfectly good set of earphones because of incompetent sales people.  They’ve lost a small sale but, more importantly, I won’t be buying anything else from them for a long while.

Motto:  treat your customers with respect.

A less scrappy business

 

We have a Vauxhall Zafira which is now a few years old.  It ran perfectly until it failed its MOT as a small valve switch on the engine management system was acting up.  It didn’t actually stop the car from running but it meant that emissions could potentially breach ULEZ limits. 

The car is used for transporting lots of “junky” stuff for various projects and we would like to still have use of it.  Partly because of the expense of replacement, partly because we are quite fond the vehicle which has give us years of service, but mainly because the idea of replacing a whole car for the sake of a £50 component is simply horrible. 

But Vauxhall have stopped making the part.  All the spares companies have done likewise and we can’t find a breakers yard who will get one from a scrapped vehicle.  So it looks as if we will be forced into buying a new vehicle and scrapping the old.

This is, of course, what car manufacturers like us to do.

And it’s not only car manufacturers.  A component of our curtain rail (in a large bay window) broke.  It was a small plastic piece no more than a couple of centimetres long. You know where I am going with this: it’s no longer made and the equivalent component in the replacement system is incompatible (“improved” in sales-speak).  We could of course buy a whole new curtain rail, have it moulded to shape and installed at a cost running into hundreds of pounds. 

This does have a happy ending (I wish the first example could).  Our son had access to a 3D printer and we scanned the parts of the broken component in, tidied it up in FreeCAD, and printed a new one.  Works perfectly.  Cost: 3 hours of spare time (mainly learning how to use FreeCAD) and about £1 worth of plastic or less; installation time 10 minutes.  So if we can make replacements to order, why can’t the company?  At the very least, it could publish the template for such components so consumers could print them directly.  It won’t, of course, it wants the profits from forcing us to buy new.

I loathe, as you may gather, the throw-away society.  So it comes with some relief to read that IKEA has seen the light and plans to sell replacement parts such as chair legs and arm rests for its furniture, although those plans are at an early stage.  The company already buys back certain used items, such as its Billy bookcases, for resale or recycling if they cannot be sold, but this is a welcome extension of sustainability from the world’s largest furniture business.

As consumers we need to encourage all other businesses to ensure that their products are repairable and reusable for longer.  We need to look at retaining and repairing products rather than disposing of them.  Landfill sites are still full of mobile phones, upgraded every year by throwing away the old and buying the new.  The seas are full of micro-plastics much of which comes from plastic
clothing, worn for a season or two and discarded.  I could go on.  I confess that I have my own limits on ecology:  the car is too useful to give up, but manufacturer could do a lot more to help us (and the planet) and it is up to us to insist they do.  So, well done, IKEA. 

How has Excel faired - a five year review

 

 One of the few blogs I have retained from my old collection was my wishlist for changes to Excel that I raised at a meeting with Microsoft back in 2015.  Which of my wishes has come true?

     First, let me say, Excel has added a whole lot of stuff which makes it a far better product than it was then and I wouldn't now be without.  Of the many changes, I rate the following as the star points:

• Power Pivot and M-language which allows a much greater ability to import all sorts of data and manipulate it without resorting to VBA
• Dynamic arrays
•  XLOOKUP() – well overdue although limited use in production spreadsheets until it has been rolled out to everybody and, indeed, the same with LET() and LAMBA()
•  Stocks and Currency Data types and the now in-test historical stock information (admittedly only replacing what we had once upon a time before the plug was pulled on it)
•  Slicers, Timelines and new chart types
•  Much more flexibility in building dashboards via BI
•  Better collaboration tools with notes and comments.

That said, almost all I wished for five years ago still isn’t there.  There remains a real lack of tools for structuring and documenting spreadsheets: no notes pages, no easy way of keeping track of linked spreadsheets or documenting ranges and no way of linking conditional formatting or data validation to styles.

My second set of wishes were to do with spreadsheet protection – particularly around ranges.  Indeed, the problem has been made worse by the introduction of Power Pivot and dynamic arrays.  Locking down the right parts of spreadsheets was nigh-on impossible and still is.

In my minor amendments, I came up with the following list:

• a better way of tracking circular errors – no change
• all functions should work if they refer to ranges in closed workbooks – no change
• Improvements to array formulae – generally can avoid the need for this now with dynamic arrays
• Ensure cells with spaces in them are treated consistently – no change; and we now need a better way to deal with null cells given that Power Pivot treats them differently to the Excel engine
• Introduction of RegEx for find and replace – no change
• Expand the use of the CELL() function – no change.

I could add to that the need to increase the number of events trapped by VBA to give the sort of functionality that is available in Access, again even more important now that we can have values returned by Power Pivot, but the list goes on and on.

The verdict with 2020 hindsight: we now have a version of Excel with more functionality than it ever possessed in 2015 but with some gaping holes – some easy to fix, others more difficult, but all still needed.

Microsoft asks for people to vote for the improvements they would like to see and you can add your voice at http://excel.uservoice.com and I would encourage you to do so.

So what are these new lambda functions Excel has?

 LAMBDA: The great new addition coming to Excel and available in Microsoft's beta programme. 

Excel's Lambda function is a method of a taking a formula that you would normally enter into a cell and giving it a name.  You can then use that name wherever you like instead of repeating the formula. 

Or as Microsoft puts it, users will be able to use the Excel formula language itself to define short reusable functions within the spreadsheet itself without resourcing to VBA.  As one function will be able to call another, there is potentially no limit to the complexity and power of what you can do through this function.  It is, for the geeks and Microsoft says, making the language Turing Complete.

More detail can be found here in Microsoft's technical blog.

This looks as if it will be hugely useful and I can see it simplifying the maintenance of spreadsheets everywhere.  It will potentially mean that some spreadsheets can be re-written without using VBA at all,  a great benefit for those organisations where security prohibits the use of VBA code. It comes however with the downside that the use of the LAMBDA() function as implemented will make spreadsheets more difficult to follow and debug – but more of that anon.  

But it isn’t really a lambda though.

Lambda functions are normally described as anonymous functions and would be used without naming.  The purpose of the Excel LAMBA() function is, somewhat different, and serves to encapsulate an Excel formula and deliver it as a named function for multiple use in cells.

Let us give an example.  I’ll use Python to illustrate:

I could write a simple function as follows:

def square(x): return x**2                                                                       (1)

and use it as follows:

map (square,[0,1,2,3])                                                                          (2)

This is pretty standard approach to a function and you can see how something similar can be done in Excel.  In Python however if I didn’t want a function which could be used elsewhere I could write a lambda statement thus:

map(lambda x: x**2,[0,1,2,3])                                                              (3)

This arguably is similar to the new Excel LET() function which can be used in a similar way:

=LET(x,{0,1,2,3,4},x*2)                                                                        (4)

The Excel LAMBDA function is arguably much more useful as it is reusable, as in (1) above.  We write a function:  =LAMBDA(x,x^2), and once we have added it to the Name Manager and call it “square”, we can use it in any cell as in:
  =square(2).

But do you see what we have done by adding a name?  We have really turned it into a version of (1).  In fact, it is functionally identical to the VBA:

public function square (x as double) as double
     square = x^2
end function                                                                                          (5)       

It’s not a lambda function as such but a clever way of introducing re-usable formulae as user-defined functions without needing to resort to VBA.

As I say, it is a welcome addition to make maintenance of formulae much easier – a “change-once change-all” approach which has to be commended. It however still a little clunky in its implementation though than it should be. The formulae, rather than being in visible cells, are now hidden away in the Name Manager.  This makes its use more tricky and debugging spreadsheets considerably more difficult.  It almost seems as if it has been shoe-horned into Name Manager and needs a little bit more thought around its interface.  

It would be much better if the LAMBA() function names were in their own manager.  The Lambda Name manager could then be designed to check the syntax of the function in the same way as it is checked when a formula is entered into a cell and have a help dialog as the fx function does in the formula bar.  In fact, the function bar functionality should be imported wholesale into a Lambda Name manager. Finally, when a LAMBDA() function is used, the Lambda function manager should be automatically available showing the underlying formula being called, rather the convoluted way we will need to check at present via Name Manager.

I’ve made these suggestions to Microsoft and I am sure others have had similar ideas.  It is, like a lot of early iterations of Excel additions, an extremely powerful new tool which just needs a better implementation.

Overall, what is the conclusion of its first use?   It's not a lambda function: it has the potential to be very much more useful.

A new begining

 In celebration of  the end of 2020 (an awful year for all of us) and the beginning of a new year, I've taken down almost all my old posts.  They were either wholly out of date or need a modicum of updating - the perils of writing about business and accountancy.

I shall be starting again.  So watch this space...

How to improve Excel

Does Excel do everything you want, easily properly and first time?  If not, read on and bear with me.

A recent event held by the Institute of Chartered Accountants in England and Wales, discussed the 20 principles of best spreadsheet practice and something very interesting came to light.

I raised a question about the difficulty that Excel sometimes placed in the way of adhering to these principles, particularly as regards spreadsheet design and security.   James Akrigg of Microsoft who is Head of Technology for Partners at Microsoft came up with an answer which surprised us.  Not because he answered the question (which he didn’t directly) but because he mentioned a website where Excel users could write in and vote on suggestions to be incorporated in forthcoming releases.  He even implied that the turn-around on adding to Office 365 products could be weeks rather than years if it was practical and enough users wanted it.  Almost nobody – maybe nobody – at our meeting had heard about it.

The website is http://excel.uservoice.com/ and I’d urge all Excel users to visit it.

  

There were very few suggestions there when I logged on, so I think I doubled the number.  

The first ones I've made were to help with keeping a consistent structure and documenting spreadsheets: 

• Provide more worksheet functions for working with coloured cells: we all love to colour code cells to show different types of value, so for example being able to use SUMIF() to total a range of certain coloured cells would be boon
• Provide free-form notes 'pages' which could be added to sheets so that it is easy to record the purpose of sheets, calculations etc as you go
• Provide prompts to document and describe range names on the notes pages when the ranges are created; do something similar when styles and user defined functions are created.
• Provide, again possibly through the notes pages, a method of tracking which other spreadsheets are linked to data FROM your spreadsheet
• Provide a method of  automatically listing of ranges, names etc (as the Access documentator does)
• It would help spreadsheet design if specific conditional formatting rules and data validation rules could be set within styles.

 I was also going to add something about providing better graphical representations of the dependence trees but somebody got there first on that, so I voted for it.

I then moved on to the spreadsheet protection principle:

• Expand the range of protection options so add/edit/delete comments is a separate option for example to allow users to add comments to cells where they are not permitted to change values
• Allow addition/deletion of rows within a protected spreadsheet for only a certain range of rows (for instance allow rows to be added within a table, but in the rows above or below)
• And do the same thing for columns.

 Then there were just a few which  I felt were just helpful to users or bug-fixes: 

• Provide a better way of tracking circular errors possibly through a diagrammatic representation of cell linkages
• Certain functions such as  COUNTIF and SUMIF give #VALUE errors if they are referring to ranges in closed workbooks, others such as MATCH don't.  This often means complex formulae such as SUMPRODUCT need to be used to avoid the problem. This needs fixing: formulae should not given #VALUE errors just because the underlying workbook is not open.
• Array formulae should work consistently with all worksheet formulae.  At present some can be used in arrays and others not.
• Ensure cells with spaces in them are treated consistently.  For example if B4 contains a space and B5 contain 0 then =sum(B4:B5) gives 0 but gives a #VALUE error if the range referred to is on a linked workbook.  This should not happen.
• Search and replace needs refining to use a more extensive RegEx : at present doing any complex search and replace within formulae is tricky or impossible.
• Expand the range of information to be returned by CELL() to include the interior colour of the cell, and the cell formula as text.

So that was as far as I got.  I’m sure you could add some more.  Or just go and vote for mine.

It seems like we have a chance to make Excel an even better product, so take James up on his challenge and go and do it.

A reminder, now you have got to the end, of the website: http://excel.uservoice.com/ 

And if you want to look at ICAEW's list of 20 principles of good spreadsheet practice you can read them here.  

Systems design and why today's anniversary is important

The night of the 20^th/21^st July is an anniversary we seem to forget all too readily and yet it will probably be one of the few dates in the 20^th century that will go down in the history books.  It was when, in 1969, man first properly transcended the globe:  the day Neil Armstrong and Buzz Aldrin in Apollo 11 landed on the moon.

Many people recalling the space programme think only of the astronauts – in popular history they are the ones remembered.  But the achievement had a cast of thousands and, for me, one of the most astounding things about the whole Apollo era – and one reason why I lecture on it even today – is the sheer scale of the management behind it.

There was less than the full ten years available to NASA when Kennedy announced that America would land a man on the moon within the decade and bring him safely back to Earth.  At the time of that announcement, man had been no more 250 miles above the Earth.  Kennedy was charging NASA with sending man 250,000 miles -1,000 times further - from the Earth in a short eight years.  It would require rockets 40 times larger than ever been built before,  the largest ever land transporters, the largest transport aeroplanes,  the largest free-standing building that had ever been built, and some of the biggest computers and some  of the smallest of their time.  New methods of manufacturing, of navigation, new foods, new materials, even down to new methods of packing parachutes were needed.

At during that period – 8 years - NASA grew from a couple of thousand employees and a handful of contractors, to over 35,000 direct employees, and around 450,000 workers directly employed by over 20,000 contractors, subcontractors and other government agencies.  The total budget ran to USD$24 billion. 

As a result NASA also had to invent new management techniques.  Its management rapidly had to learn the hard way about budgeting and quality control and develop techniques we still apply today such as phased planning, configuration management, PERT analysis and systems engineering – all terms themselves invented for the missile programme.  The rigorous methodology – enforced after the Apollo 1 fire – showed up nowhere more than then in the differences between the failure of the fledging European Launcher Development Organisation in the 1960s and the later success of the European Space Agency and the Ariane launcher subsequent to their adoption of these techniques.

It is impossible to do this justice in a single blog.  It is even hard to convey the level of the issues involved.  There were 5 major manufacturing centres across the US to co-ordinate; and thousands of subsidiary locations for those contractors each with their own subcontractors, all of whom needed to develop their components to fit perfectly together and on time.  And those components needed to work first time, every time, once in space.  There were, of course, fail-safes built in with as many back-ups as possible, but even if failure was not fatal (and space is an unrelenting environment) component failure could easily prejudice the decade deadline, as the Apollo 1 fire nearly did.   Critical therefore was design and control.  

As one senior engineer put it:  “If you get the design right and you manufacture it right, you shouldn’t need to test it.  All the testing does is wear the components out and ensure they were more likely to fail.”  And, as was pointed out, how many Apollo Saturn spacecraft would you need to test launch to ensure 100%reliablity:  if you had to do it that way, you wouldn’t get to the moon in a hundred years.

Ok, that is rather a radical approach and, of course, components were tested.  But they were also rigorously controlled.  Not only for example, could management tell exactly the compositions of the various aluminium alloys used in fabrication and which manufacturer made it in what batch, they could tell you where, when and by whom, the aluminium was mined, down to the precise seam it came from.

As Dr George Mueller, Associate Administrator of NASA’s Office of Manned Space Flight, said in an interview:  “Reliability analysis should be in the design process.  You can’t measure reliability in, you have got to design it in”.

And this was true – and remains true – not only about hardware.  It is true about all management systems and was applied through the Apollo program in that way.  Reliability needs to be designed into human resources systems, into accounting systems, into corporate governance systems, into all critical systems from day one.  And the systems need to be looked at as a whole: hardware, software and manuals do not make a system.  People, processes and relationships make the system and we all too often forget this in business.

To take one example, probably closer to home for those who work in the city.  Most mergers, we hear, fail to deliver any value.  The whole becomes less than the sum of the parts and that is often down to failure to plan the proper systems – and more often than not – a failure to understand where people and relationships fit into those systems.

There are many lessons to be learnt from the Apollo program and one that many businesses still need reminding of again and again is:  understand, design and manage the systems properly and you can, in Dr Mueller’s words “do  the impossible”.