elaborate a one to two (1-2) paragraph max analysis from your work up to this point. Your report should consider: What business recommendations will you give Label XYZ in regards to the marketing/promotional proposal? Support your report based on the facts found through your analytics work. Wear the hat of a Business Analyst and help decision-makers at Label XYZ make the most valuable and perhaps correct decision and use of the company's resources.

THE MUSIC RECORD LABEL Founder of Music Label XYZ decided to invest some marketing money, but before signing the budget, asked the exec team for a descriptive analysis of sales to date of the catalog. Use XYZs License to Microsoft Suite (Excel and Word) and data provided to prepare the data and generate the report using the following guidelines and requirements. ***************************************************************************** Note: Values in tables, pivot tables, calculations, and graphs in the final should MATCH. For grading purposes do not replace formulas with resulting values. Aesthetics and final formatting (colors, chart formats, styles, and symbols, etc.) are only a suggestion. Total freedom to adapt to your style, improve on the work, and present as if you would in a real-life situation. ***************************************************************************** # 1. Starter Files (2 files) 5 Pts Download starter_SALES.txt Download starter_COUNTRIES.csv Open Excel and select Open from left pane. Select Browse and navigate to folder where you downloaded starter files. In the file explorer, select to see ALL FILES (*. *) to be able to see *.txt files. Select file starter_SALES.txt, follow the Text Import Wizard. o Follow the Text Import Wizard (3 Steps): File DELIMITED, Start Import Row 1, Check My Data has headers -> Next Delimiter = Tab -> Next WIZARD: Colum Data Format = General -> Finish See images below for additional guidance. Your file should have a total of 93,489 rows. Open from within Excel the starter_COUNTRIES.csv file. o Move or Copy worksheet to workbook -> starter_SALES.txt (move to end) In workbook starter_SALES.txt rename worksheets: o starter_SALES to SALES o starter_COUNTRIES to COUNTRIES. From here on, you should only have 1 file to work on with both data sets in different worksheets. Save as LAST_NAME_Exam1.xlsx o Make sure to save as Excel Workbook Dont forget to save your work frequently. # 2. Formatting & data expansion 20 pts Format both worksheets as TABLES, using the same naming convention. o Table Name: SALES and Table Name: COUNTRIES In the SALES worksheet: The report requires the use of full country name, region, and subregion. The SALES table currently records countries using the ISO3166_1_Alpha_2 coding convention. Use a VLOOKUP formula to extend the data in the SALES worksheet to include to the right of Delivery Country column (the last column in the worksheet) 3 new columns titled as follows: o Country -> use (official_name_en) from COUNTRIES (Column 2) o Region -> use (Region_Name) from COUNTRIES (Column 37) o Subregion -> use (Sub_region_Name) from COUNTRIES (Column 40) Using the filtering functionality of the TABLES, double check the values for newly created Column: Country in the SALES worksheet. Did you get 0 and/or #N/A ? Filter the data and fix the data accordingly with the following steps: o The COUNTRIES table is missing official_name_en, Region and Subregion in Row 2 (TW). Fill in TW -> Taiwan; Asia; Eastern Asia respectively (3 rows will be fixed after this, see last 3 rows of your filtered data) o In the SALES worksheet, change UK to GB in the Delivery Country using Find and Replace all, remember to select the specific column first, to fix #N/A. (57 Replacements) o In the SALES worksheet, change EN to GB in the Delivery Country column again. Use Find and Replace all, remember to select the specific column first, to fix #N/A. (2 Replacements) # 3. Days to report Analysis 1. 25 pts Some PARTNERS (Stores) are taking too long to report sales after the sales date. Please find out the top ten (10) and bottom ten (10) reporting partners by AVG days, months, and years to report. Include as well max, and min for AVG Days for top and bottom reporting partners. Follow these steps: Additional data prep in SALES worksheet: Add a Column after column B and call it DAYS_TO_REPORT. Using the DAYS() function, calculate the number of days it takes to report for each row transaction. (# of days between Sales Date and Report Date) The resulting value, if formula is built correctly, will be another date. Change the Data Type of the DAYS_TO_REPORT column to General using the dropdown list in the Numbers group top menu. The result will be the number of days to report. (e.g., 49) Add 2 Columns after DAYS_TO_REPORT and call them MONTHS_TO_REPORT & YEARS_TO_REPORT Populate these columns using the =DATEDIF() function. The third argument of the formula (interval) requires m for Months and y for years, respectively. o Note: The DATEDIF() function will not show up in your quick access box, so you will need to type this in manually. The syntax is: =DATEDIF(start_date, end_date, interval) Warning: If the Start_date is greater than the End_date, the result will be #NUM!. Do not worry about this now, we will fix it later after you create the Pivot table. So far you should have: (on all 93,489 rows) To the right of the Price Column, add a column, name it Sales Per Row. This should contain the Multiplication of (Quantity * Price) Create the following NAME RANGES: o DAYS_TO_REPORT using data from COLUMN: Days to Report. o TRANSACTIONS_QTY using data from COLUMN: Quantity. o SALES_PER_ROW using data from COLUMN: Sales per Row. o PARTNER_STORE using data from COLUMN: Partner Name. (Double check name ranges were created correctly either testing or via the Formulas / Name Manager pop up window) Create a PIVOT TABLE (PT) from the SALES Table in a new worksheet to show best reporters. Rename the Tab of the Pivot Table worksheet to TOP and BOT Reporting Partners. Create the Pivot using: o Partner Name on Rows o Show values for: Average, Min, and Max of Days to report. Average Months to report. Average Years to report. o Sort, using the More Sort Options in Ascending by Average Days to Report. o Notice the First Partner shows Negative days to report! Data Issue. Go to the SALES Worksheet, filter to see only the Rows for that Partner Name and manually change 1 row, that shows an error in the Sales Date column. Change the Year 9999 to 2009. That should fix the Negative number and #NUM! error. o Refresh the Pivot table, that Partner should no longer be at the TOP! o Filter the PT to show the 10 fastest reporting Partners. o Move the Pivot Table to cell B9 and on B8 add a title: PARTNERS THAT TAKE THE LEAST TO REPORT SALES o Apply Conditional format to each column of the Pivot Table independently, using the Color Scale Yellow-Green. o Do not show grand totals. o Center align data points. o Your work should look like this: Create a second PIVOT TABLE (PT) from the SALES Table in the same worksheet to show the 10 worst reporters. Hint: To make your life easier and work efficiently, copy the previous PIVOT TABLE you just created and paste in cell B23. Important, clear the filter in the drop-down menu of the new PIVOT TABLE before making any sorting and filtering. Add an appropriate title in cell B22. o Your work should look like this: # 4. Manual Calculations. AVERAGE, MIN, and MAX functions w/ NAME RANGES. 20pts Include an overall descriptive stats section for the entire SALES table to gain insights in the spread between the TOP and BOTTOM findings of the PIVOT TABLES. On the same worksheet of the PT, insert in cell B1 the following title: MUSIC CATALOG SALES - Descriptive Stats, format cell style to TITLE. Add the following text as follows: in B3 Average, B4 Max, B5 Min, B6 Total. Add the following text as follows: in C2 Days to Report, D2 # of Transactions, E2 Value of Transactions. Select B2:E6, cell style Heading 2. Calculate with Functions: o Days to Report. C3 Average days to Report (use name ranges) C4 Maximum # of days to report. C5 Minimum # of days to report. C6 add text N/A , not applicable. o # of Transactions D3 Average Quantity of Transactions, (use name ranges) D4 Maximum Quantity of Transactions D5 Minimum Quantity of Transactions D6 SUM of Transactions o Values of Total Transactions E3 Average Sales per Row (use name ranges) E4 Maximum Sales per Row E5 Minimum Sales per Row E6 SUM Sales per Row Format accordingly (numbers, decimal points, etc.) For Value of Transactions use ACCOUNTING data type. # 5. Digging deeper into the WORST reporting PARTNER (Store) 10 pts Identify the worst reporting store in your PT to calculate a few more metrics. To the right of the bottom PT of partners that take the most to report, add a title in cell I23, Should we worry about our Worst Reporting Partner? Add the following headings in consecutive rows, starting on I24: o Worst Reporting Partner o # of transactions o % of Total transactions o Value of Transactions o % of Total Value Transactions Manually calculate the number of transactions for that PARTNER. (Hint -> use name ranges and a formula to SUM IF a Given CONDITION is met. Manually calculate the % of total transactions, value, and % of value for the worst reporting partner. Same hint. Format accordingly (numbers, decimal points, currency, etc.) # 6. Digging deeper into the BEST reporting PARTNERS (Store) 25 pts Studying the 1st Pivot Table, best reporters, it looks like that they all belong to the same company. Improve the report by combining PARTNERS that belong to the same company to learn about other companies that are also good reporters. BACK to DATA PREPARATION! Prepare the data by identifying Partners that are part of APPLE COMPANY. If a Partner is either iTunes or Apple, it is part of APPLE COMPANY. In the SALES worksheet. Insert a column to the right of PARTNER NAME, call it APPLE COMPANY Populate the APPLE COMPANY column using a series of nested functions. o It can be done several ways. One way is thru a combination of LEFT, OR and IF nested functions. o Start by Identifying common characters to the left of Partners values o Then, with the OR function select these partners. o Finally wrap it in an IF. If a partner is an Apple Company then APPLE COMPANY, else leave it blank. Create a column to the right of APPLE COMPANY, call it PARTNERS AND APPLE GROUPED. Populate this last column with an IF formula. If APPLE COMPANY partner tests TRUE, then APPLE COMPANY, else the value of the Partner Name. E.g. first 15 rows: To conclude this section, create a 3rd Pivot using the following: o PARTNERS AND APPLE GROUPED on Rows (Dont forget to refresh PT to see all fields.) o Show values only for: Average Days to report. o Filter PT to show 10 fastest partners. o Place the new Pivot Table to the RIGHT of the other PT # 7. Create a Scatter Plot. 15 pts Insert a scatter plot of DAYS to Report vs. Sales per Row. Save your chart in a new worksheet, rename it SCATTER PLOT Format the Y axis to consider only positive numbers and MAX to about $ 10 (Axis Options / Bounds) Format X axis to consider only positive numbers and the MAX possible Days to Report of the data set. (1941 days) Chart style 6 Add a trend line, color it to contrast against data points. Do not forget to include appropriate Axis Labels and chart Title. # 8. TOURING SCHEDULE based on FASTEST REPORTING STORES. 15 pts. To take some of the bands on the road considering the previous analysis, plan a route considering the TOP 5 Fastest Reporting Stores using Average Days to Report and GEO location. Working the geo data in the SALES worksheet. Create NAME RANGES from the COUNTRIES worksheet for: o official_name_en o Capital Insert a new column at the end of the SALES worksheet and call it Capital. (To the Right of Sub Region) Using MATCH and INDEX functions, fill in the CAPITALS of EACH country. Hint: Use Country column from SALES and the official_name_en name range to match. Then search the index using the capital name range. DO NOT use ISO3166_1_Alpha_2 nor a VLOOKUP function. Finally, concatenate Capital, Country, Region and Subregion in another column at the end of the data set, name it DESTINATION. CREATE a Pivot Table from the SALES worksheet (Do not forget to Refresh the DATA!), rename the new worksheet TOUR. Use PARTNER NAME & DESTINATION for Rows And AVG Days to report for values. Filter to only the TOP 6 reporting PARTNERS Free formatting. Use the features from the TAB DESIGN when clicking on the pivot table. Hide Totals and Subtotals. Etc. #