I need help with this excel work! I need it in less than an hour please! thank you so much!

Wood Hills Animal Clinic August Drug Sales Starting Inventory Drug Advantage Blue Advantage Green Advantage Red Advantage Teal Anipryl Anipryl Anipryl Anipryl Anipryl Aristopet Ear Canker Bomazeal Cat Bomazeal Senior Clomicalm Clomicalm Clomicalm Cosequin Chewable Cosequin Double Cosequin for Cats Cosequin Regular Deramaxx Deramaxx Droncit Tapewormer Drontal Allwormer Drontal Allwormer Drontal Allwormer Drontal Allwormer Drontal Allwormer Ectosoothe 3X Enacard Enacard Enacard Enacard Enacard Epismoothe Oatmeal Epismoothe Rinse Etiderm Exetick Feline 3 Way Fel-O-Vax Frontline Plus Blue Frontline Plus Green Frontline Plus Orange Frontline Plus Purple Frontline Plus Red Heartgard FX Heartgard FX Heartgard Plus Blue Heartgard Plus Brown Heartgard Plus Green Hydroxyzine HCL Hydroxyzine HCL Hydroxyzine HCL Laxatone Laxatone Leo Ear Cleaner Lotagen Lotagen Gel Maxicat All Wormer Otomax Pancrezyme Pancrezyme Pancrezyme Preventic Proin Proin Proin Revolution Revolution Revolution Revolution Revolution Revolution Rimyadyl Chewable Rimyadyl Chewable For use on Flea Flea Flea Flea Endocrine Endocrine Endocrine Endocrine Endocrine Ear Arthritis Arthritis Anxiety Anxiety Anxiety Joint Joint Joint Joint Anti-inflamatory Anti-inflamatory De-wormer De-wormer De-wormer De-wormer De-wormer De-wormer Flea Heart Heart Heart Heart Heart Shampoo Rinse Shampoo Paralysis Ticks Vaccine Vaccine Flea Flea Flea Flea Flea Heartworm Heartworm Heartworm Heartworm Heartworm Allergies Allergies Allergies Hairballs Hairballs Ear Skin Wounds Skin Wounds De-wormer Anti-inflamatory Pancreatic enzyme Pancreatic enzyme Pancreatic enzyme Tick Incontinence Incontinence Incontinence Heartworm Heartworm Heartworm Heartworm Heartworm Heartworm Arthritis Arthritis To treat Dog Dog Dog Dog Dog Dog Dog Dog Dog Dog or Cat Cat Dog Dog Dog Dog Dog Dog Cat Dog Dog Dog Dog or Cat Cat Puppy Dog Dog Dog Dog or Cat Dog or Cat Dog or Cat Dog or Cat Dog or Cat Dog or Cat Dog or Cat Dog or Cat Dog or Cat Dog Cat Cat Dog Cat or Kitten Dog Dog Dog Cat Cat Dog Dog Dog Dog or Cat Dog or Cat Dog or Cat Cat Cat Dog or Cat Dog or Cat Dog or Cat Cat Cat Dog Dog Dog Dog Dog Dog Dog Dog Dog Dog Puppy or Kitten Cat Dog Dog Dog No. of Cases 12 22 15 8 8 9 10 12 12 8 6 14 7 9 15 8 8 8 4 9 10 6 8 6 8 15 20 29 10 6 6 14 12 25 19 4 8 16 14 5 20 10 9 6 4 5 18 30 22 5 5 8 9 13 7 10 7 5 7 6 7 9 9 7 10 15 7 9 12 10 12 21 8 10 Ending Inventory Items per Case Loose Items Items on Hand 20 6 246 25 14 564 20 19 319 25 21 221 20 11 171 20 4 184 20 4 204 20 19 259 20 6 246 50 24 424 50 21 321 50 42 742 18 5 131 18 6 168 18 15 285 30 3 243 30 21 261 30 27 267 30 14 134 12 7 115 12 8 128 100 88 688 100 98 898 25 9 159 18 7 151 18 11 281 18 6 366 12 9 357 30 14 314 30 8 188 22 8 140 30 3 423 22 20 284 12 10 310 12 8 236 12 0 48 35 7 287 40 12 652 38 31 563 30 6 156 20 4 404 30 21 321 30 23 293 30 18 198 50 18 218 32 14 174 75 42 1,392 75 19 2,269 75 36 1,686 600 419 3,419 600 295 3,295 600 131 4,931 50 43 493 50 32 682 25 23 198 15 9 159 25 13 188 100 38 538 12 6 90 18 8 116 50 16 366 50 41 491 100 84 984 35 14 259 35 21 371 35 33 558 32 21 245 32 27 315 32 15 399 32 16 336 32 16 400 32 6 678 30 18 258 30 22 322 Items Remaining 218 268 234 191 110 135 171 152 202 171 268 382 103 149 187 215 168 152 81 89 67 432 318 127 131 220 295 274 158 126 116 222 61 221 208 31 193 578 483 138 319 187 261 169 172 38 968 918 1,296 1,528 1,341 1,945 379 638 143 104 112 241 63 84 329 388 363 193 273 390 146 145 304 252 366 325 216 169 No. Sold 28 296 85 30 61 49 33 107 44 253 53 360 28 19 98 28 93 115 53 26 61 256 580 32 20 61 71 83 156 62 24 201 223 89 28 17 94 74 80 18 85 134 32 29 46 136 424 1,351 390 1,891 1,954 2,986 114 44 55 55 76 297 27 32 37 103 621 66 98 168 99 170 95 84 34 353 42 153 Item Price $ 32.95 $ 19.95 $ 22.95 $ 21.95 $ 49.00 $ 60.00 $ 50.00 $ 53.00 $ 55.00 $ 3.90 $ 17.15 $ 24.95 $ 29.00 $ 39.00 $ 34.00 $ 47.90 $ 49.50 $ 24.90 $ 27.90 $ 38.45 $ 62.50 $ 28.95 $ 8.50 $ 9.50 $ 68.30 $ 5.95 $ 7.30 $ 15.95 $ 32.95 $ 41.00 $ 30.00 $ 31.95 $ 25.00 $ 13.95 $ 9.95 $ 17.50 $ 18.95 $ 4.69 $ 35.99 $ 23.95 $ 22.95 $ 22.95 $ 24.95 $ 25.95 $ 17.95 $ 17.95 $ 14.95 $ 24.95 $ 19.95 $ 0.50 $ 0.70 $ 0.60 $ 7.75 $ 4.50 $ 9.30 $ 72.15 $ 17.45 $ 4.45 $ 19.75 $ 19.25 $ 109.00 $ 45.00 $ 4.95 $ 28.00 $ 20.00 $ 30.00 $ 25.95 $ 27.95 $ 28.95 $ 22.95 $ 22.95 $ 26.95 $ 49.00 $ 32.50 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Total Sales 922.60 5,905.20 1,950.75 658.50 2,989.00 2,940.00 1,650.00 5,671.00 2,420.00 986.70 908.95 8,982.00 812.00 741.00 3,332.00 1,341.20 4,603.50 2,863.50 1,478.70 999.70 3,812.50 7,411.20 4,930.00 304.00 1,366.00 362.95 518.30 1,323.85 5,140.20 2,542.00 720.00 6,421.95 5,575.00 1,241.55 278.60 297.50 1,781.30 347.06 2,879.20 431.10 1,950.75 3,075.30 798.40 752.55 825.70 2,441.20 6,338.80 33,707.45 7,780.50 945.50 1,367.80 1,791.60 883.50 198.00 511.50 3,968.25 1,326.20 1,321.65 533.25 616.00 4,033.00 4,635.00 3,073.95 1,848.00 1,960.00 5,040.00 2,569.05 4,751.50 2,750.25 1,927.80 780.30 9,513.35 2,058.00 4,972.50 Sales Analysis Total Sales Sales of dog only products Sales of cat only products $220,887.16 $155,942.90 $18,912.31 Sales of flea products Sales of flea and tick products Sales of heartworm products Other sales $17,769.00 $0.00 $73,385.90 $129,732.26 Inventory Storage Locations Category Hardware Hardware Software Assessories Software Hardware Accessories Accessories Software Product ID FK3847 LR3141 MR2180 SE2855 PX5833 FL5821 SZ6752 RS2325 LE3841 Total Weight of Stored Products Hardware Warehouse Long-Term Storage Software Warehouse Long-Term Storage Accessories Warehouse Long-Term Storage Weight in Weight in Long-Term Long-Term Warehouse Warehouse Storage Storage 23 257 5911 257 5911 41 3820 156620 2510 102910 0.25 5000 1250 55 13.75 2 583 1166 2583 5166 0.25 3581 895.25 251 62.75 55 251 13805 509 27995 21 528 11088 1011 21231 15 59 885 1481 22215 0.25 51 12.75 20115 5028.75 Per Item Weight (lbs) Contoso, Ltd. Last Name Barnhill Metters Moreland Hensien Kenneth Moore Dellamore Hamilton Hoeing Murray Poland Giest Banai Bourne Hezi Hicks Holliday Iragavarapu Laszlo Wilson Da Silva Delaney Gottfried Valdez Munson Wang Kane Popkova Posti Trenary Position Billing Clerk File Clerk Medical Assistant Nurse Practitioner Office Manager Physician Physician Assistant Receptionist Referral Specialist Registered Nurse First Name Josh Susan Barbara Kari Kevin Bobby Luca David Helge Billie Jo Carole Jenny Yossi Stephanie Mor Cassie Nicole Srinivas Rebecca Dan Sergio Aidan Jim Rachel Stuart Jian John Darya Juha-Pekka Jean Job Title Billing Clerk Billing Clerk Billing Clerk File Clerk File Clerk File Clerk Medical Assistant Medical Assistant Medical Assistant Medical Assistant Nurse Practitioner Office Manager Physician Physician Physician Physician Physician Physician Physician Physician Physician Assistant Receptionist Receptionist Receptionist Referral Specialist Referral Specialist Registered Nurse Registered Nurse Registered Nurse Registered Nurse Count 3 3 4 1 1 8 1 3 2 4 $ $ $ $ $ $ $ $ $ $ Average 30,333.33 27,500.00 35,000.00 95,000.00 55,000.00 372,800.00 83,000.00 30,948.33 32,750.00 67,031.25 2013 Salary $ 30,000 $ 32,000 $ 29,000 $ 24,500 $ 26,000 $ 32,000 $ 41,000 $ 28,000 $ 42,000 $ 29,000 $ 95,000 $ 55,000 $ 292,800 $ 246,000 $ 352,475 $ 481,750 $ 360,000 $ 324,500 $ 522,875 $ 402,000 $ 83,000 $ 33,675 $ 26,695 $ 32,475 $ 32,500 $ 33,000 $ 63,350 $ 66,650 $ 70,000 $ 68,125 2014 Salary $ 33,000 $ 35,200 $ 31,900 $ 26,950 $ 28,600 $ 35,200 $ 45,100 $ 30,800 $ 46,200 $ 31,900 $ 104,500 $ 60,500 $ 322,080 $ 270,600 $ 387,723 $ 529,925 $ 396,000 $ 356,950 $ 575,163 $ 442,200 $ 91,300 $ 37,043 $ 29,365 $ 35,723 $ 35,750 $ 36,300 $ 69,685 $ 73,315 $ 77,000 $ 74,938 2015 Salary $ 37,500 $ 40,000 $ 36,250 $ 30,625 $ 32,500 $ 40,000 $ 51,250 $ 35,000 $ 52,500 $ 36,250 $ 118,750 $ 68,750 $ 366,000 $ 307,500 $ 440,594 $ 602,188 $ 450,000 $ 405,625 $ 653,594 $ 502,500 $ 103,750 $ 42,094 $ 33,369 $ 40,594 $ 40,625 $ 41,250 $ 79,188 $ 83,313 $ 87,500 $ 85,156 This exam includes three required problems, Problems 1, 2 and 3 are required. The required datasets are included in Blackboard posting for this exam. Your solutions must be posted to Blackboard no later than the end of class. Problem 1 (40 points) 1.OPEN the 12 Medical workbook. 2. Create a 3-D Stacked Column chart using the text in A4:A6 as the category axis labels, the text in D3:F3 as the series labels, and D4:F6 as the data. (Hint: You may need to use Switch Row/Columns to get the right orientation.) 3. Remove the chart title. 4. Move the chart to a new sheet. Name the sheet Billing Clerk Salaries. 5. Apply Style 6 from the Chart Styles gallery. 6. Create a 3-D pie chart from A35:B45. 7. Apply the Data Callout style of data labels. 8. Remove the legend and the chart title. 9. Reposition the chart so that its upper left corner is at the top of cell E35. 10. Resize the chart so that its frame is 5\" wide and 5\" tall. 11. SAVE the document as 12Medical_LastnameFirstName and post to Blackboard. Problem 2 (40 points) 1. OPEN the 09 Pharmacy workbook. 2. Append the contents of 09 Records.txt to the existing data starting in cell A81. 3. Adjust the column widths as needed to prevent any labels or data from appearing truncated. . Change the number format for cells J81:K93 to Accounting so that the values match the other values in that column in formatting. 5. Convert A6:K93 to a table. If asked whether you want to remove all external connections, click Yes. 6. Apply Table Style Medium 15 to the table. 7. Enable the Banded Columns table style option. 8. Filter the table to show only items where the To Treat column includes dogs or puppies. 9. Sort the table first by the Drug column in A to Z order, and then by the To Treat column in A to Z order, and then the No. Sold column in Largest to Smallest order. 10. SAVE the document as 09Pharmacy_LastnameFirstname and post to Blackboard. 2 Problem 3 (20 points) 1. OPEN the 10 Inventory workbook. 2. In cell B16, create a SUMIF function that sums the values in column E for rows in which the category from column A is Hardware. 3. In cell B17, create a SUMIF function that sums the values in column G for rows in which the category from column A is Hardware. 4. Repeat steps 2 and 3 to create functions in B19, B20, B22, and B23 for the respective categories. 5. SAVE the workbook as 10Inventory_LastnameFirstname and post to Blackboard. 3 Contoso, Ltd. Last Name Barnhill Metters Moreland Hensien Kenneth Moore Dellamore Hamilton Hoeing Murray Poland Giest Banai Bourne Hezi Hicks Holliday Iragavarapu Laszlo Wilson Da Silva Delaney Gottfried Valdez Munson Wang Kane Popkova Posti Trenary Position Billing Clerk File Clerk Medical Assistant Nurse Practitioner Office Manager Physician Physician Assistant Receptionist Referral Specialist Registered Nurse First Name Josh Susan Barbara Kari Kevin Bobby Luca David Helge Billie Jo Carole Jenny Yossi Stephanie Mor Cassie Nicole Srinivas Rebecca Dan Sergio Aidan Jim Rachel Stuart Jian John Darya Juha-Pekka Jean Job Title Billing Clerk Billing Clerk Billing Clerk File Clerk File Clerk File Clerk Medical Assistant Medical Assistant Medical Assistant Medical Assistant Nurse Practitioner Office Manager Physician Physician Physician Physician Physician Physician Physician Physician Physician Assistant Receptionist Receptionist Receptionist Referral Specialist Referral Specialist Registered Nurse Registered Nurse Registered Nurse Registered Nurse Count 3 3 4 1 1 8 1 3 2 4 $ $ $ $ $ $ $ $ $ $ Average 30,333.33 27,500.00 35,000.00 95,000.00 55,000.00 372,800.00 83,000.00 30,948.33 32,750.00 67,031.25 2013 Salary $ 30,000 $ 32,000 $ 29,000 $ 24,500 $ 26,000 $ 32,000 $ 41,000 $ 28,000 $ 42,000 $ 29,000 $ 95,000 $ 55,000 $ 292,800 $ 246,000 $ 352,475 $ 481,750 $ 360,000 $ 324,500 $ 522,875 $ 402,000 $ 83,000 $ 33,675 $ 26,695 $ 32,475 $ 32,500 $ 33,000 $ 63,350 $ 66,650 $ 70,000 $ 68,125 2014 Salary $ 33,000 $ 35,200 $ 31,900 $ 26,950 $ 28,600 $ 35,200 $ 45,100 $ 30,800 $ 46,200 $ 31,900 $ 104,500 $ 60,500 $ 322,080 $ 270,600 $ 387,723 $ 529,925 $ 396,000 $ 356,950 $ 575,163 $ 442,200 $ 91,300 $ 37,043 $ 29,365 $ 35,723 $ 35,750 $ 36,300 $ 69,685 $ 73,315 $ 77,000 $ 74,938 2015 Salary $ 37,500 $ 40,000 $ 36,250 $ 30,625 $ 32,500 $ 40,000 $ 51,250 $ 35,000 $ 52,500 $ 36,250 $ 118,750 $ 68,750 $ 366,000 $ 307,500 $ 440,594 $ 602,188 $ 450,000 $ 405,625 $ 653,594 $ 502,500 $ 103,750 $ 42,094 $ 33,369 $ 40,594 $ 40,625 $ 41,250 $ 79,188 $ 83,313 $ 87,500 $ 85,156 Registered Nurse; 13% Referral Specialist; 7% Receptionist; 10% Physician Assistant; 3% Billing Clerk; 10 F Registered Nurse; 13% Billing Clerk; 10 F Referral Specialist; 7% Receptionist; 10% Physician Assistant; 3% Offi Physician; 27% Billing Clerk; 10% File Clerk; 10% Medical Assistant; 13% Nurse Practitioner; 3% cian; 27% Billing Clerk; 10% File Clerk; 10% Medical Assistant; 13% Nurse Practitioner; 3% Office Manager; 3% $120,000 $100,000 $80,000 $60,000 $40,000 $20,000 $- $29,000 $32,000 $35,200 Barnhill; $30,000 Metters; $33,000 Barnhill 2013 Salary $36,250 $31,900 Metters 2014 Salary $40,000 Moreland; $37,500 Moreland 2015 Salary Inventory Storage Locations Category Hardware Hardware Software Assessories Software Hardware Accessories Accessories Software Product ID FK3847 LR3141 MR2180 SE2855 PX5833 FL5821 SZ6752 RS2325 LE3841 Total Weight of Stored Products Hardware Warehouse Long-Term Storage Software Warehouse Long-Term Storage Accessories Warehouse Long-Term Storage 176336 136816 2158 5105.25 13139 48612 Per Item Weight (lbs) Weight in Long-Term Warehouse Warehouse Storage 23 257 5911 257 41 3820 156620 2510 0.25 5000 1250 55 2 583 1166 2583 0.25 3581 895.25 251 55 251 13805 509 21 528 11088 1011 15 59 885 1481 0.25 51 12.75 20115 Weight in Long-Term Storage 5911 102910 13.75 5166 62.75 27995 21231 22215 5028.75