Mar... what?

Just had my normal breakfast in the dining room and I was being treated well by the waiter as I was the only guest there. So life went on and. As I got to buttering my toast I asked the waiter if they had any Marmite; ever keen to please, he dashed to another table looked at whatever was there and, with a smile, lifted a small jar and brought it over to me. Marmalade. I said thank you! He HAD answered my question: no, they don't have any Marmite; but he hadn't understand my question.

Never mind, just a little story and no harm done!

I realise that some people come to this blog who will have no idea of the difference between Marmite and Marmalade: the following two links should help!

Marmite:     http://www.marmite.com/love/
Marmalade: http://www.bbc.co.uk/food/marmalade

DW

Back Home Again!

Back Home Again!
I returned from my latest trip but not in the rudest of health. I have fallen victim to air conditioning and I am hot and weak! I slept all morning and have come back to bed already ... It's only 18:18 but I feel comfortable with my bedside cabinet full of fluids.

DW

You MUST see these

I have just listened to the BBC Radio 4 programme Soul Music and this time it was discussing Handel's Messiah. They mentioned a flash mob rendition of the Halleluja Chorus so I went to YouTube and found this one ... I wish I'd been there!!

http://www.youtube.com/watch?v=SXh7JR9oKVE

As I looked for this video, I found this one from Antwerp ... music and dance ... do re mi from the Sound of Music ... http://www.youtube.com/watch?v=bQLCZOG202k&feature=related

And from T5 Heathrow ... http://www.youtube.com/watch?v=bMbYPVxfRmw&feature=related ... our lot can ruin any good idea.

A much better one from Singapore's Changi airport ... http://www.youtube.com/watch?v=Q6-fHQZU9vk&feature=relmfu

And this one from Birmingham ... http://www.youtube.com/watch?v=WM2Agu_DktM&feature=related ... Michael Jackson Thriller

Best, formal, choreography has to go to this one http://www.youtube.com/watch?feature=fvwp&NR=1&v=9OawiTae0bA ... another Michael Jackson based one.

There are other flash mob videos on YouTube that you have probably seen and I think they are all fabulous too.

DW

British Politicians

I have seen politicians come and go in the UK but it strikes me that the current batch are the biggest crowd of sub standards we have ever had. Having made that assertion, I will tell you now that I have done a bit of digging into the careers to date of some of our most senior politicos.

PM Cameron ... no real experience and did PPE at Oxford
Deputy PM Nick Clegg ... no experience and did Social Anthropology at Cambridge
Chancellor Osborne ... no experience and did History at Oxford
Foreign Secretary Hague ... limited experience and did PPE at Oxford
Education Secretary Gove ... journalist and did English at Oxford

Ed Miliband ... no real experience and did PPE at Oxford
David Miliband ... no real experience and did PPE at Oxford
Ed Balls ... no experience and did PPE(?) at Oxford

Get the drift? We are all at sea aren't we?

DW

Birdwatch!

Earlier this Winter I felt sorry for any birds around my house and having found myself with some waste lard to get rid of, I made up a fat candle: lard, seeds and breadcrumbs, moulded it and put it outside for the birds to eat.

Day 1 ... no birds
Day 2 ... no birds
Week 1 ... no birds
Week 2 ... no birds

For several weeks there were no birds in sight. Eventually the fat candle went and I started putting out my waste bread and that went quickly.

So, I am getting the birds used to my free food and now I want to set up a hide in my kitchen so that I can take photos of them.

I'll keep you fully informed!

DW

Recipe from my Khmer Cookery Course

I did promise to share some of the recipes from the cookery course I did in Siem Reap and here is a dessert for you.

Nom Tong Noun

Ingredients

Rice flour 1/2 Cup

Duck egg x 2

Sugar 1 tablespoon

Coconut cream 1/2 cup

Black sesame seeds 1 teaspoon

Oil 1/4 cup

Method

1 Set aside one duck egg and the oil

2 Take the rest of the ingredients and combine to make a batter

3 Beat the remaining duck egg and mix with the oil

4 Heat your NTN or waffle pan until its hot

5 On one side brush some of the oil mix - until the side is covered

6 Place your batter on the other side

7 Cook for a few minutes then roll into a cone shape while they are still hot.

These are a delicious snack and perfect to serve with coffee or tea

DW

How to look gud!

Isn't it odd that at the time when we need to be perfect we are often at our most stupid or lax?

