Analyzing Sales Data for Golden Old Sweets Company (GOSC) using Excel

Part 1 - Analysis of the existing sales data

3. Apply statistical principles to discern meaningful information based on a given scenario we must use excel The Golden Old Sweets Company (GOSC) is a small organisation based in Keswick, Cumbria. At their simple factory they make nostalgia sweets that is sweets that are no longer available in the shops and in which the main confectionary manufacturers have lost interest. The sweets are then sold in bulk to various tourist shops around the Lake District. For the past 10 years GOSC has only produced 5 brands of confectionary namely: Kola Cubes, BlackJacks, Mojos, Spangles and Humbugs. The company has ambitious plans to increase their product and customer base substantially over the next two years. At this stage in the organisation's development, it has been decided that it is time to look closely at the sales data for the past ten years and to create a way of collecting data that is more detailed and is easier to use. In the first part of this assignment you will use Excel to analyse the sales information that GOSC has collected over the past decade. In the second part of the assignment you will use Excel again, this time to create a user-friendly access application that will collect sales information in the future. Part 1 - Analysis of the existing sales data In Appendix A are the GOSC sales figures for the past 10 years.  These figures could be described as raw data. In this assignment you are required to manipulate this data using Microsoft Excel to create a series of readable and meaningful charts that provide information about the company progress. Based on the work you have done you are then required to report on the general condition of the company sales Manipulate the data and create charts to represent the following: · The actual sales of each product over the past ten years, that is a chart of the data contained in Appendix A · The total quantity of sales on a monthly basis. · The total quantity of sales on a quarterly sale · The annual sales totals A three-year moving average (based on the annual sales totals) A time series analysis (based on the quarterly sales totals) Using these charts as a basis, produce a summary of the general condition of the GOSC sales. Ensure that you explain how you interpreted these charts to provide this information. You can include in your summary any information you want to, however for guidance purposes you may like to consider the following: · a summary of which products have been the clear best sellers and which products have not sold well. · a description of the seasonal variations in the sales · based on the time series analysis, a written summary of which have been the strong and weak years. · a general description of how much sales vary from year to year · an holistic over view of the trends that can be observed in the sales figures · a forecast as to how one may expect sales to be in the future   Part 2 - A system for collecting future sales information   GOSC have requested that a spreadsheet be developed that is easy for them to use and that is capable of collecting more detailed sales information in the future. This spreadsheet should be capable of recording how many of each product, each customer has purchased each month. It should also hold information about the products and customers. Designed for the non-technical user, this spread sheet should be very simple to use, it should have a cosmetically pleasing front end and should use lookup tables and macros wherever possible to reduce the chances of the user making a mistake. Deliverables A standard assignment report containing the following: Charts that you have produced to analyse the GOSC sales for the past decade. A written summary of the general condition of the GOSC sales Screenshots of each sheet in the workbook(s) that you have produced in response to part two of the assignment.