Create a pivottable on a new worksheet

Assignment Help Accounting Basics
Reference no: EM131576525

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.

480_figure.png

Reference no: EM131576525

Questions Cloud

Explain the pros and cons of adjusting pay : Explain the pros and cons of adjusting pay based on cost-of-living differences from company's perspective and an employee's perspective.
Evaluate nike by using the competitive forces : 1. Evaluate Nike by using the competitive forces and value chain models.
Bad news demonstrating application : Ajax Company employees that delivers this "bad news," demonstrating application of the use of the four-paragraph model shared
Create a family or friend tree using a php script : Create a family or friend tree using a PHP script. The tree should date far enough back to include at least 5 family members.
Create a pivottable on a new worksheet : Profit by Product Category Using the Pivot Data worksheet, create a PivotTable on a new worksheet that shows Profit by Category
Dividend-share you can pay original shareholders in year : Calculate the dividend/share you can pay the original shareholders in year 1 for Policy II? (2)
Successful student of the future : 1. Generate your own list of the 5 core competencies of the "successful student of the future".
EBIT indifference level associated with two financing plans : Find the EBIT indifference level associated with the two financing plans.
Identify which sdlc step is associate with project component : Be sure to define the SDLC model and methodology and identify which SDLC step is associated with each project component.

Reviews

Write a Review

Accounting Basics Questions & Answers

  How much control does fed have over this longer real rate

Hubbard argues that the Fed can control the Fed funds rate, but the interest rate that is important for the economy is a longer-term real rate of interest.   How much control does the Fed have over this longer real rate?

  Coures:- fundamental accounting principles

Coures:- Fundamental Accounting Principles: - Explain the goals and uses of special journals.

  Accounting problems

Accounting problems,  Draw a detailed timeline incorporating the dividends, calculate    the exact Payback Period  b)   the discounted Payback Period. the IRR,  the NPV, the Profitability Index.

  Write a report on internal controls

Write a report on Internal Controls

  Prepare the bank reconciliation for company

Prepare the bank reconciliation for company.

  Cost-benefit analysis

Create a cost-benefit analysis to evaluate the project

  Theory of interest

Theory of Interest: NPV, IRR, Nominal and Real, Amortization, Sinking Fund, TWRR, DWRR

  Liquidity and profitability

Distinguish between liquidity and profitability.

  What is the expected risk premium on the portfolio

Your Corp, Inc. has a corporate tax rate of 35%. Please calculate their after tax cost of debt expressed as a percentage. Your Corp, Inc. has several outstanding bond issues all of which require semiannual interest payments.

  Simple interest and compound interest

Simple Interest, Compound interest, discount rate, force of interest, AV, PV

  Capm and venture capital

CAPM and Venture Capital

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd