CSE200 AU04
KREEVES MIDTERM
SEAT# ___________
NAME
____________________________________________________________________________
Lab Time (check one): _________
Instructions:
·
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.
·
Only one answer should be provided per question; if multiple answers are
given, a score of 0 will be given. You
may circle the formula to show which answer is your final answer.
·
Be sure to determine the “best” answer.
That is, if the worksheet cannot be updated with your answer and there
is another answer that works that can be updated automatically, you will have
points deducted.
·
Do not put extra worksheet names on your answers when the defaulting to
the current worksheet will work.
·
Do not put extra $ on your cells unless it is a cell/range that does not
move.
·
Whenever a TRUE or FALSE value is referenced, it is assumed to be a
boolean value, not a label.
·
When time has run out you will be told to put all pens/pencils down. Failure to do so will result in point penalization.
·
Use cell references and named ranges whenever possible.
·
We will only be grading the answer sheet!
If you have information on the question sheet, it will not be looked at
or used in grading the answer to the problem.
·
Be sure to put your name, lab and seat# on this page and the answer sheet
page.
·
You must turn in ALL of the pages of this exam!
PROBLEM DESCRIPTION
The
Frog Factory was started by F. R. Ogden who started raising frogs when he was
just a little toad. Frederic Richard
Ogden, affectionately known as “Froggy”, started with 8 frogs and wanted to
keep track of the costs of his hoppy habit as well as other detailed
information about each type of frog he collected. Although Froggy only started with 8 frogs, he
was smart enough to know that he didn’t want to set up his information multiple
times, so made sure that most of his data would be easy to update with
additional frog facts in the future.
The
first worksheet that Froggy set up was his input/output worksheet,
appropriately named FROGS. Although
Froggy understood that having both input and output data in one worksheet might
or might not be the best scenario, Froggy wasn’t taking CSE200 for credit so
didn’t have to worry about having his instructor deduct points for the “best
solution”! However, in order to help
keep the input and output data straight, Froggy chose a fill color of gray for
his input data. The input data includes:
¨
the name or type
of the frog (for those of you who are frog connoisseurs, i.e. experts, some of
these “frogs” are actually toads!)
¨
its length in
inches
¨
if it is
poisonous or not (TRUE for poisonous, FALSE for not poisonous)
¨
the type of
habitat (water, tree, or land)
¨
food preference
(crickets, bloodworms, or mice)
¨
amount of food
per week the frog eats (which is the number of food preferences it needs to eat
per week)
¨
the cost of
buying the frog
¨
the size of tank
area required (in square inches), and
¨
the amount of
vegetation the frog enjoys.
On
the UNITS worksheet, there are conversion factors (be sure to look at the
worksheet), as well as stationary data facts, such as costs for food and
vegetation. The data here that is not
gray will be explained in a later problem.
The
third and last worksheet that Froggy set up is called TANK. Tanks are glassed in enclosures with a
screened top that will contain each frog’s habitat, as well as the frog
itself! The cost of the tank depends on
the area in square inches which is specified in the input data for each frog.
RIBBETT-RIBBETT CROOOAK-CROAK! (this means GOOD LUCK in frog talk!)
Name: __________________________________ Lab: _______
|
Write
an Excel formula in cell FROGS!J5, which can be copied down, to determine if
a layer of stone will be needed at the bottom of the tank for the OFB
(Oriental Fire-Bellied) frog. A frog
needs stone at the bottom of their tanks if they have a water habitat. The result of the formula should be TRUE if
they need stone at the bottom of their tank and FALSE otherwise. |
|||
|
Write an Excel formula in cell FROGS!K5, which can be copied down, to
determine if a layer of dirt will be needed at the bottom of the OFB frog’s
tank. Only dirt or stone will be put
at the bottom of a frog tank, so if the bottom of the tank is not stone, then
it should be dirt. |
|||
|
Write an Excel formula in cell FROGS!L5, which can be copied down, to
determine how much it will cost to feed the OFB frog for a week. Notice that each frog has input data
designating the food type in column E as well as how many pieces of that food
item eaten per week in column F. Also,
UNITS!B5:B7 have the cost per piece of food type. You can assume, for this problem only,
that the type of food given to the frogs (those listed on the UNITS! worksheet)
will remain the same; no different or extra types of food will be added to the
list. |
|||
|
Write an Excel formula in cell FROGS!M5, which can be copied down, to
determine how much the tank will cost for the OFG frog. Remember that the tank area required in
square inches is an input value (column H) and the tank costs associated with
the tank area are given on the TANK worksheet. |
|||
|
If you were asked to use a named range in the solution to problem #4
above, called TANKINFO, what would be the definition of that range? Be very specific! |
|||
|
Write an Excel formula in cell FROGS!N5, which can be copied down and
across to FROGS!O12, to determine the cost, rounded to the nearest cent,
for adding 1-inch of stone to the bottom of the OFB frog’s tank. Notice that in FROGS!J3:K3 are the prices
for stone and dirt, respectively. This
price is in dollars per square foot
for a 1-inch depth of stone or dirt.
Remember the tank area is in square
inches! See the UNITS
worksheet for conversions. Also,
notice that some frogs use stone and some use dirt (columns J and K,
respectively), so $0.00 should be put in the cell if the frog does not use
that kind material in the bottom of their tank. |
|||
|
Write an Excel formula in cell FROGS!P5, which can be copied down, to
determine how much it will cost to put the correct amount of vegetation
(input data column I) in the OFB frog’s tank.
Remember that costs are found on the UNITS worksheet. |
|||
|
Write an Excel formula in cell FROGS!Q5, which can be copied down, to
determine the total cost of creating an adequate living environment for your
OFB frog for the first time. All the
costs in putting the tank together include the cost of: the frog, food for a week, the tank, stone or
dirt, and the vegetation. You are not
allowed to use any arithmetic operators in your answer for this problem! |
|||
|
I want to be able to write an Excel formula that will automatically
determine which frog is the longest length frog in inches. That is, I want a formula to return the
type/name of the frog that has the longest length. However, the worksheet is not set up
correctly to determine this solution.
Explain what update needs to be made to the FROGS worksheet, then
write an Excel formula, using that update, to determine the type/name of the
frog with the longest length. |
Points Deducted =
___________ OUT OF 125 Score
____________ / PAGE 1
Name: __________________________________ Lab: _______
|
Write an Excel formula in cell FROGS!B15 to determine the average
total cost of putting a frog in its new environment for the first time. |
||||||||||||||||||
|
Write an Excel formula in cell FROGS!D15 to determine if ALL of the land frogs are poisonous
and none of the other frogs with
different habitats (i.e. other than land) are poisonous. For this problem, you can assume that the
input data won’t change. |
||||||||||||||||||
|
Write an Excel formula in cell FROGS!F15 to determine if the total
cost of ALL the land frogs together cost
more than the total cost of ALL
the water and tree frogs put
together. |
||||||||||||||||||
|
Write an Excel formula in cell FROGS!H15 to determine what percent of
frogs on this list are poisonous. Be
sure to write a formula that will automatically update if more frogs are
added to the list! Also, the cell has
already been formatted using the % icon. |
||||||||||||||||||
|
Write an Excel formula in cell FROGS!J15 to determine how many
gallons of water is needed to fill the tank to 9 inches deep for the |
||||||||||||||||||
|
Froggy loves his frogs so much that he wants to set up a store of his
own called the Frog Factory. There are
a huge amount of costs in setting up a store for the first time, which Froggy
has estimated to be $50,000. If Froggy
obtains a loan at 6.5% APR compounded monthly, write an Excel formula in cell
FROGS!L15 to determine the monthly payment for Froggy to pay off the loan in
5 years. |
||||||||||||||||||
|
After determining the monthly payment in the above problem, Froggy
has decided that he probably won’t sell enough frogs and/or supplies to make
this monthly payment. Instead, Froggy
figures that he can afford to pay $600 per month. Given that the same $50,000 loan amount
applies as well as the same 6.5% APR compounded monthly rate, write an Excel
formula in cell FROGS!O15 to determine the length of time in years that it
will now take Froggy to pay off his loan. =NPER(0.065/12,-600,50000)/12 |
||||||||||||||||||
|
Given the graph on the page that the Excel worksheets are given,
determine the following: a. What type of chart is shown? b. What was the data range used? c. What frog has the highest percentage data
value listed? |
||||||||||||||||||
|
Fill in the blank for the following sentence with either Goal Seek or
What-If Analysis Goal Seek __________________________ allows us to determine an input value that
would be needed to get a given output. |
||||||||||||||||||
In answering this problem, you will fill in the format/precision
table (see below) for cells UNITS!B9:B12.
In cell UNITS!B9 is the conversion factor for liters/gallon. The actual value for this conversion is
3.78 liters/gallon, however, in cell UNITS!B9, I put =ROUND(3.78,0) which gives
the value of 4. I did this so that it
would (hopefully) be easier to check your answers by hand. In cell UNITS!B10, I put the actual value
3.78 and used the decrease decimal icon to get a value of 4 in the cell. What
is the format and precision of both of these cells (units!B9 and units!B10)?
In addition, I want to convert 5 gallons (UNITS!B13) to liters. The question is, which liters/gallon
conversion value do I want to use – UNITS!B9 or UNITS!B10? I want to do both and compare! To solve this problem, I put the formula
=B9*$B$13 in cell UNITS!B11 and copied it down to UNITS!B12. I decreased the decimal on both cells to
get a whole number. What is the format and precision of both
of these cells (units!B11 and units!B12)? |
Points Deducted = ___________ OUT OF 125 Score
____________ / PAGE 2