Low Fares? Define Low

I don't like AA ... that's an airline! They try to convince us that everyone can fly now because they are so cheap.

Well, because their competitors do not fly to KL on Sunday from Bangkok I had to choose them. Kerching! Low fare? Not at all! I could actually pay less with the other airlines AND have a meal AND not scratch around worrying about the weight of my luggage AND choose any seat that's available: from previous experience,  they will give me a window seat, the worst possible option for me.

Then, due to misinformation from another carrier I found I had to change my flying time. SORRY,  it's less than 48 hours to take off so you can't change that flight. So, horror of horrors, I had to buy a new ticket. Low fare? Free meal? Guaranteed baggage allowance? No, no, no. I consider this second ticket outrageously expensive and feel sad that I have had to break my vow of refusing to fly with this airline. Until recently, I had not flown with them for 2 or 3 years. I hope this is my last trip with them for a very long time.

Appalling Behaviour on a Newspaper Forum

I have discussed many topics on newspaper forums over the years: The Independent had one then closed it; I subscribed to The Times for a few years and that was a good experience; now I am with The Guardian, new starter. I have paid membership dues to the Guardian because I feel I should but they say many people use their services but don’t pay. No judgement there, that’s how the Guardian manages things. I engaged in my first full blown discussion on the Guardian site last week when I told the assembled horde that Al Gore’s Inconvenient Truth visuals and other parts of his message were wrong or inappropriate. I said that and I believe that: my argument is that his graphs were far too nice to warrant critical review and many of his other ramblings have been challenged: I said that knowing it to be true: youtube contains many videos in which Gore is caught out. Immediately, I was attacked rather forcefully and I have to say that I did call Gore a liar. I defended myself and as the debate wore on, I was called all sorts of names and accused of all sorts of calumny. Then someone said I was a climate change denier. They made that up because I didn’t even infer that let alone say it. Well, that was it: John Samuel claimed to be a weather expert of some kind and he really stuck to that line and in spite of proving no such words came out, he called me a liar many times. Good for him. I even came here to this blog and reminded myself of what I had said about Gore when I had seen his film. I told Samuel that I had blogged about Gore in 2007 and again in 2010 … three references altogether. Since he was being so unfriendy, I suggested he search for my blog to prove for himself the truth of my assertions. He couldn’t find it! Smart as he claimed to be, in spite of clues as to a particular phrase to put into google, it was beyond him. He found an Excel blog and pretended that was the blog I meant. I said no, so he called me a liar again. I then mentioned having read and reviewed State of Fear by Michael Crichton which I said contained many interesting ideas. Samuel replied to tell me how bad the book was and that no one liked or respected it. Climate change denier that I am. You’re getting the picture now: John Samuel who does not know the difference between you’re and your, tells the world that they should not read a book because he does not like it. The reality is that Crichton might have got some of the science wrong but he questioned things and I think that was a very healthy thing to do. As Samuel tried only assassinate my character, others joined in and of course, several of them pointed out their master’s degrees, how they have been peer reviewers for academic journals and what do I know? All the while, these people have assumed that Samuel has identified the truth: he said the same thing so often that the others believed he must be telling the truth. I have seen this before and it is impossible to challenge because everything is subject to gainsay. Samuel was obviously frustrated that I wouldn’t just give him the link to my blog posts so I was branded a liar again but I felt he’s so smart he can ruddy well find it. Moreover, he would clearly have merely said something innappropriate about what I had said so I let it ride. I am sharing this post with him now and even he will then find that I really did make those posts about Gore all of those years ago and that I am not a liar! As that discussion wound down Samuel revealed another two traits: he labelled many people as liars if he didn’t like what they said; and he was obsessed with having the last word. I played the last word game and he played along exactly true to type. If John Samuel is really a scientist I just wonder if his work is ever peer reviewed as he cannot take criticism. He must be very difficult to work with when someone like me turns up and refuses to bow down to him. A very immature bully in my opinion. I decided to write this post since it is a controlled way of sifting out that man’s own lies and exaggerations and at the same time sharing with others just how he behaves in debate. Samuel and others are free to comment here and I will post everything that's not just outright offensive! I made mistakes in my discussions and admitted them: I am not too proud to do that but I will not allow some tiny minded little man to bully me or anyone else into kow towing to him. DW

Put the Bloody Phone Down or it Will Kill you

On our way to replace the driving licence I lost and as we were crossing the road a woman on a motorbike came round the corner and was driving right at me and Abi. The stupid woman was reading something on her phone as she rounded the corner and had not seen us.

I was ready to fend her off but she looked up, saw us and avoided us. I said to her, Watch where you're going and put your phone down!

How stupid and how dangerous that woman is.

Multi worksheet scenario model

Tuesday 25th October

