CSE200 AU09
KREEVES MIDTERM
SEAT# ____________
NAME _______________________________________________ Lecture: TR 9:30-11:18am
Lab section (check one): _______ F 9:30-11:18am ________ F 11:30am-1: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.
Name:
______________________________ Lab: _______ 9:30am _______ 11:30am Seat#
_______
PROBLEM DESCRIPTION
You
have started up a company to create custom t-shirts which offers several
varieties and styles of t-shirt options. 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:
REQUESTS – On this worksheet, A4:I11 is given i.e. input data,
along with G2:I2 and M2:O2. Almost all
of the solutions will be given on this worksheet. The req# in column A are
requests from certain clients. The order# (column B) is the order that the request originates from. Notice that order
#1 has several different types of requests. In the given sample of data, there
are 4 requests for order #1. Each request also includes the gender of the given
shirt size as well as the quantity and level of detail (columns C thru F). The
level of detail is given to represent the complexity of the requested t-shirt
design. The last of the input data includes the type of design that should be
included (a TRUE value) on the t-shirt: screen print, air brush and/or tie-dye
and the amount of paint in ounces that each shirt will use in G2:I2
respectively. Notice that each request can include multiple design options. For
instance, request #1 has a tie-dye and a screen print option, so any
combination of options is viable as well as none of them (see request g)!
There are a few other important things to notice:
·
The order# on
this worksheet is related to the orders field on the ORDERS worksheet
·
The men/women and
size fields are related to the information on the COSTS worksheet
·
The level of
detail data is also designated on the LEVEL worksheet
·
The design
options – screen print, air brush and tie-dye – will always be in the same
order when used per column. That is, requests!G3:I3, requests!J3:L3,
requests!M3:O3, and level!C3:E3 column names will always be in the same order.
See the other related worksheets for more information.
ORDERS – On this worksheet, A2:A4 is given i.e. input data. These values are
related to the order# field on the REQUESTS worksheet.
COSTS – All of the data on this worksheet is given i.e. input data. This
data is related to the gender (men/women) and size fields on the REQUESTS
worksheet. Each cost value is the price of one
t-shirt for the given gender and size.
LEVEL – All of the data on this worksheet is given i.e. input data. There is
a ONE TIME basic overhead cost associated with the level of detail (i.e.
complexity) for the given t-shirt request. For detail levels 1-2, there is a
$150 overhead cost; for details levels 3, 4, and 5, there is a $165 overhead
cost; for detail levels 6 and 7, there is a $181.50 overhead cost; for detail
levels 8 and 9, there is a $199.65 overhead cost; and for detail levels greater
than or equal to 10, there is a $219.62 overhead cost. NOTE: The values given in
cells C2:E2 where used to generate the values in C4:E8, but will not be used,
and are not a factor, in determining any of the solutions that you will be
asked to give.
Remember
that the columns for screen print, air brush and tie-dye are in the exact same
order as the same set of 3 columns on the REQUESTS worksheet.
*****
VERY IMPORTANT *****
There is a named range called detail which is
defined as level!$A$4:$E$8 which MUST
be used when accessing this range.
*****
VERY IMPORTANT *****
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 need to change in the future.
SCRATCH:
Name:
______________________________ Lab: _______ 9:30am _______ 11:30am Seat#
_______
Answer Sheet CSE 200 – Midterm AU09 – TR
9:30-11:18am KReeves
|
1. (12 pts) Write an Excel formula in cell orders!B2,
which can be copied down to orders!B4, to determine the total number of
t-shirts on order #1. Notice the quantity per request is in the requests
worksheet column E. |
|||
|
2. (18 pts) Write an Excel formula in cell requests!G12,
which can be copied across to cell requests!I12, to determine if only
men’s t-shirt requests were screen
printed. Remember that for this problem only, the input data in column C will
not change, but all the other data can change and your solution should still
work. |
|||
|
3. (24 pts) Write an Excel
formula in cell requests!J4, which can be copied down and across to cell
requests!L11, to determine the number
of quarts of paint needed for the screen print t-shirt design option
for request a. Remember that the
ounces of paint needed for each
screen printed t-shirt in cell requests!G2. |
|||
|
4. (15 pts) Write an Excel
formula in cell requests!J12, which can be copied across to cell
requests!L12, to determine the total number of quarts of paint needed for all
of the screen print requests. Since you can’t order 30.47 quarts of paint,
which is what the values in the range requests!J4:J11 add up to, you actually
have to buy 31 quarts for the given screen print data. You will need to use a
new function to help solve this problem. The ROUNDUP function rounds a number
up, away from zero for the given position. Here is the definition of the
function: ROUNDUP(number, num_digits). The arguments work exactly the same way as the
ROUND function, however, the result of the ROUNDUP function rounds up if the
value to the right of the num_digits position is greater than zero; whereas
the ROUND function rounds down if the value to the right of the num_digits
position is less than 5, and rounds up if the value to the right of the
num_digits position is greater than or equal to 5. For example, if I round
30.47 to the nearest whole value using the ROUND function, it will round down
to 30; if I use the ROUNDUP function, the result will return 31. |
|||
|
5. (20 pts) Write an Excel formula in cell requests!M4,
which can be copied down and across to cell requests!O11, to determine the
one time screen print cost, which is based on the level of detail, for
request a. Notice that screen print is option 1 (requests!M2), air brush is
option 2 (requests!N2) and tie-dye is option 3 (requests!O2) and remember
that you are guaranteed that the order of these design options are the same
(i.e. the screen print, air brush and tie-dye columns will always be in the
same order). NOTE: don’t forget to use the named range. |
|||
|
6. (8 pts) In the above problem, what would happen if
the level of detail in the requests worksheet was allowed to be zero but the
values on the level worksheet were not changed? Explain what happens and why. |
|||
|
7. (10 pts) Determine the chart type and range used to create the
given graph.
Graph Type: Range: |
|||
|
8. (6 pts) Write an Excel formula in cell requests!M12,
which can be copied across to cell requests!O12, to determine the average
value for all of the one-time screen print costs. |
|||
|
9. (6 pts) The values in cells requests!M12:O12,
which are results of the previous problem, do not show a decimal portion on
any of the values. Explain why not. |
Name:
______________________________ Lab: _______ 9:30am _______ 11:30am Seat#
_______
Answer Sheet CSE 200 – Midterm AU09 –
TR 9:30-11:18am KReeves
|
10. (25 pts) Write an Excel
formula in cell requests!P4, which can be copied down to requests!P11, to
determine the cost of a just one single t-shirt for request a NOT including
any additional costs (see below problem which includes the additional costs). |
|
|
11. (15 pts) Write an Excel formula in cell requests!Q4, which can be
copied down to requests!Q11, to calculate the actual/real cost per t-shirt based not only on the t-shirt
cost itself, but also the one-time cost options. |
|
|
12. (12 pts) Write an Excel formula in cell requests!R4, which can be copied down and across to cell requests!S11, to determine the ordering, from lowest to highest, of the size only cost for a request a t-shirt with respect the size only cost of the rest of the t-shirts. |
|
|
13. (8 pts) Write an Excel formula in cell requests!T4, which can be copied down to cell requests!T11, to determine (true or false) if the size only t-shirt rank and the actual t-shirt rank are different. That is, the solution should be TRUE if the two ranks for request a are not equal. |
|
|
14. (15 pts) Write an Excel
formula in the request worksheet (not shown) to determine if at least 50% of
the requests have a different rank. The answer for the given data is TRUE but
must update correctly if additional requests are added. |
|
|
15. (12 points) Write an Excel formula in cell requests!U4, which can be copied down to cell requests!U11, to determine (true or false) if request a is profitable. A request is profitable if the quantity is more than 100 or the actual cost per t-shirt is between $7 and $10 (inclusive). |
|
|
16. (20 pts) Write an Excel formula in cell requests!V4, which can be copied down to cell requests!V11, to determine the priority based on the following: · If the request is profitable, then the priority is “high” · If the request is not profitable and none of the design options are being used (no screen print, air brush or tie-dye options given), then the priority is “low”. · Otherwise, the priority is “medium” (if not profitable and at least one design option was used). |
|
|
17. (20 pts) You are asking the bank for a loan of $400,000 to pay for your company startup costs. In addition, you are willing to make quarterly payments of $15,000 and you have $100,000 worth of investments that you can use as a balloon payment. The bank is offering you 12% annual percentage rate compounded monthly. Write an Excel formula in the requests worksheet (not shown) to determine the number of years it will take to pay off the loan. NOTE: the answer is 11.6 years. |
|
|
18. (12 pts) From the above problem, a few things have changed… you have no balloon payment, you are compounding the rate quarterly (not monthly) and you are making your payment at the beginning of the period. The loan amount of $400,000 is the same as is the $15,000 quarterly payments. Write an Excel formula in the requests worksheet (not shown) to determine the number of years it will take to pay off the loan. NOTE: the answer is 12.7 years. |
Points
Deducted = ___________
Score ____________ / 250