Competitor Analysis

When we see competitor analysis, we always think of us versus our competitors, how we fare against our competitors.

If we are comparing quotations from supplier, we called it supplier evaluation.

I did a supplier evaluation on the retail electricity pricing for consumers. And I called up Sembcorp and Union Power for their bulk purchase rates (neigbours all sign up together). Since it is bulk purchase the rates should be better. 

And when Sembcorp revert back with the bulk purchase rate, it was indeed better but only slightly. Looks like a good deal until I compare the bulk purchase rate that I get against the promotions they are running.

It turns out that their bulk purchase rates fare very very badly.

So I showed them my table

Being a consumer, you can also do your comparison. It is the way of data analytics. You don’t have to use expensive tools to perform this analysis.

All you need is the Power Query and Pivot Table functions in Excel. They are free in the newer versions.

Below is how I did it.

How this is calculated

The tariff may be the same but the promotions offered by the power retailer are different. The promotions will lower the effective rate further. 

We need to collect the data from the different power retailers and the promotion they offer so that we could identify which power retailer is the cheapest. It took me one Saturday morning to browse through different websites and collate their tariff plans and promotion they offer plus fees like registration, other charges.

If I do not know about Power Query, I would have simply tabulated the price plans of each retailer and the promotion they offered. 

An untrained person would simply layout the credit card promotion in each column and made a comment below each column such as $80 for 24 month and $20 for 12 months.

On the other hand, a trained data analytics guy would separate the pricing plan and the promotions into 2 different worksheets. It is easier to collate and maintain every month or quarter. Unknown to untrained data analytics guys, the 2 worksheets can be combined easily using Power Query and save some time.

 How to compare 6, 12 and 24 months plans?

It is always challenging to compare pricing plans of different duration. Longer contract is usually perceived to be the better deal. It’s like quantity discount. But that’s an assumption that always needs to be confirmed. In this analysis, we compare the net discounted tariff. This is done by calculating the total bill, minus the rebate and then divide the net amount by usage to derive the rate per kWh. In this scenario, it is easier to find the adjusted tariff and do the comparison in a table.

Choosing the optimal comparison metric requires a little training and lots of practice. My training came from the competitors analysis I did for M1 and Virgin Mobile. You can always start with this analysis.

Not everyone likes to see how you did it

Doing the calculation is important but delivering your conclusion to your boss or peers is the final step.

In any presentation, my first choice is Pivot Table. It can present the conclusion in many different views and you are sure to get one that is useful for your presentation plus a Pivot Chart to go with it. Except that Pivot Table fails me this time. Pivot Table is great to organize the numbers into groups and sorting the groups and within the groups. But in this case, I wanted to sort purely by the metric and it was not able to do it by design. 

I have to resort to tables. It would have been crazy to change gear at this stage if you do this manually. Good thing is that Power Query can easily convert my output to Tables, the new kid (function) on the block (Excel). And It comes with a slicer for me to easily toggle the output based on different conditions. I put a slicer for fixed and flexi plans. Another for duration.

A little bit of thinking helps me switch back my output to Pivot Table, my favorite reporting tool. And it was and has to be a quick switch. I don’t believe in spending too much time on reports once I get my data right.

And this is how it looks like in Pivot Table

In EverydayExcel, we test out different scenarios to make sure that we can get the best outcome for each analysis. That’s why we are called EverydayExcel Business Lab.

Presentation is important. The first rule of presentation is as easy to understand as possible, followed by as beautiful as possible, not the other way round. 

If you would like to learn data analytics and perform this type of analysis for your job now or in future, drop us an email to enquire.