This is on its way ... as promised ... I will upload it later today or early tomorrow.

Duncan Williamson

The Charge of the Light Brigade

As I was travelling across country by bus today I thought about Alfred Lord Tennyson's poem The Charge of the Light Brigade. I went and found it, read it and then created a voice file of a professional actor reading it out loud. The professional actor is me! Unfortunately I can't upload the voice file here ... I will find a way. The Charge of the Light Brigade Half a league half a league, Half a league onward, All in the valley of Death Rode the six hundred: 'Forward, the Light Brigade! Charge for the guns' he said: Into the valley of Death Rode the six hundred. 'Forward, the Light Brigade!' Was there a man dismay'd ? Not tho' the soldier knew Some one had blunder'd: Theirs not to make reply, Theirs not to reason why, Theirs but to do & die, Into the valley of Death Rode the six hundred. Cannon to right of them, Cannon to left of them, Cannon in front of them Volley'd and thunder'd; Storm'd at with shot and shell, Boldly they rode and well, Into the jaws of Death, Into the mouth of Hell Rode the six hundred. Flash'd all their sabres bare, Flash'd as they turn'd in air Sabring the gunners there, Charging an army while All the world wonder'd: Plunged in the battery-smoke Right thro' the line they broke; Cossack and Russian Reel'd from the sabre-stroke, Shatter'd and sunder'd. Then they rode back, but not Not the six hundred. Cannon to right of them, Cannon to left of them, Cannon behind them Volley'd and thunder'd; Storm'd at with shot and shell, While horse and hero fell, They that had fought so well Came thro' the jaws of Death, Back from the mouth of Hell, All that was left of them, Left of six hundred. When can their glory fade? O the wild charge they made! All the world wonder'd. Honour the charge they made! Honour the Light Brigade, Noble six hundred

Can I automate the functions found in the 'analysis ToolPak' in Microsoft Excel?

Someone asked this question in Quora and here is my answer which I think many of you will find useful:

If you use Excel on a Mac the chances are that you are not running Excel 2016 for the Mac and that your Mac does not have the ToolPak at all … I know, older versions have it and I know you can get alternatives!

In that case, I often demonstrate to Mac users how to create and automate the functions in the ToolPak: correlation matrix, regression analysis, moving averages, descriptive statistics … the others as well!

Descriptive statistics, for example, could be, for data in column A:

=AVERAGE(A:A)

=STDEV(A:A)

=KURT(A:A) …

=SKEW(A:A)

and so on.

Other answers have mentioned statistics software packages and that’s fine except they might not be free! Yes, if you are a student, your college or university is likely to have statistics software free for you to use.

How about R and R Studio, however? Open source, free, with massive amounts of support? Of course, it takes time to learn R but here is the code for some descriptive statistics using the psych package in R:

describe(order_sales_profit$Sales)

That’s it! This is what I get from my current data set, sales values: not exactly the same as the ToolPak but my point is, it is very easy to replicate. Look at the screenshot of the output from R.



By the way, as a novice or beginner level user of Excel, there is a lot to learn from manually automating what’s in the ToolPak. Moreover, if you take my next learning point, use this opportunity to set up templates for you to analyse your data sets: that means, you automate the ToolPak elements once and that is it!

Finally, many elements of the ToolPak return non volatile results which means that if you change your data, you have to run the ToolPak again. If you automate it yourself, the formulas you create will all be volatile: change the data, change the answers!

Duncan Williamson

I like such photographs

I hope you can see this photo.

DW

Top Tips: rules you really should follow

19th August 2016

I have just completed another very successful Financial Modelling course and as you know, at the end of such courses, I come here and offer something new: a new topic, a new file or some advice. In this case, it is advice: things that you really need to think about when you create and work on any Excel file.

• Tab/Sheet Names


• Links


• Dead Cells 125,433 rows created but only 831 needed/active ... files that balloon to many Mb for no real reason

