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 $ 61 64 $8985 83 5 4,939 $391 S525 $ 267 $ 916 5213 $ 49 $ 818 $204 $317 S 2,192 $203 $ 400 $ 208 $ 401 $464 S $ Oct Nov Dec Grand Total Woon Grand Total 300 487 117 $ 272 $100 $ 235 348 68 $ If And OR Point Possible: 40 January April June February March 306 340 474 379 144 457 May 462 318 311 332 445 396 Penalty Description ABC Commercial Printers Phone South Louisiana Power & Light NO Power Light NO Power Light NO Power Light Office Warehouse Office Warehouse Office Warehouse Office Warehouse Postmaster Richard Cummins, CPA Sandy McGuire The Highpower Report Fuller Farm Equipment Riley Tractor Center Riley Tractor Center Ellen Desert Fred Kaplan Horace Olim Jose Gordon Jose Gordon Jose Gordon Lloyd Dropkin Robert Eldin Robert Eldin Robert Eldin Rodney Hammer Rodney Hammer Ruth Menigore ABC Feed Seed Crawl Feed Supply Crawl Feed Supply Crawl Feed Supply Crews Feed Supply Dr. Smith L. Schuyler Martins Votinary Center Perl Drugs Perr Dries Per Dues Home Fodt Home Fun Jacks Repair Jack's Repair Jack's Repair DY. Schuler Dr. Schulter Dr. Smith Martins Vetinary Center Martin Vetinary Center Date Paid Check Category Amount Outstanding 11/5/10 10210 Administrati 86,60 NO 12/10/10 10232 Administrati s 67.25 No 11/16/10 10217 Administrati 5 44.08 No 11/5/10 10208 Administrati s 64.94 No 11/8/10 10213 Administrati 23.40 No 12/20/10 10237 Administrati s 49.63 No 11/5/10 10206 Administrati 10.67 No 11/16/10 10216 Administrati 250.00 No 12/7/10 10230 Administrati s 44.95 NO 12/10/10 10233 Administrati 14.57 No 12/6/10 10228 Administrati 5 68.00 No 11/19/10 10218 Administrati s 175.00 NO 12/21/10 10241 Administrati 50.00 No 11/19/10 10220 Administrati $ 9.00 No 12/30/10 10245 Equipments 386,25 Yes 11/8/10 10215 Equipment $ 4,938.75 No 12/30/10 10248 Equipment $ 1.250.00 Yes 12/28/10 10242 Farm 5 31.00 Yes 12/2/10 10227 Farm $ 100.00 No 12/10/10 10231 Farm $ 899.00 No 10/26/10 10202 Farm S 75.00 No 12/10/10 10236 Farm $ 15.49 No 12/29/10 10244 Farm $ 48.50 No 10/26/10 10203 Farm $ 119.4) No 10/31/10 10205 Farm $ 25.69 No 11/5/10 10207 Farm $ 122.16 No 11/27/10 10225 Farm s 550,00 No 10/22/10 10201 Farm $ 22.00 NO 12/3/10 10228 Farm 5 130.00 No 11/29/10 10226 Farm $ 500.00 No 12/30/10 10246 Feed $ 525.56 Yes 11/5/10 10212 Feed $ 325.95 No 11/8/10 10214 Feed $ 724.J6 No 12/20/10 10238 Feed 5 226.28 No 12/21/10 10240 Feed $ 200.09 NO 11/5/10 10209 Medicine $ 865.69 NO 11/20/10 10222 Medicine S 105.02 No 12/19/10 10235 Medicine $ 117.32 No 11/21/10 19223 Medicine $ 50.05 No 10/20/10 10199 Medicine $ 64.00 No 11/19/10 10219 Medicine $ 47.24 NO 12/20/10 10239 Repairs $ 425.35 Yes 12/10/10 10249 Repairs S 207.45 Yes 11/26/10 10224 Repairs 5 915.69 No 12/28/10 10243 Repairs 5 124.95 No 12/30/10 10247 Repairs $ 310.00 Yes 11/5/10 10211 Vet 5 25.00 12/15/10 10234 Vet 5 100.00 No 10/22/10 10200 Vet SX45157 No 10/26/10 10104 Vet $ 1,000.00 NO 11/19/10 10221 Vet 5 400.00 N 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 $ 61 64 $8985 83 5 4,939 $391 S525 $ 267 $ 916 5213 $ 49 $ 818 $204 $317 S 2,192 $203 $ 400 $ 208 $ 401 $464 S $ Oct Nov Dec Grand Total Woon Grand Total 300 487 117 $ 272 $100 $ 235 348 68 $ If And OR Point Possible: 40 January April June February March 306 340 474 379 144 457 May 462 318 311 332 445 396 Penalty Description ABC Commercial Printers Phone South Louisiana Power & Light NO Power Light NO Power Light NO Power Light Office Warehouse Office Warehouse Office Warehouse Office Warehouse Postmaster Richard Cummins, CPA Sandy McGuire The Highpower Report Fuller Farm Equipment Riley Tractor Center Riley Tractor Center Ellen Desert Fred Kaplan Horace Olim Jose Gordon Jose Gordon Jose Gordon Lloyd Dropkin Robert Eldin Robert Eldin Robert Eldin Rodney Hammer Rodney Hammer Ruth Menigore ABC Feed Seed Crawl Feed Supply Crawl Feed Supply Crawl Feed Supply Crews Feed Supply Dr. Smith L. Schuyler Martins Votinary Center Perl Drugs Perr Dries Per Dues Home Fodt Home Fun Jacks Repair Jack's Repair Jack's Repair DY. Schuler Dr. Schulter Dr. Smith Martins Vetinary Center Martin Vetinary Center Date Paid Check Category Amount Outstanding 11/5/10 10210 Administrati 86,60 NO 12/10/10 10232 Administrati s 67.25 No 11/16/10 10217 Administrati 5 44.08 No 11/5/10 10208 Administrati s 64.94 No 11/8/10 10213 Administrati 23.40 No 12/20/10 10237 Administrati s 49.63 No 11/5/10 10206 Administrati 10.67 No 11/16/10 10216 Administrati 250.00 No 12/7/10 10230 Administrati s 44.95 NO 12/10/10 10233 Administrati 14.57 No 12/6/10 10228 Administrati 5 68.00 No 11/19/10 10218 Administrati s 175.00 NO 12/21/10 10241 Administrati 50.00 No 11/19/10 10220 Administrati $ 9.00 No 12/30/10 10245 Equipments 386,25 Yes 11/8/10 10215 Equipment $ 4,938.75 No 12/30/10 10248 Equipment $ 1.250.00 Yes 12/28/10 10242 Farm 5 31.00 Yes 12/2/10 10227 Farm $ 100.00 No 12/10/10 10231 Farm $ 899.00 No 10/26/10 10202 Farm S 75.00 No 12/10/10 10236 Farm $ 15.49 No 12/29/10 10244 Farm $ 48.50 No 10/26/10 10203 Farm $ 119.4) No 10/31/10 10205 Farm $ 25.69 No 11/5/10 10207 Farm $ 122.16 No 11/27/10 10225 Farm s 550,00 No 10/22/10 10201 Farm $ 22.00 NO 12/3/10 10228 Farm 5 130.00 No 11/29/10 10226 Farm $ 500.00 No 12/30/10 10246 Feed $ 525.56 Yes 11/5/10 10212 Feed $ 325.95 No 11/8/10 10214 Feed $ 724.J6 No 12/20/10 10238 Feed 5 226.28 No 12/21/10 10240 Feed $ 200.09 NO 11/5/10 10209 Medicine $ 865.69 NO 11/20/10 10222 Medicine S 105.02 No 12/19/10 10235 Medicine $ 117.32 No 11/21/10 19223 Medicine $ 50.05 No 10/20/10 10199 Medicine $ 64.00 No 11/19/10 10219 Medicine $ 47.24 NO 12/20/10 10239 Repairs $ 425.35 Yes 12/10/10 10249 Repairs S 207.45 Yes 11/26/10 10224 Repairs 5 915.69 No 12/28/10 10243 Repairs 5 124.95 No 12/30/10 10247 Repairs $ 310.00 Yes 11/5/10 10211 Vet 5 25.00 12/15/10 10234 Vet 5 100.00 No 10/22/10 10200 Vet SX45157 No 10/26/10 10104 Vet $ 1,000.00 NO 11/19/10 10221 Vet 5 400.00 N