Final Project: Milestone Three

This aspect of the final project, milestone three, is to reveal more information about Northern California Winery and its sales to management. Previous information calculated included total sales broken down by salesperson, red wine sales in the past six months in California, the top sellers further detailed by the type of wine, overall salespersons’ performance and commissions received by each salesperson.

Milestone Three calculates and analyzes the following items:

1 – Measures of Central Tendency for White and Organic Wines

  • For white and organic wine, what were the measures of central tendency based on the sales figures?
  • What would the total profit for each type of wine be given the following production costs for the wine: white wine is 17% of total sales, red wine is 12%, and organic wine is 21%?
  • Deduct the sales commission discussed in question 2 from Milestone Two: Data Analysis Part II and recalculate. Draft and present the answers graphically.
  • Summarize the provided data? Develop pivot tables using the cleansed data to illustrate

Measures of central tendency is a value that best illustrates a data set by using the middle position of the data set (Measures of Central Tendency, 2018). The specific names for the measures of central tendency include mode, mean, and median (Measures of Central Tendency, 2018).

Below is the calculated measures of central tendency for white and organic wine.

For White Wine:

MonthSum of Total Sales
January$74,153.00
February$64,279.00
March$51,915.00
April$51,226.00
May$34,207.00
June$25,924.00
Grand Total$301,704.00
Mean:$50,284.00
Mode:#N/A
Median:$51,570.50

For Organic Wine:

MonthSum of Total Sales
January$12,958.00
February$10,695.00
March$23,025.00
April$30,929.00
May$45,868.00
June$42,699.00
Grand Total$166,174.00
Mean:$27,695.67
Mode:#N/A
Median:$26,977.00

The measures of central tendency, in this case the mean and median, clearly show that the white wine is a better seller as compared to the organic wine. The calculations show that the white wine has a mean and median value of almost double of those from the organic wine.

2 – Total Profits Considering Production Cost

Management would like to know the total profit from each type of wine. The associated production costs are 17% of total sales for white wine ($51,289.68), 12% for red wine ($35,923.32), and 21% for organic wine ($34,896.54).

The profit calculations are shown below in the table having taken into account total commissions paid to the salespeople and the given production costs per wine.

Type of WineSum of Total SalesSum of Total CommissionsSum of Production CostsSum of Profit
Organic Wine$166,174.00$8,308.70$34,896.54$122,968.76
Red Wine$299,361.00$14,968.05$35,923.32$248,469.63
White Wine$301,704.00$15,085.20$51,289.68$235,329.12
Grand Total$767,239.00$38,361.95$122,109.54$606,767.51

3 – Total Profits Considering Production Cost Deducting Seller’s Commissions

Management is curious as to what the total profits are per wine type factoring in the production costs, but not total commissions paid. A table was created to depict the calculations of profits with only production costs considered, as shown below.

Type of WineSum of Total SalesSum of Production CostsSum of Profit
Organic Wine$166,174.00$34,896.54$131,277.46
Red Wine$299,361.00$35,923.32$263,437.68
White Wine$301,704.00$51,289.68$250,414.32
Grand Total$767,239.00$122,109.54$645,129.46

Senior Management requested to be shown the profits as compared to production costs in a graphical representation. The following graph shows the profit per wine type and corresponding production costs as calculated above for question 3.

4 – Summarize the Data and Develop Pivot Tables.

The accompanying spreadsheet (Milestone 3 – Kile.xlxs) contains the calculations, respective tables, charts, and graphs that were needed and calculated to answer the specific questions covered in this report. As always, the team is readily available to answer or clarify any additional questions.

Best regards,

Tammi Kile

Measures of Central Tendency. (n.d.). Retrieved February 11, 2018, from https://statistics.laerd.com/statistical-guides/measures-central-tendency-mean-mode-median.php