Grenadiers Corp. is a wholesale business. The company's customers range from small businesses to large enterprises and various government agencies from many countries around the world.

You are an intern working for the Finance Department at Grenadiers Corp. You supervisor sent you this message:

"Attached is the Excel file containing financial data related to our product sales that contains answer to the following questions. PivotTable and PivotChart will do:

• • Analysis 1: What were the product sales for each country in 2014 (i.e., sales by product and country)?
  • Analysis 2: How much discount were given to each segment by discount band? -- Also create a PivotChart from the PivotTable.
  • Analysis 3: Key product performance by country and segment.

Please also complete the information on the Summary tab so I can see your results. Good Luck!"

Attachment: FINBPN0658.xlsxDownload FINBPN0658.xlsx

Please complete the following steps:

1. Download and open the Attachment.
   • Save the file using a meaningful file name. (2 points)
   • The Summary worksheet is where you will type in your answers from the analysis. Enter your name in B2.
2. Using data from the starting data file, please create ONE PivotTable that can be used to answer the questions for EACH analysis.
   • There should be THREE PivotTables overall, i.e., one for each analysis listed above.
   • For Analysis 2, Discount, please also create a PivotChart (based on the PivotTable) to visually compare the discount information.
   • For Analysis 3, Product Performance, the following Key Performance Indicators (KPIs) should be included in this PivotTable: (1) Product along with the total Cost of Goods Sold (COGS), (2) Total units sold for each product, and (3) Average of Sale Price. Add Slicers or Filters that can be used to show the information specifically for each Country and Segment. Sort data in the PivotTable to help with your analyses.
3. Each PivotTable should...
   • contain all required information (5 points)
   • be well structured in order to easily locate information and provide accurate and complete answer(s) to the question. (5 points)
   • use clear and meaningful title, headings and labels (2 points)
   • utilize appropriate number format (2 points)
   • be placed on its own worksheet with an appropriate worksheet (i.e., tab) name. (2 points)
   • The PivotChart (for Analysis 2) should be on the same worksheet as the PivotTable. Make sure to use an appropriate chart type with complete and meaningful chart elements (e.g., chart title, data labels, legend keys, etc.) and use clear and appropriate titles and labels without being too cluttered. (5 points)
4. Complete the Summary worksheet
   • The Summary worksheet has been programmed to give you some feedback as your enter the numbers. You won't be able to select of make changes to certain parts of the worksheet.
   • Sort the data in the PivotTable to help with your analyses, especially for Analysis 3.

• 

A13 fx [Enter your analysis here] A B C D E Grenadiers Corp. -- Financial Analysis Prepared by: Analysis 1: Sales Total Sales in 2014 Product with the highest Sales in 2014 % of sales of this product compared to Total Sales Country with the lowest sales in 2014 Country with the highest sales for product "Velo" in 2014 Analysis 2: Discount 11 Total Discount given to Small Business segment Discount Analysis: The Channel Partners at Grenadiers Corp. have access to the product price at near-cost because they provide customer service to the clients. Most Small Business customers purchase the products directly from Grenadiers Corp. as it includes 5-year customer service and warranty. To compensate for the higher price point, Grenadiers Corp usually offer more discount for Small Business customers. 12 Based on your discount analysis, do you think the data accurately reflects this corporate strategy? Why or why not? Please explain your answer. [Enter your analysis here] 13 14 Analysis 3: Product 15 In Mexico, which product has the lowest COGS in the Midmarket segment? How many units of Montana was sold to the Government in Canada? In Germany, what is the COGS of Carrerera sold to the Enterprise segment? What is the average price of Paseo sold to the Government in France? A13 fx [Enter your analysis here] A B C D E Grenadiers Corp. -- Financial Analysis Prepared by: Analysis 1: Sales Total Sales in 2014 Product with the highest Sales in 2014 % of sales of this product compared to Total Sales Country with the lowest sales in 2014 Country with the highest sales for product "Velo" in 2014 Analysis 2: Discount 11 Total Discount given to Small Business segment Discount Analysis: The Channel Partners at Grenadiers Corp. have access to the product price at near-cost because they provide customer service to the clients. Most Small Business customers purchase the products directly from Grenadiers Corp. as it includes 5-year customer service and warranty. To compensate for the higher price point, Grenadiers Corp usually offer more discount for Small Business customers. 12 Based on your discount analysis, do you think the data accurately reflects this corporate strategy? Why or why not? Please explain your answer. [Enter your analysis here] 13 14 Analysis 3: Product 15 In Mexico, which product has the lowest COGS in the Midmarket segment? How many units of Montana was sold to the Government in Canada? In Germany, what is the COGS of Carrerera sold to the Enterprise segment? What is the average price of Paseo sold to the Government in France