CSE200 WI08 KREEVES                                  QUIZ#3                       SEAT# ____________

NAME _______________________________________________        Lecture:  TR 1:30-3:18pm 

Lab section (check one):            _______ R 3:30-5:18pm             ________ F 1:30-3:18pm

Instructions:

· Filling in all the student information data above correctly is worth 2 points.

· Put away all books, papers, and calculators. 

· Turn off all beepers and cell phones.

· Read each question carefully and fill in the answer in the space provided.  Answers must be legible or they will be marked incorrect.  If there are multiple answers to choose from, please CIRCLE the correct answer. The question will not be graded at all if there are multiple answers to choose from.

· When time has run out you will be told to put all pens/pencils down. 

· Be sure to use values as determined by previous problems and do not use values from problems that have not yet been solved per the ordering of the questions.

· Use cell references whenever possible.

· Don’t use a $ if NOT copying

· Only use the functions given.

· Your answer should update correctly when additional input data is added to the problem or when input data is changed.

· All answers given should be “updatable” unless otherwise noted.

· Do not use extra IF structures. An example of using an extra IF structure is IF(cond,true,false) when just the condition would be the correct answer.

· Only use quotes when necessary – do NOT use quotes around Boolean values.

· Always use a reference functions versus a nested IF when 3 or more nested IFs might be used and a reference function will work using the given data.

The Worksheets:

SNEAKER – has input data in columns A thru E.

FINANCE – the years and annual interest rates are given in cells A3:A7 and B2:D2 respectively.

DISCOUNT – includes discount amounts based on the type of shoe.

WEAROUT – gives a level (A-E) based on the amount of miles it takes before a shoe will wear out and buying a new pair of shoes will be necessary. Wear level A wears out in less than 100 miles; wear level B lasts at least 100 miles but will wear out before 250 miles; wear level C lasts at least 250 miles but will wear out before 400 miles; wear level D lasts at least 400 miles but will wear out before 500 miles; and wear level E lasts at least 500 miles.

1. (9 pts) Write an Excel formula in cell sneaker!F3, which can be copied down to sneaker!F15, to determine the discount price for the nw1 nike shoe. The discount amounts are given on the discount worksheet based on the shoe type. NOTE: this formula should return the discount price for the shoe (answer in sneaker!f3 is $120), not just the discount amount (which for nw1 shoe is $30) or the discount percent (which is 20% for a walking shoe).

=(1-VLOOKUP(C3,discount!A$2:B$6,2,FALSE))*E3

=E3-VLOOKUP(C3,discount!A$2:B$6,2,FALSE)*E3

Optional $ on column

2. (7 pts) Write an Excel formula in cell sneaker!G3, which can be copied down to sneaker!G15, to determine the wear level for the nw1 nike shoe. The wear level is based on the miles to wear out found in D3 for the nw1 nike shoe. See the description above for more information about the information on the wearout worksheet.

=HLOOKUP(D3,wearout!B$1:F$2,2,TRUE)

Optional $ on column

Can default the 4^th argument TRUE

3. (7 pts) Write an Excel formula in cell wearout!B3, which can be copied across to wearout!F3, to determine the percent of shoes with wear level A on the list in the sneaker worksheet (i.e. as solved for above).

=COUNTIF(sneaker!$G3:$G15,B2)/COUNT(sneaker!$D3:$D15)

Optional $ on row

Can use any numeric column for the COUNT function (i.e. columns D, E or F)

4. (9 pts) Write an Excel formula in cell wearout!G3 to determine the wearout rating for the Excel Sneaker Shop based on the following:

· The wearout rating is “excellent” if the % wear level for D and E together is at least 30%

· The wearout rating is “good” if the % wear level for C is greater than the % wear level for B

· Otherwise, the wearout rating is “other”.

=IF(E3+F3>=30%,"excellent",IF(D3>C3,"good","other"))

Okay if used AND(E3>=30%,F3>=30%) for excellent option

Lots of options for ordering the 3 possible results

No $ allowed

5. (7 pts) The owner of the Excel Sneaker Shop mentioned that it took $150,000 to start up his company. You are interested in doing the same, but want to determine what the monthly payment will be based on several options from 1 to 5 years (see cells finance!A3:A7) and different interest rates (see cells finance!B2:D2). Write an Excel formula in cell finance!B3, which can be copied down and across to cell finance!D7, to determine the monthly payment for a one year loan (see cell finance!A3) at an annual interest rate of 10% (see cell finance!B2) compounded monthly.

=PMT(B$2/12,$A3*12,150000)

No extra $ allowed

6. (9 Pts) Write an Excel formula in cell finance!B10, which can be copied down and across to cell finance!D14, to determine how long it will take in years to obtain enough money to start your own sneaker shop (i.e. for you to accumulate $150,000) if you invest the monthly payment given in cell finance!B3 at an annual percentage rate of 15% (see cell finance!A9) compounded quarterly.

=NPER($A$9/4,B3*3,0,150000)/4

No extra $ allowed

Okay if leave out the zero argument but must have two commas present

SCORE __________/50