Business intelligence required…

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Up and down the country, businesses are running on Excel, instead of using a proper business intelligence (or even management information) system. The one I look after is no different but, as I pieced together yet another spreadsheet last weekend, I learned a few Excel tips that might be useful to share…

=SUMIF()

I’ve been trying to pull together a resource forecast in order to work out how quickly to grow my team. The approach I look was to list all the projects we have coming through, with headcount requirements split out by grade, then to total each column based on the grade of staff required.

Seems fair enough, but the trick to making this work is reading a cell and then only including its value in the total if a condition is met (e.g. the indicated grade matches the one I’m adding up).

Stack Overflow came to my rescue, describing Excel’s SUMIF() function

In my case, the formula was something like:

=SUMIF(E4:E148,E154,F4:F148)

Where E4:E148 contained the grades of people for each identified project, E154 contained the grade I was looking for (e.g. Exchange Designer) and F4:F148 were the numbers of people needed for each project that month. Repeat for each grade, and then for each month, and a table of resource requirements can be built up…

There may be better ways to do this, but it will save me some time adding up the totals each time I revisit the task list…

More margins…

Of course, knowing how many people I need is one thing – making some crude assumptions about the likely revenue they might attract to see if I’m close to my numbers for the year is the next question I’ll be asked.

Last week, I blogged about the difference between mark-up and margin, and this week I needed to put that into practice.  I found a forum post that explained the formula (sale price = 100/1-margin * original cost), so I put that into practice, multiplying by a day rate, an assumed number of working days in the month and the total of that grade of person:

=(D165*(1/(1-D174)))*D175*F154

Which translates to:

=(dayrate*(1/(1-margin)))*number of days*number of people

Displaying data in 1000s

The last part was displaying data. Some of the revenue numbers I ended up with are big – and I’m only interested in 1000s of pounds, so I needed to adjust the formatting of the results.  The trick here is to use a custom number format on the cell of 0, (zero comma) for thousands (or 0,, can be used for millions). Add a K or an M on the end for units, and a currency symbol up front too. You can also add a decimal point using 0.0, (e.g. £0.0,K for £1500 to be displayed as £1.5K) or, if the numbers get into the millions, then try something like £0,000,K.

Margin, or markup?

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A big chunk of my current role involves trying to convert a capability unit (with some great skills in the team, it has to be said) into a profitable business. That’s not necessarily easy – changing a culture created over years where utilisation was king – as long as we were busy, life was good – to one where we need to be busy but only if we’re doing the right things to keep projects profitable: get in; deliver a defined work package; avoid scope creep (Project Managers like to grab hold of good people); move on to the next thing.

That means that, in addition to managing a team of my own for the first time, this technical manager is also on a very steep learning curve as he grapples with being a business manager too (but I can’t forget my technical roots – I’m also Messaging Lead Architect – and I’ve got a number of technical activities to juggle as we improve our capabilities, standardise our delivery, and drive out further efficiencies).

I learned an important business lesson a few weeks ago, when my Manager sent me a “handy cheat sheet” for calculating margins on our day rates.  “But it’s wrong”, I exclaimed – “look, if I put 10% margin on £100 it says the answer is £111.11 – that’s 11% margin!”. “No Mark, that’s not how it works” explained my, extremely patient, Manager (let’s call him Alan because, well because that’s his name…).

Alan explained that I was applying mark-up, not margin (“Doh!”, thought I).

Alan went on to explain that margin requires working back from the price to work out the difference from the cost – whereas markup is simply adding a value on top of the cost to reach a price. So, if something costs £50 and is sold for £100 – that’s 50% margin but 100% markup.

That was an important lesson for me – thankfully one that I learned on a £25K piece of consulting, rather than a multi-million pound managed service…

Now onto the next challenge, making sense of revenue and margin flowing through umpteen cost centres…

Fixing Feedly

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A couple of weeks ago, I wrote a post about switching to Feedly from Google Reader. Since then, Google has switched off Reader – and my feeds went AWOL in the process.

Thankfully, Feedly are taking the issue seriously (I was amazed to get a tweet back from them after I tweeted a picture of their “over capacity” screen)

Even before then, I’d found the answer to the missing feeds on the Feedly blog. Until 15 July 2013, it’s possible to use Google Takeout to export the data that was previously held in Reader – so get in there quickly if you want to export your feeds. Once I’d imported the OPML Feedly was happy – at least on the web.