Here is an example from one of my own web pages:

"This page begins by reminding us that there is always someone more stupid than we are. However, non of us is perfect so the file then goes on to show us the best way to do things."

Otherwise that is a good page on spreadsheet good practice!

See http://www.duncanwil.co.uk/spread_prac.html

DW

How to Construct a Population Pyramid Style of Chart Using Excel 2007

The population pyramid is a very effective way of presenting data in situations in addition to reporting populations. The following example shows how true this is when I compare some basic data for British Airways and easyJet:

This is a very effective form of presentation, don’t you think? It is! After all, there are times when you may well want to compare this year with last, one company with another, male with female … and a table of data or even a line or ordinary bar chart just aren’t effective enough. 

How did I prepare that chart, then, using Excel? What follows is all you have to do!

Firstly, derive, find or grab your data. As you are working along with this example, just copy and paste the following into a blank Excel work sheet:

To begin with, make all easyJet values NEGATIVE. Just put a minus sign in front of them all. It will become obvious why we do this shortly.

Then select these data and press the F11 key and Excel will then draw a graph for you … automatically. Excel will draw your default style chart when you press the F11 key and the chances are that it won’t look anything like the population pyramid you want but don’t worry! Since we are here to sort that our. Click on the Chart and on the Chart Tools Menu, Design sub menu. You can then change the chart type if necessary ... see the screenshot below to see all of this:

Choose Clustered Bar Chart, as you can see in the dialogue box that follows:

Click OK and you should see something like the following:

If necessary, right click on the chart and select Source Data and click on the Series tab.

Remove the first series per 10,000 pax km

For British Airways, click on the Category Axis Label icon and select the appropriate range, Excel will show it like this ='Sheet1'!$X$X:$X$XX ... it says Sheet1 or whatever it says on the worksheet tab where the data are kept

Now select the second series that also says per 10,000 pax km

Select the Name icon and then select the cell where it says easyJet

Format the chart in terms of title and axes labels as you wish:

Click next again and type in the titles you want to put onto your Chart … you can see what I did. If you are using different data, type what it most informative and appropriate. At this stage, my chart looks like this!

As it stands, the negative values are mixed up with the vertical axis labels. To sort this out, right click the vertical axis and make the following settings:

The legend is very useful for this graph but it is better to put it at the bottom of the chart: right click the legend, Format legend, Placement, Bottom. You should edit the legend so that it reads BA and easyJet rather than the lengthy versions you might be able to read in the chart above. If you chart has a coloured background, it’s best to get rid of it ... and grid lines: right click, clear each of them will do that for you

Change the colours of the bars if you want: right click format … you choose!

Very, very nearly there now. All that’s wrong is that the bars are offset from each other. You could leave them like that but it’s not so attractive is it? So let’s adjust the bars:

Right-click on any one of the bars and select Format Data Series

Click Options tab and make the following changes:

Overlap = Change this to 100 in the appropriate text box.

Gap width = Change this to 0 or 10 or 20 ... in the appropriate text box ... try them all and see which you prefer

OK

That’s it: you’ve now got a population pyramid of your dreams! Change the colours of the bars as you wish. Change the font and the font size and colour too.

For the final version I have removed the two data points Selling Costs and Handling Charges ... This is my final version: orange bars for easyJet because that’s its house colour … I changed the font size, I put a border on my chart … and so on.

I have taken you through this process step by step. This process is a little complicated but once you have done it twice or three times, you will breeze through it!

 

Duncan Williamson

Interest Based Functions in Excel

This page has the simple purpose of introducing you to the interest, loans and annuities based functions that Excel has built in to it that you might find of use in your work as an accountant.

The purpose of Excel’s in built functions is that they are fully comprehensive attempts at working out, for example, the interest implications you need to take into account in a variety of settings.

I am demonstrating these functions using Excel 2007: it will be different if you use an earlier version of the software.

Finding what Functions are Available

Open Excel and click on the Insert Function icon:  it looks like this fx and is to be found on the left of the formula bar, as below.

This opens a dialogue box and to find out the categories of function available, click where it says or select a category … highlighted below. Choose from Financial functions, date and time, mathematics and trigonometry … and many more.

We are primarily interested in the financial functions and you are presented with the list of all of the financial function available in your version of Excel … there are many!

You should see the list beginning with ACCRINT, ACCRINTM ... 

