Name all cells and ranges used in formulas except for Part 6 (multisheet) 1. IfAndor Sheet: (40 points) a. In Column G, calculate the Penalty for Outstanding payments according to the following rule. i. If Outstanding's value is "No", then the penalty is 0. ii. For Outstanding payments where the Category equals Equipment, the penalty is 2% of the Amount provided the Amount is less than $500 and 5% of the Amount otherwise. iii. All other outstanding amounts carry a penalty of 3% of the Amount b. Using the data on this sheet, construct a pivot table (on a new sheet) showing average amount by Category and Date Paid where Date Paid is grouped into Months. The result should be similar to the following. Average of Amount Column Labels Row Labels Administration Equipment Farm Feed Medicine Repairs Vet Grand Total Oct 5 61 $ 64 $898 $ Nov 83 $ 4,939 5391 $525 $ 267 5 916 5213 S 487 Dec 49 $ 818 5204 $317 $ 117 5 272 $100 $ 235 Grand Total 685 2,192 $203 $400 5 200 $ 401 $.454 S 3.48 300 G H B D E E If And OR Point Possible: 40 3 4 January February March April May June 5 306 340 344 462 311 445 6 474 379 457 318 332 396 7 8 Description Date Paid Check Category Amount Outstanding Penalty 9 ABC Commercial Printers 11/5/10 10210 Administratic $ 86.60 No 10 Phone South 12/10/10 10232 Administratic $ 67.25 No 11 Louisiana Power & Light 11/16/10 10217 Administratic S 44.08 No 12 NO Power & tight 11/5/10 10208 Administratic $ 64.94 No 13 NO Power & Light 11/8/10 10213 Administratic S 23.40 No 14 NO Power & Light 12/20/10 10237 Administratic $ 49.63 No 15 Office Warehouse 11/5/10 10206 Administratic $ 10.67 No 16 Office Warehouse 11/16/10 10216 Administratic $ 250.00 No 17 Office Warehouse 12/7/10 10230 Administratie $ 44.95 No 18 Office Warehouse 12/10/10 10233 Administratic $ 14.57 No 19 Postmaster 12/6/10 10228 Administratic S 68.00 No 20 Richard Cummins, CPA 11/19/10 10218 Administratic S 175.00 No 21 Sandy McGuire 12/21/10 10241 Administratic $ 50.00 No 22 The Highpower Report 11/19/10 10220 Administratie $ 9.00 No 23 Fuller Farm Equipment 12/30/10 10245 Equipment S 386.25 Yes 24 Riley Tractor Center 11/8/10 10215 Equipment $ 4,938.75 No 25 Riley Tractor Center 12/30/10 10248 Equipment S 1,250.00 Yes 26 Ellen Desert 12/28/10 10242 Farm S 31.00 Yes 27 Fred Kaplan 12/2/10 10227 Farm $ 100.00 No 28 Horace Olim 12/10/10 10231 Farm $ 899.00 No 29 Jose Gordon 10/26/10 10202 Farm $ 75.00 No 30 Jose Gordon 12/10/10 10236 Farm $ 15.49 No 31 Jose Gordon 12/29/10 10244 Farm $ 48.50 No 32 Lloyd Dropkin 10/26/10 10203 Farm $ 119.43 No 33 Robert Eldin 10/31/10 10205 Farm $ 25.69 No 34 Robert Eldin 11/5/10 10207 Farm $ 122.16 No 35 Robert Eldin 11/27/10 10225 Farm $ 550.00 No 36 Rodney Hammer 10/22/10 10201 Farm $ 22.00 No 37 Rodney Hammer 12/3/10 10228 Farm 130.00 No 38 Ruth Menigore 11/29/10 10226 Farm $ 500.00 No 39 ABC Feed & Seed 12/30/10 10246 Feed $ 525.56 Yes 40 Crawl Feed Supply 11/5/10 10212 Feed S 325.95 No At Low 10 Doc IfAndor DataTable Lookup Macro Chart MullSheat A F G 39 ABC Feed & Seed 40 Crawl Feed Supply 41 Crawl Feed Supply 42 Crawl Feed Supply 43 Crawl Feed Supply 44 Dr. Smith -45 L. Schuyler 46 Martins Vetinary Center 7 Perl Drugs 18 Perl Drugs 19 Perl Drugs - Home Fixit 1 Home Fixit 2 Jack's Repair 3 Jack's Repair Jack's Repair 5 Dr. Schuler Dr. Schuler Dr. Smith Martins Vetinary Center Martins Vetinary Center B 12/30/10 11/5/10 11/8/10 12/20/10 12/21/10 11/5/10 11/20/10 12/19/10 11/21/10 10/20/10 11/19/10 12/20/10 12/30/10 11/26/10 12/28/10 12/30/10 11/5/10 12/15/10 10/22/10 10/26/10 11/19/10 D 10246 Feed 10212 Feed 10214 Feed 10238 Feed 10240 Feed 10209 Medicine 10222 Medicine 10235 Medicine 10223 Medicine 10199 Medicine 10219 Medicine 10239 Repairs 10249 Repairs 10224 Repairs 10243 Repairs 10247 Repairs 10211 Vet 10234 Vet 10200 Vet 10204 Vet 10221 Vet E 525.56 Yes $ 325.95 No $ 724.36 No $ 226.28 No $ 200.09 No $ 865.69 No $ 105.02 No $ 117.32 No $ 50.05 No $ 64.00 No $ 47.24 No $ 425.35 Yes $ 207.45 Yes $ 915.69 No $ 124.95 No $ 330.00 Yes $ 25.00 No $ 100.00 No $ 795.57 No $ 1,000.00 No $ 400.00 No
