Assignment: Southern Gourmet Specialty Foods
Southern Gourmet Specialty Foods is an established high-end food distribution company that actively markets its products to restaurants and high-end grocers. You have been hired by the company to analyze data on shipments from the first six months of 2016 and make recommendations for a seasonal marketing plan. Download the data file southerngourmetfoods.xlsx and follow the instructions below to complete your analysis and prepare your report to the company. The PivotData worksheet has the correct totals to check your work for Part 1.
Instructions: Part 1: Data Analysis
On the Jan - June worksheet: (1) add three columns and calculate the revenue, total cost, and profit for each shipment (be sure to use formulas cell references so the spreadsheet can be updated).
Sort the data by product category and then use the subtotal function under "Outline" to subtotal the data by product category. Display the subtotals and grand total by category. Add a column to your worksheet and calculate the percent of total profit each product category represents (level 2 of your outline). Check your totals on the PivotData worksheet.
Part 2: PivotTables & Charts
a. Profit by Product Category Using the Pivot Data worksheet, create a PivotTable on a new worksheet that shows Profit by Category. Label the worksheet "Analysis" and the PivotTable "Profit." On the same worksheet, create a PivotChart that shows the profit by product category.
Place the PivotChart next to the table on your worksheet. Be sure all number formats and labels accurately reflect the data.
b. Profit by Month Use the same Pivot Data worksheet to create a new PivotTable that shows the Profit by Month for all product categories combined. Name the PivotTable "Trends." Next to the Trends PivotTable, create a PivotChart (line) that shows the organization's total profits by month for the first 6 months.
Part 3: Summary and Recommendation On a separate worksheet (named "Recommendations"), insert a textbox and summarize your results in a brief statement for the owner of the company. Copy both pivot charts and place them under the textbox to illustrate your recommendations. Add a title and professionally format your report.
In the analysis, identify which product categories have the highest profit margins (use % of total profit information from Part 1) and discuss the trends in profit by month. Based on the results, what recommendations would you suggest to the company owner? Include at least two specific recommendations based on your analysis of the data.
Submission: Upload a single Excel 2016 data file with worksheet tabs clearly labeled in the CougarView Dropbox. Prior to submission, verify that all formulas and functions use cell references, formulas, functions, and currency in accounting format - 0 decimals. All columns, rows, and PivotTables and Charts should have appropriate labels or titles. Your submission should be your original work.
