20.12.16
Michaela Finnegan
17.12.16
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.
16.12.16
Appalling Behaviour on a Newspaper Forum
8.11.16
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.
25.10.16
Multi worksheet scenario model
This is on its way ... as promised ... I will upload it later today or early tomorrow.
Duncan Williamson
25.9.16
The Charge of the Light Brigade
22.9.16
Can I automate the functions found in the 'analysis ToolPak' in Microsoft Excel?
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
9.9.16
20.8.16
Top Tips: rules you really should follow
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
15.8.16
Boeing 787 ... 3 out of 10
11.8.16
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
22.6.16
30.5.16
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
8.5.16
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.
10.4.16
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
30.3.16
Business Intelligence
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
9.3.16
SUMDIVIDE ... sort of
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
8.3.16
When the TV Doesn't Work
7.3.16
Are you Following me?
In addition to this blog you might want to follow me on LinkedIn too ... I don't accept everyone there but if you tell me you subscribe to this blog I guarantee acceptance.
I am working in Dubai this week: presenting a three day course on Financial Modelling and Business Intelligence. Then a two day course on Budgeting and Cost Control.
With my courses you get a tool box of functions and techniques. You also get one to one time with me as I solve your problems ... financial modelling etc! You take away all of my notes and PowerPoint slides as well as all of my fully worked Excel files.
I use real world data in my models and demonstrations. You get full explanations from me. I am also honest: if you ask me something I will give you the right answer, even if it means I have to do further research or ask a friend!
Find out where I am working and what I am doing and who knows, you might even join me one day. Invite me to speak where you are and maybe we can get together that way.
Duncan Williamson
5.3.16
Let me try 23 Mbps!
See my previous story about my wifi connection.
We found out by accident that the router in the sales office of our new wifi provider pumps out 20+ Mbps speeds. I thought, I'd like to see what that's like.
I set my phone to download a 330 Mb video and stood outside the wifi provider's showroom. Nothing. It didn't start!! I waited a while then checked the speed ... very slow at about 1 Mbps. I thought: ah! It hasn't connected properly yet. I went away for a while and then returned. The same.
Anyway, having checked download speeds again I concluded that the office had not turned on their cable router! So I was using their ordinary system from outside this building.
End of my experiment!
DW
Monopoly Broken
I have been suffering for 18 months or so from a monopolist wifi supplier. Variable connections. Breaks in connections for as long as two weeks. 18 breaks in service in a January alone. February started with the first 6 days dead.
I searched in vain for an alternative all of this time and became most frustrated and angry when I realised that these people were managing my business and private life. I need a decent connection for work and for my relaxation, entertainment and so on.
A friend told us about a router they had bought in Switzerland ... good solution but very expensive to buy. It sowed a seed, though.
Then a chance conversation sent us to a local provider of service who offered 4G pocket routers. Really? We looked at their brochure and asked questions. While 4G doesn't reach to our village, it will soon.
I thought about it for a while: it would cost us the same as our current provider per month and the pocket router came free!
I said, I want to take the risk: there's a chance that this will be better ... we did it. We came away with the router and some hope.
We got it home and it works. At times we can get speeds of 5 to 6 Mbps. As importantly, after 5 days, service has been unbroken and because it is a pocket router, we can take it with us wherever we go.
I stopped paying for the old service immediately. They are no more.
Since I have been sending the old providers an sms whenever there has been a significant break of service, I said: they will realise something is different when they realise I have stopped texting them!! After all, we didn't tell them we were leaving since they never told us that their service had stopped. I felt no loyalty to them.
After 5 days they asked how things were going and we told them we no longer needed them. They said oh!
Incidentally I used to send tweets to the head office of our old provider: they never replied.
Another case of monopoly madness.
7.2.16
Reward or Retard?
I wrote a long and solidly constructed piece for this post then I lost it, in the spirit of this weekend.
Bottom line: I did a job that should have brought me £0.50 per unit. I have been paid £0.04 per unit. It's a matter of control over channels in the same way that I have no control over the awful broadband connection I suffer from here.
So, shit happens as they say :)
DW
18.1.16
Flat Fish or Flat Cat
The cat you see is scraping some fish off the road for breakfast.
The cat got a whole fish from the rubbish bin and took it there to eat: middle of the road in a car park.
A car came along and as the cat was reluctant to move, the driver very carefully inched the car forward. He didn't see the cat moving out of the way so he reversed the car until he saw the cat again. The cat had moved out of the way but now it came back.
The driver then drove to the right then the left as he tried to ensure he didn't kill or injure the cat. He was successful: the cat survived!
What the driver did do was to drive over the fish that the cat left on the road. Hence flat fish but not flat cat!
DW
13.1.16
Flat Bread
For some reason I decided I wanted to make some flat bread. Today I did it!
I didn't have yoghurt so I used milk instead. Otherwise, so simple and they turned out pretty well. I turned one of them into a pizza!!
Look:
Cramp, wifi and a water pump
I was woken this morning by cramps in my left calf. Horrific. Extremely painful. It wouldn't stop. Eventually I was able to move to get out of bed but I cannot remember such a bad attack.
Our wifi is currently broken ... for the EIGHTH time this month. Not bad since it's only 13th January.
Our water pump stopped working this morning too: ants had been crawling through it and shorted it out. It's ok now thankfully.
DW
The Books Are Available Now
9.1.16
Biff!
Daughter Abi is 7 months old now and can crawl and stand using furniture. Hooray!
As everyone was getting ready this morning I checked on Abi in the living room: she was standing, holding on to the settee. She was alone and I thought, she'll be fine. And she was.
After a few minutes I went back in the room to see she had crawled quite a way across the room.
I left her and she started calling so I shouted back for her to follow me. She did. As I waited, I saw her appear in the doorway ... then suddenly ... boof! Her hand stuck to the floor and as she motioned forward that caused her head to go down instead of along.
She got a temporary fat lip and dented pride.
Otherwise, all is well!!!
DW
8.1.16
My Kitchen Rules Australia 2014
New Book: Finance for the Non Financial Manager
5.1.16
Happy New Year
I know the weather has been bad in lots of places but the Christmas holidays have been and gone now for most people. So as you settle back in at work or college or even at home, here's wishing you a happy new year 2016.
DW
Weather Forecast? Far From it
Before Christmas I called my old friend Malcolm in Halifax for a chat and we talked about this and that, as usual.
Since he has no real idea of where I am living and what it's like, he asks me questions about all sorts of things, including the weather.
I told him it was winter now, the dry season: it probably won't rain again until April next year, possibly, I said. Oh! He said.
Of course, within two hours of ending the call, we had at least an hour of solid rain here!!!
DW