Computer Prep Math Project 8



Lab 1: Use of Microsoft Excel

The three Excel assignments in this Lab should adhere to the following format:

A. Your name should appear left justified at the top of each page of each spreadsheet. Immediately below your name the Assignment number must appear in the form "Assignment _ ". The date of the assignment's completion should appear at the top right of each page of the spreadsheet. To do this use the Custom Header option of the Header/Footer menu accessed through the command sequence File/Page Setup.  

B. All the assignments of this Lab should be handed in at the same time, stapled to the first page. Do not hand in the assignments piece meal. Wait until all three assignments have been completed.

I. Introduction

A spreadsheet program like MS Excel allows you to enter numbers and labels into cells which make a two-dimensional array or table called a spreadsheet. The rows of the spreadsheet are labeled by numbers and the columns by numbers. In addition to storing information, a spreadsheet's real power comes from performing calculations and generating graphs. Furthermore, if the contents of a given cell are changed, this change is automatically incorporated into the calculations or graphs. For this reason, calculations in a spreadsheet should always be stated as formulas involving the addresses of the cells containing the data rather than the numerical values of the data. This illustrated below.

Cells D2-D4 contain the values 9, 8, and 6 respectively. To place the sum of this column (23) in cell D5, we type the equal sign in the command line. This tells Excel that we are entering a formula. The "function" name we want is sum. Selecting "SUM" from the functions bar results in the dialogue box shown below. We need to indicate that the block of cells to sum is D2-D4. One way to do this is to click on the "little spreadsheet" icon shown in the extreme right of the Number 1 data box. This returns you to the spreadsheet and allows you to select with the mouse those cells which are to be summed. Once the selection has been made press the enter key. Then click on "OK" in the dialogue box.

[pic]

A second alternative is to simply type "sum(d2:d4)"  in the command line and then press the enter key.

[pic]

A third alternative is to place the cursor in the cell D5 and left click on the sigma (summation) button and press the enter key..

[pic]

In any case what is shown in cell D5 is the computed sum. (To actually see the formula in a cell rather than the result of its evaluation use the command sequence Tools Options View and click on Formulas check box.)

Another feature that makes spreadsheets easy to use is the use of relative addressing in copying and pasting. Suppose we have two columns x and y, with x in cells C2-C7 and y in cells D2-D7. We want to make a new column z, where each entry in the z column is the sum of the corresponding entry in the x column with twice the corresponding entry in the y column. Stated as a formula z = x + 2y . To put this formula into spreadsheet language, we compute the first entry in the z column, E2 as +C2+2*D2. Using the leading + sign lets Excel know that we are entering a formula without having to depress the = sign in the command line.

[pic]

We could enter the corresponding formulas for cells E3-E7, but this is rather tedious, especially if there were a large number of cells. Here's where the magic of relative addressing comes in. We select cell E2 and copy its contents into the clipboard. Then we select the cells E3 -E7 and right click with the mouse to get the selection menu. We choose to paste the clipboard into the selected cells. You might think that this would paste +C2+2*D2 into each cell.

[pic]

In fact, we get just what we wanted. Excel is "smart enough" to know that the C column is being added to twice the D column, so it changes the cell addresses for each cell. This is what relative addressing is all about.

[pic]

Sometimes we don't want to copy and paste cell address "relatively". For example, if a formula must always use the contents of cell G17 regardless of what cell contains the formula, we would refer to cell G17 in the formula as $G$17 . The use of the $ means that if this formula is copied and pasted into another cell, the contents of cell G17 will still be used in the computation. The use of the $ with cell addresses is called absolute addressing.

Excel Assignment 1: (15 points)

Go to the website and download a copy (“right click” on the link and choose “Save Target As”) of the Excel file Salesort.xls. In Excel open this file. Using formulas and the Format menu modify the worksheet so that it appears as follows:

A. All column widths are adjusted so that all column headings fit.

B. All columns except "Sales Associate" and "Employee Code" are displayed as currency with 2 decimal digits.

C. The "First Quarter Sales" Column is the sum of each associate's sales from January through March.

D. The "Commission" Column is 6.5% of the First Quarter Sales Column.

E. All Column headings are bold and a blank row separates the column headings from the sales data. A single width line underlines the row of Column headings.

F. In addition to the original order of associates, three other listings of the spreadsheet results are to be shown, each separated by a blank row. So make three copies by using select, copy, and paste. The first listing should have the associates' names in alphabetical order. The second listing should be in ascending order of the Employee Code, while the last listing should be in descending order according to First Quarter Sales. To sort a block of cells, select Sort from the Data menu. Block here refers to the entire set of cells to be included in the sort. Since all four listings are to be displayed in the final form of the spreadsheet, be sure to make a copy of the rows before executing the sort. That is, first make the changes to the rows in the order given, then make a copy of these rows below them, then sort the copied rows. Repeat this process until all four listings are in the same spreadsheet.

G. Insert a three-D pie chart showing the contribution of each associate to first quarter sales. To access graphics use the command sequence Insert Chart. Select a three-D pie as the Chart sub-type. Choose Series (rather than Data Range) and click Add. Select the column of first quarter sales as the Values (cells to plot) and the column of names of sales associates as the Category (X) axis labels. This graph should have a title of First Quarter Sales. Once the chart appears right clicking on it (or any portion of it such as titles or labels) brings up an "object menu" which allows you to make changes. The chart can be resized in the same way as graphic images in MS Word. Insert the chart into your spreadsheet.