And that overloaded error in the iOS app? A temporary glitch caused by some Google code changes – as soon as Apple let’s the updated app into their store (Android is already sorted), that should be good too.

Google Reader is retired next week – have you switched to Feedly?

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Next week, Google is set to retire Google Reader. When I wrote this post (back in March), almost 75% of the subscribers to my feed (already dwindling, partly as a result of Google algorithm changes that seem to penalise independent views in favour of branded content) came via Google Feedfetcher (used by Reader to grab RSS or Atom feeds), suggesting that lots of you use Google Reader.

Hopefully you’ve all found a way to move forward but, if you haven’t, I recommend checking out Feedly.

If you migrate before Google turns off Reader, it’s a one-click migration (just log into Feedly with your Google account) – I did it weeks ago and haven’t looked back since!

Here are a couple of links that might be useful:

Now I need to look at moving my site’s RSS away from Feedburner, before Google kills that off too (I’m sure it’s only a matter of time…)

Short takes: searching in Outlook; duplexing in Excel; merging in Word; and going wild in Salesforce

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

This week I’ve mostly been… working in pre-sales. Consequently, this is perhaps not the most exciting blog post I’ve written… but hey, it’s a post and there haven’t been many of them recently!

First up: searching Outlook

Since I changed jobs in April, my email volume has increased by 300x. My mail archive has more messages in it as we approach the end of June than it did for the whole of 2012, and most of them have been sent/received in the last three months.  In short, being able to quickly and accurately search Outlook is important to me.

Microsoft’s website has some good advice for narrowing search criteria for better results in Outlook – for example, if you’re looking for that email from Mark Wilson with the attachment you needed? Try from:"Mark Wilson" hasattachment:yes.

Next: opening two Excel workbooks side by side

If someone sends you a spreadsheet that you need to complete, and there’s information to pull from another spreadsheet, it can be a nuisance to keep switching back and forth between windows inside the application. The answer is to use Task Manager (taskmgr.exe) to open a new copy of Excel so you now have two running processes.  Each one can be used to open a different workbook (e.g. on different monitors) and contents can be copied back and forth.

Then: merging revision comments in Word

Perhaps you work in a team where instead of collaboratively editing one document, people each create their own versions with their own comments? Thankfully, Word 2010 (and probably other versions too) can merge the comments and changes into a single document. That single feature saved me hours this morning…

Finally: wildcards in Salesforce.com reports

My final tip from “Mark’s exciting week in pre-sales” (I jest) was gleaned whilst trying to create a report in Salesforce.com to show my team’s pipeline. I can’t rely on opportunities being correctly tagged, so I needed a report that used searches on a number of fields (and a filter to apply Boolean logic) but was picking up some false positives.  The problem was that one of the search criteria was also a partial match on some other results.  By changing the “contains” criteria from thing to thing*, I got just the results that started with “thing” and not the ones that included “thing” (like “something”).

That explanation is not as clear as I’d like, but I don’t want to spill the beans on some proprietary information – just take a look at the Salesforce.com advice for refining search using wildcards.

Book review: Getting started with Raspberry Pi (so what exactly is it for?!)

Posted on By Mark Wilson
This content is 12 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A few weeks ago, we were visiting friends who have a teenage son. He’d received a Raspberry Pi for Christmas but was struggling to understand what to do with it.  You see, he’d loaded Raspbian, fired up Scratch, etc. – but still had a pretty big question: what could he do on the Raspberry Pi that he couldn’t already do on his Windows laptop?

That made me stop and think. You see, for as much as I think the Raspberry Pi is a fantastic device for low-cost computing – and a great entry point for those who have a TV but not a PC;  many UK families already have at least one PC – indeed I used to think I was in the minority with my assortment of computing devices but even non-geek friends have multiple laptops (kids need them for school work, parents for their professional work), smartphones/tablets, and games consoles.

So what can the Raspberry Pi do that a PC can’t?  For starters, the GPIO pins mean it’s (potentially) easier to interface with other hardware. Secondly, the lower price point means that, if you blow one up, it’s less of a problem than a PC.  Also, as someone whose computing education started out with logic gates and boolean algebra, it allows one to get a lot closer to core computing principles – you can directly interact with a Pi in a way that’s not possible (or at least not as simple) with modern PCs.

That didn’t help my friends’ son much – although I did help to configure their router to allow him to run a Minecraft server, which scored me a few Brownie points…

