CSE200 SP09
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 the correct name, seat# and lab section on both the test and the answer sheets is worth 2 points each.
· 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.
· Do NOT put quotes around Boolean values.
· Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.
· Your answer should update correctly when additional input data is added to the problem or when input data is changed (unless where specifically noted otherwise).
PROBLEM DESCRIPTION
You
have started up a company for lawn services which includes mowing and trimming
lawns as well as putting in plants and trees. Computerizing your business is
your next step in creating a smoothly run schedule and finance division. You
are mocking up some of your current data (just a portion of it) and have set up
the following worksheets:
LAWN
– On this worksheet, A4:G10 is given i.e. input data. The lawn size is given in
feet for both the length and the width. Column D is true if the customer wants
their lawn to be trimmed. The cost of trimming the lawn is 10% (as shown in D2)
of the cost of mowing the lawn (restated in the question, as well, when asked
to determine costs). The planting options are plants that the customer is
requesting to have put in their yard by your service. Each customer is allowed
to choose three plant options (as described on the plant worksheet). The
remaining information will be determined by answering the given problems.
PLANT
– On this worksheet, A2:E8 is given i.e. input data. The name of the plant, the
type of the plant (either a shrub or a tree), the cost of the plant, whether or
not it is a perennial type of plant (true means the yes, this plant is a
perennial), and it’s preferred planting location is listed. A perennial is a
plant that returns each year even though it might go dormant during the winter;
this in contrast to a plant that is an annual which means that it dies each
season needing to be replanted with something else the following season.
COST
– All of the data on this worksheet is given i.e. input data. It defines the
cost to mow the lawn based on the size in square yards. It costs $40 to mow a
lawn less than 500 square yards; it costs $55 to mow a lawn greater than or
equal to 500 and less than 800 square yards; it costs $60 to mow a lawn great than
or equal to 800 and less than 1,000 square yards; etc. There is a named range sizecost which is defined as
cost!$B$1:$M$2 which MUST be
used when accessing this range.
UNIT
– Conversion factors are given here. Although well known facts, you are to use
these cell references whenever necessary to show proficiency and knowledge in
using constant values that may actually change in the future.
(2 pts) Name: ___________________________ Lab: _______ 1:30p _______ 3:30p Seat#
_______
Answer Sheet CSE 200 – Midterm SP09 – TR
1:30-3:18p KReeves
|
1. (25 pts) Write an Excel formula in cell
lawn!H4, which can be copied down to cell lawn!H10, to determine the size of
smith’s lawn in square yards rounded to the nearest hundreds. |
|
|
2. (25 pts) Write an Excel
formula in cell lawn!I4, which can be copied down and across to cell
lawn!K10, to determine how many miles a person will walk while mowing smith’s
lawn with a 21 inch width mower. Notice that the 21 inch mower width value is
in cell lawn!I3 and should calculate the miles walked with different width
mowers as you copy across. |
|
|
3. (30 pts) Write an Excel formula in cell lawn!L4,
which can be copied down to cell lawn!L10, to determine the cost of mowing
the lawn (see the cost worksheet) as well as the cost of trimming, if needed.
The cost of trimming is 10% (as given in cell lawn!D2) of the mowing amount. |
|
|
4. (5 pts) In the above problem, if you use the other
option for the 4th argument of the reference function, what will the
result be, of only the reference function portion, for anderson’s cost and
WHY? |
|
|
5. (20 pts) Write an Excel formula in cell lawn!M4,
which can be copied down and across to cell lawn!O10, to determine the
planting costs for smith’s option 1 plant choice. The cost of each plant is
on the plant worksheet. |
|
|
6. (8 pts) Write an Excel formula in cell lawn!P4,
which can be copied down to cell lawn!P10, to determine the total cost for
mowing, trimming and planting. |
|
|
7. (12 pts) Write an Excel formula in cell lawn!Q4,
which can be copied down to cell lawn!Q10, to determine who is spending from
the least to the most money on their lawn at this point. |
|
|
8. (8 pts) The smith family does not want to spend
over $400 for total lawn service today. They want to update their total cost (lawn!P4)
to be no more than $400. Will they use a goal seek or a what-if to determine
the solution to this problem? |
|
|
9. (10 pts) Determine what type of chart is given here
and the exact range location (worksheet and cell references) of the data
shown.
|
Points
Deducted = ___________
(2 pts) Name: ___________________________ Lab: _______ 1:30p _______ 3:30p Seat#
_______
|
10. (12 pts) Write an Excel formula on the plant worksheet (not shown) to determine (true or false) if only trees (i.e. and not shrubs) are perennials. NOTE: column B will not change for this problem, but all the other data can, including column D, and your answer should still work. |
|
|
11. (10 pts) Write an Excel formula in cell plant!F2, which can be copied down and across to plant!H8, to determine (true or false) if the sps1 plant needs shade. |
|
|
12. (12 pts) Write an Excel formula in cell plant!F9, which can be copied across to cell plant!H9, to determine how many shade plants are on the list. |
|
|
13. (12 pts) Write an Excel formula in cell plant!F10, which can be copied across to cell plant!H10, to determine the percent of shade plants on the list. |
|
|
14. (12 points) Write an Excel formula on the plant worksheet (not shown) to determine (true or false) if there are an equal number of shade, partial and sun location plants on the list. NOTE: the result for the given data is FALSE. |
|
|
15. (18 pts) Write an Excel formula in cell plant!I2, which can be copied down to cell plant!I8, to determine the rating for the sps1 plant. The rating is based on the following: · If the plant is a perennial, then the rating is “best” · If the plant is a shrub and is either sun or shade, then the rating is “good” · Otherwise, the rating is “UGH”. |
|
|
16. (5 pts) What would be the result of the above problem for the sfs1 plant if the word “shrub” in cell plant!B5 were spelled incorrectly? |
|
|
17. (20 pts) You want to own your own nursery instead of buying plants wholesale and then selling it to your customers. You will need a $450,000 loan to startup your company with 8% annual percentage rate compounded quarterly and a balloon payment of $50,000 that you will pay at the end of the 5 year loan period. Write an Excel formula (not shown) to determine the monthly payments of your loan. |
Points
Deducted = ___________
Score ____________ / 250