math excel project

Math 115 Excel Group Project 3

1

Data and Sample Calculations The file Excel Project 3 Data.xlsx includes three tabs. • The first and second tabs contain data on demand and price for gasoline by region. • The third tab contains sample calculations for the Group Component.

Rubric This assignment includes a Group Component (50%) and an Individual Component (50%). The requirements summarized here are described in detail in the pages that follow.

Group Component (submit 1 report per group) Typed report with correct grammar and spelling, and all required content (see Step 6) 80% In-Class Worksheet (must attend to obtain credit) 20% You can do this before getting together with your group:

Step 1: Read about Gasoline Prices The following is from the introduction to an article called, "(Un?)Happiness and Gasoline Prices in the United States," published in September 2010 by the Brookings Institute. The report includes a discussion of the spike you will see in your price data in 2008.

Gasoline purchases are an essential part of the American way of life. There were about 250 million motor vehicles in the United States in 2008 – just under a vehicle per person. Americans drive an average of more than 11,000 miles per year, and gasoline purchases are an essential part of most households’ budgets. Between 1995 and 2003, gasoline prices in the U.S. averaged about $1.49 a gallon, with average prices rising above $2.00 in 2004. By the summer of 2008, gasoline prices had reached a national average of $4.11 per gallon. At that time, Americans earning less than $15,000 a year were spending as much as 15 percent of their household income on gasoline – double the proportion from seven years earlier. In addition, unpredictable fuel costs make planning monthly household expenditures difficult, which can be detrimental to individual welfare and even to the overall economy.

Gasoline prices fell in the aftermath of the 2009 economic crisis. Prior and during the financial crisis, rising gasoline prices were seen as a symptom of an uncertain economic situation, as well as evidence of the questionable sustainability of our future oil supply. Gasoline prices abated along with the decrease of economic activity that accompanied the onset of the recession, reaching their minimum in late December 2008. A few months later, as the economy entered a gradual recovery phase, gasoline prices also trended upward. In contrast to the previous period of great uncertainty about future oil supplies, however, these price trends were considered more positively as signs of the U.S. economic recovery.

Consider reading the full report. Here is the source; to download the full report, follow the link on the right of that page. Copy and paste the url into your browser, if the link does not work. http://www.brookings.edu/research/reports/2010/10/09-gas-prices-happiness-graham

Math 115 Excel Group Project 3

2

More About Gasoline Prices The U.S. Energy Information Administration, whose data we use for this assignment, presents insights about gasoline prices. (These brief articles are a good way to get an understanding of the big picture.) Copy and paste the url into your browser, if the links do not work.

• Why gasoline prices fluctuate http://www.eia.gov/energyexplained/index.cfm?page=gasoline_fluctuations

• Factors affecting gasoline prices http://www.eia.gov/energyexplained/index.cfm?page=gasoline_factors_affecting_prices

• How gasoline prices vary across the U.S. http://www.eia.gov/energyexplained/index.cfm?page=gasoline_regional

Group component Work as a group to complete the following analysis and report.

Step 2: Prepare your data • Open the spreadsheet ExcelProject3Data.xlsx. Create a new spreadsheet that has only the data for your region, as follows: • Copy and paste the columns entitled Line Number, Month and Year into your new

spreadsheet, columns A, B and C. • Copy the column for your region from the Demand tab and paste it in column D. • Copy the column for your region from the Price tab and past it in column E. The dates on the price and demand tabs coincide, but do make sure that the data you paste in lines up with the correct dates.

Step 3: Display and describe the price-demand data Economists always plot the price on the vertical axis and the quantity demanded on the horizontal axis, when graphing the relationship between price and demand. • Create a scatter plot that displays the demand from column D on the horizontal axis and the

price from column E on the vertical axis. Format the chart as follows. o Delete the legend, since you will display only one data series on this chart. o Title the chart. o Format the vertical scale to include dollar signs and two decimal places. o Format the horizontal axis labels to include 1000's place commas. o Label the horizontal and vertical axis appropriately, including units. o Change the colors as you see fit.

This data appears to have a large variation in prices, but a relatively small variation in demand, but looks can be misleading. For example, if you changed the horizontal scale to range from 7000 to 10,000 the data would appear more spread out horizontally. To understand the relative spread of the data ranges, do the following calculations:

• Calculate the median volume of gasoline sold per day. In cell D171 (at the bottom of the demand column), use Excel's MEDIAN function to find the median of the demand data. Label the calculation for easy reference by typing, "Median," in cell C171.

C D 171 Median =MEDIAN(D2:D170)

Math 115 Excel Group Project 3

3

• Similarly, find the minimum and maximum volumes of gasoline sold per day, using Excel's

MIN and MAX functions, in cells D172 and D173. Again label the results "Minimum" and "Maximum" respectively, in column C.

C D 172 Minimum =MIN(D2:D170) 173 Maximum =MAX(D2:D170)

• Find the range of the data in cell D174, by subtracting the minimum from the maximum.

C D 174 Range =D173-D172

