VIEWS: 5 PAGES: 5 POSTED ON: 7/21/2011
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 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 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.