Normal Title The 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: 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 Retun Revenue in column V il. 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) column M. by Shipped Quantity in column S, but only f Shipped Quantity is greater than zero. (If . SALE_YEAR in Column Y, defined as: The four digit year contained in the Order date in V. PERCENT RETURNS in Column Z, defined as: Retuned Quantity in column U divided v. NET QUANTITY in Column AA, defined as: Shipped Quantity in column S minus vi. PRICE CATEGORY in Column AB, defined as: 1' if Extended Price in column O is less Shipped Quantity is zero, the value in Column Z is to be entered as zero also.) Returned Quantity in column U than $100, '2, if Extended Pnce is at least $100 but less than $200, '3 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 mostly similar to practice problem) Copy the original sheet (Sheetl') 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 quantty in a single order? Identify year and highest net quantity 1. 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 Copy the original sheet (Sheet') containing data in all of your columns A through AB into a new sheet called 'Fiter2. 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 tho highest net revenue (top 4%)? Provide the list of products that have highest net revenues (top4%) 3. 4. This is new part: 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: What is the impact on net profit if price is raised by 15% (hint create a new column with 15% price ncrease. calculate profits based on new price-same as what you did in part i of data preparation .. and then find % change in new and old profit as (in anew column) (new net Profa-old net profit)/old net profit MacBook A Normal Title The 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: 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 Retun Revenue in column V il. 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) column M. by Shipped Quantity in column S, but only f Shipped Quantity is greater than zero. (If . SALE_YEAR in Column Y, defined as: The four digit year contained in the Order date in V. PERCENT RETURNS in Column Z, defined as: Retuned Quantity in column U divided v. NET QUANTITY in Column AA, defined as: Shipped Quantity in column S minus vi. PRICE CATEGORY in Column AB, defined as: 1' if Extended Price in column O is less Shipped Quantity is zero, the value in Column Z is to be entered as zero also.) Returned Quantity in column U than $100, '2, if Extended Pnce is at least $100 but less than $200, '3 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 mostly similar to practice problem) Copy the original sheet (Sheetl') 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 quantty in a single order? Identify year and highest net quantity 1. 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 Copy the original sheet (Sheet') containing data in all of your columns A through AB into a new sheet called 'Fiter2. 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 tho highest net revenue (top 4%)? Provide the list of products that have highest net revenues (top4%) 3. 4. This is new part: 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: What is the impact on net profit if price is raised by 15% (hint create a new column with 15% price ncrease. calculate profits based on new price-same as what you did in part i of data preparation .. and then find % change in new and old profit as (in anew column) (new net Profa-old net profit)/old net profit MacBook A