• What percent of the median does the range comprise? Calculate the percentage in cell D175. Change the format of that cell to display the result as a percentage. Tip: If you can't figure out how to do something in Excel try a Google search. For example, search for "Format cell percentage Excel 2008 Mac."

C D 175 Percentage of

the median =D174/C171

• Select Cells D171:D175 and fill to the right by one cell to calculate these values for the price column. Select cells E171:E174 and format them to match the formatting of the prices in column E. Make sure E175 displays a percent.

From what you see here, do consumers tend to change their purchasing habits due to changes in the price of gasoline? You will address this question in your report.

Step 4: Display the price-demand data as a time-series We have the month and year in separate columns so that we can sort the data in the next step. For this chart, you will create a column that has both the month and the year, so that you can use these as labels in your chart. • In Column F, use Excel's CONCATENATE function to combine the text in column B with

the year in column C. CONCATENATE combines multiple text strings into one string. For example to combine "Green" and "frog," with a space between them, we would use the command =CONCATENATE("Green"," ","frog"). The cell would contain the text "Green frog". Similarly, in cell F2, combine the contents of cell B2 with cell C2, with a space in between.

F 2 =CONTENATE(B2," ",C2)

You should see January 2000 in cell F2. Fill down to get the month and year for each row of the data. Adjust the column width so that the words fit in the cells in column F.

• Select the data in columns D and E, and create a Line Chart (not an X Y Scatter plot). • Change the Category (X) axis labels to the labels you created in column F. Tip: Right click

on the chart and choose Select Data from the drop down menu, to reveal the Select Data Source dialog box. Your first data series is selected. Click in the Category (X) labels box, and then select F2:F170 on your spreadsheet. Then click on your second data series, and do it again. While you are there, you can add or change the Name of the series, to how you wish it to appear in the legend.

Math 115 Excel Group Project 3

4

• Of course the data in these two columns are not measured using the same units, so you'll need to use two vertical axes, as you did in Excel Project 2. Initially, only one vertical axis will appear. Double click on the data series showing demand, to reveal the formatting menu. Select "Secondary Axis" from the Axes options.

• Look at the graph and say, "Whoa! What happened in 2008?" Reread the paragraph in Step 1, and consider reading the entire article.

• To make the graph easier to read, adjust the horizontal (category) axis scale. Double click on the axis, and select Scale in the dialog box. Set the interval between labels and the interval between tick marks to 12 (since there are 12 months in the year, this will mean that only January will be labeled).

• On the chart, display the vertical gridlines. • Format the chart as follows.

o Title the chart. o Format the vertical price scale to include dollar signs and two decimal places. o Format the vertical demand scale to include 1000's place commas. o Label the two vertical axis appropriately, including units. o Change the colors as you see fit. o Include a legend. Consider moving it to the top or bottom to make a more

economical use of space. From what you see here, does price fluctuate with the seasons? Does demand? You will address this in your report.

Step 5: Compile a group project report (1 per group) Work together to create a 1-2 page well organized and well written report showing your

group's work. Include the following. • An introduction saying what data you are studying. Include a brief statement about the

location of your region (Here's the map: http://www.eia.gov/petroleum/gasdiesel/diesel_map.cfm)

• The chart you created in Step 3, with the following. o No legend. o A title. o Dollar signs and two decimal places on the price-axis, 1000's place commas on the

demand axis. o Labels on the axes, including units.

In Excel, you can click on a chart to select it, and then copy and paste it directly into a word file. Within the word file, you can still change the formatting (colors, fonts labels, line thickness and so on) of your charts to your liking.

• A table that shows the Median, Maximum, Minimum, Range, and Percentage for both price and demand, which you calculated in Step 3. You can paste this in directly from Excel: select cells C171:E175, copy and then paste into Word. Reformat it as you see fit.

• A paragraph answering this question: Do consumers tend to change their purchasing habits due to changes in the price of gasoline? When you answer this, verbally describe the information in the table above. Give the median price and demand for your region, and explain how much the price and demand each varied between January 2000 and January 2014. Use the percentages you calculated to compare the variation in price to the variation in demand. Your paragraph should include numerical values with correct units.

Math 115 Excel Group Project 3

5

• The chart you created in Step 4, with the following. o A title. o A legend. o Dollar signs and two decimal places on the price-axis, 1000's place commas on the

demand axis. o Labels on the two vertical axes, including units. o Horizontal axis scale showing labels for each January in the data set only (see Step 4). o Vertical and horizontal gridlines.

• A paragraph discussing seasonal variation in demand. Address the following: o How you can tell from the graph that the demand varies with the seasons? o In which seasons do consumers appear to have the highest and lowest demand for

gasoline? Describe the maximum and two relative minima that appear each year. (Tip: In Excel, click on the graph to select it. Then mouse-over the data; Excel will show you the values.)

o What are consumers doing at these times that could affect their demand for gasoline?

Step 6: Acknowledgement of participation Each student should write a sentence or two saying what contribution you made to the group's part of the project and turn it in to their instructor when the group's assignment is collected.