Project 2 – Product Sales Analysis

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

·       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.

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.