So rather than doing what I have just suggested, let’s target the functions we need specifically. That is, click on fx but this time enter depreciation in the Search for a function area then click Go and it provides you with a list of all in built functions that Excel thinks relate to that search string. 

As you can see with the dialogue box here, it shows seven functions in Excel relating to interest although there are 31 in total:

 

You can also see here that when you highlight one of the functions, there is a short explanation of how that function works and what it does. So we can see that the ACCRINT function is the Returns the accrued interest for a security that pays interest at maturity.

Please note: choose ACCRINTM, as a matter of interest and you will see that it is one of a group of functions that are not available by default. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in:

 

• Office Button
• Excel Options…
• Add-Ins on the left hand menu

Take a look at the graphic below to see what to do now to install the ToolPak by clicking the go button next to Manage Excel Add-Ins …

This now opens a dialogue box where you can now select the ToolPak Add-In:

Click OK now and note that you MIGHT have to insert your Office original CD into the drive to install this ToolPak. That #NAME? Error should have disappeared now: if not, close Excel and reopen it and it should have been solved!

Don’t forget, there is always a link to Excel’s Help files: it’s a hyperlink in blue at the bottom left of the dialogue box. Click on the link and it will explain in detail, with a fully worked example, what to do. The help file for ACCRINTM is:

You can even copy and paste the example into a worksheet and see the function in action.

Interest, Loans and Annuities

You are now aware of the choice of methods for dealing with interest, loans and annuities available as in built functions in Excel. What follows now is a brief review of some of them and some tips on how to set up a worksheet with any or all of the functions built into it.

The functions:

• FV = future value
• PV = present value
• PMT = payment
• PPMT = principal payment
• IPMT = interest payment
• ISPMT = payment interest
• RATE = interest rate per period
• NPER = number of periods

Of course, the chances are that you will not need to use all of these at any one time. I am demonstrating them at one time for convenience! The data we need to calculate and demonstrate these methods are:

 

Range Names 

Before we take this any further, I want to introduce, or remind you of, range names because I am about to set up a worksheet that will allow you to calculate the provisions for depreciation using one or all of the above methods. However, I am assuming that the calculations will relate to the same asset at the same time etc therefore we will be providing for the same asset using the same basic data. 

All we do to assign a range name is the highlight a cell or a range of cells that we would like to give a name to

Then whenever we need to call on or use that value or range, we just enter the range name in the formula or function we are using.

Like this:

I have assigned the range names … NB these relate to depreciation and you should change them if you wish to work with range names for the interest calculations

• cost to cell B11
• salvage to cell C11
• life to cell D11 

Then for the SYD calculation, we would have:

=SYD(cost,salvage,life)

Now you don’t need to look for where the data are to be found, whatever you do to your worksheet and if you change the basic data, the formula automatically finds and updates itself and its result. More than that it doesn’t matter where in your worksheet or file you place your function either, using range names makes it really easy, just use those range names and Excel finds what you want.

Finally for now, if you open up the list of range names and click on any one of those names, the cursor goes to that range: again useful features if you have forgotten the name and where to find it.

Assigning Range Names

How do you set or assign range names? To assign a range name, highlight the cell or range you want to name and then type your chosen name in the name box:

If you close your spreadsheet and want to work on it again later but have forgotten which range names you have used in it, just click on the down arrow at the right of the name box and the list of names opens for you to use again. Excel never forgets these things!

If you redesign your worksheet or file, you can change the address of the range name … we’ll do that another time. If you are impatient, Excel’s Help files can sort this out with you.

The Interest, Loan and Annuity Spreadsheet

I have set up a really simple spreadsheet to cope with our chosen four methods: it looks like the following in which the range names are

Please note that some of the variables need to be entered as negative values. PMT, for example, the monthly payment amount is negative which reflects that fact that it is a cash outflow. Similarly with PV: as with capital budgeting, we assume here that the initial investment is a cash outflow and hence negative.

In this case, I have set up the spreadsheet for you and worked through all of the calculations: FV is £167,156.1 … what does that mean? PV is £3,790.79 … what does that mean? And PMT, PPMT and so on … what do they all mean?

I hope you have found this page useful and feel free to add your comments to this page to let me know the good and the bad!

 

Duncan Williamson

The 12 year olds at the BBC

