Excel homework marketing

Marketing Research

Utilizing the Homework2.xls attached at the bottom, answer and perform the following questions and summarizations.

Within the excel file, label each request (1a, 1b, etc.) as a separate worksheet within the excel file. Make sure to answer the questions asked within the sheets and not just do the tables and charts. Before jumping right into the questions, it is recommended that you look at the data and the fields first to understand what the information is and its importance.

1.      The Makeup worksheet gives sales data for a small makeup company. Each row lists the salesperson, product sold, location of the sale, units sold, and revenue generated. Use this data to perform the following exercises:

a.       Summarize the total revenue and units sold by each person for each product. How many lipsticks did Betsy sell? What is the total amount of revenue for Jen?

b.      Summarize the percentage of each person’s revenue sales that came from each location. Create a PivotChart to summarize this information. What area is Cristina’s strongest?

c.       Summarize each girl’s unit sales (not revenue) by location and use the Report Filter to include the subsets of products. What location has the most number units sold? What location has the most unit sales of eyeliner?

2.      The Tape worksheet contains data for the unit sales of 3M tape, price charged, whether an ad campaign was run that week (1 = ad campaign), and whether the product was displayed on the end of the aisle (1 = end cap). Use this date to perform the following exercises:

a.       Analyze the nature of the monthly seasonality of tape sales. Use average monthly sales for your table and chart. Is there a season where tape sales is high? Low?

b.      What are the total unit sales using an ad vs. no ad? What are the average unit sales using an ad vs. no ad? Does an ad campaign appear to increase sales? How many weeks was an ad used?

c.       What are the total unit sales using an end cap vs. no end cap? What are the average unit sales using an end cap vs. no end cap? Does placing items on the end cap appear to increase sales?