CSE200 AU07 KREEVES QUIZ#3 SEAT# ____________
NAME _______________________________________________ Lecture: MW 1:30-3:18pm
Lab section (check one): _______ W 3:30-5:18pm ________ R 1:30-3:18pm
Instructions:
· Filling in all the above data is worth 1 point.
· 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.
· Don’t put quotes around Boolean values.
· Don’t use an IF structure when it is not necessary.
· Use a reference function, if possible, when using more than two nested IF functions.
PROBLEM DESCRIPTON:
VISITORS – This worksheet has input data in columns A-E. The other gray area (column I) is given data to help with the problem, but is not solved for until the below problems specify that fact. The input data includes the name of the person traveling, an abbreviation of the park they are visiting (see the parks worksheet below for the description), the ticket type of either “senior”, “child” or “regular” as well as the number of days spent at the park and the cost of the hotel per night. In columns J and K, rows 1-3 are interest rate values (J1 and K1), compounded values where 12 stands for compounded monthly and 4 stands for compounded quarterly (J2 and K2), plus another set of values to be described in a problem given below (J3 and K3). Also, the answers to the problems given below are put on this worksheet.
PARKS – This worksheet is all input data. It designates the park abbreviation and description as well as the state, distance in miles to travel to get there as well as the regular ticket cost per day.
TAX – This worksheet is all given data and specifies the tax on the cost of the vacation based on the number of days spent at the park. That is, the number of days given in row represents a range of values where a 20% tax is given for less than 3 days at the park, 15% tax is given for less than give but more than 2 days at the park, 11% tax is given for less than 8 days at the park but more than 4 days, etc.
1. (10 pts) Write an Excel formula in cell visitors!F5, which can be copied down to cell visitors!F12, to determine the discount amount given for person A based on the ticket type. The ticket type is specified in column C and the discounts are as follows:
· A 25% discount if given to children i.e. the “child” ticket type
· A 15% discount is given to seniors i.e. the “senior” ticket type
· No discount is given for a regular priced ticket i.e. “regular” ticket type
=IF(C5="senior",15%,IF(C5="child",25%,0%))
=IF(C5="senior",15%,IF(C5="regular",0%,25%))
=IF(C5="child",25%,IF(C5="senior",15%,0%))
=IF(C5="child",25%,IF(C5="regular",0%,
15%))
=IF(C5="regular",0%,IF(C5="senior",15%,25%))
=IF(C5="regular",0%,IF(C5="child",25%,15%))
Okay if missing the % on the zero – will look funny but
precision is the same
Okay if using decimal values instead of percent
Optional $ on column
2. (10 pts) Write an Excel formula in cell visitors!G5, which can be copied down to cell visitors!G12, to determine the ticket cost per day for person A based on the discount given, if any, and the park they are visiting. That is, apply the discount determined above, if any, and produce the actual cost of the ticket for each person based on the regular price given on the parks worksheet.
=VLOOKUP(B5,parks!A$2:E$7,5,FALSE)*(1-F5)
=VLOOKUP(B5,parks!A$2:E$7,5,FALSE)- VLOOKUP(B5,parks!A$2:E$7,5,FALSE)*F5
Optional $ on column
No quotes allowed around the FALSE
3. (8 pts) Write an Excel formula in cell visitors!H5, which can be copied down to cell visitors!H12, to determine the tax amount for person A. The tax information is given on the tax worksheet and a description of this data is given above.
=HLOOKUP(D5,tax!B$1:F$2,2)
Optional $ on column
Optional TRUE as 4th argument
No quotes allowed around the TRUE (if given)
FYI: At this point, the total trip cost (including tickets, hotel and appropriate tax costs for the given number of vacation days) is calculated for you.
4. (3,12 pts) To pay for the trip which will take 2 years to pay off, you can take out a loan at 9.5% annual interest rate compounded monthly (see cells J1 and J2) or charge the trip cost on your credit card at 10% annual interest rate compounded quarterly. Write an Excel formula in cell visitors!J5, which can be copied down and across to cell visitors!K12, to determine the monthly payment for person A to take a loan. Notice that cell K3 is blacked out. You need a value here to be used in the formula you will be writing to correctly convert the quarterly payment to a monthly payment for the charge option.
a. What value needs to go in cell K3? ________________ 3
b. Write the formula for the question:
=PMT(J$1/J$2,2*J$2,$I5)/J$3
No extra $ allowed
5. (6 pts) Write an Excel formula in cell visitors!K14 (not shown) to determine, “loan” or “charge”, which is the better financial decision (i.e. a smaller payment) to make for the above problem. Remember that the values in J1 and K1 can change and your answer should still work. NOTE: For this data, the answer is “loan”.
=IF(K5>J5,"charge","loan") okay if >=
=IF(K5<J5,"loan","charge") okay if <=
No $ allowed
Watch carefully for relational operator… the values given in
the worksheet are negative!
Can use any row 5-12
Can sum each side of the condition given for argument 1, etc.
SCORE _____________/50