As Ed Reardon points out, the BBC looks as if it is staffed by 12 year olds. I mention here from time to time as well as elsewhere what it is that these 12 year olds are prepared to do.

I have just listened to the final episode of Gulliver's Travels on BBC Radio 4 and half way through that adaptation they make Gulliver say, " ... people are starving out there."! Out there ... this comes from the X Files, of course when we learned that, "The Truth is Out There"!

A clot on Radio 4 has just said that, "... the stand out character ... ". We used to have outstanding things until the 12 year olds decided that they would go to Oxbridge for their degrees but working class USA as the source of all changes in English English.

Now they are reading from a short story in which the author has let us know that he knows the proper names of several parts of the human brain and their processes. He then told us that a bullet left  a brain having exited it: what kind of a nonsense word is that?

DW

Rachmaninov's 2nd Piano Concerto

There is a programme on BBC Radio 4 at the moment and they are discussing Rachmaninov's 2nd Piano Concerto. This is one of my favourite pieces of music and I recommend it strongly to everyone.

DW

Plane Spotters!

After spotting planes myself this afternoon, I went to the rooftop pool/bar and started snapping photos as planes took off from the airport and headed my way.

I saw a chap with his computer ... and he stood up and started looking through his binoculars. I then noticed that he was locating and looking at planes that seemed to be in random parts of the sky! I then noticed that he was running plane spotting software ... After that I noticed another man, other side of the pool, doing the same. This chap had HUGE bins though!! Take a look at the photos ... I threw in a pigeon too. By the way, zoom in on the bird and it's feathers are suave!

And here's a photo I took and cropped earlier! It's Emirates' aeroplane A6-EMM, a Boeing 777-31H that first flew in 1999.

DW

How did this happen?

Excel 2007 with a worksheet 180,000 lines deep ... only 5 columns.

I prepared a data profile that included classifying the data and then using SUM to add up the values.

I prepared another analysis as preparation to draw a histogram and then used SUM to add up the values.

Then  I created a very simple IF statement to check that total 1 =  total 2 ... they didn't agree. For some reason, although ALL values have only two decimal places, when added together, total 1 could be expanded to ...247.900883 while total 2 could be expanded to 247.90003.

As a workaround I amended the IF statement:

=IF(INT(A1)=INT(B1),"Reconciled","Error") ... that worked

and so did this:

=IF(ROUND(A1,3)=ROUND(B1,3),"Reconciled","Error") but =IF(ROUND(A1,4)=ROUND(B1,4),"Reconciled","Error") did NOT work, for what is probably an obvious reason!

 

Duncan Williamson

 

You get what you pay for ... another top tip!

Have been food shopping at M&S of late and it is definitely a good thing to do. Well, mainly! Yesterday I was buying milk and saw a bargain: a pint of milk marked down to 10 pence so I bought it.

In the evening I was drinking coffee and thought, eeeuuggghhh! What's happened to that??

The penny dropped eventually. Cheap milk and good coffee do not mix.

Otherwise, M&S food is very good.

DW

Test Match Special

I have been given the opportunity of returning to Dubai again next week and whilst I will be there, most of the third Pakistan v England cricket test match will be played. Whilst I work every day until around 2:30 - 2:45, I have bought tickets for Sunday and Monday to go and watch the game.

Watch this space for photos and other observations.

DW

Eye? Aye!

Everyone who comes here regularly knows that two years ago to the day I had an operation to correct a detached retina in my right eye. As is normal, given the treatment I received, I also needed a lens replacement in the same eye around 6 months later.

Not perfect but I have absolutely no complaints at all: the knowledge and skills of the surgeon and his team to rebuild my retina from outside fills me with massive admiration.

Well, a month or so ago I realised that the vision in my right eye had regressed: nothing like the same symptoms as the PVD I had and then the detached retina. More of seeing through watered down milk. I monitored carefully as I travelled away from home and did a bit of reading about what might be wrong.

In the end I wasn't worried but of course I had to see an eye specialist and did that on Friday of last week. The diagnosis is that my new lens is attracting gunge to it: much quicker than normal, apparently, the lens has clouded over somewhat, explaining the effect of looking through watered down milk!

I need to have to lens laser zapped now so I am waiting for that to happen.

In the meantime, I am seeing things in a lop sided way as watered down milk vision is blurry vision!

DW

Managing and Interfering

Nick Edwards has written a very engaging autobiography that I recommend everyone to read.

