Instructions You may work with at most one other person on this assignment, though you are free to complete it individually. If you choose to work with someone, only one of you should submit the assignment on eLearning, with both of your names clearly indicated in your submission. Make sure one of you has submitted and that both names appear; there will be no retroactive joining of someone elses assignment because they forgot to put your name on the submission. You will use the file hw3 data.xlsx for this assignment, which has worksheets titled item master, inventory weekly summary, and sales order weekly. You must submit your spreadsheets in addition to your answers. Your final answers to each question should be clearly labeled, but also answers submitted with no supporting materials will receive no credit. Specifically, you should submit your final versions of each of the three files above. NOTE: Because of the way Excel handles numbers, always create a new column to the left of the Item column called Item Text. The values in this column should be equal to the item number converted to text format: otherwise, when we do XLOOKUP or SUMIF, Excel may not match the items correctly (or at all). For example, suppose that the Item Text column is Column C, and the Item column is Column D. Then, in cell C2, we put the formula =TEXT(D2,0). We paste this formula down the rest of the Item Text column. Whenever we match from one Excel worksheet or workbook to another based on Item number (e.g., if were using XLOOKUP), we will use the Item Text column for the match rather than the Item column. Sales Order File and Item Master File There is 4 weeks worth of sales order data on the sheet titled sales order weekly. In this section you will be computing some statistics for the weekly sales of each item, and then matching that information back to the corresponding item details on the item master sheet. 1. (4 points) Which item has the highest average weekly unit sales (give the item number)? What is the average weekly unit sales for this item? Hint: follow these steps... Copy the values in the Item column of the item master worksheet and paste them onto a new worksheet. Create the Item Text column as described in the instructions. Then, create columns on the new sheet for each weeks sales (Week 1 Sales, Week 2 Sales, . . . ). Use SUMIFS based on the item number and week number to compute the total sales for each item in each week, and finally take the average (dont forget 1 OPRE 4330 HW 3: Logistics Data and Data Analysis to create an Item Text column on the sales order weekly sheet also, and use it to do the matching). Then, report the item with the highest average weekly unit sales, which you can find using XLOOKUP. 2. (3 points) Compute the standard deviation of weekly unit sales for each item (use STDEV.S, not STDEV.P). Which item has the largest standard deviation of weekly unit sales (give the item number), and what is its standard deviation of weekly unit sales? 3. (3 points) The coefficient of variation is the ratio of the standard deviation to the average (be sure to put the standard deviation as the numerator of the fraction). Calculate the coefficient of variation of weekly unit sales for each of the items. Which item has the largest coefficient of variation for weekly unit sales, and what is the coefficient of variation for this item? 4. (2 points) Use XLOOKUP to match the standard deviation of weekly unit sales for each item to the other item details in the item master worksheet. For the items in the first 100 rows of item master (based on the order that the rows were in when you first downloaded the file), what is the average standard deviation of weekly unit sales? That is, create a new column in the item master worksheet and populate it with the standard deviation of weekly unit sales for the corresponding items. Then, take the average of the first 100 rows of this new column (because the column headers are in row 1, this means you will need to include rows 2-101 in your formula, and the same goes for Question 6). Inventory History File and Item Master File 5. (3 points) Use AVERAGEIF to compute the average inventory valuein dollarsfor each item (you can just create an AIV column on the item master worksheet and enter your formulas in this new column). Which item has the highest average inventory value (give the item number)? What is the average inventory value for this item? 6. (3 points) Populate the item master file with the average inventory values of the corresponding items. For the items in the first 100 rows of item master (based on the order that the rows were in when you first downloaded the file), what is the total average inventory value (i.e., what is the sum of the first 100 rows of your new average inventory value column)? 7. (2 points) Calculate the combined average inventory value for the items from each supplier (the supplier for each item is listed in item summary, and you can copy, paste, and remove duplicates to get a list of the distinct suppliers). For which suppliers items is the combined average inventory value the highest? What is the combined average inventory value for this suppliers items?