CSE200 WI10
KREEVES MIDTERM
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 your name, correct seat# and lab section on both answer sheets is worth 4 points.
· Put away all books, papers, calculators, cell phones, music devices, etc... you should only have a writing utensils at your seat. A deduction of 50 pts minimum, and possibly more at the discretion of the lecturer, will be given if any other materials (besides writing utensils) are present.
· Turn off all beepers, cell phones and anything else that you can turn off that might make noise.
· 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.
· Please stay in the answer box for each question i.e. do not write an essay for a short answer problem!
· 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 and appropriate.
· Do not use a $ if NOT copying.
· You are only allowed to use the given functions.
· Do NOT put quotes around Boolean values.
· 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 this example), it’s 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.
· All of the values are formatted as shown unless otherwise specified. That is, do NOT use a ROUND function unless you are specifically asked to round the value(s).
· 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.
· Your answer should update correctly when additional input data is added to the problem or when input data is changed – unless otherwise noted.
Worksheets
on other side ==========è
Name:
______________________________ Lab: _______ 1:30pm _______ 3:30pm Seat#
_______
PROBLEM DESCRIPTION
You
own a roller coaster park, which offers several different types of roller
coaster rides. You have decided to computerize some of the data about your
current rides (just a portion of it) and have set up the following worksheets:
RIDES – On this worksheet, A4:I17 is given as input data. Each roller coaster is defined here starting
with the name of the ride and the level (a value from 1 to 5 where 1 is an easy
ride and 5 is fast and furious) in columns A and B, respectively. The next 4
columns include TRUE/FALSE Boolean values that designate if the ride includes a
150 foot or more drop (column C), seats that are suspended from the track
(column D), if the ride includes at least one loop (column E) and if the ride
includes at least one corkscrew turn (column F). The next three columns (G, H,
and I) include, in order, the average speed of the ride in miles per hour, the
length of the ride in feet and the total number of riders on each “trip” or
train.
LEVELS – On this worksheet, only column A is input data. The data in this
column relates to the level information given on the RIDES worksheet in column
B (described above). It is important to notice that columns D thru G have the
exact same meaning and order as columns C thru F on the RIDES worksheet.
UNITS – Conversion factors are given here. Although some are well known
facts, you are to use these cell
references whenever necessary to show proficiency and knowledge in
using constant values that may need to change in the future.
SCRATCH:
Name:
______________________________ Lab:
_______ 1:30pm _______ 3:30pm Seat# _______
Answer Sheet CSE 200 – Midterm WI10 – TR 1:30-3:18pm
KReeves
|
1.
(15 pts) Write an Excel formula in cell rides!C18,
which can be copied across to rides!F18, to determine if ONLY level 5
coasters have a 150+ foot drop. Remember that for this problem only, you can
assume that the information in column B will not change, but all the other
data can change and your solution should still work. |
|||
|
2. (18
pts) Write an Excel formula in cell rides!J4, which can be copied down to
cell rides!J17, to determine how many seconds the top thrill dragster ride
will last for each person based on the speed in mph and length in feet given
for the ride. NOTE: Check out the conversion factors on the units worksheet. |
|||
|
3.
(6 pts) Determine the chart type and range used to create the given
graph.
Graph Type: Range: |
|||
|
4.
(20 pts) Write an Excel formula in cell rides!K4, which can be copied
down to rides!K17, to determine the number of riders that ride the top thrill
dragster per hour. Since we don’t have a half a person ;o) be sure to round
the value to the nearest whole number. |
|||
|
5.
(6 pts) Once the riders per
hour was determined above, I was curious to know if I was given the number of
riders per hour that I wanted the ride to service, how fast would the ride
have to go. Is this a What-If or Goal Seek problem? Explain why you chose
your answer. |
|||
|
6.
(12 pts) Write an Excel formula
in cell rides!L4, which can be copied down to rides!L17, to determine (true
or false) if the top thrill dragster gets a “cool” rating. The coaster gets a
TRUE “cool” rating for either of the following two criteria: (1) the coaster
has both a 150+ foot drop and a loop; OR (2) the coaster has both suspended seats
and a corkscrew turn. |
|||
|
7. (12 pts) Based on some
calculations needed at a later date, write an Excel formula in cell rides!M4,
which can be copied down and across to rides!P17, to determine if the top
thrill dragster has a 150+ foot drop by designating a 1 if there is a 150+
foot drop and a zero if there is not a 150+ foot drop. |
|||
|
8.
(22 pts) Write an Excel formula
in cell rides!Q4, which can be copied down to cell rides!Q17, to determine a
new level for the top thrill dragster based only on the speed of the ride.
The new criteria for the speed based level is the following: ·
The new level 1 is for average speeds less than 40 ·
The new level 2 is for average speeds greater than or
equal to 40 and less than 60 ·
The new level 3 is for average speeds greater than or
equal to 60 and less than 70 ·
The new level 4 is for average speeds greater than or
equal to 70 and less than 80 ·
The new level 5 is for average speeds greater than or
equal to 80 Using the below given worksheet
portion, fill in the cells so that you can use a reference function to solve
this problem. A named range called mphlevel has been defined
for speed!$B$1:$F$2 which MUST be used in your solution.
|
Name:
______________________________
Lab: _______ 1:30pm _______ 3:30pm Seat# _______
Answer Sheet CSE 200 – Midterm WI10 – TR
1:30-3:18pm KReeves
|
9.
(20 pts) Based on the average
speed in mph given in column G, determine the name of the roller coaster
average speed is the fastest. In order to complete the solution to this
problem, you will need to update the rides worksheet. Specify how you are
updating the worksheet (do not change anything that currently exists because
you don’t want to mess up any of the previous problems/solutions) and where,
then solve for the problem using a reference function. NOTE: the answer to
the problem with the given data is “top thrill dragster”. |
|
|
10.
(16 pts) Write an Excel formula in cell levels!B2, which can be copied
down to cell levels!B6, to determine the percent of rides that were
ORIGINALLY defined (i.e. using the input data specified on the rides worksheet)
to be level 1 rides. NOTE: Using the level from the input data on the levels
worksheet is explained in the worksheet description. |
|
|
11. (6 pts) Explain why the values
in the range levels!B2:B6 do not add up to 100%. |
|
|
13. (15 pts) Write an Excel formula in cell levels!D2,
which can be copied down and across to cell levels!G6, to determine the
number of level 1 rides that have a 150+ foot drop. |
|
|
14. (24 pts) Write an Excel formula
in cell levels!H2, which can be copied down to cell levels!H6, to determine
the rating for each level based on the following: ·
If the level has the most number of rides in the park,
then the rating is “best” ·
If the level has an above average number of rides in
the park, but not the best, then the rating is “above” ·
Otherwise, if the level has a below average number of
rides in the park, the rating is “below” |
|
|
15.
(6 pts) Determine a range of data that could be used for a pie chart
and explain what the chart would look like. |
|
|
16. (18 pts) The top thrill dragster
costs $25 million (yikes!) You will take a loan at 5% annual percentage rate
compounded quarterly and will take 10 years to pay the loan off. However, due
to some investments that will come due at the end of the loan period, you
have a $5 million balloon payment. Write an Excel formula to determine the
monthly payments you will be making on this loan. |
|
|
17. (18 pts) In a separate
calculation (i.e. forget about the previous problem)… The top thrill dragster
costs $25 million (yikes!) but investors have contributed funds to pay for
the roller coaster at the beginning
of this year for a total of $15 million. Given that the interest rate is 8%
annual percentage rate compounded monthly, determine how long in years until
the investment makes enough to pay for the top thrill dragster. |
Points
Deducted = ___________ Score
____________ / 250