CSE200 SP10 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 out the correct seat# and lab section on both the test and the answer sheet is worth 2 points.

· Turn off all beepers, cell phones and anything else that you can turn off that might make noise.

· Put away all books, papers, calculators, cell phones and music devices.  There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

· When time has run out you will be told to put all pens/pencils down.  There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

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

· Please stay in the answer box for each question.

·     Use cell references whenever possible and appropriate.

·     Don’t use a $ if NOT copying.

· Do NOT put quotes around Boolean values.

·     Only use the functions given.

· Do not use unnecessary functions, for example =average(sum(a1:a5)). If you use an extra function that is not needed (the “average” function in the given example), it is assumed that you thought it was necessary thus will be marked incorrect.

· Caveat/Reminder to above: Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.

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

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

·     Do NOT use a nested IF function when a reference function will work since the reference function is a better more updatable less complex solution.

 WORKSHEET DESCRIPTIONS

The Excel-lent summer job options file has all the summer job opportunities that you are considering as well as calculations that create new information to help you determine the best job from the list.

Q3jobs –The input data is given for this worksheet in the range A4:G11 and F2. For each job, the company, city, state, position, summer and yearly salaries, as well as benefit options are given; the benefits column contains Boolean values where the TRUE means that you will receive some kind of benefits at that job. If benefits are given, the amount of benefit given is in cell F2. The remaining information will be determined in the problems given below.

Q3position – This worksheet gives a choice option for each type of possible summer job position. The “other” option/position is given for updatability purposes.

CSE200 SP10 KREEVES           ANSWER SHEET QUIZ#3            SEAT# ____________

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

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

Q#	PTS	MINUS	QUESTION and ANSWER
1	6		Write an Excel formula in cell Q3jobs!H4, which can be copied down to cell Q3jobs!H11, to determine the summer salary with benefits, if benefits are applicable, to the job at longhorn. Notice that if no benefits are given, the summer salary with benefits is the same as the given summer salary value. =IF(F4,F$2,0)+E4 Optional $ on column
2	8		Write an Excel formula in cell Q3jobs!I4, which can be copied down to cell Q3jobs!I11, to determine if the yearly salary at longhorn is a low, medium or top choice option. The choice type is determined by the following: · The job is a “top” choice if the yearly salary is at least $30,000 · The job is a “low” choice if the yearly salary is less than $25,000 · Otherwise, the choice is “medium” =IF(G4>=30000,"top",IF(G4>=25000,"medium","low")) =IF(G4>=30000,"top",IF(G4<25000,”low”,"medium")) =IF(G4<25000,"low",IF(G4<30000,"medium","top")) =IF(G4<25000,"low",IF(G4>=30000,"top”,"medium")) Optional $ on column Cannot use AND so not other optional solutions are viable
3	10		Solve the above problem using a reference function. First, you will need to fill out the given SALARY worksheet cell references (given below), then write the function necessary to answer the above question. =HLOOKUP(G4,salary!B$1:D$2,2,TRUE) Optional $ on column 4^th argument can default to nothing i.e. okay if 4^th argument not there

4	8	There is another, more detailed, choice type option based on the job position as seen on the Q3position worksheet. Write an Excel formula in cell Q3jobs!J4, which can be copied down to cell Q3jobs!J11, to determine the choice type for the longhorn job position given the values on the Q3position worksheet. =VLOOKUP(D4,q3position!A$2:B$6,2,FALSE) Optional $ on column
5	8	You decide that you can afford a car that costs the same as your yearly salary. Write an Excel formula in cell Q3jobs!K4, which can be copied down to cell Q3jobs!K11, to determine the monthly car payment for a 5-year loan that you received from the bank at 10% annual percentage rate compounded quarter based on the yearly salary you earn from longhorn. =PMT(10%/4, 5*4,G4)/3 Optional $ on column Optional 4^th and 5^th arguments of zero
6	8	I want to buy a $20,000 car and have it paid off in 3 years. However, I can only afford a payment of $400 per month. This will not pay off the car in a 3 year period. I want to know if I can invest the money instead, at 10% compounded monthly, and be able to afford the $20,000 car in three years. Write an Excel formula (not shown) to determine (TRUE/FALSE), if investing the payment for 3 years (as stated originally) will give me enough money to buy the car. =FV(10%/12,3*12,-400)>=20000 no $ allowed Optional 4^th and 5^th arguments of zero

SCORE _____________/50