Ever seen a tab name like this: FBU or OPT? I bet you have: short and sweet and probably mnemonic so easy to read and remember. How about L_P_Obasange_receivables_dont_forget_to PRINT_it_out? You think I am joking? I am serious! Just imagine you are working on your file with the large tab name and you want to link to a cell on that tab from another one: this is what will appear in your formula, by way of an example ... =IFERROR(AND(A15=45,D26="Jack",L_P_Obasange_receivables_dont_forget_to PRINT_it_out!BA154 ...

I am sure you see the point now. Keep tab names short and simple! More than that, if you do feel the need to use tabs to give instructions, colour code them to pass such messages: there are many colours to choose from so do that. Have a table of contents too. Give everyone a chance for a simple life!

Links

If you share a file with someone, make sure any links in your file are either live or delete them. If you receive a file with links that you cannot use or update, you know how frustrating it is. Think of the user before you send linked files.

Dead Cells

It is the easiest thing in the world to create a worksheet and as you work and improve what you are doing, to delete cells and ranges. We all do that. We create new ranges too, don't we! Check your work now and again though and if these happen, take a break and check your file:

• it takes 30 seconds 45 seconds or even longer for the file to open


• what seems like a small file in terms of content and complexity has ballooned in size to 20 or 30 or more Mb


• saving the file takes an age too

If these things happen, go to a worksheet and press Ctrl+End and see where that takes you. You work only in the range A1: CD831 but Ctrl+End has taken you to CG125433 ... what? How did that happen?

Even if there are not as many as an additional 1.8 million cells but just 500,000, look in those cells for formulas  that are trying to find something from somewhere that is not there ... in some of these extra cells for example. Delete all of these extra cells. I did that this week: an extra 1.8 million cells in TWO separate worksheets complete with formulas. File size down from 28 Mb to 0.8 Mb, opening time just seconds, recalculation time hardy noticeable.

They were just some of things to report on from this week. Otherwise, this group of delegates really enjoyed the work and their end of course presentations were interesting and showed that significant learning had taken place!

Duncan Williamson

 

This Week's Delegates

Here they are, the chosen few from my course in Ghana this week.

Good delegates, successful course: financial modelling.

DO

Accra Ghana Spark Page

Just take a look at this very simple Spark Page I have just created.

<a class="asp-embed-link" href="https://spark.adobe.com/page/rdgNxpzgPBQ5R/"><img src="https://spark.adobe.com/page/rdgNxpzgPBQ5R/embed.jpg?buster=0" alt="Accra Ghana" style="width:100%" border="0" />Duncan's Accra Spark Page</a>

I hope you like it.

DW

Boeing 787 ... 3 out of 10

I don't normally talk about aeroplanes and flying but sometimes ... You have probably heard of the Boeing 787 Dreamliner and how marvellous it is. I have just flown on it for the umpteenth time and again I come away wracked! The seats are bone hard and the economy cabin is cramped: they cram us in. For anyone in the first row in any cabin in economy, they have put the TV remote at hip level within the seat AND it's non removable. That means you need to be a contortionist to use it and if you are even slightly on the large side you will not be able to see it let alone use it. I firmly believe that this aeroplane was designed and built by people who never fly in it or never fly economy in it. In my opinion, it's an insult although I know the airlines love it because it runs cheaply compared to other planes. Well, here you are Boeing: 3 out of 10. DW

In the Current Climate

As I was about to get to the passport control desk at DXB, a man in the queue behind me pointed out to one of the staff that there was an unattended bag in the middle of the floor. There was!

The staff member suggested that someone had probably left the bag there as he went round and round the queue system to save having to carry it.

The man responded with. I realise that but no one should leave their bag like that in the current climate.

The man was absolutely right but I loved his use of the phrase, in the current climate!

DW

Fungus is here

These just grew in the garden!

DW

Fascinating Story

Read this story if you can. Very interesting in every way!

The Iraqi who saved Norway from oil - http://www.ft.com/cms/s/0/99680a04-92a0-11de-b63b-00144feabdc0.html

Champion Burnley FC

Well done Burnley FC,  promoted to the Premier League for next season. A 23 game unbeaten game run in too.

Let's hear it for the team and for the manager Sean Dyche.

Of course, Burnley has never been a fashionable team so the response of the media to their achievement has been grossly understated.

How to Erect a Pillar Perfectly

They have erected the support pillars for our new venture and they are perfectly vertical.  To get them vertical they used two pieces of string, a plumb line and two bits of wood to adjust them if necessary.

Fantastic skills.

DO

The Start of a new Project

Here is the ceremony to celebrate the start of a new building and a new venture.  The ceremony went well and I hope the project does too.

DW

And She's Off!

Daughter Abi will be 10 months old tomorrow and already she is standing for a short time without holding on. She's also taking steps or trying to.

Good progress

DW

Business Intelligence

Are you interested in using BI? Do you already use it?

Did you know that BI is free to create?

I will be adding some BI resources here this week ... but there are already some here! Take a look at Power Query and Power Pivot for a start.

Back soon and if there's something you want me to write about, let me know.

Duncan Williamson

SUMDIVIDE ... sort of

A delegate asked me today if there was such a function as SUMDIVIDE. There isn't of course, but I found a way to simulate it.

What would SUMDIVIDE do? SUMDIVIDE would have array 1 divided by array 2 and the results then added together: in the same way that SUMPRODUCT multiplies and then adds.

This is how it works: imagine A1:A5 contains array 1 and B1:B5 contains array 2 then the SUMPRODUCT function to divide them will be =SUMPRODUCT(A1:A5,1/B1:B5). Simple, eh? Who'd have thought it would be so simple.

Duncan Williamson