Instructions- 1. There are 2 deliverables for this assignment- a) An excel file with the necessary computations. b) A word doc with the corresponding explanations. (For the question involving macro, a separate file could be submitted. However, a single excel file is preferred). 2. Solve each question in a separate sheet in one excel file. 3. You’re required to state all your assumptions in the first sheet of the excel file. These assumptions should be written for all the sub-parts separately, however in the same sheet, wherever applicable. 4. Maintain uniformity in structure and formatting. The assignment would be penalized for lack of hygiene. 5. Name all your sheets appropriately. 6. Naming Convention for the file Name_Roll_No_Section.doc/.xls. Example- Hermione_Granger_91023_G.xls
Assignment-
i) Use column V to create a new character variable of length 10- ABCDEFGHIJ where AB=Region, CD=Province, E=Product Category, FG=Customer Segment, H=Ship Mode, I=Order Priority, J=Product Container. (You’re free to use your own nomenclature. Specify the same in the assumptions sheet. Eg- Use initial 1(or 2) characters of the corresponding variable.) ii) Summarize the data using a pivot table by region and province. Subsequently, find the province with highest profit per shipment and highest profit per Unit. iii) Create a pivot table where you provide a summary of sales, cost and profit statistics. How will you summarize? State the variables used as row labels, column labels and filters. Justify your selections. Do you think it would make sense to use some of the statistics variables in percentage terms? If yes, create those variables in the pivot table. (Note: The new variables are to be created in the pivot table itself. You’re not supposed to create the variable in the raw dataset and then using it in the pivot table). iv) Find