MID-TERM PRODUCTION

This project places you, the student, as an intern to the regional marketing manager of a yogurt production company.The manager completes two separate analyses each quarter: Analysis 1) to compare yogurt flavor sales volumes from all regional locations with the same quarter sales volumes from the previous year, and Analysis 2) to compare total sales in dollars, including mean, median, mode, and standard deviation, of sales by store.

Analysis 1 - 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. To eliminate any impact of pricing changes or special promotions, the marketing manager uses the yogurt volumes in gallons to compare sales by flavor.The manager has created two workbook files:

Workbook 1) MIDPROD.xlsx, which contains the current quarter's sales on a worksheet named Flavors.

Workbook 2) Historic.xlsx, which contains the corresponding historical quarterly data from the previous year on a worksheet named HFlavors.

For convenience, the flavors in both data sets are in identical order except for two new flavors [Chocolate Fudge & Cherry Vanilla] introduced this year, which appear at the bottom of the current data setin MIDPROD.

When analyzing this data keep in mind the following conversions:

> There are 8 pints per gallon. > Each tub holds 10 gallons.

Also, when calculating values for 10-gallon tubs and converting pints to gallons, the manager has asked you to use the convention of rounding down the values to the nearest whole tub.

Analysis 2 - Sales by Store in Dollars

The second analysis for which you will be requested 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 [MIDPROD] contains sales by store# for the current quarter in dollars rounded to the nearest dollar. You need to calculate some basic statistics for store sales. In the same quarter of the previous year, these values were:

> 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 well as relative and absolute cell referencing.Also, pay attention to the order of precedence rules when writing the formula.

With that as a brief informative narrative, complete the following:

1.Open the workbook named MIDPROD.xlsx and then save it as MIDPRODxlsx. 2. On the Flavors worksheet, first, above the list of flavors, in a separate area, list and clearly label the conversion values.Second, for the current quarter, develop a copiable formulation to calculate the total number of gallons sold for each favor. Place this calculation in the column adjacent (Col. E) to the data provided.Remember to round down the tub quantities to the nearest whole tub when completing the calculation.Then format the column of data to accounting with 0 decimals & no $. 3.. Add another column (Col. F) in which to enter the total number of gallons from last year. Copy the values for the total amount of yogurt sold for the corresponding flavor for last year from the Historic.xlsx workbook into the newly added column of the Flavors worksheet. 4. Develop a copiable formulation to 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. In Cols. G develop a copiable formulation to calculate for each flavor the percent of total gallons this flavor represents compared with total sales for the current quarter of all flavors. This formulation should be copiable to the adjacent column (Col. H) to calculate the percent of total gallons this flavor represents compared with historical total sales. 6. In the two adjacent columns (Cols. I & J), for each flavor and totals [created in Step 4], calculate the difference and percent difference, respectively, in the current and historic total gallons sold. Flavors without sales in the previous year should be left blank; these cells should be completely empty. 7. Switch to the Stores worksheet. Within the range of D5:K20 complete instruction steps 7, 8&9.Place the calculations of the total sales, mean, median, mode, and standard deviation for this data set. Label the cells and columns so that they can be easily identified. 8.On the same Worksheet, within this same range [D5:K20], set up a table to analyze the percent change of each of these statistical values as compared with the historical values. Based on the changes, explain on the worksheet (just below your analysis) whether the stores are doing better or worse this year, and if sales in stores are more or less likely to vary from the mean sales than they did last year.Again, be sure to label the cells and columns so that they can be easily identified. 9. Within this same range, D5:K20, based on sales to all stores and total gallons sold for the current quarter, what is the average price of a gallon of yogurt? what is formula to determine this value and place it below the statistical analysis on the Stores worksheet. 10. Add any appropriate worksheet titles and formatting to make all worksheets easy to read. 11. Generate a combo chart, on a new chart sheet, comparing the mean, median, mode and std.dev. for the current and historic total sales and percent change.The percent change should be a line measured on the second axis. Provide appropriate title and labeling for readability. 11. Save and close the MIDPROD workbook for submission with FC1.