Excel Spreadsheet

Turner Management Training, LLC sells online management training books and streaming videos to customers in the U.S. who want to improve their management skills. The company promotes its products in two ways: through (1) direct email and (2) web-based banner ads. Your manager has asked you to analyze sales data and make recommendations to improve its overall sales based on the results of the analysis. To get started, download the Excel data file A1_TurnerManagementTrainingSales.xlsx. The data file includes sales transactions for a single day. Other data include Customer ID, Region of Purchase, Payment Method, Source of Contact (e.g., did the customer respond to a direct email or a web-based banner ad), Amount of Purchase, Product Purchased (either online book or video), and the Time of Day of each sale (24-hr format). Instructions: Part 1: Data Analysis Review the data elements in the file and then create PivotTables and Charts to answer the following analysis questions. Note the PivotTables and Charts can be stacked on a single worksheet labeled “Analysis.”. Clearly label PivotTable and Chart that correspond with the question (see examples below). NOTE: the PivotTable and Charts need to correctly show the unit of measure (for example, if the question is asking for the number of customers then the unit of measure is “Count”; If the question is asking about sales, then the unit of measure is total sales or average sales, depending on the question). Analysis Questions: 1. What region do most of Turner Management Training Company’s customers come from? 2. Are there regional differences in total sales ($$) by the source of contact (e.g., which type of ad - email or web-based banner ads- do customers in in each region response most to)? 3. What type of payment method is used most frequently to, and does it differ by the source of contact (whether the customer responded to an email ad or a web-based banner ad)? Use total sales volume in $$. 4. What times of day have the highest product sales and does this differ by source of contact? Use a line chart to illustrate sales by hour based on source of contact (email vs. web-based banner ads). 5. What percent of total sales does each product (online book or video) represent and are there regional differences in the type of product customers purchase? Part 2: Summary and Recommendations On a separate worksheet (labeled “Recommendations”), insert a textbox and summarize the results of the analysis followed by your recommendations (see example below). Your summary should identify sales trends by product and by region, and reference results of your analysis (e.g., The East Region has the highest volume of overall sales with streaming video accounting for 35% of total sales and online books accounting for 65% of total sales). Following the summary, recommend at least three specific actions you think the company should take (e.g., Is there a region the company should focus its marketing efforts on to increase product sales?). Include relevant Pivot Charts to support your recommendations.