This is an excel assignment w analysis
ACTUAL EXCEL ASSIGNMENT Page 1 of 2 MERRICK SCHOOL OF BUSINESS, UNIVERSITY OF BALTIMORE INSS 300.WB2 – MANAGEMENT INFORMATION SYSTEMS SPRING 2019 ‘ACTUAL EXCEL ASSIGNMENT’ NOTE: This is the actual Excel assignment that you will need to complete and submit for grading. Before starting this assignment, however, it is highly recommended that you first complete the practice assignment provided in another document. That ungraded practice assignment is very similar to this actual assignment, and it is accompanied by a step-by-step walkthrough complete with screenshots. This assignment is based on the attached Microsoft Excel spreadsheet called ORDERS_TRUNCATED.xlsx. 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 2 of 2 Data Preparation (If you have previously completed the Practice Excel assignment, use these 6 steps and go directly to the Analysis section below.) 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 Shipped Quantity in column S) 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. Analysis 1. Copy the original worksheet (‘Sheet1’) containing data in all of your columns A through AB into a new worksheet called ‘Question 1.’ Then apply a filter to the copied data in this new sheet in order to answer the following question: Which year had the fourth highest net quantity in a single order? Provide the pertinent details. 2. Copy the original sheet (‘Sheet1’) containing data in all of your columns A through AB into a new sheet called ‘Question 2.’ Now consider ALL orders in the year you found in Question 1 above. Compare them collectively with all orders in each of the 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 ‘Question 3.’ 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 5%)? 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 ‘Question 4.’ Then apply conditional formatting to the copied data in this new sheet in order to highlight orders made by division 01 where net revenue was in the top 10% of all orders. 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? Rename the worksheet as ‘Question 5.’ 6. How does the answer to Question 5 above change if each of the four price categories in column AB are analyzed separately? Rename the worksheet as ‘Question 6.’ 7. Which specific combination of sales channel and year had the three highest average percent returns, compared to all other combinations of sales channels and years (e.g., mail order sales in 2011, or, Web sales in 2010)? What was the percent value of this third highest average return rate? Rename the worksheet as ‘Question 7.’ 8. Create a bar or column chart of average of net quantity on the vertical axis against price category on the horizontal axis. Do you observe a distinct pattern? Suggest a possible historical rationale for this pattern. Rename the worksheet as ‘Question 8.’ Submit your Excel file containing your analysis, and your Word report containing your answers.