H. Print the spreadsheet. Use the Page Setup and Print Preview commands in the File menu to get a printout that is easy to read and understand. Use the Scaling adjust option under the Page index label of the Page Setup menu so that all columns printed fit on one page.

Excel Assignment 2: (15 points)

Go to the website of the National Weather Service Forecast Office. Choose the following options: 1. Product: Daily Climate Report, 2. Location: Madison, 3. Timeframe marked: Archived Data to find the maximum, minimum, and average temperatures recorded in Madison for each day of the preceding calendar month. In an Excel spreadsheet enter this temperature data in four columns. The first column is Day and runs from 1 (the first of the month) to the last day of the month. Compute the average, median and standard deviation (using the Excel functions AVERAGE, MEDIAN and STDEV) for the Maximum, Minimum, and Average columns and format these results to two decimal places.

[pic]

Next create a 2-D XY (Scatter) chart that shows the Maximum, Minimum, and Average versus Day all on one graph. The Chart sub-type should be Scatter with data points connected by lines. Click on the Series tab and press the Add button. There should be three Series. The X Values for each series is the Day column. The Y Values will be the Maximum column for Series 1, the Minimum column for Series 2, and the Average column for Series 3. The graph should have a title, the x axis should be labeled, and each series should have a Name to identify Maximum, Minimum and Average on the graph.

[pic]

Right click on objects such as the X and Y axes and the titles to adjust the scale, the size and types of fonts, colors, etc. Generate a clear and understandable graph similar to the one shown below. Insert the chart into the spreadsheet and print the result.

[pic]

II. Generating Linear Models in Excel: (15 points)

Given a set of  X, Y values where Y is the dependent variable and X is the independent variable, the "best" straight line (which Excel calls a "Trend Line) through the data can be determined by the method of "least squares". This linear modeling of the relationship between X and Y is calculated by the equation Y = m*X+b , where m is the slope of the line and b is the y intercept. In Excel this least squares or "regression" slope can be calculated with the SLOPE( ) function. The SLOPE function's input menu requires the cell addresses of the Known_y's and the Known_x's. These may be entered directly, or if you click on the "little spreadsheet" icon you will return to the spreadsheet where you can select the appropriate cells. After selecting the correct input, click on the OK button to calculate the slope. The regression y intercept is calculated in a similar fashion with the INTERCEPT( ) function.

[pic]

To calculate the estimated Y values based on the linear model copy and paste the formula Y=m*X+b as shown below. Note: the use of absolute addressing for the cells containing the slope and y intercept.

[pic]

Finally, a 2-D XY (Scatter) plot of the data plus the linear model can be constructed as shown below. The chart sub-type chosen was Scatter with no connecting lines or curves between data points. Then the series option was used to plot both the (X, Y) data pairs. Right click on a (X, Y) data point and add a "Trend line" with the options to display both the regression equation and R2. R2 is the fraction of Y that a linear equation in X explains, it is always between 0 and 1. To change the background color of the plot right click within the plot and select Format Plot Area. To insert grid lines right click within the plot and select Chart Options. The chart shown below was modified from what Excel originally generated by various re-sizing and "much" right clicking on its components.

[pic]

Excel Assignment 3: (20 points)

Below is data giving the yearly energy consumption in millions of BTU's and the area in thousands of square feet of building shell for 22 warehouses all subject to the same climatic conditions.

 

|Shell Area (Kft2) |MBTU/Year |

|30.00 |3,870 |

|13.53 |1,371 |

|26.06 |2,422 |

|6.36 |672 |

|4.58 |233 |

|24.68 |219 |

|2.62 |354 |

|23.35 |3,135 |

|18.77 |1,470 |

|12.22 |1,408 |

|25.49 |2,201 |

|23.68 |2,680 |

|5.65 |337 |

|8.00 |568 |

|6.15 |555 |

|2.66 |239 |

|19.24 |2,629 |

|10.70 |1,102 |

|9.13 |424 |

|6.51 |545 |

|13.53 |1,691 |

|18.86 |1,870 |

A. Enter the data as two columns in an Excel spreadsheet. Consider the column of Shell Areas to be X and the Energy Consumption to be Y. In the cell beneath each column calculate the average value of each variable in fixed digit format with 1 decimal place. Calculate the regression slope and regression y intercept for this data.

B. Create a third column by applying the linear model. That is, for each value of shell area calculate what the linear model predicts for the energy consumption. In the cell beneath this column calculate the average of the predicted energy consumption in fixed digit format with 1 decimal place.

C. Generate an 2-D XY (Scatter) graph of Energy Consumption versus Shell Area. Give the graph an appropriate title and label the axes. Plot the trend line on the same graph and have Excel display both the equation and R2 .

D. Insert your graph in the spreadsheet and print out the results.

E. In the spread sheet compose answers to the following questions, print the spreadsheet.

How does the average predicted energy consumption compare to the average actual energy consumption?

How well does the linear model fit the data?

What data point deviates the most from the linear model?

Estimate the yearly energy consumption of a building whose shell area is 17,500 square feet. Explain how you obtained this estimate.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download