I went to the Asian Civilisation Museum to see the Terracotta Warriors exhibit. You also get to see the Singapore Merlion.
A while ago, I received an email from a distraught salesman. He believed his sales commissions were wrongly calculated, and asked me to shed some light.
Note that I’m not using the exact numbers he gave in his email.
The story goes that Michael (as I’ll call him) and his colleagues were given sales targets that were negative. How could sales targets be negative? Shouldn’t you be trying to sell something? The reason given was that the current economy was disastrous, and basically each sales person was trying to not lose sales.
You’re gonna bleed. It’s how much you bled.
Anyway, given Michael’s negative sales target, he managed to exceed it. He didn’t manage to bring in sales (positive sales numbers), but he didn’t lose too much money (slight negative sales numbers). But his sales commissions didn’t reflect that.
Now I’m not going to discuss how that works out. I can’t presume to understand the business logic behind the sales commission in this case, but I’ll discuss the mathematics behind the numbers.
The normal sales targets and commission
Let’s say your sales target for this month is $1000. This means you’re expected to sell about $1000 worth of products or services. We’ll ignore the condition that you will get some commission based on what you sell, regardless of how much you sold (my brother’s a sales person), as well as other types of commissions.
Let’s say the sales commission is based on how much extra you sold beyond your sales target. Makes sense, right? Let’s use simple percentages.
If you sold $1100 worth of products or services, then your percentage commission might be calculated as follows:
(Difference between Your Sales and Your Sales Target) / (Your Sales Target)
Or ($1100 – $1000) / ($1000) = 10% commission.
This is assuming that your sales amount exceeded the sales target, of course.
The case of negative sales targets
Now if the sales target is negative, as in Michael’s case, the mathematical formula still applies. But you have to note the negative sign. For some reason, “business” people (no offense to business people) tend to see -4567 as larger than 12, even though 12 > -4567. They see the magnitude first, not the value itself. (It’s also why I get emails about calculations involving negative numbers… anyway…)
Let’s say the sales target is -$1000. Everyone’s expected to lose money, but you try not to lose more than $1000. At least that’s what I’m interpreting it as.
Let’s say Michael managed to lose only $50. Or -$50 to be clear. The formula
(Difference between Your Sales and Your Sales Target) / (Your Sales Target)
have to be modified to this
(Difference between Your Sales and Your Sales Target) / (Magnitude of Your Sales Target)
In maths and programming terms, the “magnitude” part refers to the absolute function. Meaning you ignore any negative signs. Actually, the modified version works for the normal case too (which is why you should use it for the normal version anyway to take care of weird cases like this but I digress…).
So, we get (-$50 – [-$1000]) / abs(-$1000) = $950 / $1000
Actually, you should use this:
abs( [Your Sales] – [Your Sales Target] ) / abs(Your Sales Target)
That’s the “foolproof” version. Consider it a bonus for reading this far. Frankly speaking, any competent programmer should be able to come up with that formula, even without much maths background. You just need to think about the situation a little carefully (ask “what if?” more often).
Michael’s calculated commission
When Michael wrote to me, he said his commission was calculated as follows (given that he only lost $50):
-$50 / -$1000 = 5%
Let’s say someone else lost -$900 that month. With the above calculation, that person gets:
-$900 / -$1000 = 90%
Clearly it makes more sense to lose more money! This was why Michael wrote to me.
I don’t propose the method I gave is correct, business-logic-wise. Michael didn’t give me any details on what he’s selling, or what his company is (or even why it’s acceptable to have negative sales targets, regardless of the economy). So I cannot give any help other than from a pure mathematical point of view. But I hope it’ll at least give Michael a fairer commission amount.
Given Michael’s situation, what do you think is an appropriate calculation formula?
Can you think of (or know of) a realistic situation where a negative sales target is acceptable? I say “acceptable”, but seriously, no company should “accept” that they lose money every month.
It rained suddenly just when I wanted to capture some outdoor footage. I might as well make use of the wet weather…
Also, I dug up my 10 (?) year old keyboard!
Make the time and money you spend while studying in university count. The value of a degree doesn’t fluctuate much, year to year. But if you take just one year longer to obtain that degree, it means you’ve wasted one year of your life and another few thousands of dollars in tuition fees. Which means it takes that much longer for you to repay the tuition fee loan (if you took out one).
University/college tips from Bryarly
University/college tips from Emily
In which you learn some Malay/Indonesian/Hokkien/Chinese words. This is when you’re ordering tea or coffee from a local coffee shop or drinks stall in Singapore. Not Starbucks.
What’s the longest beverage customisation you’ve ever ordered?
The Tea Project:
In the course of writing my guide on Open XML and Excel spreadsheets, I discovered some things about the Open XML SDK. There’s been some flak about the SDK or even on Open XML in general. But the price of the SDK is free, so you can’t beat that. While there are some inconveniences with using the SDK, the solutions and workarounds aren’t particularly nasty.
Here we go.
Open XML SDK is not a convenience library
If you’re looking for a neat little function that will toast bread, mop your floor, press your shirt and wish you “Have a nice day!” as you’re leaving the house, Open XML SDK is not what you’re looking for. (Actually if you find a software library that does the above, I wanna know.)
The Open XML SDK is a software development kit (hence the “SDK”). It is not a conventional software library in that sense. It provides atomic functions that allow you to do all sorts of nifty things to create/manipulate Open XML spreadsheets, word processing documents and presentation slides. The keyword is “atomic”.
This isn’t necessarily a bad thing. I know some companies will prefer not to use a software library (or third-party software). For example, government agencies or financial institutions, where data security is a concern and all software is (preferably) written in-house. The furthest they would allow is probably standard libraries.
I’m not going into a discussion about whether Open XML SDK is a “standard” library, since it’s from Microsoft. There seems to be a lot of unhappiness with Microsoft. Is it because of their monopoly? Because Bill Gates is rich? I like the .NET Framework, because it makes coding easier. Open XML SDK is built on top of that, so that makes Open XML documents easier to create and manipulate.
That said, because Open XML SDK offers atomic functions to do atomic tasks, this allows you to create your own functions to encapsulate whatever you’re trying to achieve. This flexibility is sometimes more valuable than convenience. Why do you think software companies charge a lot more for the source code of their libraries? You want to know what’s going on behind that function from a third-party library, because you don’t know if that bug is due to your own code or their code. (It’s probably your own code though…)
Open XML SDK is not a data manipulation library
There are 4 main parts to the Open XML SDK:
- Word processing documents
- Presentation slides
The graphics part is a common set of class objects used by the other 3. Out of the first 3, spreadsheets are typically used to hold lots of data. Financial data, fiscal summaries, call logs, database dumps and so on. Text documents and slides hold data too, but not usually as intensive as spreadsheets.
Now it might surprise you that Open XML SDK offers no functions to manipulate any kind of data. None at all.
Need to dump 100 database records with 8 fields into a spreadsheet? You need to write code to fill in a 8-column-100-row block of cells.
Need to sort data? Do it within the database environment (there’s a SORT BY clause, you know?), or write your own sorting function. Because Open XML SDK ain’t got it.
This isn’t a big problem, but it does make writing code a little bit of a hassle. Particularly when you’re creating or manipulating a data-intensive spreadsheet. It’s not like when you’re attaching the DataReader object to a DataTable, and voila! All the data is nicely formatted and shown on a web page.
But this is also where the flexibility of writing your own functions come in.
Code execution order is important
When writing code with Open XML SDK, you have to put aside how you normally work with spreadsheets, text documents and slides. I’ll lump them together as “office documents”. Small “o” so it’s not copyright, right?
As a user, you would normally just go about your task of typing text, inserting images, styling text, cut-and-copy sections of an office document. The order of how you go about doing it is irrelevant, because you get to see the final result. And the final result is all that matters.
When working with Open XML SDK, you need to keep to an ordering of code sections. Each class in the SDK has to be appended to the appropriate parent class in the correct order, or you’ll get a corrupt file. This means your code has to be executed in the correct order too.
Go to the Open XML SDK help file. Let’s say we look at the Worksheet class. There will be a section called XML schema. In that, there will be an XML tag called “sequence”. See those “element” tags? That’s the order.
This code order isn’t a big problem. In your spreadsheet, it looks like an image is at the top, with a table of cell data below it. In code, you need to code for the table of cells first, then the image. Because that’s the order.
The solution is to ignore the visual placements of the data sections in your office document, and look at what is in your office document. Then you rearrange all the data parts according to the order, and then write code for that.
The Open XML SDK offers you flexibility in how you write your code and a great price (it’s free). It is backed by a large software company (Microsoft), so it’s not likely to disappear with no support in the future.
Sure it’s not as easy to use, but sometimes your situation doesn’t allow you to use a third-party library (cost, licensing, security). Hey, it works well enough. And sometimes, when you’re in a tight project, that’s all you really need. That you produce software (that produces office documents) well enough.
If you’re interested, you can check out my guide. I teach you how to create a spreadsheet with the standard functions in Excel such as text styling, image insertions, multiple worksheets, cell formulas. I also show you how to use a template in case you need something really complicated. And there’s working source code in C# and VB.NET.
In the August 2011 issue of Singularity, I interviewed Dave Doolin from Website In A Weekend. Also, I show you a couple of optical illusions (you can see more in the magazine). Which brings me to the Gestalt theory, where you see shapes when none was supposed to be there.
Also, my cat makes a special guest appearance.
In this issue of Singularity, you’ll read my interview with Dave Doolin, who writes at Website In A Weekend. We talked about the US economy, the prospects of being a generalist in Singapore and WordPress. He’s also on Twitter @websiteweekend.
Download the August 2011 issue (about 11 MB).
And if you haven’t watched it, I also explain the maths concept of Cantor sets in a video. Harry Potter references and special yellow screen effects. Watch it. Now.
You’ll also read about the second part of my visit to the Singapore Science Centre. There’s a special section on optical illusions. See if you’re stumped.