Edwards is a doctor in an A&E department at a General Hospital somewhere in the UK. The book discusses two main themes:

medical stories
management and interference stories

I enjoyed the book and reviewed it in full for my own web site: http://www.duncanwil.co.uk/book_reviews/edw.html

DW

What I have been up to!

Those of you who see me on Facebook and Twitter know that after a week at work in Dubai I have spent almost two weeks in the Far East: Cambodia, Singapore and Bangkok. I am in Bangkok now and will fly out of here tonight to go home via Dubai: Airbus A380 all the way to Manchester.

Anyway, here are some links to two slideshows I have put together for you: one shows me at work in the kitchen, cooking Khmer food, in Siem Reap; and the other shows snaps I took in Singapore.

Enjoy them and all feedback is gratefully received.

http://www.youtube.com/watch?v=kjTPQxmCha4&feature=youtu.be

http://www.youtube.com/watch?v=84dIBPHj1zQ&feature=youtu.be

DW

What a (Little) Gentleman!

The wallpaper on my iPad is of the new Bentley V8 superb looking vehicle: that really is a lottery win dream. Here is that photo, courtesy of those awfully nice Bentley people themselves!

Anyway, as I was returning to my room at the hotel, I saw a massive Bentley outside of the back entrance. There were a few people milling around, including the driver, who was wearing a baseball cap!

A tiny man, all dressed in white but with a significantly bald head, walked slowly towards the car with his hands behind his back and his head semi bowed down. The driver opened the back door for the man who almost vanished inside that cavernous vehicle as he climbed inside.

Behind this man was a woman of the same height, wearing a sari: she was following the man fairly closely and as he climbed into the car she stopped. She was waiting for the man to move over to let her in: he didn’t. Dignity maintained, as her halt was fleeting, she quickly changed her direction and went round the back of the car and the driver opened the rear door on the other side of the car to let her in.

Man and woman were now ensconced and the driver went round the car again, to get into the driving seat.

A young man and woman were now left on the pavement and clearly they were confused as the young woman made a move as if to open the driver’s door but she also halted fleetingly before she changed direction and the couple started to walk away.

At this point I heard myself say, what a shitty little man, a shitty little man; and left the scene to return to my room.

Lovely car, though!

As I was in the lift a memory came flooding back of a situation that arose in Almaty back in 1998/99. We had arrived there in August 1998 and after a whole term of living in a dormitory, we were given some hope of moving out into the community and living in our own flats or houses.

We were asked to describe our hopes and aspirations for the housing we were asking for and one of our team wrote the most inappropriate list of demands one could ever imagine. We were in post Soviet Kazakhstan where the majority of the population didn’t have two ha’pennies to rub together; flats and houses were often dangerously badly built; as many as three and even four generations of families shared one flat; and the furniture was Soviet utility style and standard.

Apart from tiger skin rugs, Italianate furniture and bath fittings, this colleague, also a small man, asked for a king sized bed with some outrageous bedding and a duck down duvet! This latter request led me to make a drawing of the impact of this request having been granted.

As a matter of interest, I last saw this man in 2003 when he left our project and I heard nothing about him until late last year, 2011, when I saw him on one of the social networking sites and he had written this astonishing plea: la la la, I am looking for work but have been unemployed for two and a half years … Well, this man is the best part of 80 years old now so fair play to him that he might be capable of working. However, trying to generate sympathy as a reason for giving someone a job is not the best of strategies in my book. 




DW

Cheating Examiners: a GLIMMER of hope?

In addition to my earlier post, http://duncanwil.blogspot.com/2012/01/cheating-examiners-another-secretary-of.html, I am a little encouraged by some of the correspondence I have seen over this matter.

Sec of State Gove’s speech, linked to in my post, is so badly littered with literary allusions as to make it unreadable. The other two letters mention education assessment markets, textbooks and other forms of communicating with teachers and students. As long as honest and independent people are allowed into this debate there might be some hope.

However, given the history of this debate I am really wary of the vested interests that litter the education market now and that are bound to cause problems for us all.

DW

Reminder of those letters:

http://media.education.gov.uk/assets/files/pdf/l/letter%20to%20ministers%20from%20glenys%20stacey%20-%2028%20nov%202011.pdf

http://media.education.gov.uk/assets/files/michael%20gove%20letter%20to%20glenys%20stacey.pdf