MAJOR Excel Assignment Project

Anonymous
timer Asked: Mar 8th, 2019
account_balance_wallet $125

Question Description

READ THIS ENTIRE POSTING BEFORE ATTEMPTING ANYTHING.

This assignment comprises 15% of your total course grade. It is therefore very important that you start working on it early and complete it carefully.

Two Excel assignments are attached here -- a practice assignment, and the actual assignment. Both are based on a common Excel data file called ORDERS_Truncated.xlsx, which is also attached.

For the practice assignment, there is a step-by-step walkthrough with instructions and screenshots. [ [ [ [ [The walkthrough is available at this location] ] ] ] ] (opens pop-up window). Alternatively you may copy and paste the following link (http://home.ubalt.edu/rmirani/web/inss300/Practice_Excel_Assignment_Walkthrough.htm)

The actual assignment closely mirrors the practice assignment, and is not accompanied by detailed instructions. It is highly recommended that you first complete the practice assignment in order to smoothly complete the ACTUAL assignment


Please submit ONLY the ACTUAL Excel assignment in the form of two attachments here -- your .xlsx Excel file and your Word report.

Do NOT submit the practice assignment.

Work not deemed original will receive no credit. Submissions in any other formats will not be graded. A Word report unaccompanied by an Excel file will not be graded.

Note:

  • 10% will be deducted for each day past the deadline.
  • 70% will be deducted for a week late.
  • Assignments will not be accepted after a week.
  • There will be no make-up assignments if you fail to submit this assessment.
  • There will be no exceptions to the above rules.

Additional resources for assignment are attached below

Unformatted Attachment Preview

MERRICK SCHOOL OF BUSINESS, UNIVERSITY OF BALTIMORE INSS 300 – MANAGEMENT INFORMATION SYSTEMS Spring 2019 MICROSOFT EXCEL ASSIGNMENT Due March 17. DO NOT WAIT UNTIL THE LAST WEEK TO START THE PROJECT!!!! Time management is a Critical Success Factor for this Assignment. Total points 50 (this will be converted in a percentage for your final grade in Sakai - e.g. 50/50=100%) It is highly recommended that you first complete the practice assignment (see assignment instructions in Sakai) in order to smoothly complete this module. This assignment is based on the Microsoft Excel spreadsheet called Orders_Truncated.xlsx also posted with your assignment. In this assignment, you will first add several new columns to the existing data fields and populate them with computed variables using appropriate Excel formulas. Next, you will analyze the data in the spreadsheet using Excel features such as data filters, conditional formatting, and pivot charts. You will also prepare a word-processed report documenting your answers. The attached spreadsheet contains sales orders data for a specialty products company that offers numerous products under several product categories through three channels – phone (PH), mail order (ML), and online/Web (WE). The original spreadsheet contained hundreds of thousands of records. It has been pared down to 4068 records for the purpose of this assignment. Each row pertains to a single item ordered by a single customer. There are 22 columns (data fields), as follows: Page 1 of 4 PART A: Data Preparation (this part is the same as practice problem) Add the following new columns to the right end of the Excel spreadsheet, and populate them with computed data by applying appropriate formulas, based on the following information. i. NET_REVENUE in Column W, defined as: (Extended Price in column O times Quantity in column Q) minus Return Revenue in column V. ii. NET_PROFIT in Column X, defined as: (Shipped Quantity in column S minus Returned Quantity in column U) times (Extended Price in column O minus Extended Cost in column N). iii. SALE_YEAR in Column Y, defined as: The four digit year contained in the Order date in column M. iv. PERCENT_RETURNS in Column Z, defined as: Returned Quantity in column U divided by Shipped Quantity in column S, but only if Shipped Quantity is greater than zero. (If Shipped Quantity is zero, the value in Column Z is to be entered as zero also.) v. NET_QUANTITY in Column AA, defined as: Shipped Quantity in column S minus Returned Quantity in column U. vi. PRICE_CATEGORY in Column AB, defined as: ‘1’ if Extended Price in column O is less than $100, ‘2’ if Extended Price is at least $100 but less than $200, ‘3’ if Extended Price is at least $200 but less than $300, and ‘4’ if Extended Price is greater than $300. PART B: Analysis (this part is different but similar to practice problem) 1. Copy the original sheet (‘Sheet1’) containing data in all of your columns A through AB into a new sheet called ‘Filter1.’ Then apply a filter to the copied data in this new sheet in order to answer the following question: Which year had the highest net quantity in a single order? Provide the pertinent details. 2. Now consider ALL orders in the year you found in Question 1 above. Compare them collectively with orders from other years. Provide two meaningful observations about this particular year from your comparative analysis. 3. Copy the original sheet (‘Sheet1’) containing data in all of your columns A through AB into a new sheet called ‘Filter2.’ Then apply a filter to the copied data in this new sheet in order to answer the following question: Which product categories pertained to orders with the highest net revenue (top 2%)? Provide the pertinent details. 4. Copy the original sheet (‘Sheet1’) containing data in all of your columns A through AB into a new sheet called ‘What-IF.’ Idea is to study the What is the impact on net profit if price is raised by 10%. (hint: create a new column with 10% price increase, calculate profits based on new price –same as what you did in part ii of data preparation -- and then find % change in new and old profit. Page 2 of 4 PART C: Pivot Charts --Create pivot charts to answer questions 5 through 8. 5. In how many distinct years during the period 2009-2012 did product category P generate the highest total net profit, compared to other product categories? Which years were they, and what were the total net profits for product category P in those years? 6. How does the answer to Question 5 above change if each of the four price categories in column AB are analyzed separately? 7. Which specific combination of sales channel and year had the highest average percent returns, compared to all other combinations of sales channels and years (e.g., Web sales in 2010, or, mail order sales in 2009)? What was the percent value of this highest average return rate? 8. Create a bar or column chart of average of net quantity on the vertical axis against price categories on the horizontal axis. Do you observe a distinct pattern? Suggest a possible business or consumer rationale for this pattern. Submit your Word report containing your answers AND your Excel file containing your analysis. Upload first the Word file, then upload the Excel file and, finally, submit your assignment. Grading Rubrics: Requirements Part A: Data Preparation Maximum Points 10 Part B 1 2 3 4 20 5 5 5 5 Part C 5 6 7 8(make sure to provide rationale; see question) 20 4 5 5 6 TOTAL 50 Page 3 of 4 Your Points Additional Resources available: ..Achievement and Learning Center (410-837-5383) … Free EXCEL Tutorial http://www.gcflearnfree.org/excel2016 (go through lessons 1,5,6,7,10,13,15,16,20,21,23,24,27,28) Page 4 of 4 CUSTNO 10000004365 10000004365 10000028727 10000048890 10000049377 10000085017 10000094635 10000094635 10000549062 10000094635 10000108981 10000114600 10000138269 10000185880 10000204286 10000214525 10000220934 10000225092 10000225092 10000225092 10000226197 10000230858 10000798442 10000244397 10000265369 10000286036 10000297798 10000307296 10000311165 10000320216 10000323384 10000335089 10000335865 10000354847 10001268190 10000357802 10000357802 10000357802 10000357802 10000358107 10000365557 10000369030 10000374725 10000377075 10000381835 10000391660 ZIP 10021 10021 53217 77043 34655 35216 92130 92130 91210 92130 18603 33759 02886 32751 90254 23503 38242 91210 91210 91210 37065 92626 27615 47901 90066 45013 48116 95493 94521 02339 10983 27104 80129 60015 20854 96815 96815 96815 96815 37397 53217 91210 32503 29455 46228 75062 ORDER_LINE 1 8 1 1 1 2 1 1 1 2 1 2 1 1 1 1 1 4 1 1 6 1 1 1 2 1 2 1 1 3 3 1 1 1 2 2 5 1 1 1 2 3 1 1 1 1 ORDER_NO 128512068 128512068 131922138 129264138 128883092 124464135 124431317 128769497 131755419 131932934 128671648 128163017 125277943 128526310 127988079 127931001 128474838 129142063 128982630 129252017 128042692 129209978 128445222 128452994 128202539 128828941 130980838 128182041 128416898 129945460 128114850 128463456 128569662 128539096 131094837 124423991 128191474 129301515 131969774 131552492 130676883 128285344 127867594 128298442 131160806 128465914 PRODUCT_NO 984255 981042 991205 985806 983048 982921 980843 984571 988805 992476 984685 982921 976140 983048 980674 975592 983758 975883 985942 987056 978602 985814 981329 983695 980393 985223 987157 982925 983060 985165 972897 983703 989352 984765 985388 981057 982938 984601 991574 989392 991250 983586 963468 983268 988604 983527 BO_DATE CANCEL_DATE 3/18/2009 11/18/2008 6/7/2007 11/24/2009 11/29/2006 9/12/2012 6/11/2007 12/15/2006 10000397921 10000428100 10000430535 10000435402 10000445828 10000463586 10000477552 10000485076 10000486165 10000486165 10000486280 10000494620 10000495469 10000495469 10000501227 10000515829 10000521103 10000521103 10000526688 10000536992 10000542289 10000544500 10003798762 10000563912 10000570892 10000574950 10000600591 10000606283 10000611325 10005531287 10000611325 10000619716 10000623155 10000656872 10000661781 10000663482 10000666837 10000672631 10000684865 10000697807 10000714498 10000721702 10000734933 10000741171 10000757011 10000757068 10000757068 60640 60016 06018 41075 29926 02881 25927 48105 72758 72758 02920 11743 30606 30606 15237 61102 70118 70118 37205 43023 34242 98023 13820 48640 98119 01864 94536 70360 28374 73402 28374 22551 84117 85750 91723 19128 90265 33609 48708 54601 08054 42211 06422 98506 48236 45840 45840 1 1 2 1 1 1 1 2 2 3 1 1 1 1 1 1 1 2 2 1 1 1 2 1 1 2 1 1 1 3 1 1 1 1 1 3 1 1 1 2 1 1 1 1 1 3 2 131201615 131950058 131367653 131074438 131758405 128418034 128571786 131106925 128630652 128675237 131961442 129287228 129946541 129998403 129204604 128854119 131068587 131833941 128275195 124457470 128107203 129868671 129069734 129931896 128090633 125752528 127878160 128796225 129125741 128703935 131633549 128864075 129269344 128980621 128927754 131366179 128511827 129970585 129239741 129878180 124924786 129301838 129089540 127501083 128159044 131679643 131747646 988080 991484 989226 985495 991826 983121 982697 988325 983764 983653 984977 981786 986286 988978 985927 984972 984094 985060 983471 983136 981930 982143 985324 985380 982712 984861 981541 984082 985774 986796 985781 982731 983130 984933 981364 989434 984034 987650 985781 981849 983149 987023 981786 984977 975550 991124 991123 12/12/2011 12/15/2011 2/7/2007 2/17/2009 11/26/2006 10000757068 10000757068 10000757068 10009106730 10000759264 10000759264 10000761211 10000771647 10000779129 10000779213 10000791677 10000795975 10000798442 10010791897 10000827574 10000838662 10000868222 10000936448 10000936539 10000944913 10000985966 10000992376 10000995775 10001011804 10001017761 10001025944 10001025944 10001027372 10001027372 10001046091 10001046091 10001053534 10001055605 10001056478 10001073673 10001090311 10001090467 10001090467 10001100731 10001109699 10001111283 10001111283 10001111283 10001111283 10001111283 10001111283 10001111283 45840 45840 45840 10128 05201 05201 10010 85258 15668 22003 55120 55001 27615 60610 62230 31411 75440 54501 32837 28677 33614 76008 15241 45331 11357 85250 85250 87120 87120 06880 06880 32312 44124 48084 55405 55345 92106 92106 85719 46802 80228 80228 80228 80228 80228 80228 80228 2 3 3 1 3 3 1 4 3 2 4 1 1 1 2 1 1 1 1 1 1 2 1 1 1 2 1 1 1 1 4 2 2 1 1 1 1 2 1 1 2 3 1 3 1 4 1 131798464 131827384 131883900 131509715 128297194 128747122 127939912 128290017 131357349 131703893 128879872 128152591 128129983 131404982 127965577 131545278 128066338 128062476 131198798 131747826 129207122 129297132 127962473 131381921 131617555 131473794 131846429 128186110 128393853 128487467 131220456 131644753 130995505 131954707 128198018 128932859 127954097 131271351 128085684 129209099 124416937 127869679 128403279 128642448 128860630 129085651 129271539 991123 991123 987942 988612 981304 981298 975593 989796 986526 988138 982921 982900 981329 985807 981042 987863 988392 970684 988624 991501 986777 987643 980459 989230 987056 989578 991454 981074 983180 983076 987652 989327 987393 992389 982959 983400 982579 984158 971930 986682 982465 982152 983663 983358 983890 991234 986163 7/22/2010 7/23/2010 7/8/2009 7/17/2006 12/1/2009 7/20/2008 10001111283 10001111283 10001111283 10001111283 10001111283 10001111283 10001114724 10001114724 10001114724 10001118518 10001126857 10001137444 10001137444 10001140282 10001146346 10001169875 10001169875 10001184039 10001194878 10001196949 10001196949 10001199912 10001200547 10001200547 10001202527 10001202527 10001209853 10001218625 10001218625 10001259272 10001268190 10016011452 10016046044 10001291132 10001291132 10001313358 10001313358 10001313358 10001322779 10001356383 10001382147 10001385622 10001425344 10001425344 10001425344 10017284328 10001470588 80228 80228 80228 80228 80228 80228 75022 75022 75022 43209 60611 60422 60422 15235 15206 06897 06897 60118 98277 91362 91362 93953 45429 45429 21093 21093 01505 30301 30301 34232 20854 33480 01938 91345 91345 34475 34475 34475 02030 45067 90230 37221 85284 85284 85284 10605 18702 2 1 2 4 1 2 1 1 2 2 1 1 6 1 3 1 4 1 1 2 3 3 1 1 4 4 3 1 1 2 1 1 1 1 1 1 2 2 2 1 2 1 2 2 4 1 3 131278871 131365396 131603705 131610123 131857110 131946470 125802003 131106718 131473143 127937406 131517559 130021593 130021593 129984854 128804566 127916664 127924702 127526400 129909627 124764333 128830768 129053678 129069863 129242480 128456153 128939124 128029520 129045431 131380257 125191702 128922994 131667515 128991949 129111302 131428541 124375839 128143834 128172184 127646951 128124614 124419670 129983390 128450299 129266842 131143188 131090806 128116196 984508 981930 991177 988234 991942 991145 984883 988058 985878 975592 989531 981602 981603 984972 984508 988030 987662 986824 970664 982878 991149 983149 983266 986719 983888 985091 982334 981043 980393 982942 983685 988822 970686 985940 986706 982130 975592 977231 988145 976018 972164 987628 982945 985179 985179 986870 980928 3/10/2010 4/5/2010 10001470588 10001503133 10001503133 10001503133 10001506202 10001511853 10001516565 10001522858 10001522858 10001522858 10001532907 10001532907 10001532907 10001607751 10018307872 10001630397 10001657376 10001665567 10001667547 10001677275 10001677275 10001724834 10001772456 10001784773 10001789967 10001799424 10001799424 10001835105 10001840245 10001858519 10001873634 10001893934 10021285049 10001896962 10001896962 10001910979 10001952256 10001962031 10001970537 10001975132 10002000534 10002013139 10002014550 10002034827 10002047114 10002047114 10002059134 18702 11720 11720 11720 15068 89102 85365 95642 95642 95642 07405 07405 07405 10512 33304 93921 81435 28804 78028 77080 77080 55432 32960 40475 74136 89142 89142 70001 07305 55439 91711 92646 01720 59868 59868 11509 12550 22216 48035 08628 13850 12983 14086 11365 85020 85020 37064 2 1 8 6 4 1 1 1 2 1 15 4 11 1 1 1 2 1 1 3 1 1 3 1 4 1 1 1 2 1 1 1 1 3 9 2 2 1 1 1 1 2 1 2 1 3 1 128541324 128110765 128110765 128226330 127902124 128489431 129883823 128321480 128789806 128904863 131813765 131813765 131813765 128603301 127482875 131695813 129193752 128296095 131745948 124502145 129864967 129174163 125123482 129074915 128887101 128890355 131530516 128202763 128396710 124322563 128540590 128158017 131413624 127860792 127860792 125037190 128434640 124826610 131090381 131565122 125164843 128140755 129935992 124448658 128909095 130990664 131278788 982206 982724 974052 983139 970161 984141 982728 982641 984925 985439 991848 989637 992325 983890 986620 992296 986611 983568 989150 982573 982089 981124 983695 980274 984962 978183 989327 982752 983435 970585 976140 982982 988137 970493 975873 976120 982619 983266 980274 989658 970585 982938 975592 983335 984324 988242 989420 2/13/2006 1/28/2012 1/30/2012 6/17/2009 11/25/2006 2/8/2007 5/29/2007 2/8/2005 4/16/2007 10002060140 10002060140 10002060140 10002098865 10002118402 10002126644 10002167811 10002189945 10024052613 10002233019 10002265805 10002265805 10002296477 10002296477 10002310660 10002318275 10002345062 10025048362 10027421690 10028048674 10030593475 10002455671 10002476701 10002505805 10002507397 10002515919 10002528072 10002528072 10002538014 10002539259 10002565512 10002565512 10002568044 10002571419 10002571419 10031582924 10002595819 10002606703 10002621718 10002630463 10002651368 10002671004 10002683577 10002706693 10002706693 10002733150 10002733150 75225 75225 75225 07059 30341 11590 33480 27028 84604 60012 98512 98512 33029 33029 19073 27407 14420 07410 83353 08734 45069 10708 80906 45177 07728 14057 48642 48642 95219 10301 94608 94608 07010 61036 61036 97206 33165 02138 84094 11570 72310 92116 33837 93110 93110 07677 07677 1 1 3 1 1 1 1 1 2 5 2 3 1 11 2 2 1 2 1 1 1 1 2 2 3 1 1 5 2 1 1 1 1 1 1 3 1 4 1 1 2 2 2 5 5 2 1 128749823 131366033 131936019 124495440 131090859 128283393 131807725 128193491 124467598 128255440 124454742 125497180 128988286 128988286 127920645 128064052 124423405 128228797 128903987 131114448 128629323 128228661 128481482 128659020 124403702 124461011 128023479 128260792 128230942 131182114 129217322 131076720 129022369 125738874 131579394 124475065 128292055 124819681 128931814 128613042 128801905 128326893 129273081 128628597 131702881 128709413 129132252 983665 987367 988830 982650 980274 983182 989465 982190 982657 975379 982476 984513 986543 989247 982049 973128 983388 980374 985299 987307 920957 982895 982945 984366 982234 983311 986558 983141 982242 986656 985643 985643 976494 984679 982775 983260 983149 982922 985233 983632 984922 921738 986996 984372 988844 984170 985636 7/2/2007 5/28/2007 6/11/2010 11/24/2006 12/22/2007 10002733150 10002733150 10002762298 10002762298 10002762298 10002762298 10002762298 10002771762 10002771762 10002800898 10002828982 10002848427 10002868928 10002888582 10002888582 10002890659 10002907067 10002910725 10002911491 10002912416 10033150934 10002924814 10002934599 10002938775 10002941537 10002941537 10002959060 10002985742 10002994270 10003046651 10033258357 10003057120 10003075327 10003081005 10003083464 10003083464 10003083464 10003086441 10003086441 10003086441 10003105622 10003127633 10003132220 10003141502 10003154947 10003158628 10034507771 07677 07677 20902 20902 20902 20902 20902 11030 11030 80909 84124 49670 11021 46814 46814 45140 07631 03301 32256 49444 90049 53703 92130 60540 29201 29201 33445 95020 21117 46544 01773 37701 10511 97219 76120 76120 76120 46725 46725 46725 37350 92844 07090 70601 93460 45869 60015 1 1 1 1 1 3 1 1 1 2 3 1 1 1 3 1 4 1 1 1 1 3 2 1 4 1 1 1 1 1 1 4 1 4 6 1 1 7 4 7 1 2 1 3 2 2 1 131169234 131914372 128519727 128395210 128894245 129109092 131230536 128440076 129162747 128464400 128301943 128455879 128466969 128479484 131408614 128972690 128529029 128599643 129026875 131794454 131664198 128852024 128003761 126390055 128018943 128438337 128399002 129877155 128494043 128389702 131688295 129922365 128208605 126408777 128529110 129196351 131521707 124439409 129310087 131657240 128879783 131256370 128097764 131478427 131651878 131600049 131184620 988632 992353 984334 976795 976140 985898 988805 982863 981124 983699 983122 974279 983757 982876 986782 984632 983619 982563 985799 989763 991523 984619 980152 984094 974278 982920 982937 980550 975592 983653 989226 983888 927158 975592 982697 985380 989889 982645 984334 987197 984094 988379 982457 984325 991329 988075 988327 9/6/2007 6/29/2007 7/3/2010 9/7/2007 10003182563 10003182983 10003217500 10003264509 10003303794 10036018065 10003338520 10003351002 10003414749 10003420028 10003431489 10003431489 10003447305 10003464746 10003481262 10003525051 10003525051 10003525051 10003539159 10003547386 10003601330 10003601330 10003637853 10038486367 10003698796 10003708201 10003724238 10003750013 10003758255 10003767807 10003782560 10003794907 10039268394 10003805692 10003819734 10003820766 10003827712 10003838980 10003841746 10003845722 10003900418 10003906680 10003936479 10003951890 10003954580 10040728599 10003972070 60062 01550 99203 06897 43085 32607 14222 20814 46540 03878 30533 30533 48864 90740 10458 96753 96753 96753 15050 47448 20770 20770 28056 70381 43214 46220 92227 31211 11955 46902 23005 94574 06877 19010 60448 75115 19707 89146 77042 13165 30740 46236 19968 98563 10021 32963 47225 2 1 2 1 2 1 1 1 1 1 1 2 1 1 1 3 2 6 2 1 1 2 1 1 1 1 1 2 1 1 3 1 3 3 1 2 1 1 1 1 2 1 4 1 3 2 1 128189228 128634092 128360082 129262290 128979612 129134217 128269348 129229230 129935554 128749165 126167751 131122009 128107622 129958936 128039048 124288123 125097057 126088268 129215351 128133631 129308231 131306378 124380942 129114888 128419306 129984293 131589511 131524424 131580650 127854202 129962265 128170793 127796663 129253541 128355435 131198848 127927692 128024770 128582057 131609810 127927359 124477023 128046242 128309945 128868903 131568912 129016376 980843 984300 982503 986443 985762 986164 980843 986554 985425 984300 985986 988179 982913 984972 982619 981758 9836 ...
Purchase answer to see full attachment

Tutor Answer

Robert__F
School: UT Austin

Please let me know if there is anything needs to be changed or added. I will be also appreciated that you can let me know i...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors