Worksheet 1 (15 points) ≠ name it ≥Car Loan≤

Create an Excel spreadsheet to compare two different loan options for a new car. Youπve negotiated a price of $18,700.00 for the new car, but now youπre deciding between a 3-year loan with a 5% interest rate and a 5-year loan with a 4% interest rate. Your spreadsheet should allow the user to enter in two values for each of the amount of the loan, the interest rate and the length of the loan then you should calculate the monthly payment and the total interest youπll pay for both loan options. You want to show both loans side by side in order to decide which one is better (costs you less). It is not necessary to create an amortization table (or payment schedule) to find these values. If you canπt figure out how to calculate the values without using a payment schedule, then you may create an amortization table for each loan.

Make sure the relevant values are clearly labelled and highlighted. Be sure to place everything in one worksheet. Also make sure you reference cells so that I can perform a what-if analysis.

Worksheet 1 ≠ name it ≥Algebraic Equation≤

We know from algebra that an equation of the form: has two solutions based on the quadratic formula: . Assuming the value for A will not be zero, you are to design a worksheet that will allow the user the ability to enter values for A,B, and C into cells and your worksheet calculates both values of (using two separate cells properly labeled). If is less than 0, place the word ≥complex≤ in the solution cell; otherwise, place the answer. If A=1, B=-5, and C=6, the two answers are {2,3} or {3,2} depending on which solution for x you print out first.

Worksheet 2 ≠ name it ≥Baseball≤

For this worksheet you will need to use the data contained in the file ≥cincinnati.txt≤ located in the CS130 Pub folder. Copy this file into your folder before opening it. Import the data into Excel. Ignore the characters CIN NL WEST. After that, each row contains the following, season of play in year, home game attendance, runs scored, runs allowed, wins, losses and number of games behind division leader.

Part I: Import the data into Excel, format it and label all the columns.

Part II: Add a title at the top of the worksheet with the text ≥Cincinnati Reds≤. The title must be centred and cut across all the columns.

Part III: Add a subtitle with the text ≥1969 Through 1992 Season Data≤. Again this should be centred and cut across all columns.

Part IV: Insert a line chart into your worksheet tracking the runs scored and runs allowed throughout the period. The x-axis should represent the season and the y-axis should represent the number of runs. Label everything on your chart and include a legend.

Part V: Add a column to the right of the ≥runs allowed≤ column. This column should print an asterisk whenever the difference between the runs allowed and the runs scored is greater than 100. Hint: you will need to use the absolute mathematical function.

Part VI: Turn on the auto format filter and hide all seasons except those where the difference between the runs allowed and the runs scored is greater than 100.

Worksheet 3 ≠ name it ≥Investment≤

On January 1, 2004 you received a $7,000 loan at 12% annual interest rate (compounded monthly) from the bank for a period of four years. Beginning February 1, you must begin to pay the bank back their money. Each payment is due on the first of each month until the loan is paid off. Devise a worksheet with properly labeled column headings that shows this situation. The payment is fixed over the course of the year and should zero out at the end of the 48^th month. Use the payment function to calculate the fixed payment. As column headings, use Beginning Balance, Fixed Payment, Interest, Ending Balance.

QUESTIONS

QUESTION ONE:

(A) Using Excel, create a brief table of five people (either real or fictitious) and include for each of these people their

(a) first name

(b) last name

(d) three adjectives that describe this person

Save your file as YourusernameDataHw1.

(B) Create a brief form letter in which you describe a product that you have recently invented (making certain that you leave THREE spaces to incorporate the adjectives that you used to describe each of your FIVE people). Save this file as YourusernameMainHw1.

(C) Using the MAIL MERGE function in MS WORD, merge your data source document with your main document. Save the resulting file as YourusernameLettersHw1.

QUESTION TWO:

Using the file CANDYBARS.XLS for each of the 13 column entries beginning

with CALORIES and ending with IRON, compute each of the following statistical values: AVERAGE, MAX, MIN, MEDIAN, and STDEV. Save the resulting file as YourusernameCandyHw1.

QUESTION TWO:

Suppose that the function f(t)=25+t*exp(-t/20)represents the number of bacteria in a culture at time t. Using the formulas in EXCEL,

(A) Construct a worksheet that shows the values of this function from t=0 to t=100 (in steps of 10) and

(B) Create a graph IN THE SAME SPREADSHEET that illustrates graphically the values you found in part (A).

(C) Please experiment with the format of this worksheet by changing the font, colors, etc within this worksheet. Save this file as YourlastnameBacteriaHw2.

QUESTION THREE:

Suppose you, as the biologist for campus, have been tracking the squirrels that invade the campus each night and have recorded their numbers over the course of eight years. Please fit a LINEAR function to the following data, which gives the number of distinct squirrels

seen each year on campus.

YEAR SQUIRRELS

1992 145

1993 144

1994 134

1995 103

1996 70

1997 45

1998 32

1999 22

2000 10

2001 15

Cut and paste the resulting function and graph into an MS WORD document entitled your last name SquirrelsHw2, and within this document use the regression coefficient to comment on the accuracy of the regression line.

QUESTION TWO

Copy the file WesternStatesData.xls from the CS130pub folder. You are to calculate the sum of the number of crimes in each of the states.

a) Calculate these sums using IF (as we did in the example in class). Highlight the important cells using formatting. Also, your formulas MUST include mixed references in order to receive full credit. Name this file YourusernameQ2aHw3.

b) Calculate these sums using SUMIF (as we did in the example in class). Highlight important cells using formatting. Also, your formulas must include absolute and/or relative references so that they can be copied. Name this file YourusernameQ2bHw3.