CSE200 WI09 KREEVES                                  QUIZ#3                              SEAT# ____________

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

 

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

 

Instructions:

·         Filling in name/seat#/lab information 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 (see above bullet).

·         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.

 

NOTE: BE SURE TO TURN IN BOTH PAGES OR YOUR TEST WILL NOT BE GRADED :O)

 

CSE200 WI09 KREEVES                                  QUIZ#3                              SEAT# ____________

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

 

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

 

1.      (7 pts) Write an Excel formula in cell scoop!E3, which can be copied down and across to cell scoop!G10, to determine the number of calories for the sugar cone type for the raspberry chip order. Notice that a zero will be the result if the order did not use that cone type. You must assume that the order of the cone types will be the same in scoop!E3:G3 and cone!B1:D1 even when additional cone types are added.

 

=IF($D3=E$2,cone!B$2,0)

=IF(($D3<>E$2,0,cone!B$2)

=IF($D3=E$2,HLOOKUP($D3,cone!$B$1:$D$2,2,FALSE),0) if did not assume same order

No optional $

 

 

2.      (7 pts) Using a reference function and the data on the CONE worksheet, write an Excel formula in cell scoop!H3, which can be copied down to cell scoop!H10, to determine the number of calories for the chosen cone type (given in column D) on the raspberry chip order.

 

=HLOOKUP(D3,cone!B$1:D$2,2,FALSE)

Optional $ on column

 

3.      (7 pts) Write an equivalent nested IF solution for the above problem. Be sure to use cell references whenever possible.

 

=IF(D3=CONE!B$1,CONE!B$2,IF(D3=CONE!C$1,CONE!C$2,CONE!D$2))

=IF(D3=CONE!B$1,CONE!B$2,IF(D3=CONE!D$1,CONE!D$2,CONE!C$2))

=IF(D3=CONE!C$1,CONE!C$2,IF(D3=CONE!D$1,CONE!D$2,CONE!B$2))

=IF(D3=CONE!C$1,CONE!C$2,IF(D3=CONE!B$1,CONE!B$2,CONE!D$2))

=IF(D3=CONE!D$1,CONE!D$2,IF(D3=CONE!B$1,CONE!B$2,CONE!C$2))

=IF(D3=CONE!D$1,CONE!D$2,IF(D3=CONE!C$1,CONE!C$2,CONE!B$2))

Cone!B$1:D$1 is the same as E$2:G$2

Optional $ on column

 

 

4.      (7 pts) There is another way to determine, in more detail, what the health factor is for the raspberry chip order. It’s based on total calories, which you need to determine (scoop calories plus cone calories), and is specified on the HEALTH worksheet. The data on the HEALTH worksheet says that any total calorie order less than 250 calories is considered to be an “excellent” healthy order; total calories more than 250 but less than 325 calories is considered to be a “good” healthy order; etc. Write an Excel formula in cell scoop!I3, which can be copied down to cell scoop!I10, to determine the total calorie health description for the raspberry chip order.

 

=VLOOKUP(C3+H3,health!A$2:B$6,2,TRUE)

No quotes around TRUE value

Optional TRUE value

Optional $ on column

 

 

5.      (6 pts)  Originally, I had set up the data on the HEALTH worksheet to look like the given two columns. Give two reasons why was this not a good idea?

 

No zero value so anything less than 250 gives an error

Excellent is associated with >=250 and less than 325 instead of >=0 and <250

 

 

calories

health

 250

excellent

325

good

500

fair

750

not good

900

poor

 

 

6.      (7 pts) You have taken a 5-year loan for $100,000 at 10% APR compounded monthly to open your own ice cream parlor restaurant. You estimate your income to be $2500/month (i.e. these are your company earnings which are considered incoming cash flow). Considering that your monthly bank loan payment includes all outgoing costs, write an Excel formula to determine how much profit you will make each year.  NOTE: Profit is incoming cash, in this case $2500/month, minus the outgoing costs for the payment.

 

=(2500+PMT(10%/12,5*12,100000))*12

No comma allowed for 2,500 or 100,000

 

7.      (7 pts) Instead of getting the loan as in the above problem and starting a new restaurant, you decide to keep your current job and invest $10,000 a year at 10% apr compounded quarterly for 5 years. Write an Excel formula to determine how much you have saved after 5 years in an effort to buy the restaurant using your investment instead of getting a loan.

 

=FV(10%/4,5*4,-10000/4)

No comma allowed for 10,000

 

 

                                                                                                                   SCORE ___________/50