Energy Resources: Data Analysis

*Excel Energy Data: download here


The primary sources of energy used in the U.S. today are fossil fuels, with over 85% of total energy coming from petroleum, natural gas, and coal. Nuclear and hydroelectric sources together provide about 12% of the total energy with additional minor, but locally important, amounts coming from geothermal, solar, wind, and other alternate sources.
This activity is broken into a few sections. In the first, you will examine data detailing global fossil fuel reserves and the use of oil in the US. In the second, you will compare electricity generation in the US to Germany and analyze trends over the past 20 years. Finally, you will perform some calculations using data about renewable energy resources that demonstrate the potential for renewable sources of energy.

Fossil Fuel Resources

Question 1: Look at “R/P Ratio” on the Oil - Proved Reserves, Gas –Proved Reserves, and Coal – Reserves worksheets. Take a look at the footnotes to determine what the R/P Ratio measures.

    1. Based on these values, are global fossil fuel reserves becoming scarce, or are there several decades of fossil energy left? Please site some R/P ratios in your answer.
    2. How reliable do you think these estimates are? What are some assumptions that are built into these estimates? Be as specific as possible.

    Click on the “Oil Production – barrels” worksheet in the energydata.xls file. Using Excel, plot US Oil Production from 1965-2009. To do this, select the rows containing the years and the US oil production, and click Insert->Chart. Select “Straight Lined Scatter” under the XY (Scatter) category. Years should be your x-axis, while thousands of barrels per day should be the y-axis. Be sure to add x and y axis labels, as well as a title, and name the data series as production in the legend. Your graph should look something like this now (depending on the version of excel you are using):

Right click on your figure, and select move chart, and move your chart to a new worksheet.

Next, add US Oil consumption to this figure in order to compare consumption directly to production. To do this, right click on your chart and go to select data. Near the box labeled “series,” click add. Name the new data set “Consumption,” and set your x-axis and y-axis values as the cells containing the years and consumption values respectively. Click OK.

Question 2:

    1. Insert the chart you just completed.
    2. What general trend do you observe between US oil production and consumption?
    3. How has this trend been changing across this time interval?

Comparison of US and German Electricity Production

Click on the “Electricity Generation” worksheet. Here, you will find information about how much electricity is generated by source. The units here are terawatt hours. Let’s first analyze total electricity production in the US over the past 20 years broken up by category. Select cells B3 to U12, and click on insert chart. Select “stacked area” and click okay. After adding a title and axis labels, your chart should look like this:

Question 3: Make the same chart for electricity production in Germany, and answer the following questions (include both charts with your answers):

    1. What resource is used to generate the greatest amount of electricity in each country?
    2. How has total electricity production changed through time for both countries?
    3. Which country produces more electricity from renewable sources? Which country produces the greatest percentage of its electricity from renewable sources?

Let’s display the information in question 3c above in a different way.  Select the same data range before to make a new chart, but instead of “stacked area” choose “100% stacked area. This chart shows the percentage of total electricity production from each source. Your chart for the US should look something like this after labeling it:

Question 4: Make the same chart for Germany and compare them. Include both charts in your word document and answer the following question:

  • How do the sources for electricity production in the two countries change over time?

Renewable Energy Resources

Question 5: Click on the renewable calculations worksheet in the energydata.xls document. Under question 1, the total average solar energy reaching the Earth’s surface, and the area of Earth’s surface required to meet the average global power demand (assuming that after installation, a solar array can convert only 5% of the solar energy reaching the panels into electricity) are calculated. In the spreasheet, only change the values highlighted in yellow.

    1. How does the amount of solar energy reaching the Earth’s surface compare to the average global power demand?
    2. How does the area of land needed to meet global power demand compare to the area of the Earth? What about only the land area of Earth?
    3. How do these values change if you increase the solar panel efficiency to 20% (change the value in the spreadsheet).

Question 6: A recent article suggests 40-85 TW (1 TW = trillion Watts = 1012 Watts) of wind power might be readily available to produce electricity. How do these values compare to the average global power demand?

Summary Question
Question 7:You are the policymaker: Based on your answers to the previous questions, write a paragraph with your recommendations for how the U.S. should change its energy policy (or why it should stay the same), with respect to production and consumption of energy resources. You may consider the implications of being dependent on imports, for example, or environmental considerations. Supplement your answer with data from the Excel worksheet or information from outside research. Keep this brief, but do include a plot or a small table if it helps illustrate your answer.
Note: the first link below has well organized data which is more comprehensive than the lab dataset.

Turn in a single document containing the answers to questions 1-7. There should be at least 5 Excel charts (1 from Q2a, 2 from Q3, 2 from Q4, optional from Q7).

References and Useful Links
US Energy Information Administration
BP Statistical review of world energy
Investigations in Environmental Geology. Foley, D. et. al. 1993: Prentice Hall,
Englewood Cliffs, New Jersey.
Laboratory Exercises in Environmental Geology. Blatt, H. 1994: Wm, C.
Brown Communications, Inc., Dubuque, IA.