You probably could not excape noticing that Excel is rather adept at graphing. If you were running your desktop publishing company or your household, you would be keeping track of expenses like utilities. Graphing expenses is a good way to keep track of how they change over time so that you know what to expect and what not to expect. If your water bill, for instance, increases dramatically, you might need to hunt for a leak as the potential cause of the unexpected increase. Not surprisingly, creating a graph can also teach us more about Excel. You will add a chart and axis titles to this graph as well as a linear trend line. In the end it will be the sort of graph that you might also use for other classes. The trend line will also introduce the Linear Regression project coming up soon. For that project, you will calclate the formula for the trend line yourself.
You will be given a printed version of an electricity bill and a water bill along with a printed copy of these instructions. You will be expected to keep track of these for the duration of the project. The electricity bill also includes information about the average temperature, which you will also graph. An example spreadsheet may be provided and you can use it to check your work. It is not supplied in advance so that it does not overly influence your own spreadsheet design. The instructions below, however, should narrow down the possibilities enough for you to know in moderate detail what needs to be done.
Create a new spreadsheet called UtilityBill. This one will not use macros, so you can save it without changing the type.
Label the three sheets Electricity, Temperature, and Water.
On the electricity sheet set up two columns to record the data from the electricity bill.
Enter the usage data from the bill.
Select the cells you have filled in (including the column headers) and then on the Insert tab find the Scatter category and choose the one with Straight Lines and Markers. A rough draft of your chart should appear. Several context-sensitive tabs should also appear: Design, Layout, and Format. Choose Layout among the Chart Tools.
First change the Chart Title to Electricity Usage. Click on the title Usage and make it more specifically Electricity Usage.
Add a title for the x-axis: Read Date. To do this, click Axis Titles, Primary Horizontal Axis Title, and finally Title Below Axis. Enter the name.
Add a title for the y-axis: Usage (kWh). Click Axis Titles, Primary Vertical Axis Title, and finally Rotated Title.
On the layout tab there is also a button called Trendline. Press it and add a linear Trendline. This should show that electricity usage is generally decreasing. How this line is calculated is the subject of an upcoming project.
The chart should look reasonable now, but probably boring. Experiment with the settings and "improve" it in some obvious way. If you right click in various parts of the chart, you can format various elements. The Layout tab also offers access.
There is probably a reason the usage has declined, and it may be related to temperature. That's why the electricity company prints temperatures on the bill. Heating and cooling are major expenses. Make column headers and record dates and temperatures on the second sheet.
Select all the data and insert another scatter plot.
Add appropriate horizontal and vertical titles and check the chart title. Be sure to indicate the units for the temperature. The electricity company uses Fahrenheit, but you can convert and graph in Celsius for extra credit.
Add another linear trendline. This one should slope up. Can you make a hypothesis about the cause of the lower electricity bill?
Customize this chart as well.
The third sheet should be used for the water bill. The day of the month is not indicated except for the current reading. For date just specify 10/2008, 11/2008, etc.
Usage is measured in Ccfs, which is hundreds (C in Roman numerals) of cubic feet. Indicate this in the column header.
Although you may be familiar with the Fahrenheit scale for the temperatures, Ccfs are probably unfamiliar. Create a third column and label to show how many gallons are used.
One Ccf is 748 gallons. The first formula (cell C2) should therefore be something similar to =B2*748.
Copy the formula to the rest of the cells in the column.
Graph the values, not using the Ccf measurements but gallons. To do this you will probably need to select two non-contiguous regions. First select the dates and release the mouse button, then hold down the Ctrl key and select the usage in gallons cells. After they are both selected and are being highlighted, insert the scatter plot.
Add titles for the chart and the pair of axes.
Make another linear trendline. Is water usage increasing or decreasing?
Customize the chart again.
Finally, calculate some averages so that you can compare utility use between different households over various time periods. On the Electricity sheet add three more rows in column A: Total, Days, and Per Day.
In column B and across from Total insert the sum from Oct. 13 through Sept. 15. The reason for leaving out the initial value is that you don't know how many days were included in the very first Sept. 12 measurement.
Figure out how many days elapsed over the period of the bill. In the cell for Days, take Sept. 15, 2009 and subtract Sept. 12, 2008.
In the Per Day cell take the Total and Divide by Days.
In a very similar way, find out the per day water usage in gallons. Add lines for Total, Days, and Per Day.
Total the usage in gallons from the Nov. 2008 through Oct. 2009. Skip the Oct. 2008 value.
For the number of days, take Oct. 2009 and subtract Oct. 2008.
Divide the total by the number of days to get the per day usage. The average usually quoted for an American is 100 gallons per day per person. If this bill applies to one or more people, it is below that amount. However, it is increasing, so that is something to watch out for.
For extra credit you can look up and document average values and compare them to the averages in the spreadsheets.
Decide which of your chart looks the best and print it to a PDF or XPS printer. Select the chart before you go to print and it should be printed by default.
Call the resulting file UtilityBill.pdf or UtilityBill.xps.
Submit one of these files along with the spreadsheet to show your classmates.
When finished, submit UtilityBill.xls(x) and UtilityBill.pdf or UtilityBill.xps attached to a single email or transferred as a pair from a USB drive. The due date will be announced in class and published on the projects page as well as the calendar.
Points are granted in the categories below and a student choice award will be presented. Extra credit options are indicated with a +.
Create a new spreadsheet called UtilityBill.
Label the three sheets Electricity, Temperature, and Water.
Set up two columns.
Enter the usage data from the bill.
...your chart should appear.
First change the Chart Title to Electricity Usage.
Add a title for the x-axis: Read Date.
Add a title for the y-axis: Usage (kWh).
...add a linear Trendline.
Experiment with the settingsand "improve" it.
Make column headers and record dates and temperatures.
Select all the data and insert another scatter plot.
Add appropriate horizontal and vertical titles and check the chart title.
Add another linear trendline.
Customize this chart as well.
For date just specify 10/2008, 11/2008, etc.
Indicate [Ccfs] in the column header.
Create a third column and label ... gallons.
The first formula should be similar to =B2*748.
Copy the formula to the rest of the cells in the column.
Graph the values, not using the Ccf measurements but gallons.
Add titles for the chart and the pair of axes.
Make another linear trendline.
Customize the chart again.
On the Electricity sheet add three more rows in column A.
In column B sum from Oct. 13 through Sept. 15.
Figure out how many days elapsed over the period of the bill.
In the Per Day cell take the Total and Divide by Days.
Add lines for Total, Days, and Per Day.
Total the usage in gallons from the Nov. 2008.
For the number of days, take Oct. 2009 and subtract Oct. 2008.
Divide the total by the number of days to get the per day usage.
+Look up and document average and compare.
Print it to a PDF or XPS printer.
Call the resulting file UtilityBill.pdf or UtilityBill.xps.
Submit one of these files.