CALVIN by jizhen1947


									                     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
(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
           a. Please write the formula here: B2= IF(A2<100,"Keep Driving",IF(A2<150,"Speed
           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?
          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

                                                                                   You will need to create
                                                                                   this checkbox and link
                                                                                   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
                                                                    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

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

              False                 True
                        ROrder =

        RushFA = 0                     RushFA = RushF

            TSCost = RegSCost + RushFA

                  RushFA, TSCost


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