Zen and the art of Business Operations Management #5

13

Spring follows winter.
Targets follow performance,
which follows targets.

14

Timesheet overheads,
like tigers prowling: unseen,
until it's too late.

15

Far forest zephyrs -
barely heard whispers - sighing:
"Why's your forecast late?"

Zen and the art of Business Operations Management #4

10

Do not lose your way
in the forest of finance.
Swap cake for guidance.

11

Dragon's piercing gaze
falls on RAG status reports,
probing for weakness.

12

Departments: towers
of bamboo. Between, torrents
of resources flow.

Zen and the art of Business Operations Management #3

7

Target: our desire.
Forecast: your expectation.
Plan: mutual journey.

8

Projects, when gardened,
grow, live, breathe. Left untended,
they wilt, choked by weeds.

9

Targets are as dreams
on waking: swiftly fading,
barely remembered.

Zen and the art of Business Operations Management #2

4

Among the columns
imagined figures flicker,
grow, become real.

5

Your project sets sail
on dark and stormy oceans.
I am your lighthouse.

6

In the beginning,
no process, no procedures;
great minds, working hard.

Zen and the art of Business Operations Management #1

1

Targets. Plans. Forecasts.
Is your forecast real or dream?
Do your dreams come true?

2

Consideration
should always be given to
utilisation.

3

Talent - ideas -
technology - time - testing -
revenue - profit.

Four functions I'd like to see in the next version of Excel

Originally published on my company internal blog.

Excel provides a bewildering array of built-in functions for all manner of scientific and statistical analysis. It even lets users write their own functions. But no matter how flexible a tool is, users will inevitably want immediate, specific functionality ​that meets their needs now. Here are some Excel functions I'd love to see.

ACTUALPERCENTAGE()

Copy-and-paste a percentage from a text source, and the value is stored is a number such as 50. But Excel stores actual percentages as a fraction (0.5) and merely dislays them as a number with a % sign. This means that 50, when imported from a text source, displays as 5000%. That's definitely wrong.

This useful function looks at the context of your number, including the number of decimal places that you've set, and decides whether it should be divided by a hundred or not.

CELLFORMAT()

Conditional formatting lets you set the format of the cell based on the cell's contents. For example, cells containing negative numbers can be highlighted in red.

But what if you want to change the contents of the cell based on its formatting? For example, what if all red cells should have their values weighted? That's where CELLFORMAT() comes in. It returns a description of the format of the cell against which you can do useful comparisons and adjust your business logic accordingly.

=IF(CELLFORMAT()="Red with yellow stripes", "Safe to bathe", "Do not go in the water")

Remember: It's considered best-practice to design your spreadsheets so that business logic depends upon the formatting of the cell.

ABACUS()

Excel already includes a ROMAN() function which converts numbers into Roman figures. But what if your Excel user isn't an ancient Roman, but instead is a Mesopotamian or Egyptian from 2,500BC? That's where ABACUS() comes in: it converts numerals to a visual representation of beads. An optional second argument returns the result in the Sumerian base-60 system.

CPP()

Everyone knows that VBA is a toy language used only by failed software engineers who've turned into ops managers through sheer bad luck. What Excel really needs is a way of executing a proper language, within the constraints of a normal formula (ie 32,767 characters long). CPP() executes C++ code within the context of the spreadsheet. Spreadsheet objects are available in the same way that they are to VBA.

=CPP("#include <iostream> ; #include <worksheet> ; int main() { cout << \"Hello, \" << Worksheet.Cells(\"A1\").Value(); return 0; };")

See? Simple, clear, powerful.

Six baffling Excel functions

Originally published on my company internal blog.

BAHTTEXT

What it does: The baht is the currency of Thailand. BAHTTEXT(123) returns the words "One hundred and twenty-three baht", but in Thai.

Why it's baffling: Because this is the only function in Excel that does anything like this, and it's only available for the baht. There's no equivalent for converting a number to text in any other currency or language.

CODE

What it does: CODE("HELLO") returns the code for the letter "H" in the computer's current character set

Why it's baffling: For a start, it's somewhat difficult to see why anyone might ever want to use Excel to get the ASCII / ANSI / whatever code for the first letter of a string. And then there's the contortions you'd have to go through if you wanted, say, the ASCII code for the second letter instead.

DAYS360

What it does: Returns the interval between two dates, in days, whilst assuming that all years have 360 days in them.

Why it's baffling: I can just about see why a company might have equal-length accounting periods - 12 months times 30 days - but I'm seriously struggling to see why you'd want to do date arithmetic on this basis. An optional second argument gives you a modicum of control over which five or six days of the year get ignored but still leaves open the awful question: what if my birthday no longer exists? Do I still get cake? Also, there are now automatically five (or six) fewer shopping days left until Christmas... I think. And there now exists both a 29th and a 30th of February every year.

ROMAN

What it does: Converts from numbers to a text string in Roman numerals. For example, ROMAN(5) returns "V" and ROMAN(21) returns "XXI".

Why it's baffling: Three reasons. First, unless you need to put a copyright date at the end of a TV programme, why would you ever want Excel to work in Roman numerals? Second, there's a bewildering array of optional arguments that return strings in different forms, most of which, I suspect, wouldn't mean much to a real Roman. Third, there's no function for converting a Roman numeral string back into conventional Arabic digits - so having converted a number to Roman, it can't be processed any further.

SUBTOTAL

What it does: Sums across a range of cells but excludes any cells that already contain a SUBTOTAL function. This means that you can use a SUBTOTAL function on a group of lines, and a SUBTOTAL again against a group of groups, without counting the same figures twice.

Why it's baffling: Because it doesn't actually sum, and hence doesn't actually provide a subtotal, unless you give it a very specific first argument: the number 9. Not only do you have to know that the first argument is one of 22 pre-defined function reference numbers, but you also have to remember that the most obvious default number is not 1, but 9.

DAVERAGE

What it does: There's a range of database functions whose names start with a "D". This one returns the average from a database or list of values.

Why it's baffling: OK, actually, it's quite straightforward and not baffling at all. But I like to think of this function as being called DAVE_RAGE which nicely defines my normal experience when using Excel.