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 Raider, 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:
• Open the workbook named Raider.xlsx and save the file as Raider Analysis (Last Name).xlsx.
• Create a series of charts that compare gas prices by time of the year and by location. The time can be in 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 a chart type that provides ease of visual interpretation. Include readability features such as providing the actual names of locations instead of the codes (use a formula to populate these values in the marked cells in column G of the Data worksheet), answering which area is the highest priced location in your chart, and giving proper axes labels and chart titles.
HINT: To do this, create a data series or table that summarizes and displays the gas price data by the location for each individual date. For example:
Place the data in the marked cells on each of the three chart sheets.
• For each location—region, state, and city—determine if a particular location has higher gas prices than the others. Selected the correct value from the drop-down list in the marked cell to explain your findings.
• For each chart, determine if a particular season has higher gas prices than the other times of the year. Add a comment near each chart to explain your findings.
• On the Bubble Chart sheet, 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 each week; the number of weeks that it is below the gas price for the entire United States each week; 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 like this:
HINT: Use the Polya method. You know the first what question is being asked (see paragraph before this). The second step is determining what data you will need. Once this is determined, the third step is determining what you need to do to that data to make it ready for interpretation (meaning, what needs to be done with the data to change it from how it is right now into the data that you need to create the chart.
My advice is first figure out what three pieces of data for each city you will need to make the chart (The X [Weeks above], Y [Weeks below], and Z [gas price] axes). 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. A good first question to ask yourself is: How can I determine if the price of gas in a city on a given day (say, Chicago on 1/1/2021) is higher or lower than the US average gas price on that same day?
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.
• 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:
• Open the workbook named Home Station-Austin.xslx and save the file as Home Station-Austin (your last name).xlsx.
• Using the data in the Sales worksheet, create a PivotTable in the AustinQuarter worksheet. 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? Select the correct answers from the drop-down lists.
• Create the same PivotTable that’s in AustinQuarter in the AustinManager worksheet. 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 marked cells.
• Create the same PivotTable that’s in AustinQuarter in the AustinDepartment worksheet. 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 marked cells.
• In the AustinPivotChart worksheet, create a PivotTable and PivotChart based on the PivotTable. Place the pivot chart in cells XX;XX. 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. Add data labels to show the values of sales per manager per department. Remove all these data labels from the managers in each department that didn’t have the highest sales in that department. Write your answers to this question (who should manage which department) in the marked cells.
• Save and close Home Station-Austin.xslx.
