Here is the guidelines Applying What You Learned Assignment Excel: Creme Current Sales Analysis Judd Hamming is the eastern regional marketing manager for Crèmes Ice Cream. Each quarter, he completes two separate analyses: an analysis comparing ice cream flavor sales volumes for all regional locations with the same quarter sales volumes for the previous year, and an analysis comparing total sales in dollars, including mean, median, mode, and standard deviation of sales by store. Sales by Flavor in Gallons The first analysis, sales by flavor, compares the total quantities sold in gallons. The data collected provides for each flavor the number of pints, gallons, and 10-gallon tubs sold for all stores. Pints and gallons are sold directly to the public, whereas 10-gallon tubs are used for in-store sales of cones, cups, and specialty items such as sundaes and banana splits. To eliminate any impact of pricing changes or special promotions, Judd uses the ice cream volumes in gallons to compare sales by flavor. Judd has asked for your help this quarter in completing this analysis. He created two workbook files, 2-1.xlsx, which contains the current quarter’s sales on a worksheet named Flavors, and 2-2.xlsx, which contains the corresponding historical quarterly data for the previous years on a worksheet named HFLavors. For convenience, the flavors in both data sets are in identical order except for two new flavors introduced this years, which appear at the bottom of the current data set. Keep in mind the following conversions when analyzing this data. • There are 8 pints per gallon • Each tub holds 10 gallons Also, when calculating the total gallons sold for the current quarter, Judd has asked you to use the convention of rounding down the values to the nearest whole gallon. You will need to use the rounddown functions. (refer to assignment tips on using the rounddown function) Sales by Store in Dollars The second analysis you need to complete is to summarize sales in dollars by store and compare the result with the previous year’s sales. The Stores worksheet in the same workbook contains sales by store for the current quarter in dollars rounded to the nearest thousand dollars. You need to calculate some basic statistics for store sales. The following values should be used for the historical values same quarter of the previous year. • Mean: $8,817 • Median: $8,000 • Mode: $5,500 • Standard Deviation: $2,920 When writing formulas, be certain to use the most efficient method, including the use of functions as a well as relative and absolute cell referencing. Complete the following: 1. Open the workbook named 2-1.xlsx and the save it as Lastname_Firstname_Creme Current Sales Analysis.xlsx. 2. On the Flavors worksheet, for the current quarter, calculate the total number of gallons sold for each flavor. Place his calculation in the column adjacent to the data provided. Above the list of flavors, in a separate area, there is a list and clearly label the conversion values. These need to be reference when calculating total number of gallons. Remember to round down the total number of gallons when completing the calculation. 3. Add another column in which to enter the total number of gallons for last year. Copy the values for the total amount of ice cream sold for the corresponding flavor for last year from the 2-2.xlsx workbook into the newly added column of the Flavors worksheet. 4. Calculate the overall total and mean number of gallons sold for all flavors for this quarter and for this quarter last year. (Note: Include all flavors in your calculation, whether or not there are sales for them in a given year.) 5. Calculate for each flavor the percent of total gallons this flavor represents compared with total sales for the current quarter of all flavors. Copy this formula to the adjacent column to calculate the percent of total gallons this flavor represents compared with historical total sales. 6. In the two adjacent columns, calculate for each flavor and for the totals the difference and percent difference in sales. Assume a positive value represents an increase in sales. Flavors without sales in the previous tear should be left blank. (These cells should be completely empty.) TROUBLESHOOTING: In order to complete this step successfully, pay attention to the order of precedence rules when writing the formula. Percent difference is defined as the difference between the new values minus the old values divided by the old value, so specifying the order in which the operations must occur is critical. 7. Switch to the stores worksheet. Calculate the total sales, mean, median, mode, and standard deviation for this data set Label the cells so that they can be easily identified. 8. On the same worksheet, set up an area at the bottom of to analyze the change and percent change of each of these statistical values as compared with the historical values. The following values should be used for the historical values same quarter of the previous year. • Mean: $8,817 • Median: $8,000 • Mode: $5,500 • Standard Deviation: $2,920 9. Based on the results, explain on the worksheet (just below your analysis) whether the stores are doing better this year, and if sales in stores are more or less likely to vary from mean sales than they did last year. 10. Based on sales to all stores and total gallons sold for the current quarter, what is the price of a gallon of ice cream on average? Write a formula to determine this value and place it below the statistical analysis on the Stores worksheet. Do not type in the values for total gallons. You should reference the values on the flavors sheet. 11. Add appropriate worksheet titles and formatting to make the worksheets easy to read. 12. Insert a header on both worksheets with your name on the left side, the sheet name in the center, and the file name on the right. Be sure to use the Header and Header Elements for the sheet name and the file name. 13. Save and close the Lastname_Firstname_Creme Current Sales Analysis.xlsx workbook.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
