Using Excel Pivot Tables for Analytics
|STUDENT NAME||Your name here|
|Provide screenshots to support your answers. Questions 1-24 are 2 points each and questions 25-28 are 4 points each for a total of 64 points.|
- What was the overall Revenue and Net Sales for all years? Which year had the highest Revenue and Net Sales and what was the Net Sales during that year? Why do you believe the Revenue and Net Sales values are different? (If necessary, do a Google search to answer this question.)
- What was the Sales and Revenue for E-Bikes in all years? Which years had no sales or revenue?
- Which customer sold the most E-Bikes in 2020 and what was the Total Sales Quantity by all customers for that year?
- What year had the highest Sales Quantity?
- What year had the highest Net Sales and what year had the lowest Net Sales? Is this consistent with the question 4 response for the year with the highest Sales Quantity. What would be one possible explanation for an inconsistency?
- In the year with the lowest Net Sales, which Material Desc had the lowest Net Sales? Which Material Desc had the highest Net Sales?
- Which material had the lowest Net Sales overall?
- In the year that had the highest Net Sales, what division and customer had the highest Net Sales?
- In the year with the lowest Net Sales, what division and customer had the highest Net Sales?
- What customer provided the highest Net Sales for Accessories (AS Division) in all years?
- In 2019, for Bicycles (BI Division), what is the total Net Sales for the top three customers?
- What is the average Net Sales per customer?
- What was the average Net Sales per customer each year?
- What was the average Net Sales per customer per year per division?
- What was the average Net Sales per customer for each of the years? Which year has the highest average Net Sales per customer? How much was that amount?
- Is there seasonality in Net Sales? If so, what month has the highest Net Sales? Is the seasonality similar in both Germany and US?
- Do any products show a significant change in Net Sales over time? Provide one example and are the changes the same for both Germany and US?
- Do any of the AS Division materials not display significant seasonality? Explain your answer.
- What customer has the highest percentage contribution to Total Sales? What has been the trend of that customer’s percentage contribution over the years?
- What year had the highest overall gross margin? What was the gross margin during that year?
- What was the gross margin (as a percentage of sales) for Germany for the year with the highest gross margin? Comment on the trend.
- What was the gross margin (as a percentage of sales) for US during the same year?
Question 25: Develop a 2021 forecast trendline chart (with formula and R2 value) for Tailwind E-Bikes annual sales quantity. Which trendline option would you select to forecast 2021 E-Bike sales? Comment on any concerns with using this forecast approach for 2021 planning.
Question 26: Develop a 2021 forecast chart for Tailwind E-Bikes monthly sales quantity using an Excel Forecast Sheet and a 95% confidence level. Comment on the differences between the monthly forecast and the annual trendline forecasts.
Question 27: Which method (Annual Trendline or Monthly Forecast Sheet) and what 2021 forecast quantity would you recommend to the team? What is your rationale?
Question 28: Is there another timeseries forecasting approach other than Annual Trendline or Monthly Forecast Sheet you would consider for Tailwind E-Bike? What is your rationale?
Do you need urgent help with this or a similar assignment? We got you. Simply place your order and leave the rest to our experts.