4. On the Shopping List sheet, check all the formulas. Cells to check are filled with the light orange color. Most of them need to be corrected. Many of the problems on this worksheet can be solved by creating named ranges or using a name that already exists. a. The formula in cell B2 uses the wrong function. b. The formulas in cells A9:A23 reference a named range that doesn't exist. There is more than one correct way to fix this problem using the cell range A5:H18 on the Places to Shop worksheet. You can create the named range referenced in the formulas, or you can change the function arguments to reference the cell range instead. c. The formula in cell H9 results in the correct value. However, the workbook author copied this formula to the remaining cells in the column and those values are definitely not correct! Fix the formula in cell H9 and copy it to cells H10:H23. Hint. Notice that cell H8 is named Tax. 5. If you've fixed the formulas in cells H9:H23 correctly, the formulas in cells 19:123 and G5 should be calculate properly now. However, the formulas in cells G2:G4 still have errors that need to be fixed. Hint: Use error checking as needed and/or display the formulas on-screen for easy viewing. a. Correct the function used in cell G2 to average value of the Cost column. b. Correct the function used in cell G3 to display the largest value in the Cost column. C. Correct the function used in cell G4 to display the smallest value in the Cost column. 6. On the Summary sheet, you will be entering all the formulas. Cells to complete are filled with the light orange color. Hint: Use error checking as needed and/or display the formulas on-screen for easy viewing. a. Cell B2 should use a function that will update the date to the current date every time the workbook is opened. b. Cell B4 references a named range that doesn't exist. It should reference cell A4 on the Guest List sheet. You can create the named range or edit the formula to reference the cell instead. c. Cell B5 references a named range that doesn't exist. It should reference cell A3 on the Guest List sheet. You can create the named range or edit the formula to reference the cell instead. d. Cell B8 is missing the formula to calculate whether or not the total Cost with tax on the Shopping List sheet + the total Cost for purchasing and mailing invitations on the Guest List sheet is greater than the available cash. The cell should display yes or no. e. Add a formula to cell B9 to calculate the amount to borrow (total Cost with tax on the Shopping List sheet + the total Cost for purchasing and mailing invitations on the Guest List sheet - the cash available) if the value in cell B8 is yes. If the value in cell B8 is not yes, the cell should display 0. f. Add a formula to cell B12 to calculate the monthly loan payment based on the information in cells B9:B11. Use a negative number for the Pv argument. A 1 Guest List 2 3 =COUNTA(J10:367) 4 =COUNTA(E10:E67) 5 =COUNTA(J10:367) 6 =MAX(J10:167) AVERAGE(J10:J67) B Total number of guests attending Count of invitations sent Count of missing responses Largest group attending Average number of guests in each group D E F H cost per invitation 0.97 postage cost 0.56 Total cost for purchasing and mailing invitations =A3*F3+A3*F4 City State Zip Phone Num Rocklin Newcastle CA 95602 916-598-6952 1 CA 95602 916-741-8526 4 Auburn CA 95602 530-888-0805 1 Auburn CA 95602 530-885-6523 1 Saramento CA 95602 (916)348-9982 2 Santa Rosa CA 95602 (707)521-3478 3 San Jose CA 95602 (415)441-8639 Rocklin CA 95602 Roseville CA 95602 Rocklin CA 95602 Rocklin CA 95602 (916)315-8690 (916)789-4470 555-1324 555-2453 Rocklin CA Rocklin CA 95602 95602 Rocklin CA 95602 Rocklin CA 95602 5366 Onyx Drive Rocklin CA 95602 2323 Windyl Drive Rocklin CA 95602 Roseville CA San Diego CA San Diego CA Auburn CA 95602 95602 95602 95602 Auburn CA 95602 Weimar CA 95602 555-2606 555-2690 555-9902 (916)521-6654 (916)632-5152 (916)632-0127 (916)624-5656 (619)279-9486 (619)560-2356 (530)823-4983 (530)823-4983 (530)637-9901 212 00 1 1 COLORA 0 1 1 4 0 1 Dark City CA 95602 458-6288 1 Watershed La Beach 33 CA 95602 626-8324 1 CA 95602 364-6274 5 Dark City CA 95602 458-5826 2 7 8 9 10 Mr. 11 Mrs 12 Miss Title First Name Cindy Larrina Katherine James William Bethany Louisa Harold Michael Last Name Jacobs Grande. Hairless Muchley Holland Sanchaz Watkins Thatcher Cater Roger Name Tag =PROPER(CONCAT(B10," ",C10)) =PROPER(CONCAT(B11, "",C11)) =PROPER(CONCAT(B12," ",C12)) =PROPER(CONCAT(B13,"",C13)) =PROPER(CONCAT(B14," ",C14)) PROPER(CONCAT(B15," ",C15)) EPROPER(CONCAT(B16, ",C16)) =PROPER(CONCAT(B17, ",C17)) =PROPER(CONCAT(B18," ",C18)) =PROPER(CONCAT(B19," ",C19)) =PROPER(CONCAT(B20, ",C20)) =PROPER(CONCAT(B21,"",C21)) =PROPER(CONCAT(B22,"",C22)) =PROPER(CONCAT(B23," ",C23)) EPROPER(CONCAT(B24, ,C24)) =PROPER(CONCAT(B25, ",C25)) =PROPER(CONCAT(B26, ",C26)) =PROPER(CONCAT(B27," ",C27)) =PROPER(CONCAT(B28, ",C28)) =PROPER(CONCAT(B29," ",C29)) =PROPER(CONCAT(B30," ",C30)) =PROPER(CONCAT(B31, "",C31)) EPROPER(CONCAT(B32, ,C32)) =PROPER(CONCAT(B33, ",C33)) Street 417 9th St. 87441 Palace Square 2068 Harry Hill 87543 Baldys Road 9225 Marchmont Dr 8852 Jones Lane 446 Chest Ave 2275 Oak Park Lane 852 Paly Place 14 Header Dr 543 Tulip Ave 387 Daly St 9083 Rocky Rd 883 Forest Drive 5366 Onyx Drive 13 Mr 14 Mr./Mrs 15 Mr/Mrs 16 Mrs Gerald 17 Mr Emily 18 Mr/Mrs 19 Mr. 20 Mr. 21 Mr. Adam: Slocum Melon 22 Mr. Claire Ace 23 Mr. Jackson Perry 24 Mr. James Smith 25 Mrs. Jim Smith 26 Ms. Tom Jackson 27 Mr. Chuck 28 Miss. 29 Mr. 30 Mrs. Barbra Walter Newman Williams Pell Jerry Jones 31 Mr. Christopher Jones 32 Miss. Keith Lee 33 Mr. Byung Nagato 34 Mr. Sharon Nagasaki =PROPER(CONCAT(B34," ",C34)) 35 Ms. David Lonsdale =PROPER(CONCAT(B35," ",C35)) 36 Mr. Karen Negreedy =PROPER(CONCAT(B36," ",C36)) 858 Steal Way Guest List Shopping List Places to Shop Summary + 4477 Jade Road 1453 Frosty St. 890 1st Ave. 5678 Oak Ave. 5678 Oak Ave. 7823 Seacrest 23 Shadow Ln 410 Lemoen Dr 725 Wave st. C E F 1 Shopping List 2 number of different items 3 |=COUNTA(C9:C23) 4 5 6 7 8 9 City Source Item Description Quantity Units 10 =VLOOKUP(B10, Places_to_Shop,3,FALSE) Super Supermarket Sharons Bakery Appetizers Cake 12 1 pounds Each 10 350 =@AVG(G9:G23) =@maximum(G9:G23) =@minimum(G9:G23) =SUM(19:123) Average item cost Highest Item Cost Lowest Item Cost Total Cost with Tax CostperUnit Cost Tax Total Cost =F9*D9 =F10*D10 0.06 -G9*H8 =G10*H9 =G9+H9 =G10+H10 11 =VLOOKUP(B11, Places_to_Shop,3,FALSE) The Party Store Dessert Forks, Cofee spoons 100 each 0.5 =F11*D11 |=G11*H10 |=G11+H11 12 =VLOOKUP(B12, Places_to_Shop,3,FALSE) 13 =VLOOKUP(B13,Places_to_Shop,3,FALSE) 14 =VLOOKUP(B14,Places_to_Shop,3,FALSE) 15 =VLOOKUP(B15, Places_to_Shop,3,FALSE) 16 =VLOOKUP(B16,Places_to_Shop,3,FALSE) 17 =VLOOKUP(B17,Places_to_Shop,3,FALSE) 18 =VLOOKUP(B18, Places_to_Shop,3,FALSE) 19 =VLOOKUP(B19, Places_to_Shop,3,FALSE) 20 =VLOOKUP(B20, Places_to_Shop,3,FALSE) 21 =VLOOKUP(B21,Places_to_Shop,3,FALSE) 22 =VLOOKUP(B22, Places_to_Shop,3,FALSE) 23 =VLOOKUP(B23, Places_to_Shop,3,FALSE) 24 Party Rentals The Party Store Engravers The Entertainers The Party Store The Party Store Music Napkins Salad Plates The Party Store Dessert Plates 100 each 1 =F12*D12 =G12*H11 =G12+H12 The Party Store Dinner Plates 100 each 1.25 =F13*D13 |=G13*H12 =G13+H13 The Party Store Dinner Silverware 100 sets 1 =F14*D14 =G14*H13 =G14+H14 Flower Power Florist Flower Arangements 20 each 40 =F15*D15 |=G15*H14 |=G15+H15 Glass Coffee Cups and Saucers 50 each 2 =F16*D16 =G16*H15 Glasses 100 each 1.5 =F17*D17 =G17*H16 =G16+H16 =G17+H17 Invitations 70 each 0.89 =F18*D18 =G18*H17 |=G18+H18 1 each 1500 =F19*D19 =G19*H18 |=G19+H19 100 each 0.5 =F20*D20 =G20*H19 =G20+H20 100 each 0.75 =F21*D21 =G21*H20 |=G21+H21 Costco - Folsom Soft Drinks and Water 8 case 9 =F22*D22 =G22*H21 =G22+H22 The Party Store Table Cloths 10 each 4 =F23*D23 =G23*H22 =G23+H23 C5 A 1 look up table 2 3 1st column 4 B fx Sacramento C D E F G H J K 2nd column 3rd column CompanyName Street City State Zip ContactFN ContactLN Cell Phone 5 BevMO 2000 Price Dr. Sacramento CA 95643 Brenda Green 888-8888 6 Costco Folsom 345 Stanford Ranch Road 7 Cravens 456 Bishop Street Folsom Folsom CA 95612 CA 95613 Juan Whitenhouse 666-7878 Manuel Enriquez 667-7879 Citrus 8 Creative Ballons 9 Engravers 454 Vernon Street 600 Arden Way Heights Carmichael CA 99509 Karen Bonnet 677-7654 3 CA 95605 David Newsom 878-9089 10 Flower Power Florist 3456 Tulip Drive 11 John's Dishes 12 Party Rentals 13 Sharons Bakery 14 Super Supermarket 15 The Entertainers 16 The Flower Shop 9857 First Street 455 Douglas Bld. 700 Leidersdorf 1345 Sierra Blvd. 1600 Broadway 12Marh Lane Roseville Folsom Folsom Folsom Roseville Sacramento CA 3 3 5 5 3 5 5 CA 95613 Shane David 666-7911 CA 95614 Bob Adzich 666-7122 CA 95643 Techi Smith 765-0987 CA 95612 Sharon Coon 555-6666 CA 95643 Marie Jorgenson 676-8765 95643 Larry Shane 786-8998 Sacramento CA 95644 Sue Marshal 786-8449 17 The Party Store 18 Total Wines 333 Galilee Drive 652 Sutter Street Citrus Heights Folsom 33 CA 99509 CA 95612 Matt Byung Canoli 555-8989 Jacobs 777-7777 19 20 21 222222222 23 24 25 26 27 Guest List Shopping List Places to Shop Summary + B11 A 1 Party Financing fx 12 B C D E F 2 As of 1/6/2017 3 4 # of Invitations #NAME? 5 # of Guests #NAME? 6 7 Cash Available for Event $2,000.00 8 Do We Need to Borrow? 9 Amount to Borrow 10 APR 7.5% 11 # Months to Pay 12 Monthly Payment Amount 12 13 14 15 16 17 18 19 Guest List Shopping List Places to Shop Summary