microsoft_excel

4 Step Guide On Analyzing Price Plans

4-step-guide-on-analyzing-price-plans

During one of my Excel courses, one of the participants asked me how to analyse the tenders submitted by her suppliers. It looked like a strange question but I know where she was coming from. When suppliers submitted tenders, they would not submit one which would fit your exact specifications. Most of the time, they would give you bundles or quanitity discounts so that you could buy more at lower cost. And the quantity discounts differ from supplier to supplier. So what is the best way to analyse the tenders? I don’t have an example for tenders but I will share how I analyze the cheapest power plans offered by different retailers in Singapore in the following 4 steps.

Background

Singapore has opened up the electricity market to consumers starting from 1 April 2018. You can read about it in this link. With the opening of the eletricity market, electricity retailers started wooing consumers with different price plans and promotions, hoping that customers would sign up with them. The problem is, each retailer offered different price plans, plain vanilla plans with the cheapest rate or higher rate with a quantum rebate, just to mention 2 examples. Some retailers would attract you by publishing a super low price (without including GST). If you are not careful, you will be taken in by their low prices. Others will give you rebates or discounts instead and make their plans as attractive to you as possible. Yet, another group will entice you with a combination of price and promotion. With all the different permutations, how are you evaluate accurately and pick the best price plan for yourself? Here is the 4 steps I use to evaluate the price plans. This can be used for mobile phone plans, broadband plans, services on contract or tenders.

Step 1 – Choose between fixed price plans and discounted rate price plans

Given the current market condition where oil prices are going up, it makes sense to choose a fixed price plan. If oil prices are going down, choose a discounted rate plan. Based on market sentiment, oil prices are expected to go up in 2019 and for the next 2 years. So it makes sense to choose a fixed price plans. At the time of this analysis, the official rate offered by Singapore Power is $25.52 cents per kWh.

Step 2 – Listed down all the cost components

When the retailers offer you a fixed rate plan, the rate may or may not include the essential price components. It is important to find out what they were and factor them into the calculation. Some examples of price components that need further clarifications (in the context of my open electricity market example) are:

a) Is Goods and Service Tax (GST) included in the rate given

b) Did they include TLF, known as Transmission Lose Factor?

c) Did they include carbon taxes?

Align all the rates to make sure that the above components are included. Now, we should have a rate that is good for comparison.

Step 3 – Evaluate the promotions bundled with the plans

Retailers were going to woo you with discounts, rebates and credit card promotions, especially for new customers. Some of the huge discounts such as 50% off the bill were only valid for one or two months. Others may offer bigger discounts with shorter duration or smaller discounts with longer duration. To fairly evaluate these promotions, it is important not to just look at the bill for the next one or two months. Compare these plans based on the contract period. I am going to sign up for a 24 months plan and lock in the low fixed rate as I am expecting the oil price to increase over the next 2 years. Hence, I am going to evaluate the retailers based on my electricty usage for 24 months. Some of the details are given below

a) Calculate your total bill for the entire contract period for the next 24 months, using your current electricity consumption as the base.

b) Deduct from the total bill discounts and rebates offered by the retailers. Be careful of promotions that have a quota. You wouldn’t want to sign up for a plan with a promotion that has already been fully redeemed. I will only calculate those promotion in the next step 4.

c) Add on any fixed monthly fees you have to incur. Some retailers charge lower rate but added a fixed monthly amount into your bill.

d) Take the total cost and divide it by 24 months. Use this rate to compare across the retailers.

Once you are satisfied with the calculation, review the top 2 or 3 plans. I use Excel to perform the comparison. Here is how I tabulate the results

Compute power plan prices

The results are then presented in Pivot Table and Pivot Chart, sorted from the cheapest to the most expensive.

price comparison

After using these steps to compare the retailers and their plans, this is my conclusion. Keppel Electric and Sembcorp Power (bulk purchase for min 30 signups) offer the best deal at this moment. They were lower than other retailers by a few dollars per month. The rates in fact are almost the same. Now the question is who should I go with.

Step 4 – Look for current time-limited or quota-based promotion

I looked at the “extra promotion” they offer. Keppel bank gives rebates if you refer your friends to them. The referral does not expire. They have a credit card promotion with OCBC 365 credit card which I can make use of. They have one with UOB as well. This promotion is dependent on the credit cards you own. Not for everyone. But the rebate is for everyone to enjoy.

Sembcorp offers similar packages. They have a wider range of promotions for credit cards. You could find one that meet your needs. They also offer OCBC 365 credit card promotion, similar to Keppel Electric.

Compare the rates below against SP rate in Q1 2019 (25.52 cents/kWh). You save 30% of your electricity bill with these fixed plan. I have summarized the results into the table below for the top 2 retailers. You cannot compare all. Top 2 to 5 is good. Beyond that, you may end up with analysis paralysis.

comparison for top 2

In this case, which one am I choosing?

Keppel Electric because I can enjoy the rebate of $60 with a referral code. For credit card promotion, there is a risk. If I fall outside the time limit or quota, I may not enjoy it. Do there is a risk there. And the quota is not transparent during sign up. I prefer a sure get promotion. So in the long run, Keppel’s deal is better. The price comparison above has already been factored in with the rebate. If you are looking for a electricity retailer and would like to get the $60 rebate from Keppel Electric, click below to sign up. The rebate is applicable for 24 months plan (Fixed24).

Keppel Rebate

I hope you have a better picture now about analyzing price plans for yourself and the company.

facebook-comments

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here's how...


Copyright © advanced-excel.com 2007 - 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft

>