Even so, I decided to buy a book to investigate further – partly with my friends’ issue in mind but also out of interest for myself. The book I selected was Getting Started with Raspberry Pi (Make: Projects/O’Reilly) by Matt Richardson and Shawn Wallace and it really is a pretty good introduction.  In a handful of easy-to-read chapters it skims the surface of getting up and running, understanding some Linux essentials, Python, Scratch, interfacing with other boards like Arduino, basic I/O, and working with webcams and Internet resources. Plenty of food for thought, to develop ideas for new projects (I still want to explore options to control a train set with some sort of Pi/Arduino setup when I find the time…). It doesn’t go deep, but nor should it – as one Amazon review says “You will need to be comfortable with computers in general, but if you’re, say, happy installing software on your standard Windows machine, you’ll be fine”.

I’ll be handing my copy over to my friends’ son – to see what a 15 year old makes of it… in the meantime, if you’re struggling to see the purpose of a Raspberry Pi (except as a small, inexpensive general purpose computer), this book might help to generate some ideas.

A toolkit for successful Office 365 deployment

Posted on By Mark Wilson
This content is 12 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Over the last few weeks, I’ve attended a couple of Microsoft Office 365 workshops during which I picked up quite a bit of useful information, not least Malcolm Bullock’s (@MBullock) list of Office 365 deployment planning resources.

Office 365 is straightforward: it’s a service; it does what’s in the service descriptions. But successful migration to the service depends on thorough planning.

Of course, this is the point where I should say, “talk to Fujitsu; I’ve got a team of guys who can help you through this” – and I have* but that’s not why people read this blog! What I’ll do instead, is outline some of the resources that Malcolm shared and which should be considered an essential “Office 365 deployment toolkit”:

  • Office 365 Service Descriptions. These are gospel. If the service description says you can do something with the service, you can; and if it says you can’t do it, you can’t. If the information is not there, you probably can’t. It’s binary; black and white – no grey. Office 365 is not for everyone – that’s why on premise and hybrid options exist.
  • Software Requirements for Office 365. Generally, Microsoft gives 12 months’ notice of changes to system requirements but they’re also introducing an n-1 stance on browser support. Bear in mind that, just because something isn’t supported doesn’t mean that it won’t work but using unsupported software is far from ideal and it’s entirely possible that legacy software may be denied access in order to avoid security issues.
  • Office 365 Enterprise Deployment Guide (MODG). Put simply, this describes how to deploy Office 365 in the enterprise.
  • Exchange Deployment Guide (ExDeploy). This is a software tool to run through for on-premise, hybrid or cloud deployments of Exchange.
  • Solution Alignment Workshop. These workshops are professional services, offered by Microsoft and their partners to make sure that the customer requirements are aligned with the Office 365 service. These workshops (together with a number of tools, such as the Deployment Readiness Tool and the Office Alignment Index Calculator) are now being replaced by OnRamp, a streamlined on-boarding process allowing customers to take a staged approach to their Office 365 migration.
  • Test/demo environment. Register for a trial Office 365 tenant.
  • Office 365 Pro Plus Deployment Tool. A tool to configure the click-to-run functionality for local streaming of Office 365 ProPlus desktop software.

There are also many tools and diagnostic utilities referenced from the Office 365 Community website.

* In all seriousness, I’d be happy to discuss Office 365 opportunities with any UK-based enterprises looking at migrating their email to the cloud, or even looking for a hybrid or fully-managed Exchange/Lync/SharePoint solution but this is my personal blog and in no way endorsed by my employer. If you would like a professional conversation, please do get in touch.

Redirecting users from a PC browser to a mobile app: one of the few good uses for a QR code?

Posted on By Mark Wilson
This content is 12 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A couple of years ago, QR codes were all the rage. The groovy little black and white hieroglyphs were on every bus-shelter advert, leaflet and even business cards.  Some were in colour, and some either relied on the built in error correction to become a piece of art! I wasn’t convinced that they always made sense though – and it seemed I wasn’t alone…

Some studies showed that consumers didn’t know what they were. Others warned of malware hidden in QR links. Some were cynical. And some analysts warned of their impending demise:

QR codes are ugly. Give me ubiquitous, directional RFID instead. We won’t be plagued with QR codes in 2012

Tuesday 20 September 2011 18:46 via TweetDeckReplyRetweetFavorite
@mgualtieri

Mike Gualtieri

Earlier today I was asked to join a business partner’s Yammer network.  This particular (Redmond-based) partner has a “special” interest in Yammer (ahem), so I dusted off my old, not-used-for-a-couple-of-years Yammer credentials, signed in and accepted the request. Yammer encouraged me to update my profile (fair enough… it was 2 years out of date), and then to download the mobile app (sure, why not?)…

