Courseproject Wilson

Wilson Financial Consulting
ABC Sales Data Analysis
Created by
Tanisha Wilson
6/15/13
1 | PAGE
Ladies and Gentleman: Below is my report based on the sales data given to me for analysis. This report contains 6 areas of interest. I analyzed and organized the data according to the bulleted points listed below. This report describes the tools used and the object achieved within the attached Excel spreadsheets. It also out lays additional uses and tools for the information that is presented. Refer to the Documentation page of the workbook for contents to reference items included on each sheet. Thank you, Tanisha Wilson
Sales Data
This first section is an overall view of sales for the purpose of financial analysis. The original unformatted sales data is in the first sheet (after Documentation) in the workbook. This sheet contains the original sales data as it was given to me. The first sheet contains a sales data table. This is the original information modified to show total sales. As you can see the total sales for the periods given is $1,234793.40 This is a table in Excel that can be sorted to display sales by any of the column titles including category, salesperson, country, order date, quantity, discount, unit price, and actual sale amount. The next sheet includes Chart 1: Total Sales by Month. This outlays the total monthly sales over the given time period listed in whole dollars (units of $20,000). Within this chart you can see economic rises and dips in sales over the given time period. As you can see, sales dipped dramatically in the fourth quarter of 2005 and rose tremendously in the following February. Sales have were consistently rising from January of 2006 to May of 2006. (Refer to sheet S1: I conclude that all sales data for the month of June 2006 has yet to be submitted).
2 | PAGE
Total Sales by Month

140000 120000 100000 80000 60000 40000 20000 0 Aug-2004 Sep-2004 Oct-2004 Nov-2004 Dec-2004 Jan-2005 Feb-2005 Mar-2005 Apr-2005 May-2005 Jun-2005 Jul-2005 Aug-2005 Sep-2005 Oct-2005 Nov-2005 Dec-2005 Jan-2006 Feb-2006 Mar-2006 Apr-2006 May-2006 Jun-2006 Sales in $$
Total
Product Category
This section is designed to show the sales statistics achieved in each product category and to give an overview of the sales of each product. Page PC1 is a pie chart to easily display the top selling categories of products. PC2 in a line chart representing the same data reflected over time. You can see what product categories were purchased in each month. The table on PC4 gives exact figures and months. Please notice that Beverages is the highest followed by Dairy Products. If you refer to sheet C1 you will see that the USA buys the most beverages, while Germany purchases the most dairy. This information can be used to review and reflect upon marketing strategies for products and countries at certain points in the year. The last sheet in this section has a group of related charts showing what each individual product was sold and gross sales for the product in each category. This group of charts is designed to assist in production and inventory.
Country
The next sheet titled C1 has two pivot tables Category Sales in Dollars and by Quantity respectively. These tables are based upon the original data set, or more accurately the Sales Data table, which allows me to arrange the data in order for creating these two tables. These tables are important to exports and customs. They allow you to see how much money is being earned in each country. They also show the quantities sold. This will help to better analyze seasonal purchases, marketing strategies, and shipping and export costs. 3 | PAGE
Forecast
This is the fun part. This section presents the data for your analysis. F1 is my analysis of the averages of sales according to the data given. It also offers a projection of sales for the next year based upon a 10% percent increase in sales. I made projections of total sales in dollars based upon the current average discount rate and projected sales increase. In sheet F2 I created several scenarios in line with the projected sales figured to show total sales in dollars with varying discount rates. The discounts are based upon a third of sales at varying discount rates with different percentages of sales increase.
Analysis
Overall sales are up towards the end of the reporting period and then drop again in June of 2006. (OS3 Summary). Sales were rose exponentially in the first part of 2006 as shown by a 274% increase from 2004. Then it dropped again in June of 2006 to lower than August of 2004. Departmental review should be taken into account for deeper analysis of the 2006 to figure out the increase and changes.
Month Comparison
Aug-2004 May-2006 Jun-2006 Difference from August 2004 to May 2006 Difference from May 2006 to June 2006 $ $ $ $ $ 30,741.90 115,000.87 12,885.06 84,258.97 17,856.83 274% -58%
The varying rates show the projected sales totals for each discount and increase. Marketing strategies for products and countries can be reviewed and updated to increase sales. Observe the trend in sheet PC2 Chart 3, (below) Beverage Sales are the highest. This would reflect the idea that a concentration in this category would produce higher sales. Also, marketing and discount rates should be reflected upon and revised to increase the lowest selling categories Grains/Cereals, Produce, and Condiments. The tables shows the individual items can also be consulted.
4 | PAGE
Sales by Product Category

80000 70000 60000 Product Sum of Beverages Sales in $$ 50000 40000 30000 20000 10000 0 2004 Qtr 3 2004 Qtr 4 2005 Qtr 1 2005 Qtr 2 2005 Qtr 3 2005 Qtr 4 2006 Qtr 1 2006 Qtr 2 Sum of Condiments Sum of Confections Sum of Dairy Products Sum of Grains/Cereals Sum of Meat/Poultry Sum of Produce Sum of Seafood
Salesmanship
Last but not least is the Salesmanship section of this report. Whether sales are good or poor, product is selling or not, the company depends upon its salesperson to move the product. The next two sheets in the workbook summarize sales data by salesperson. The first gives a view of the overall sales per person by month in the reporting period. The chart in S2 shows a salesperson to be commended and the lower line is next years candidate for the greatest improvement. These two data tools can help every salesperson to reflect on their methods and enthusiastically improve upon their performance.
Authors Note: What I learned from this project I learned to format, organize and analyze large amount of data to form a presentable and accessible workbook. I included charts, tables and scenarios that tested my knowledge and skills in Excel. I also
5 | PAGE
found that certain tables and charts work better for certain data. The organization of data is more important than I thought when creating chart and tables. Scenarios are also a viable tool for estimating varying data (amounts). This project taxed my knowledge of Excel and tested the limits of my knowledge. Data organized into tables is much easier to work with and data displayed in charts is easier to read.
6 | PAGE

Courseproject Wilson

Uploaded by

Copyright:

Available Formats

Courseproject Wilson

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Courseproject Wilson

Uploaded by

Copyright:

Available Formats

Wilson Financial Consulting

ABC Sales Data Analysis

Total Sales by Month

Sales by Product Category

You might also like