Describe and visualize the proportion of sales of products sold internationally.  What trends do we see?  Which countrie

As an analyst for a large worldwide sporting goods manufacturer, you want to answer questions and make some predictions using sales data for the last 3 years. With over 80,000 rows of data, you’ll need to use tools within Excel to identify trends and patterns with the data.  You’ll also use Excel to forecast the impact of different possible future scenarios.  Use the data provided in Sporting_Goods.xlsx to complete the following.

Complete the following objectives in Excel:

Pivot Tables and Pivot Charts

·       Modify the Sales_Products worksheet to include the following calculated columns:

o   Profit per Unit 

o   Total Profit

o   Revenue per Unit 

* Initial values have been inserted so you can verify your results.

o   Show the total amount of revenue by country.  Separate the data by year.

o   Show the percent of total revenue by country.

o   Show the number of items distributed in each country.  Separate the data by year.

o   Show the amount of revenue and profit for each product line and product type.  Use slicers to allow selection of the country and year to view.

·       Create appropriate Pivot Charts within their own worksheets to visualize the following items(2 total):

o   Show the percent of total revenue by country for Europe only.

o   Create a useful visualization of your choice that provides insight on the data set.  Within that worksheet, provide an explanation of your findings. 

What If Analysis

·       International countries are subjected to geo-political risks associated with doing business in foreign countries.  One risk that can have a major impact on a business is the implementation of tariffs imposed by a foreign entity.  

Assume that given the current political environment, there is a significant possibility of tariff being levied on products sold in China in 2022.  For this analysis we will assume that no price increases, or changes to demand will occur in 2022.  Using the Tariff Analysis worksheet, complete the following:

o   Using 2020 sales and profit as a basis, create appropriate formulas in column C in the Tariff Analysis Worksheet.  Next, create a data table that shows the impact on tariffs ranging from 5% to 40% and lasting 3-12 months.  Use increments of 5% and 3 months.

o   Your advisors are indicating that there are three likely outcomes of tariff implementation in 2022.  Use Scenario Manager to create a Scenario summary showing the expected profit on goods sold to China based on the following three outcomes:

§  No tariff

§  A 20% tariff implemented Halfway through 2022

§  A 30% tariff implemented for the entire year    

Report your findings

·       Using Word, compose a 1-2 page synopsis of your findings from this project.  Be sure to focus your report on 2 primary considerations:

o   Describe and visualize the proportion of sales of products sold internationally.  What trends do we see?  Which countries or geographical areas are responsible for a large percentage of sales?  Provide appropriate visualizations.

o   Discuss the potential impact of Tariffs.  What is potential impact of tariffs in 2022?  How does this affect total profit?  What would the impact of tariffs be if they were to be implemented in Mexico and Canada?      

Be sure to:

·       Refer to Homework and Project Guidelines located in Canvas.

·       Apply appropriate formatting across all worksheets.

·       Pay careful attention to document formatting in word.  Be sure to include caption and footnotes as necessary.  

·       Provide references when necessary to the Excel file.

When complete, upload your report and a copy of your Excel file in Canvas.