Model # 1 Design and Layout
As briefly described in the analytical view of how to produce the spreadsheet, the spreadsheet will be set out with the list of appliances, then with a series of columns along side., showing how many kilowatts the appliance uses per hour, needed to work out the units per quarter. Then the next three columns show us how long the item is used for each time it is used, and for how long, and then total amount of time it is used for in a week. These three columns are used because the final outcome of this will be multiplied with the amount of kilowatts the appliance uses to give the amount of kilowatts used per week. This calculation is then multiplied together with ‘npower’s’ basic rate (6.72pence) give the final cost of each item. The remaining columns include; units used per week, units used per quarter. These columns tell us how many units are used in total, throughout the 13 weeks, and multiplied together with the basic to give the cost, and then the final column telling us how much each item costs per quarter. This is the totalled up along with the kilowatts used per quarter, and shown in a separate row at the foot of the table. Below this, there would be the additions, standard charge plus the V.A.T., and then finally the total billing cost.
The columns, which contain formulae, are the total amount of hours (per week). This is calculated by multiplying the amount of times each item is used per week, by the average time it is used for. The formula for this is ‘=C(row number) x D(row number)’. To fill in this whole column, quickly, rather than just type out each formulae one by one, we fill in the first entry, ‘=C4 x D4’, then highlight the column, click on format…column…fill down. To quicken this process up, you could simply press Ctrl+D.
The next column containing formulae is the amount of kilowatts used per week. This is done by multiplying the time use per week by the amount of kilowatts an appliance uses per week. This formula is ‘=E(row number) x B(row number)’. To do this efficiently, highlight the areas in need to fill, then either click on format…column…fill down, or to do the shortcut, press Ctrl+D.
This next formula, is to calculate the individual cost of each item. The formula is, ‘=$K$2xG4’. To fill this column, repeat the process by going through the menu bar. This formula is known as absolute cell reference. This is used because when you fill the formula down, the new row number will still turn out being multiplied by the cell with the rate in, rather than having an equation like, ‘=K9*G9’, when the formula should actually be ‘=K2*G9’. This column gives us the cost of each appliance for the 13 weeks, which is then totalled up at the base of this table, using the formula ’=SUM(H4:H23)’ this automatically adds together all the prices between cell H4 and H23. This total then has V.A.T. added onto it, ‘=H23 x 0.05’, and the standard charge. Giving you the total bill for the basic npower rate. A formula sheet showing all of these calculations can be seen on page
Model #1 Implementation
This section is to show the functioning of the spreadsheets, showing what the spreadsheet does and what are the effects of this. The task we were given was to design a suitable and easy to use spreadsheet that would work out a bill if one single piece of information were changed. The bill does this because of the formula in the various columns. If we were to change the appliances around, into alphabetical order, or into subsections, which list all the appliances room by room, the bill would not change because the principal and the original formulae are still there. To test this, we came up with a series of ‘what if’ questions, where we take a specific scenario and apply that to the spreadsheets. Here are the following three ‘what if’ questions that were tried to show the validity of the spreadsheets. Examples of these can be found on pages
What if #1: What if my lounge was burgled.
I chose this as one of the ways to check that the spreadsheet functions correctly, because I wanted to see how much electricity is actually used in the living room area. The living room consists of a television, Hi-Fi system, video recorder and the vacuum cleaner, and since my mother constantly hoovers the room, that I watch a lot of television and I occasionally listen to the radio, I presumed that the cost of using these appliances for thirteen weeks would take up a large percentage of the final electricity bill. So I therefore predicted that the cost of using these items would be £12 - $14. However, upon investigation, I found out that this prediction was far more expensive than the calculated outcome, and that the spreadsheet totalled up the cost of the appliances in my living room to £1.87! This was done by b setting the amount of times used and the time use, for the lounge appliances to zero. This was very surprising. The bad side to this is, that although I made a saving of £1.87, I lost over £800 worth of appliances, meaning it was not such as a significant saving than first thought. The reason for this is that the television set and the video recorder use very little kilowatts per hour.
What if #2: What if I renewed my kitchen
This what if question was asked to see whether or not the tumble dryer and the washing machine is as expensive as my parents make it out to be. Also since the refridgerator and the Freezer is on 24 hours a day, and is never switched off, I predicted that the cost of running these appliances per quart would be fairly expensive. I predicted that I would save around £15. The appliances in the kitchen include the microwave, iron, tumble dryer, washing machine, freezer, refridgerator and the kettle. Despite this, the cost of running the appliances in the kitchen is more expensive than I thought, and when I ran this data through the spreadsheets, by setting the amount of times used and the time use, for the kitchen appliances, to zero, it showed that in total I would make a saving of £22.78. This money could then be used when purchasing new appliances for the new kitchen.
What if #3: What if it is summer and I decide to dry my clothes on the washing line
This what if question was applied to the spreadsheet to whether in the summer months, people are sensible at saving money, and use the weather to their advantage by drying their clothes outside in the sunshine. I also wanted to see how much the tumble dryer costs per 13 weeks, in relation to the other kitchen appliances. To begin with, I set the time use and the amount of times the tumble dryer is used for, to zero, and then, using the formulae entered into the spreadsheets, the sheet would calculate the cost of the bill automatically. When the result was recorded, I calculated that the saving made by not using the tumble dryer would be £6.55. The reason that the tumble dryer costs more than the other kitchen appliances, with the exception to the washing machine, is that it is the one appliance, excluding the shower, that uses the most kilowatts in the house.
The following graph compares the costs between the original bill, and the three ‘what if’ questions. The costs that are used to produce the graph, all have V.A.T. and the standard charge added on to it.
As you can see from this graph, the biggest decline in costs is the ‘What if I renew my kitchen’ question. This evidently shows that the electrical appliances in the kitchen take up a huge percentage of the final bill. Also, if we were to subtract the standard charge and the V.A.T., the cost for the ‘what if’ would be an even smaller cost.
Model #2 Design and Layout
This second model is designed to show a progression from the first model, and is supposed to show a change in design. The change in design for these sets of spreadsheets is that the tariff has been changed from the basic npower rate, of 6.72p per kilowatt-hour, to what is known as economy 7. This tariff is designed so that you are charged a more expensive rate, of 7.8p, during the day, but then charged a much cheaper rate for the electrical appliances used during the night, of about 2.8p per kilowatt-hour. This information is then put into the spreadsheet, one spreadsheet for the day rate and one for the night rate, and then a third sheet is needed, to add together the two bills, add the standard charge and the V.A.T.
The original model is to be changed by duplicating the original model twice, one for the day rate and one for the night rate. We then need to work out how many hours of the day each appliance would be used, and then for the night hours. The time-span for the day rate is from 6am to 11pm, and the time-span for the night rate is from 11pm to 6am. Therefore, if an appliance is used during the day and the night, for example, the refridgerator, the amount of hours it is used for during both time-spans would need to be calculated. On the other hand, some parts of the table will remain the same. This would be the formula in the columns, with the exception to the last column, which calculates the cost for each individual item. For the economy 7 tariff the rates for the night and day would be needed at the side of the table. In the day rate spreadsheet, the column calculating individual costs would be needed to be filled in using absolute cell references (see introduction for explanation). The absolute cell reference equation, to fill the items’ costs, for the night rate is ‘=$k$7 x G (row number’. For the night rate, the equation is ‘=$K$8 x G (row number). Then to get all the prices, simply repeat the process to fill.
The bill below the table stays the same, but the standard charge and the V.A.T. are not added to the day or night rate bill, but it is added on to the third spreadsheet, which works out the total bill. To put the costs in the new spreadsheet, using a formula, you will need to do a ‘3D absolute cell referencing’. This is the same as absolute cell references, but is used between spreadsheet models. The formula for a 3D absolute cell reference is ‘=’sheet name’! Cell number’. Formulae sheets and bills for this tariff, are found on pages
Model #2 Implementation
This section is to show the functioning of the spreadsheets, showing what the second models spreadsheets do and what the effects are, under a different tariff. The task we were given was to design suitable and easy to use spreadsheets that would work a series of spreadsheets for the day rate, the night rate, and a third spreadsheet to show the total billing for these two rates. The bill does this by using 3D absolute cell referencing, where it performs the absolute cell reference, over on or more spreadsheets. This works because of the formula in the various columns. If we were to change the appliances around, into alphabetical order, or into subsections, which list all the appliances room by room, the bill would not change because the principal and the original formulae are still there. To test this, we came up with a series of ‘what if’ questions, where we take a specific scenario and apply that to the spreadsheets. Here are two ‘what if’ questions that were tried to show the validity of the spreadsheets. Examples of these can be found on pages
What if I got a night job, and used the appliances during the night
I chose this as one of the ways to check that the spreadsheet functions correctly, because I wanted to see how much money could be saved if I used all the appliances throughout the night, instead of during the day. This is because, the night rate has shorter hours, ten less than the day rate, and it has a much lower rate in the night, than the day rate and the basic npower rate. I predicted that the cost of using these appliances for the same number of hours as in the day, but during the night, with the different tier. So I therefore predicted that the cost of doing this would amount to about £20. As predicted, I found out that this it was a reasonable presumption, and that the spreadsheet totalled up the cost of the appliances being used throughout the nigh came to £ . This was done by setting the amount of times used and the time use, for the appliances used in the day, to zero, with the exception to the refridgerator and the freezer, which is constantly in use. We then copied and pasted all the information in the uses per week column and the average length of use column, to get the same amount of hours as in the day. The total bill was calculated, and worked out that I would make a saving of £
What if #2: What if I renewed my kitchen
This what if question was asked to see whether or not the tumble dryer and the washing machine is as expensive as my parents make it out to be. However, this time it was done under a different tariff, to see if I saved or lost any money. Also since the refridgerator and the Freezer is on 24 hours a day, and is never switched off, I predicted that the cost of running these appliances per quart would be fairly expensive. I predicted that I would save around £20, as the night rate is much cheaper than the basic rate, and the day rate is not that much more expensive than basic rate. After running through all this information into the spreadsheet, it was discovered that I only made a saving of £
The following graph compares the costs between the economy 7 what if questions, with the basic rate question for this. The costs that are used to produce the graph, all have V.A.T. and the standard charge added on to it.
As you can see from this graph, the economy 7 rate shows a slightly cheaper saving, which could mean even more money to go towards the new kitchen appliances. This saving is due to the cheaper night rate, at approximately 2.8p, which is 5p cheaper than that of the day rate.
Evaluation
I feel that switching my family’s electricity tariff to economy 7, would work out approximately £7 cheaper
This is because if, for some reason, I need to stay up late to finish off homework, or that I was throwing a party, then the cost for running the electrical appliances in my home would be much cheaper, as the night rate tier, is less than the basic rate tier, thus meaning the cost of the final bill will go down. This is the method that some people do use, as they run the more expensive running items, like a washing machine and the tumble dryer, through the night, therefore getting a cheaper bill. However, this does not work for most of the appliances, as the T.V. or the radio will not be required throughout the night, so I suggest you should only change to this tariff if you do a lot of washing or clothes drying. Things are at a cheaper rate because the rate for the night hours, 11pm to 6am, costs approximately 5p more per kilowatt used in an hour. It is therefore cost effective to run the most expensive running appliance throughout the night.
It is a good idea, and cost effective to switch to the economy 7 tariff because:
You get the offer of a two-tier system, where it costs much less to run things during the night hours. This means that you could easily see how much money you would save by using a certain appliance at night or during the day. It is better to run the cheaper things during the day and the more expensive during the night
Companies offering economy 7 tariffs do not really offer that high a standing charge, as they know that only a few appliances will be used throughout the night, as most will be used during the day for entertainment, like the television or the radio.
The final cost of each individual item is less than using a standard rate tariff, as they only off one tier, whereas the economy 7 tariff offers two, one for the day and one for the night.
Overall, I am quite pleased on how this project turned out, as I felt that the research provided the relevant information to complete these spreadsheets. I also proved previously in the evaluation, that the economy 7 tariff would be more beneficial towards my family. I am also pleased with the validity of the spreadsheets, as they did the function they were designed to do, to calculate the costs of the electricity bill automatically, with the use of spreadsheets, concluding which tariff would be more beneficial to my family’s needs. The ‘what if’ questions provided some interesting analysis, as I found out that the living rooms appliance do not use as much electricity as expected, despite the long hours that the television is on for. I also found out that the kitchen’s appliance uses a big percentage of the total cost for the bill, as almost ¼ the bill consists of items being used in the kitchen. Finally, I was really pleased to see that all the formulae worked correctly, with simple data changes and the more complex data changes.
In conclusion, I feel that this project has met the necessary demands, and is easily usable by a naïve excel user and a less experienced one. I feel that my spreadsheets have been laid out well, and sufficiently annotated, and that I have provided a clear, detailed account of each and every aspect of the course.
If I were given more time to complete this project, and there were more facilities available, like less busier computer rooms, I think I could have made several improvements on my designs to the spreadsheets. I could have recorded exactly how many times a week each item was used for, and recorded for how long each item was used for, rather than just give averaged approximations. Average readings were used, as it would haven taken to long to record down this precise data for the whole 13 weeks, a quarter.
Also, I could have used a different variety of software to display my findings, like plotting graphs, or by providing relevant cut outs of the spreadsheets, to show the reader the point I am trying to get at.
What could have also been done, was to accurately change the two tier spreadsheets, to see how this affected the overall bill, and to accurately see whether or not the economy 7 rate was better for my family than the basic rate.
Finally, to give the most accurate reading of my family’s bill, I could have included the central heating in the appliance list, along with the cooker, but this information was hard to obtain, and to see, along with this information, which tariff would actually be cheaper.
The following graph shows the total costs for the economy 7 tariff, and the basic npower tariff. All costs used to produce this graph, include V.A.T. and the quarterly standard charge.
He graph shown on the previous page shows the difference in cost between the economy 7 tariff and the basic npower rate. The reason the economy 7 tariff is much lower than that of the basic npower rate, is because the night rate offers two tiers, one for day and one for night, and if you wre to run the most expensive running items, like the tumble dryer and the washing machine, the total bill will be reduced as these items are being used on a much cheaper tier.
Appendix
The spreadsheet seen on page , was designed to instruct the reader on how to use a spreadsheet to calculate an electricity bill. As you can see, this is a blank model of the original spreadsheet, but it has all the formulae contained in the necessary columns. The first task for the reader, is to find out how many kilowatts each electrical appliance in there homes. They then need to these results down into the first column, titled power used in kilowatts.
After doing this, the reader needs to fill in the time use and the amounts of times used. Remember, this information is only an approximate figure, and should be an accurate average, as it is extremely difficult to record the different lengths of time and the amount of times it is used for per week. Then you need to type, in the cell stated in the formula for the last column, into the basic rates cell. To change the rate, for say the economy 7 tariff, the spreadsheet will automatically re-calculate the individual cost of each appliance. The rates for this can be found on your family’s previous electricity bill.
Also used in the spreadsheet package, are ‘what if’ questions. This is where a specific scenario is placed into the spreadsheet, and you would need to change the data accordingly.
Take the ‘what if I renewed my kitchen’ question. This question was asked to see how much of the electricity bill si taken up by the appliances used in the kitchen. To do this question, you would need to set all time use and the uses per week columns, to zero, as none of these appliances would be owned. However, this must only be done for those appliances in the kitchen, see page for highlights. We then see, through the formulae, the cost of the bill, where we can then work out how much has been saved.
To display the important cells of the sheet, shade in the cells which are important, which then attracts the reader to this information. To do this highlight the cells you wish to highlight, then click on the ‘fill’ icon on the toolbar, then choose your desired colour. Drag the mouse over that colour, click, and the cell will automatically shade itself that colour.
To ensure that all column and row headings fit the cell perfectly, both press down ALT+ENTER, and the writing will appear on several lines, but still contained in the one cell, or, double click on the end of the column heading, shown by a letter, and this will fir the cell perfectly, and still on the one line.
When writing in a new spreadsheet, to save writing out all the columns, information, and the formulae, simply right click over the sheet names, located at the bottom of the sheet, the click on Move or Copy…Create a copy…(then click on spreadsheet name to where you want the spreadsheet to be situated.
To ensure that vital information is not accidentally deleted, I have used what is known as cell protection. The function of this is to stop people typing in the cells with the key formula and headings. To do this, click on Tools…Protection…Protect sheet…O.K.(password is optional). To enter information into the raw data columns, even when the sheet is protected, highlight the cells you wish to remain unlocked, right click over these cells, and then click on Format Cells…Protection…Locked. This will then unlock these cells, enabling them to be changed, even when the spreadsheet is locked.
