# CALVIN by jizhen1947

VIEWS: 5 PAGES: 5

• pg 1
```									                     ISys201 Winter 2010 Practice Excel Exam 2 Key 3/27/10 dd

All the data for this practice exam can be found in the PracticeExcelExam2.xls file.
Suggested times for completion are included. See if you can complete this in time. Save the
double- and triple-checking for the end.

(1 minutes)Use the worksheet labeled “TaxRate” to answer question 1.
1. (2pts) Enter the VLOOKUP formula you need in D8 to retrieve the tax rate. Copy this formula
down the D column.
a. Please write the formula for D10 here: D10=VLOOKUP(B10,\$B\$3:\$C\$5,2,FALSE)
b. What is the resulting value displayed in F11? \$63.45

(3 minutes)Use the worksheet labeled “Sales” to answer questions 2-3.
2. (2pts) In H13, enter the COUNTIF formula you need to count the instances of sales by Jane.
a. Please write the formula here: B21=COUNTIF(\$C\$3:\$C\$18,A21)
b. What is the resulting value displayed in B21? 6

3.   (2pts) In H15, enter the SUMIF formula you need to get the total sales amount by Tim.
a. Please write the formula here: C23=SUMIF(\$C\$3:\$C\$18,A23,\$B\$3:\$B\$18)
b. What is the resulting value displayed in C23? \$135.00
c.
(2 minutes)Use the worksheet labeled “Traffic” to answer question 4.
4. (2pts) When approaching an intersection in your car, the light turns yellow. Write an if
statement to determine whether to “stop”, ”keep driving”, or ”speed up”. If you are less than
100 feet from the intersection, you will keep driving. If you are between 100 and 149 feet, you
will speed up, and if you are 150 feet or more, you will stop. Copy this formula down the B
column.
a. Please write the formula here: B2= IF(A2<100,"Keep Driving",IF(A2<150,"Speed
Up","Stop"))
b. What is the resulting value displayed in B7? Speed Up

(2 minutes)Use the worksheet labeled “Loan” to answer question 5.
5. (4pts) If you want to have a monthly house payment of \$1400, and you are set at a 30 year
mortgage with a 6.5% rate, what is the loan principle you can afford (ignoring taxes, fees,
closing costs, etc.).
a. What is your result after running Goal Seek? \$221,495.15
b. What is the “Set cell”? D5
c. What is the “To value”? -1400
d. What is the “By changing value”? D1
(7 minutes)Use the worksheet labeled “CandyBars” to answer question 6.
6. (11 pts) Assume you are responsible for determining how many of four types of candy bars
(Twix, Milky Way, Snickers, and Nut Rolls) your manufacturing facility should make. The
demand for each type of candy bar is given on the spreadsheet. You must not exceed the
demand for any candy bar. You must schedule manufacturing make as much profit as possible
(given the price and cost information provided). Additionally, only have 2500 units of
chocolate and caramel available and 2000 units of peanuts available. We can only make whole
candy bars (meaning set your changing cells to integer).

a. What are the two other constraints (besides integer) as they appear in Solver?
\$C\$2:\$F\$2<=\$C\$3:\$F\$3
\$C\$13:\$C\$15<=\$D\$13:\$D\$15
b. What are the changing cells? \$C\$2:\$F\$2
c. After running Solver (don‟t forget to select Assume Linear Model and Assume Non-
Negative), how many Snickers do we need? 700
d. What is the total profit?     \$919.00

(10 minutes) Use the worksheet labeled “Sales Data” to answer questions 7-10.
7. (2pts) Which salesperson sold the fewest items? Ray had 41

8. (2pts) In the Salt Lake City store, what percent of total sales were from binoculars? 3.94%

9. (4pts) Looking only at sales made by Jeff, which item brought in the most total revenue, and
what was that revenue? Rifles, for a total of \$2475

10. (2pts) For the Provo store what percent of sales (from all the months) was attributed to Night
Vision Goggles sold in November? 5.5969%

11. (9 minutes) Use the worksheet labeled “Customer Satisfaction Data” to answer question 11.
For customer satisfaction, a score of 1 is the lowest rating and a score of 5 is the highest rating.
a. (4pts) Who had the most “5”s and how many did they have? Sean, 12

b. (2pts) What percent of the total “Storewide Sales” approaches (count function)
happened in the Provo store? 37.78%

c. (3pts) For the Orem store, which approach has the lowest average customer satisfaction
rating, and what is that rating? Storewide sale at 3.071
(17 minutes) Use the worksheet labeled “Bookseller” for question 12.          (r 6/27/09dd)

12. (20 pts) An online bookseller would like to automate the shipping fee calculation process. The
shipping fee for 1-3 books is \$5, the fee for 4-7 books is \$7.50, and the fee for 8 or more books is \$9.
There is a „rush” option that guarantees 2-day shipping for an extra \$10 beyond the normal shipping
charges. Because they want to build for the future, they don‟t want to use the current numbers listed
above; instead, they would prefer to use the variables shown on the flowchart that can be drawn from
spreadsheet cells. Write the code for a program based on the narrative, screenshot, and flowchart
provided.

You will need to create
it to the cell.

You will need to create
this button and assign
the macro to it.

Example VBA Syntax
Dim V1, V2 as Double
B1 = Range(“D5”).Value
Range(“D5”).Value = ZZ
Bookseller Shipping
Rev: 3-26-10 dd

Variable Legend
Start
Input Variables
NumB = Number of Books
Input                                    ROrder = Want Rush Shipping
NumB, ROrder                                Input from Setup
SmallCO = Small Cut Off
MedCO = Medium Cut Off
SmallSF = Small Shipping Fee
Input from Setup
MedSF = Medium Shipping Fee
SmallCO, MedCO, SmallSF,
LargeSF = Large Shipping Fee
MedSF, LargeSF, RushF
RushF = Rush Fee
Output Variables
RegSCost = Shipping Costs
False                  True                         RushFA = Rush Fee Assigned
NumB >                                    TSCost = Total Shipping Cost
SmallCO?

RegSCost = SmallSF                 False                   True
NumB >
MedCO?
RegSCost = MedSF                        RegSCost = LargeSF

False                 True
ROrder =
True?

RushFA = 0                     RushFA = RushF

TSCost = RegSCost + RushFA

Output
RegSCost,
RushFA, TSCost

End
Required

Create a subroutine to calculate the outputs shown in the worksheet. Create the checkbox in cell C4
and add the Calculate button and attach the sub routine to it. Use the variable names listed on the
worksheet in your code. Do all of the processing with VBA not Excel worksheet functions.

```
To top