Posted: April 13th, 2023

Mod3 essay

Module 3 Assignment: Part 1: Illustrating Patterns in Gas Prices (MO-3.1)
You have been assigned to MTSU Auto’s sales department as an analyst. The marketing team for the new Lightning Sport Utility Vehicle has become concerned that the rising cost of gas will negatively affect vehicle sales. They are considering introducing limited rebates at times when gas prices are especially high. The company might also increase its marketing of the Blue aider, the new compact, sporty SUV the company introduced late last year. Your supervisor has asked you to put together a report and presentation showing the price per gallon of gas by region and large metropolitan areas. The company is interested to see if there are any patterns in the data that can be used to better time the rebate promotions. The Raider.xlsx file contains data on gas prices for various regions and metropolitan areas around the country.

Complete the following:

1. Open the workbook named Raider.xlsx located in the Chapter 3 folder, and then save the file as Raider Analysis.xlsx.

2. Create a series of charts that compare gas prices by time of the year and by location. The time can be in quarters, months, or week of the month, as you deem appropriate. The location should be charted separately by region, by state, and by city. Each chart should be created on an individual sheet (named Region, State, and City, respectively). Select the chart type that provides ease of visual interpretation.

HINT: In order to do this, you will need to create a data series or table that summarizes and displays the gas price data by the location for each individual date. For example:

3. For each location—region, state, and city—determine if a particular location has higher gas prices than the others. Add a comment near each chart to explain your findings.

4. For each chart, determine if a particular time of year has higher gas prices than the other times of the year. Add a comment near each chart to explain your findings.

5. On a new sheet named “Bubble Chart”, create a bubble chart to illustrate the number of weeks that each city’s gas price is above the gas price for the entire United States; the number of weeks that it is below the gas price for the entire United States; and the average gas price in dollars per gallon for the year. Add appropriate titles, labels, and a legend to the chart. The final chart could look similar to this:

HINT: My best advice is first, figure out what three pieces of data for each city you will need to make the chart. Once determined, create a set of cells that would hold these values for each city. Next, take another step backwards to determine what formulas you will need to use to get the data that you need for the chart. After that, logically determine what data you need so that you can run the correct formulas. Continue stepping backwards mentally this way until you arrive at the data you are provided. In other words, how will you take data from the sheets given, reorganize it (if needed), and apply formulas to it to create the data needed for the chart.
Functions that you might consider using (though might not need all of them) include: AVERAGE, MAX, MIN, SUM, DATEDIFF, COUNT, COUNTIF.
Show all your calculations needed for creating the chart data above the Bubble chart itself.

6. Save and close Raider Analysis.xlsx.

Part 2: Analyzing Manager Performance and Home Station (MO-3.2, 3.3)
You are a regional manager for Home Station, a national chain of home renovation stores. You are analyzing the weekly sales data for one of the retail stores located in Austin, Texas. The sales data is reported by department and manager. The Austin store manager wants to rotate the department managers in each of the store’s departments so each manager becomes more familiar with the entire store’s operations. You have been assigned the task of determining the impact of rotating the managers on store sales. You will create a PivotTable report to summarize the quarterly sales by department and by manager.

Complete the following:

1. Open the workbook named Home Station-Austin.xslx located in the chapter 3 folder, and then save the file as Home Station-Austin (your last name).xlsx.

2. Create a PivotTable using the data in the Sales worksheet and place the PivotTable report in a new worksheet named AustinCopy this worksheet into a new sheet, named AustinQuarter. In this new sheet, rearrange the fields in the PivotTable to analyze department sales by quarter. Which department had the highest quarterly sales, and in which quarter did it occur? Write your answers to this in the cells below the PivotTable.

3. Copy this worksheet into a new sheet, named AustinManager. Add the Manager field to the PivotTable to analyze each department’s quarterly sales performance by manager. Which department resulted in the highest sales for each manager? In which quarter did the highest sales occur per manager? Write your answers to this in the cells below the PivotTable.

4. Copy this worksheet into a new sheet, named AustinDepartment. Rearrange the fields in the PivotTable to analyze each manager’s quarterly sales performance by department. Which manager had the highest sales for each department? In which quarter did the manager’s highest sales occur? Write your answers to this in the cells below the PivotTable.

5. Create a new sheet to the right of AustinDepartment and name it AustinPivotChart. Create a Pivot Table and Pivot Chart (based on the Pivot Table). Select the chart type and display options to help you visually answer the following question: Based on the data collected, which manager would you choose to manage each department on a long-term basis (one person can only manage a single department)? Support your recommendations with data from the PivotTable/Pivot Chart. Write your answers to this in the cells below the PivotTable. Expand the size of the chart so all labels are legible. Add data labels to show the values of sales per manager per department. Remove all of these data labels from the managers in each department that didn’t have the highest sales in that department.

6. Save and close Home Station-Austin.xslx.

New England RegionCentral Atlantic RegionLower Atlantic RegionEast Coast RegionMidwest RegionGulf Coast RegionWest Coast RegionAll US

1/10/2020325.8326.5309.9325310.6301.9339.5325.4

Check Price Discount

Study Notes & Homework Samples: Discussion Healthy Eating Approach Essay »MKT00720 – Marketing Assignment 2: Report