Pivot Tables and Charts (7 pts) You have been asked by the Sales Manager to do analysis for a company that sells three products (LM2500, NX900, GF2345) in four regions (North, South, East, and West). The data you have been given is for 2016 (it's FY similar is similar to the federal government) and you must follow these instructions: 1. Use the file MIS301-MIS303_Homework4_Data.txt posted on Blackboard as the datasource. You may assume the raw data is correct and it is not necessary to format this worksheet - just leave it as-is - but you must rename your worksheet to 'Raw Data' and make it the first one in your spreadsheet. (1 pt) 2. Label your filename '[Your LastName_FirstName]_Homework4' and save it in an .xls or .xlsx format. 3. Create a worksheet labeled 'Pivot Table' and create a Pivot Table that has the Sales Person as the filter and shows region and products by rows and the value for the column. Show the summation for the column of the 1st and 3rd quarter of 2016 for sales reps James and Lin. Format the cells with no decimals but with commas in the appropriate places. (3 pts) 4. Create a worksheet labeled 'Pivot Chart' and create a Pivot Chart that shows the average sales of Debbie and Rajiv for 2016 for each region to one decimal point. Format the pivots chart so it appears as: East, North, South, West. It is not necessary to add a chart title because of the dynamic nature of the Pivot Chart. (3 pts) Goal Seek (4 pts) 1. Name a worksheet as 'Goal Seek' and label cells as shown below (be sure to follow this explicitly as it makes grading much easier on me). A B Loan Repayment Base Scenario Interest Rate # of Payments Principal Payments Total Payments Total Interest Using the following values, calculate Payment, Total Payments, and Total Interest for your Base Scenario. Format all payment fields as currency ($) with appropriate decimals and format all other fields correctly. Payment is assumed at the beginning of the period. Interest Rate - 3.24% (annual interest rate)| # of Payments - 360 (30-year mortgage) | Principal - $849,999 (1 pt) When you present these figures to your clients, they say the maximum monthly payment they can - afford is $3125. Using the same initial values for Interest Rate, # of Payments, and Principal, use Goal Seek to adjust the following variables for the maximum payment the client can afford (i.e., you need to create three scenarios, label them 'Revised 1', 'Revised 2', 'Revised 3'): Revised 1 - Principal (1 pt) Revised 2 - # of Payments (1 pt) – whole number (use ROUNDUP function) Revised 3 - Interest Rate (1 pt) – 2 decimal points Solver (5 pts) 1. Name a worksheet 'Solver 1'. Using the following data, use Solver to calculate weekly Maximum Revenue while staying within the following prices and constraints (4 pt): Product Price Regular $3.78 Cappuccino $3.91 Premium Latte $4.35 Premium Mocha $4.52 Product Cups/week All 625 Max Cappuccino 100 Max Premium Cups 225 Max Premium Mocha 155 2. Name another worksheet 'Solver 2'. Using the data in Problem 2, use Solver to calculate Revenue = $2,125. You will need to solve the problem and then make it into a integer using the constraint function (1 pt). DATA: file MIS301-MIS303_Homework4_Data.txt Region Product Sales Person - Name 1Q 2016 2Q 2016 3Q 2016 4Q 2016 West LM2500 Debbie 7562 42903 57350 51730 West NX900 James 18055 52460 51840 25304 East GF2345 Lin 75666 58178 24782 12868 East GF2345 Debbie 47428 21242 66448 60894 West NX900 Debbie 51309 71791 10331 53839 West LM2500 Lin 26839 42210 36967 8434 West LM2500 Lin 35751 62526 53345 52396 East GF2345 Debbie 32886 72502 78724 29725 East NX900 Rajiv 17016 77204 58708 42596 East GF2345 Lin 26745 13532 43235 48950 East NX900 Lin 54992 70817 9381 46635 East NX900 Lin 18792 49113 22939 65318 East LM2500 Rajiv 9808 25048 11387 29158 West NX900 Debbie 43913 34922 32494 49528 East GF2345 Debbie 26460 65143 17692 38880 West LM2500 Rajiv 54256 72994 29651 33575 East NX900 Lin 53552 40896 33990 65178 West GF2345 James 60639 69104 63496 32511 West LM2500 James 6269 21940 17092 54315 West NX900 Debbie 30370 16486 72322 71587 East LM2500 Debbie 32845 22509 8802 23014 West LM2500 James 21431 27835 53722 13407 East GF2345 Rajiv 65720 27382 42487 22060 East GF2345 Rajiv 20316 41839 75841 66561 West NX900 Debbie 35772 30181 32287 71648 East LM2500 James 41398 66582 28835 52205 East GF2345 Debbie 17588 37277 36956 25405 West GF2345 Rajiv 67724 42417 63130 35981 East GF2345 Lin 65324 11392 73924 35897 East LM2500 Debbie 48948 32693 52630 23176 East NX900 Debbie 24774 26400 47731 72190 East LM2500 Debbie 38842 12377 78888 15484 East LM2500 Lin 36429 60107 75288 69583 East LM2500 Lin 11521 15428 53320 7811 East GF2345 Rajiv 44766 44653 62290 69391 West GF2345 Lin 30102 62436 29466 5950 West GF2345 James 33810 24705 49382 34652 East NX900 James 68066 51658 35707 41035 West LM2500 Lin 18952 60816 42776 68505 West NX900 Rajiv 33192 33561 19560 76436 East GF2345 James 48257 51758 20439 5907 West GF2345 Lin 33390 41281 49300 55410 East LM2500 Lin 8057 42584 75076 68060 East NX900 Debbie 10178 53164 30509 23018 West GF2345 Rajiv 27113 5994 73029 47864 West LM2500 Debbie 19542 24346 53591 37312 West LM2500 James 63253 74466 76497 10784 East LM2500 James 14423 12465 19705 15155 West LM2500 Rajiv 22432 66803 68397 56958 East LM2500 Rajiv 10432 19972 57284 32605 East LM2500 Rajiv 61778 72126 75000 14428 West GF2345 Lin 36134 53336 73566 15705 West NX900 James 61995 23580 64541 6469 West LM2500 Debbie 13654 34896 37403 21074 East GF2345 Rajiv 33479 32110 38599 51673 West LM2500 Rajiv 38503 50268 74222 26681 East LM2500 James 53124 27865 54496 24600 East LM2500 Lin 34867 70045 52291 22581 West LM2500 James 25165 25426 16858 44593 East NX900 Rajiv 8424 12769 77334 71825 West LM2500 Lin 10386 27446 61276 17359 West LM2500 Lin 57661 15400 59114 29436 West GF2345 Lin 13730 7090 8897 44956 East NX900 Debbie 26927 10557 35216 64493 East GF2345 James 52234 52370 32173 12918 East LM2500 James 28448 78701 59017 75380 West LM2500 James 32603 6359 66550 69345 East LM2500 Lin 28639 48458 36164 32155 West GF2345 Debbie 19491 13424 62193 47419 East GF2345 James 54895 66007 34926 51978 East NX900 James 62272 55308 16148 78053 East LM2500 Rajiv 47570 19758 33226 62437 West NX900 James 44313 55930 27252 44772 East GF2345 Debbie 58119 40433 19387 8266 West LM2500 Rajiv 65440 66962 18732 36506 East NX900 Debbie 55717 75454 18845 53810 East NX900 Debbie 46450 67733 77425 71298 East LM2500 Rajiv 45593 28867 17475 30601 West LM2500 James 57641 63524 42951 26340 East LM2500 Lin 47350 66213 74892 32993 West GF2345 Debbie 18405 53434 41888 17141 West NX900 Debbie 5659 33519 36782 7805 West NX900 James 11608 73463 54374 57516 West LM2500 Lin 55487 11255 17175 23470 East GF2345 Rajiv 18464 36775 21811 37720 East LM2500 Debbie 29687 70616 8673 43373 West GF2345 Rajiv 47639 76637 39704 63925 West GF2345 Debbie 13657 40521 41953 28103 West NX900 James 21705 62245 17805 58368 North LM2500 Lin 18247 51014 59892 62324 North LM2500 Lin 64507 37458 38741 14525 North LM2500 Lin 19395 47449 26593 9083 North LM2500 Lin 78129 14006 61427 40660 East GF2345 James 53629 29061 8328 30525 West LM2500 Debbie 14123 77703 73918 8212 West NX900 James 34536 28172 62802 35883 East GF2345 James 12367 20076 20362 20461 East LM2500 James 17658 17745 57456 32314 West GF2345 James 33513 14224 14345 68831 West GF2345 James 68671 6420 68081 11079 East GF2345 Lin 27589 19950 12394 72828 East LM2500 Rajiv 64501 36976 53876 16049 East LM2500 Debbie 22175 68444 10947 7024 West LM2500 Rajiv 47533 6727 58743 48662 West GF2345 Rajiv 14186 9514 54950 31146 East LM2500 Debbie 43228 15315 40807 8276 East NX900 Rajiv 65036 14232 50713 76449 West NX900 Debbie 54615 20357 12369 76110 East NX900 Debbie 22080 67035 26276 78696 West GF2345 Rajiv 17681 59567 6734 74387 East GF2345 Lin 52114 76303 17451 6409 East NX900 James 35045 30494 7598 27159 West GF2345 Lin 8449 55596 64052 58126 West LM2500 Rajiv 67271 43822 37573 55990 East LM2500 James 18889 35109 36865 69004 East NX900 Lin 31816 57220 49395 72444 East NX900 Lin 17902 6540 21386 33961 East NX900 Lin 37350 31823 51896 65587 West NX900 Lin 14964 27875 48833 50079 West NX900 Lin 62219 38450 78290 30788 West NX900 Lin 25135 35552 23157 36157 West NX900 Lin 50829 12000 27698 9677 West NX900 Lin 35815 16545 24200 10536 West NX900 Lin 7726 25925 13970 64408 East NX900 Rajiv 17686 78471 67481 26649 East NX900 Rajiv 27771 71623 25190 68459 East NX900 Rajiv 11837 32264 9571 49728 West NX900 Rajiv 19780 11655 76819 11561 East NX900 James 53055 27618 37840 58456 East NX900 James 27016 39858 61421 17607 East NX900 James 12456 45465 19658 16599 North GF2345 Debbie 35131 59999 42126 50292 North GF2345 James 44700 30904 17240 62215 North GF2345 Lin 15987 70784 56617 49560 North NX900 Debbie 42527 54243 21422 33803 North NX900 Debbie 53849 75696 36526 57763 North NX900 Lin 67679 67455 11941 47585 North GF2345 Lin 59071 35485 58407 17390 North GF2345 Debbie 71223 50261 70050 41981 North GF2345 Rajiv 63433 13511 65406 33509 North GF2345 Lin 72330 45314 5575 59144 North GF2345 Lin 19946 16446 32497 60689 North GF2345 Lin 68812 68175 54365 9268 North NX900 Rajiv 12285 65912 55444 59673 North NX900 Debbie 61142 25917 69324 26586 North NX900 Debbie 56525 13705 48846 65065 North GF2345 Rajiv 14876 37018 66839 76013 North GF2345 Lin 27132 29112 63224 63924 North GF2345 James 50993 29922 73037 54758 West GF2345 James 21769 47122 46861 64546 West GF2345 Debbie 59830 12728 54006 15639 West GF2345 Debbie 17420 8228 60634 15554 West NX900 James 28650 28994 54140 74948 West NX900 Rajiv 31562 35298 21319 9834 West NX900 Rajiv 53548 73195 17594 39554 West GF2345 Debbie 20527 19107 24477 72830 West GF2345 James 27495 68433 55447 67130 West GF2345 Debbie 27131 59730 8852 9857 West GF2345 Rajiv 47339 57437 66689 66340 West GF2345 Lin 53778 7562 58118 8229 West GF2345 Debbie 10966 64653 21355 33261 West NX900 Debbie 42487 55675 78920 29772 West NX900 Debbie 42127 22329 36837 68339 West NX900 Lin 60750 55630 78809 7947 West GF2345 Lin 30220 47404 35451 64068 West GF2345 Rajiv 6043 66636 20787 55204 West GF2345 Lin 63371 25117 5720 77380 North GF2345 James 60020 18773 64003 10719 North GF2345 James 11621 19461 62424 54220 North GF2345 Lin 52896 36700 68616 5843 North NX900 Rajiv 58455 10395 40201 44819 North NX900 James 73557 41819 52598 51354 North NX900 Lin 54062 63465 53561 21995 North GF2345 Lin 35485 17991 75164 23739 North GF2345 Debbie 20982 36375 61748 68687 North GF2345 Rajiv 44466 10132 72919 64978 North GF2345 Debbie 70961 36826 77532 77317 North GF2345 James 45957 15835 47303 59801 North GF2345 James 45275 38861 71799 65342 North NX900 Rajiv 29662 21453 50808 45183 North NX900 Rajiv 78491 47069 53873 15134 North NX900 Rajiv 7134 62163 9942 58339 North GF2345 Lin 17540 8786 54245 6333 North GF2345 James 61357 41401 19212 9035 North GF2345 Debbie 7158 70438 10575 66929 West GF2345 Rajiv 52168 10059 63842 19011 West GF2345 Rajiv 32046 41650 77831 8445 West GF2345 James 42272 78236 25183 35514 West NX900 Lin 18692 77392 34900 26113 West NX900 James 28873 47472 23446 40718 West NX900 Rajiv 73690 21300 13633 59268 West GF2345 Lin 23283 74033 52993 26959 West GF2345 Lin 14743 62499 59440 61792 West GF2345 Lin 40151 25646 21595 14668 West GF2345 Debbie 35546 37399 39389 32592 West GF2345 James 34805 69695 44285 71712 West GF2345 James 13708 17307 11984 71481 West NX900 James 54565 41297 68399 38964 West NX900 Lin 67581 41411 19470 6891 West NX900 Debbie 34491 41187 36744 36335 West GF2345 James 62920 34551 57371 36684 West GF2345 James 62496 60634 53961 14516 West GF2345 Rajiv 17065 27718 37020 29223 North GF2345 James 68579 36915 7843 51863 North GF2345 Debbie 76653 12304 54819 24301 North GF2345 Rajiv 68396 6387 21275 32460 North NX900 Debbie 68425 25235 69117 45039 North NX900 Debbie 44115 70543 59574 76058 North NX900 Rajiv 60817 65230 11896 60630 North GF2345 James 10853 13443 7764 40766 North GF2345 Lin 44708 13901 8275 35395 North GF2345 Debbie 29419 37565 33580 18231 North GF2345 Debbie 35719 41674 8497 66143 North GF2345 James 42507 41248 64005 13310 North GF2345 Lin 74745 27380 47499 54709 North NX900 Rajiv 10119 65259 9819 16809 North NX900 Debbie 30608 23724 47875 52140 North NX900 Rajiv 49833 72389 55269 77684 North GF2345 Debbie 43424 58555 74302 12204 North GF2345 James 14360 42852 25307 24619 North LM2500 James 72785 6316 53599 8961 West LM2500 Debbie 12987 14619 49120 29249 West LM2500 James 26008 73721 58841 18503 West LM2500 James 46423 32348 48129 51762 West NX900 James 10712 36417 21581 58623 West NX900 James 53236 54819 17303 15538 West NX900 James 6525 65121 68876 77078 West LM2500 Lin 78932 10880 54418 18857 West LM2500 Rajiv 20524 36889 7452 32448 South NX900 James 54849 28497 18145 63120 South NX900 Lin 12652 10727 75753 75640 South GF2345 James 32188 11195 31112 76639 South GF2345 Lin 47855 32976 65781 78723 South GF2345 Rajiv 18890 53870 69273 32381 South GF2345 James 42019 50240 59625 64866 South GF2345 Lin 38345 47676 16093 6948 West LM2500 Debbie 6664 23389 40649 57981 West LM2500 Rajiv 36825 13319 76704 59509 West LM2500 Rajiv 53638 77849 8491 20519 West LM2500 Debbie 12145 22888 10767 65690 West NX900 Rajiv 48982 11441 78664 16753 West NX900 Debbie 7151 32934 69907 70485 West NX900 Debbie 15970 7572 64993 66002 West LM2500 Rajiv 37646 19937 27729 37412 West LM2500 Lin 66627 37265 11889 62810 West LM2500 James 65136 25714 40516 12301 North LM2500 Lin 58016 74865 63225 23740 South LM2500 Rajiv 23936 71334 48104 25508 South LM2500 James 60905 48247 8907 10439 North NX900 Lin 41746 77889 64150 74497 North NX900 Lin 62332 27499 10040 50444 North NX900 Lin 23274 68410 65152 48212 North LM2500 Lin 54672 45351 25760 54386 North LM2500 Lin 47635 66195 48184 42476 North NX900 Rajiv 15808 68020 44502 56529 North NX900 Rajiv 73808 35547 68721 57195 North NX900 Rajiv 74951 19341 31710 66326 North LM2500 Rajiv 51066 28844 68101 29199 North GF2345 James 23619 34189 46491 6741 North GF2345 James 28212 59512 47419 60453 South GF2345 James 12088 66961 15657 46752 South GF2345 James 16484 70759 59206 43564 South GF2345 Lin 60151 76812 17318 26560 South NX900 Rajiv 61865 67236 51953 24896 South GF2345 James 42544 35172 48450 28035 South NX900 Lin 63579 69851 66599 43587 South NX900 Rajiv 13977 68937 13753 19262 South NX900 James 31482 15326 64427 73743 South GF2345 Lin 19962 17982 34372 67866 South GF2345 Rajiv 31028 56047 56541 18899