Microsoft Excel is an even better piece of software to use for the given task. Excel can store lots of data, and there are a lot of mathematical functions that it can do for you, such as PRODUCT and SUM. These are the simple mathematical operators that Excel can use. There are also other important formulae that can be used, such as IF and fixed cell referencing. These all come in use somewhere in calculating an electricity bill. I will explain the formulae later on in my write- up. The calculations Excel performs are by relating data through formulae. There are many different types of formulae that can be used. For example to work out a sum of the units that all of the appliances use, we can use a SUM formulae, by typing ‘=SUM( ),’ where the data that needs to be totalled is between the brackets.
I decided to choose Excel out of the software available. This is because it can handle data easily, and it can also use the data well to calculate complex formulae and calculations, easily and quickly. The formulae that Excel provides can be relevant to the task set. For example, IF, SUM, and others are useful in their own ways for taking a part in calculating an electricity bill.
However, not all of the work I did for the task was on Excel. I, at the beginning of the task, needed to use pen and paper to record the usage and the wattage of all my appliances. This was easier done by hand because I could literally walk around my house recording any data that I needed. I then transferred this pen and paper layout onto an Excel spreadsheet, so that I could create copies of the spreadsheet if needed. The pen and paper layout of the data that I collected around my house can be seen below. It is from this data that everything else works from. I didn’t carry on working by hand because Excel is easier, quicker, and more efficient. It also saves a huge amount of time, which is needed for this task.
Cell protection is another one of Excel’s advantages that I used. By protecting a cell, the data or formula of a cell can’t be changed. This is useful because some cells don’t need to be changed and therefore by protecting them, it stops them from being changed accidentally.
A pen and paper layout of my spreadsheet is given below.
After I transferred the pen and paper layout (above) to an Excel document, I then started to work and build on it to do calculations that were related and needed for the task. First, I placed all of the data in alphabetical order, so that it is easier to find electrical appliances that you need. I did this by highlighting the table – Data – Sort – Sort by column A – Ascending. This put the first column in alphabetical order, and all of the other data changed position according to the first column. The first formula I used was to convert the wattage value into kilowatts. Using a formula to divide the wattage value by one thousand did this. This gave a number, which was the amount of kilowatts an electrical appliance used. The formula I used was ‘=B4/1000,’ which calculated the kilowatt value of the first electrical appliance. I then dragged the cell down, so that the formula could be applied to all the other wattages. The spreadsheet below shows the formulae used and the result.
Spreadsheet - formula
Spreadsheet - result
After obtaining a kilowatt value, I then multiplied this with the average length I use the appliance in a week, to get a number which could tell the user the amount of kilowatt hours (kWhr) an appliance uses in a week. This number is also the unit number, as a unit is a kilowatt hour. The formula was calculated by multiplying the unit number of the first appliance by the average length that I used the appliance in a week. The exact formula was ‘=C3*D3.’ I dragged the cell down so that the formula was applied to all of the appliances. This formula was useful because it gives the user a unit number for a week. The units are what will be used to calculate the bill eventually. The spreadsheet below shows the formulae used and the result.
Spreadsheet - formula
Spreadsheet - result
After finding out the unit number per appliance, I then needed to create a formula that calculates the total number of units for all of the appliances. The spreadsheet above showing formulae can be seen above. This was done by using a SUM formula. I had to find the total of the units of all of the appliances. Therefore I used the SUM formula to find out the unit total. The SUM formula added up all of the units for the appliances to give a total unit value. The formula I used was ‘=SUM( ),’ where the units that needed to be totalled were in the brackets. I didn’t type each cell reference, but showed the range of cells that needed to be totalled. This therefore gave (E3:E10) in the brackets. The formula added up all of the data that was between these two cells, including the two cells that are in the brackets. I used INT so that an integer value could be found rather than a decimal number. This is much easier than typing every single cell that needed to be totalled. This is why Excel is useful. It makes sums like this easier.
After the units were calculated, I decided to create a small model which would calculate a weekly cost. This was done so that I could test out my formulae and see whether they were correct. To calculate the cost I had to use the IF formula, as it is useful in calculating a bill for a split rate model. I had to create a cell which shows the split value. This was 182 as I am on npower. After doing this I needed to create a formula that would calculate whether my unit total was greater than, or less than 182, so then the cost could be applied to it. The IF formula needed to be used. The IF formula works as: logical test, value if logical test is true, value if logical test is false. This is useful for a split value, because the formula can work out if the unit total is greater or less than 182. The formula I used, in words was =IF(Total number of units is greater than split value, split value, total number of units). This means that if the number of units is greater than the split value, then type the split value in the cell. But if the number of units is not greater than the split value, then show the total amount of units. Below this formula, I needed to create another formula that calculates the number of units after the split value, if there are any. I had to use the same logic: logical test, value if logical test is true, value if logical test is false. The formula I used in words, was ‘=IF(Total number of units is greater than split value, Subtract split value from total number of units, zero). This meant that if the total number of units is greater than the split value; subtract the split value from the total number of units, to give the amount of units after the split value. If the total number of units is not greater than the split value, then show a zero, meaning that there no units after the split value. I checked for the two formulae by changing the total number of units to make it less or more. The formulae worked correctly.
After working out the IF formulae, I then had to type in the data for the cost before and after the split value. I then worked out a total cost for the units by multiplying the cost of the first 182 units by the first IF formulae cell, and then adding this to the cost of the units after 182 units multiplied by the second IF cell. This gave a total cost for the units. All that needed to be worked out after that was the VAT amount and the standing charge. However, my bill didn’t have a standing charge, so I typed zero pounds, so that if a standing charge is introduced to an npower bill, the model can apply it to the total cost. VAT for my bill was at 5%. To work out the VAT, I multiplied the cell containing the 5% data by the total of the standing charge and the cost of the units. I then used a SUM formula to calculate the total of the bill. The formula totalled the VAT cost, the total cost for the units, and the standing charge, if there is one. For the total cost of the units I used “custom number formatting.” This meant that I was able to write “TOTAL COST FOR WEEK IS” and then the total cost, in one cell, and the spreadsheet treats it as if it is just the formula for the total cost. I did after the formula worked out the total cost. I then highlighted the result and went on to format cells. I then went on to the number tab and clicked on custom. I typed in what I wanted before the result, and placed speech marks either side of the text. The number was referred to as ‘general.’ I then clicked on O.K. and the cell was custom formatted. It is better to custom format because it is better than having a formula and the text that relates to it in different cells. A spreadsheet of the formulae I used and the results is given below:
Formula - version 2
Results - version2
After working out that my IF formulae work, I decided to use the formulae to work out the cost of all of my appliances. I used the same formulae to work out the total cost. I also custom formatted the VAT cell and the final cell, showing the total cost per quarter. However, I decided to work out the cost for a quarter rather than a week. This is because I could then compare it to my real bill. To work the units for a quarter, I created a formula. The formula multiplied the units for a week by thirteen, as there are thirteen weeks in a quarter of a year. The total cost was obviously larger, and it was closer to my real bill. However, it was quite a bit off. I decide to create a better version of this model, which would be more accurate.
This final version was more accurate than the previous spreadsheet. I made it more accurate by, one; changing the bulb format. Previously I had typed all the bulbs of the same wattage in one cell, as if they were all switched on by one single switch. I changed this to make the cells show bulbs that were switched on by one switch, and by a different switch, same wattage or not. This was because the bulbs that are switched on by one switch will all come on for the same amount of time.
I also had to change the length of use of my freezer and fridge- freezer. I realised that my fridge and fridge- freezer were not on for twenty four seven, but actually on for a mere three point five hours a week. I modified my spreadsheet accordingly. The final cost was a lot closer to my real bill. On my final version, I also used a ROUNDDOWN formula for the total cost of the quarter. I rounded the value down to two decimal places. I rounded it down because if I rounded it up, I would be paying for units that I haven’t used. I rounded it to two decimal places because prices can’t be paid in values of three decimal places. The ROUNDOWN formulae works as; ROUNDDOWN( ), 2). The number after the comma shows the amount of decimal places I rounded down the value.
I used the INT formula on the total units cell. This was so there weren’t any decimal places in the value. I used the INT formula by typing =INT and then placing the formula I already used in brackets.
I created several generations of my models because it can be seen how they differ. I have created 4 versions. The spreadsheets with pricing on them have had custom formatting on them. I have also used gridlines on the spreadsheets so that it is easier for a user using the spreadsheet to know the different areas of the spreadsheet such as; VAT, Cost for units, Electrical appliance table etc. I used gridlines by highlighting the area I want to border, and then going onto format cells and then the border tab. I have also used appropriate headings for the data. I think my spreadsheets are user friendly, because the user usually has to change data, which isn’t difficult, and then the formulae work out everything straight away.
My final version had to be around a 5% range above or below my real unit amount. My real house usage was 1068. My model showed a unit amount of 934. My model’s unit amount has a % difference compared to my household usage. The final version and the cost for all of the appliances can be seen below, along with formula sheets. This final version is referred to later as the normal bill.
Spreadsheet of version Spreadsheet of version 4Formulae of version 4
My household bill is also shown below it.
To test my model I decided to create ‘what if’ scenarios. These could also be useful in trying to figure out a way of dropping my bill. I created 7 ‘what if’ questions. They are all described and explained below:
What if I use less T.V.?
I decided to ask this ‘what if’ because quite a lot of units came from the T.V. Quite a lot of the time, in my household, the main television is on, even when there is nobody watching it. My T.V. is on for the majority of the day because of this. Also, by using less T.V., it would mean I can cut down the usage of my Sky® Digibox. I am sure that this will drop my price quite a bit lower. I have decided to try using my T.V. for two hours less a day. Therefore, this would mean a fourteen hour drop in usage per week for the television and for the Sky® Digibox. I changed the data showing the usage per week so that it is deducted by fourteen. The kilowatt-hours automatically calculated the unit number. This would mean a drop of 182 hours per quarter. This would mean that there would be a drop of 182 hours after the split value. If the cost per unit after the split value is around 7.04p, I would expect a drop of £12.81 on my bill. I would also expect to see a further drop of around £60p, because of VAT. This would mean my prediction is £57.86 for my bill if I use less T.V.
A spreadsheet can be seen below, which highlights the changes I made to calculate the bill for this ‘what if’ question. There is also a formula page showing the formulae used for the spreadsheet.
Spreadsheet of what if I use less T.V.
Formulae page of “ “
My prediction was about £10 off the normal bill, if I use less T.V. I was £11.42 off. This was due to a miscalculation. It is good to use less T.V, as I predicted. I needed to use 2D cell referencing for the total units used. I made a cell showing clearly what the units used was. I used 2D cell referencing because I made the cell show another cell, which was made to calculate the total units. It is simple to use 2D cell referencing. All that I needed to do was to type ‘=,’ and then highlight the cell that I wanted to reference. I also used cell referencing to calculate the total of the cost for the units. 2D cell referencing was also used to calculate the kW value, the kW hours used weekly, and the kW hours weekly. All cells were also cell protected, so that they couldn’t be changed by adding other data in them. . I used conditional formatting on the cell showing the bill. I did this by highlighting the cell - Conditional formatting. From here, I described what I wanted the computer to show, and in what circumstances. I made the cell turn red if the price was greater than my original bill, and green if the new price was less than my previous bill. This is because it can be seen whether the bill after the ‘what if’ question is less than or more than my normal bill. If the price is cheaper than my normal bill, the cell will automatically turn green. If not, and the price is more expensive than my normal bill, then the cell will turn red. This is useful, as it can easily be seen if the price is higher or lower than my normal bill.
What if I don’t use my VCR?
My Nintendo® Gamecube is connected to the 80 Watt television through a VCR. This is because the television is quite old, and can not be connected right from my Gamecube to the T.V. What I could do, however, is have my Gamecube connected to the more modern 68 Watt T.V. This would mean that I won’t be using my 80 Watt T.V. at all, and I also won’t be using my VCR at all, as I don’t watch many videos now anyway. Therefore, on my spreadsheet, I have decided to delete to change the VCR usage to zero hours. I have then deducted the amount of T.V. I use when playing on my Gamecube. This would mean a drop of 14 hours. As I don’t use my 80 Watt T.V. for anything else, I have also decided to change the usage of my 80 Watt T.V. to zero hours. The 68 Watt T.V. would need to have an increase in 14 hours of usage per week. Overall this would mean a drop of 5.278 units per quarter. This would be deducted from the units used after the split value. Therefore meaning a drop of £0.074 multiplied by 5.28 units. VAT would also mean a drop of about £3.30 Therefore, a total drop of £3.69, to give £67.58 as my prediction of my new quarter bill if I didn’t use my VCR or 80 Watt T.V., and instead used my 68 Watt T.V. A spreadsheet that shows the changes I have made to the final bill (highlighted), and the total cost per quarter if the changes above were made is below. A formula sheet is also given.
VCR spreadsheet
VCR formula
My prediction was £3.69 off my normal bill. This is probably due to rough VAT guesses. It is better to change the way I use my Gamecube. I needed to use 2D cell referencing for the same cells as the previous ‘What if.’. I used conditional formatting to show if the bill has increased or decreased from my original bill. The total cost per quarter cell should be red if the bill has increased, and green if decreased.
What if I changed my bulb wattages?
Another thing I wanted to test was how much of a drop there would be on my bill if I changed the wattage of my bill. I decided to change my wattages by changing all of my bulbs to 40 Watt bulbs. This would therefore mean a drop of 149 units per quarter. This could be deducted from the units used after the split value, therefore meaning a drop of £0.0704 per unit, which if there is a drop of 149 units means that there would be drop of about £10.49 in my bill. With VAT on this I predict a total drop of £3.08 on my previous bill, giving me a total bill cost of about £57.69. A spreadsheet is given below, showing the changes I made (highlighted), and the new cost per quarter. A formulae sheet is also given.
My prediction was 13.58 off my normal bill. I think this probably just because of VAT, and a miscalculation in unit drops. It is better to use different wattage bulbs. I used 2D cell referencing on the same cells as the previous ‘what if’ questions. The three ‘what if’ questions explained so far are all similar as they all involve changing data. The following ‘what if’ questions all involve changing the model completely. I used conditional formatting to give a red cell if the price is greater than my normal model, or less than my normal model.
What if I used a standard model?
This ‘what if’ is useful because it can show me whether a split rate model is cheaper than or more expensive than a standard model. I could also use this information to give advice to my family. A standard model, as explained before, has one cost per unit, rather than two separate costs, and a split value for these costs. I predict that my bill would be cheaper than my normal bill. This is because the npower cost per unit on a standard model is cheaper than both costs per unit for a split rate model. However there is also a standing charge on a standard model or npower. This standing charge is applied after the total cost for the units has been calculated. I think that if a household uses a huge amount of units, than a standard model would work out better than a split rate model. This is because the standing charge wouldn’t affect the price as much if the price was huge. VAT is also calculated after the standing charge and total cost for the units have been calculated. If the cost per unit for a standard npower model is 6.72p, than if I use 934 units the total cost for the units would be 934 multiplied by 6.72p. If the standing charge is £5.14, then this would mean the total of the two values is £67.90. VAT would then need to be added at 5%. This cost would probably about £3.40. Thus meaning a total cost per quarter for a standard model of £71.30, for my prediction. Below is a spreadsheet showing the changes in data (light green), and the changes in formulae (blue). A formula sheet is also shown below the results sheet.
Standing model results and formulae.
My prediction was £0.03 off my normal bill. This means that my prediction was really close. I used a ROUNDDOWN formula to two decimal places on the cell showing the total cost per quarter, so that I wouldn’t be paying for extra units that I haven’t used. I also used custom formatting so that the cell showing the total cost for the quarter reads the value and the text in one cell. I used conditional formatting to show whether the bill was higher (red) or lower (green) than my normal bill.
What if I used Economy 7 (standard model)?
Economy 7 works differently to other models, as described before. It has a different cost per unit for day units and night units. For npower, the day units are priced at £0.073, and the night units are priced at £0.029. As I don’t use much of my electrical appliances in the night, I don’t think that economy 7 will save me money. This is because the cost per unit in the daytime, where I use lots of units, is bigger than the cost per unit in a split rate model - both costs. I think that if the night cost is about £0.02 per unit then this can be worked out to be about 60 p for the night cost. The day cost however, would be a lot higher than this because there are a lot more units and the cost per unit is a lot higher. The cost per unit is about £0.073, meaning if this in multiplied by 900, it gives a value of £65.70. VAT is then added on to this at 5% for npower. There isn’t a standing value, so I predict that the total cost per quarter for an economy 7 standard model would be £68.99. I created three spreadsheets to work out the cost for economy 7 standard. One spreadsheet shows the daily usage per quarter, one with the nightly usage, and one shows the totals, with all the calculations on it. These formulae use 3D cell referencing. These formulae can be seen on the formulae page given below, along with the economy 7 day and night usage. The totals page gives the results and the total cost per quarter for economy 7 standard. The spreadsheets are user friendly because if there are any changes in data, the user just has to change the data on the two data sheets accordingly, and then the totals page will automatically work out the cost. I had to check after doing the spreadsheets whether the day and night values for the units totalled to give the total units in the normal model. I was one unit off, which wouldn’t have made much difference. The spreadsheets are given below.
Economy 7 day
Economy 7 night
Economy 7 totals
My prediction was just £2.29 off. This wasn’t far off. I think economy 7 is worse to use than my normal bill because I don’t use much units. It would be more useful if the customer uses his/her higher wattage appliances more in the night. I conditionally formatted the cell showing the total cost to show if the price was higher than my normal model (red) or lower than my normal model green).
What if I used Economy 7 (split rate)?
Economy 7 split rate combines the economy 7 standard model and the split rate model together. Npower don’t do an economy 7 split rate, so I decided to use British Gas’ costs. The daily cost is worked out by split rate (split value = 900 units), and the night cost is worked out just as if it were an economy 7 standard model. I used my previous economy 7 day and night usages to apply the costs to. All I did for the economy 7 split rate was to create a totals page. This totals page calculated all the costs that are needed or the question. I predict that the cost would be higher than economy 7 standard. This is because the split value is high, and covers all of my daily units, meaning all of my daily units are priced at £0.1214 each. I also don’t use many units in the night, so fewer units would be at a low price. The split value would work better for people who use high amounts of units, as more units would be at a low price. If 900 units are priced at £0.1214, and 31 units are priced at £0.0611, than the total cost for the units would be around £111.15. With VAT of 5% added on to that, I predict the total to be about £116.70. The spreadsheet is given below, along with a formulae page.
Economy 7 (split rate) totals
“ “ “ formulae
I was £6.64 off my normal model price. Economy 7 (split rate) was more expensive than economy 7 standard. This is probably because all of my day units were priced highly. My prediction therefore was mostly right. I used conditional formatting on the total cost cell. This, as usual meant that the cell would turn green if the new price was cheaper than my normal model, and red if the price was greater than my original model. I also custom formatted the cell so that it looked better and was easier for the user. I used an IF formula that also used 3D cell referencing. The formula, even though it is really long, is quite easy to understand. The 3D cell references are from the economy 7 day and night pages. The formula, in words is: =IF( Total number of units on the economy 7 day page is greater than split value from economy 7 day page, show split value, total number of units on the economy 7 day page). The formula follows this pattern- logical test, value if logical test is true, value if logical test is false. Therefore the formula would show the split value if the total number of units in the day is higher than the split value, and it will show the total number of units if the total number of units aren’t greater than the split value. I also had to use another IF formula to calculate the units after the split value. The formula in words is - =IF (Total number of units on the economy 7 day page is greater than split value from economy 7 day page, Total number of units on the economy 7 day page minus split value from economy 7 day page, zero). This formula meant that if the total number of units is greater than the split value, then the cell should show the difference between the split value and the number of units. If there weren’t any units after the split value, then the cell would show a zero. The night page was worked out simply by multiplying the cost per unit for night by the cell showing the total number of units used in night. I also used 3D ell referencing or this formula. The total was then worked out by totalling the VAT amount, the nightly cost, and the daily cost.
What if I switched to British Gas?
For my last what if question, I decided to find out how my bill would change if I switched suppliers. I decided to switch to the well known, British Gas. British Gas work split rate. They charge 10.34p per unit for the first 900 units the user uses. They then charge 5.54p for any units after the first 900 units. I use 934 units; so therefore, the majority of my units are valued at 10.34p each. This means that the cost would probably be quite high compared to a supplier who has a low split value. If 900 of my units cost 10.34p each, then I predict that the total cost for these units should be around £90. Therefore, this leaves 34 units at 5.54p each. This means that the total cost for all of the units should be around £91.90. With VAT on this, I predict the VAT amount to be around £6.00, meaning a total cost of around £98.00. The spreadsheets below show the formulae I used for changing to British Gas and the results of the formulae. Spreadsheet of British gas
Formulae of British gas
My prediction was £2.16 off my model value. British Gas, for me is not better than npower. This is probably because most of the units are priced at about 10p. However, with npower, most of my units are priced at 7.04p Perhaps if I used more units, then perhaps British Gas would become better than npower. I used conditional formatting on the results spreadsheet to show whether the new bill was greater than (red) or cheaper than (green) my normal model.
For the spreadsheets that show my ‘what if’ questions, I used many of Excel’s functions. I used table lines and borders to highlight the areas of the spreadsheets. I also bordered the cells that I changed for the first three ‘what if’ questions. This makes the spreadsheets user friendly. I used conditional formatting to show whether my bill after the ‘what if’ question was asked, was greater than or less than my normal model. Conditional formatting is useful because the user can easily distinguish whether the bill is higher than or less than my normal model. I used cell shading so that the user can see any changes I made to my normal model. This is useful as the user can pin point to the changes. I also used custom number formatting, which is a way of placing text next to numbers or formulae, and for the cell to treat itself as if it is just a number. I also used a ROUNDDOWN formula so that the bill can be payable and there are no tenths of pennies or other fractions of pennies. I used cell protection so that data can’t be changed. The cells that I put I cell protection on were the appliance names and any cells that contained formulae. This means that the formulae couldn’t be changed. Any changes I made in formula have been coloured blue, and any changes I made in data are coloured light green. This is so the changes can be highlighted. Any labels that I have changed have been coloured yellow, such as the ‘what if’ question that asks about using less wattage bulbs. I used cell validation so that mistakes couldn’t be made. I used cell validation on the cost per unit cells. I made the computer show a warning, if the cost per unit was higher than a pound. Cell validation was used so that mistakes couldn’t be made in costs.
Overall, I have learnt a lot of things from my ‘what if’ questions. My predictions weren’t much off. VAT was usually the cause of this. I created a chart after finding out results to my ‘what if’ questions so that it can easily be seen which prices are more expensive than others. The chart is easy to understand and can be interpreted by most people. The chart shows that economy 7 split rate (British Gas) was the most expensive by a considerable amount. This is probably because of the split value that British Gas use. Economy 7 split rate would be more efficient to my family if we use more units of electricity. Otherwise the best model to use for my family is the standard model. Economy 7 could be better for my family, but only if I make changes to the way I use my electricity. It would be more efficient if more of our electrical units could be used at night. Therefore these units would be priced at a low cost. It is possible for my family to adjust and change our electrical usage. I think my mother could start to iron her clothes more during the night. My dad could start vacuuming at night so that these units could be priced lower. I could also make my family have a suitable night period, to work with my electrical usage.
A chart is given below to show how my ‘what if’ questions compare to my normal model.
I created this chart so that it can be seen easily which ‘what if’ is cheaper than my normal model. It can be seen from my chart that economy 7 split rate (British Gas) is the most expensive for me. There are many changes my family can make to ensure the cheapest bill possible. Using less T.V. for example is a great possibility.