Almost two years ago I signed up to a two year fixed pricing contract for my power prices.  At that time I did a power comparison of various electricity providers using Excel.  Now I have to repeat the process as the prices will be changing shortly.  The Consumer Powerswitch website is very helpful. This is a good opportunity to apply the post on simple formulas.

Before we go any further, grab a recent electricity bill as we will be referring to it as we go along.

YouTube Videos

Here is a video which shows how to check your power bill:

How to Check Your Power Bill

Here is a video which follows on from there and shows you how to compare power prices in New Zealand:

How to Compare Power Prices

Overview of Power Bill

Here are the main components of an average residential electricity bill in New Zealand:

• A daily charge made up of a daily rate multiplied by the number of days of the billing period.
• A usage charge made up of a usage rate multiplied by the actual power used.
• An Electricity Authority Levy (EAL) charge, which I will just shorten to a levy.  It is calculated by multiplying the levy charge by the actual power used.  So the calculation is similar to the usage charge.  In some cases the levy is included in the usage rate already.
• Most power companies offer a prompt payment discount worked out as a percentage of the bill.

Please note that some bills are more complicated because they have two meters and two different usage rates.  This situation is not explicitly dealt with in this post but the principles can be applied in order to do the calculations and comparisons.

Consumer Powerswitch Website

Consumer has put together a website which compares all the prices of the different plans on offer in your area and calculates which one is the best for you.  In order to complete this exercise you will need to refer to it as it contains all the information we require for the calculation when we use the compare plans functionality of the site.  Here is the link:

http://www.powerswitch.org.nz

To use the website:

• Click Get Started on the first screen.
• Click the red button on the left, Find the best energy retailer for your needs.
• Proceed through the questionnaire, answering the questions in order to get to the results page.

Select plans to compare

• Select up to four plans to compare by selecting the checkboxes on the left.
• Click COMPARE SELECTED PLANS. The rates of the selected plans display.

• Deselect the Exclude GST checkbox.  It makes it easier for our calculations to compare plans with Goods and Services Tax (GST) already included. But note that some bills add the GST in at the end rather than as you go along.
• Write down or print (button – top right) the various rates for the different plans.
• If you would like to compare some further plans then click Back to results (top left) and repeat the process.

Setting up the Excel Sheet

Now open up Excel.  The first thing to do is to set up a sheet to record the information from the website for our calculation.

I set up my Excel sheet to record the rates for each plan with each plan in a different column.  I included my current plan, including GST (from my power bill), in column B as I first wanted to do the calculation using a recent bill to ensure that I was doing things correctly.

To set up the above sheet I also applied the following formatting:

1. I double-clicked on the line between the column headings A and B to expand the width of column A to fit the text in row 7.
2. I selected row 2 by clicking on the number 2 on the left hand side, then I clicked Wrap Text to cause the writing for each plan to stay within the cell and increasing the row height automatically and then I centred the text vertically.
3. I adjusted other column widths manually by clicking and dragging the line between two neighbouring columns (i.e. not double-clicking this line).
4. I formatted the rates into a currency format by clicking on the $ symbol in the Number group and then clicking on a nearby button in the group to expand it to four decimal places.
5. I clicked on the first column heading on the A to select this column and then I made the font bold.  I did a similar thing for the first two rows, rows 1 and 2.  (I had to click the bold button twice – once to turn it off because it was on by default just for the first column and once more to turn it on for the whole row.)
6. I selected all the cells where I had entered data (one option is to click in the first cell and drag to the last cell) and put borders on by clicking on the button in the Font group.

Power Comparison Calculation

I would recommend doing the calculation for a recent power bill first, breaking it down into the different parts.  Afterwards, the steps can be combined into a single calculation.

Enter the actual number of days and power used from the power bill.  Then go through each step of the calculation and compare the result with your bill.

I have changed the view in the picture above to show the formulas that I entered.  Normally Excel would just show the final values of the calculation.

In row 18 I have shown how the discounted total can be worked out in one step from the sub total in row 15. As an example, applying a discount of 10% to $100 (i.e. a discount of $10) gives an answer of $90.  This is the same as multiplying the $100 by (1-10%), i.e. multiplying by (100%-10%) which equals 90%.

Now insert three rows between rows 9 and 10.  (Click on the 10 on the left hand side then right mouse click and select Insert from the shortcut menu.  This inserts a single row.)

In cell B12 I have combined all the steps into one formula.  It looks pretty daunting when you first look at it but if you take a closer look you can see the different steps within it.

= (Daily charge +(Usage and Levy charges)) x (Applying discount)

Now I am going to set things up so that the bill price can be seen for a range of different power usages and assuming 30 days in an average month or billing period.  Power usage varies during the year so this range is designed to capture this.  Look at a winter, spring and summer bill to see where your usage fits in this range.

Now the trickiest part of this whole exercise is using the $ symbol to fix the rows or columns in the calculation so that it can be copied down (for different power usages) and copied across for different power companies, without having to change the formula any further.

The rates in B3, B4, B5 and B6 need to just have the row number fixed (and not the column number) so that they always point to the correct row when the formula is copied down and so that they point to the values for the next power company when the formula is copied across to the next column.  Hence they become B$3, B$4, B$5 and B$6.

The number of days (cell B9) needs to always point to this cell so that every calculation is done for the same number of days.  Hence the row and column need to be fixed, i.e. $B$9.

The power used needs to always point to column A but needs the row number to change when the formula is copied down for different power usages.  Hence A12 becomes $A12.

The final step of the process is to copy the formula down for different power usages and then across for different power companies.  Refer to the section on copying a formula in the earlier post.

Now pause and take a look at the numbers.  The whole purpose of this exercise for me is to review the numbers that come out of the calculation so that I can make an appropriate decision about which power company to go with it.  However, I don’t want to put any pressure on you to change power companies – that is your choice.  This exercise is a great training tool in and of itself.