[imagine sound effect of record needle scratching and music coming to abrupt end…]

Some mobile app developers are smart enough to realise that, when you navigate to a page on your PC that advertises their mobile app, you don’t actually want to go to the app store from the PC browser… so, what’s the perfect way to send you there? Exactly! Provide a QR code, which can be scanned with a mobile device’s camera to jump instantly to the appropriate Apple App Store/Google Play/Windows Marketplace location.

Yammer doesn’t do this.

Sure, it’s easy enough to search the App Store and download the app but, meh, why make it harder? Make the user experience simple. Maximise the number of conversions (or whatever the marketing speak is for “make people download your app”).

Here endeth the lesson.

Big boys toys: Griffin Helo TC smartphone-controlled RC helicopter

Posted on By Mark Wilson
This content is 12 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Last weekend, I tweeted about my “new toy” – Griffin’s Helo TC remote control helicopter, which is controlled from an iOS or Android mobile device:

I got quite a few replies asking me to write about how I got on with it so, here goes.

I already had a BlackGhost RC chopper that I was given by Microsoft at the UK Windows Server 2008 launch.  I flew that when I got back to the office (once, before one of my colleagues got annoyed) and then at home a bit, but after a few crashes it mostly stayed in the loft until my son spotted it and pestered to have a go. To be honest the BlackGhost was a bit big for indoor use, a bit rubbish outdoors and is only made of polystyrene and plastic.  My son was really keen to get something a bit better – only he fancies a decent quadcopter (no chance – it’s me who’s paying for this, not him!) so, when I saw the Griffin Helo TC in the supermarket for £30, I thought we should give it a go.

Firstly, I should point out that it’s an indoor toy.  That’s not clear from the packaging but we took it outdoors and struggled a bit. Even the lightest of breezes would carry the Helo TC out of range and send it crashing to the floor.  Thankfully it’s more resilient than it looks!

“Back at base” (after the boys were tucked up in bed), I had another go and was much more successful inside the house – but you really need a lot of space to get used to flying (more than I have).

Based on my complete lack of prior research I had expected the Helo TC to be Bluetooth controlled but it actually comes with an IR remote that clips on to the iPhone/iPad/Android phone and plugs into the headphone jack.  Engage Airplane Mode (seems appropriate!) and turn up the volume, then control the Helo TC with a free app that sends control signals via audio to the IR unit.  Power comes from a USB charge (approx 35 mins), engineering quality is pretty good for such an inexpensive device and it comes with spare rotor blades (main and tail). If you want a full review though, there’s more detailed online (including Engadget and LegitReviews).

My verdict: definitely an impulse buy; a bit of fun for some father-and-son time – but really needs a lot of space to learn to fly!

Learning Scratch, from an eight-year-old

Posted on By Mark Wilson
This content is 12 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Much has been written in recent years about the state of ICT education in Britain’s schools, so imagine my pleasure when my wife told me that my son was learning Scratch at school. I’d hoped that he would do something more than Microsoft Office and Google soon but had a suspicion we might have to wait for secondary education (he’s currently in year 3).

I asked him about it and he seemed really enthusiastic, so I asked if he’d like to do some more at home. Then, this weekend, I plugged the Raspberry Pi into the TV, loaded up Scratch, and asked him to show me how it works.

Wow! After two lessons at school, he’s off and away. Within a few minutes we (actually, no, I was a bystander – it was my son doing the “driving”) were drawing shapes on the stage with a helicopter sprite. He progressed from squares, to circles, changing the colour as each pixel was drawn on the screen, then worked out how to draw triangles and within a short while was doing what I can only describe as the modern equivalent of a Spirograph (remember them?), running two scripts in parallel with a single keypress.

Scratch

I was about 12 before I got my first computer (a Sinclair ZX Spectrum+) and took my first tentative steps in BASIC. Meanwhile, my kids are growing up in a world of smartphones, tablets and netbooks. It’s fantastic and I only wish there was some special STEM (Science, Technology, Engineering and Mathematics) programme at his school as he clearly enjoys it (sadly, he finds writing more difficult and I had to “bribe” him to complete his spelling homework by saying he could have some more time in Scratch if the spelling was completed without any fuss…). Private education is out of our reach but I’m pleased he’s getting exposure to Scratch at such a young age.

I’ll have him on JavaScript and C# next!