This report serves to inform management of Northern California Winery of the specific performance of different metrics. Data analysis was done using six months’ worth of data including the salespeople’s personal sales and results of sales of individual wines. Once the data was compiled and analyzed, marketing recommendations were given based on the calculated data and information. This business analysis report will disclose the above information, including graphs when available, and optimization.
Data Analysis
1 – Total Sales by Salesperson
Combined wine sales from the three sales people during the observed six months was $767,239. A breakdown by salesperson is shown below.
| Sales Person | Total Sales |
|---|---|
| Jane | $269,805 |
| Joe | $251,513 |
| Bill | $245,921 |
| Grand Total | $767,239 |
2 – Total Red Wine Sales
Red wine sales totaled $299,361 of the $767,239 total wine sold. Below is a breakdown by sales person.
| Sales Person | Total of Red Wine Sales |
|---|---|
| Jane | $111,937 |
| Bill | $95,536 |
| Joe | $91,888 |
| Grand Total | $299,361 |
3 – Best and Worst Sales by Wine Type
The best seller for type of wine was white wine and organic wine was the worse seller. It should be noted, however, white wine sales led red wine sales by only $2,343. Organic wine is clearly lacking in sales. A visual is provided below.
| Type of Sale | Total Sales |
|---|---|
| White Wine | $301,704 |
| Red Wine | $299,361 |
| Organic Wine | $166,174 |
| Total Sales | $767,239 |
4 – Measures of Central Tendency for White and Organic Wine
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 are the calculated measures of central tendency for white and organic wine.
For white wine:
| Month | Sum 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:
| Month | Sum 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 |
5 – Salesperson’s Performance
Combined wine sales over the six month period was $767,239. Jane, who sold $269,805 was the top seller. Joe had the second highest sales, with sales tallying $251,513. Lastly, Bill had sales that equaled $245,921 which regrettably ranked him the lowest among the sales people.
The total sales and individual sales are depicted below.
| Sales ($) | Sales (%) | |
| Bill | $245,921 | 32% |
|---|---|---|
| Jane | $269,805 | 35% |
| Joe | $251,513 | 33% |
| Total | $767,239 | 100% |
It can be shown that Jane sold $18,292 more than Joe, and $23,884 more than Bill. And Joe sold $5,592 more than Bill.
6 – Sales Commission
For a management perspective, sales commissions paid monthly to each sales person is shown below. The total commissions paid was tallied by adding the total sales each month and then multiplying by 5% [commission payout] with total monthly commission using the referenced Excel formula: (=SUM(‘Raw Data’!G2:G5)).
| January | February | March | April | May | June | Total ($) | Total (%) | ||
| Bill | $1,726.05 | $2,142.00 | $1,537.55 | $3,189.85 | $2,369.70 | $1,330.90 | $12,296.05 | 32% | |
| Jane | $2,429.50 | $1,531.15 | $1,909.90 | $1,932.55 | $2,121.55 | $3,565.60 | $13,490.25 | 35% | |
| Joe | $3,048.30 | $2,699.75 | $1,610.35 | $1,664.45 | $1,920.60 | $1,632.20 | $12,575.65 | 33% | |
| $38,361.95 | 100% |
7 – 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 Wine | Sum of Total Sales | Sum of Total Commissions | Sum of Production Costs | Sum 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 |
8 – 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 Wine | Sum of Total Sales | Sum of Production Costs | Sum 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 for the above for question.
9 – Summary
The accompanying spreadsheets (Milestone 1 – Kile.xlxs, Milestone 2 – Kile.xlxs, Milestone 3 – Kile.xlxs, and Milestone 4 – Kile.xlxs) contain 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.
Optimization and Decision Table
10 – Variables and Constraints
The relevant decision variables are directly related to four main constraints. Maximum production ceiling for each type of wine is the first constraint: 15,000 bottles of red wine, 15,000 bottles of white wine, and 5,500 bottles of organic wine. The second constraint is the maximum amount of grapes available for wine production for six months. The cap is 100,000 pounds of grapes with each bottle of wine requiring approximately 2.5 pounds each.
11 – Optimized Solution
Management would like to know the right mix of sales of each type of wine to maximize profit while maximizing the use of the available grapes for production. This solution is best calculated using linear programming with the results shown below.
The final calculations show maximum profit by producing 16,000 bottes each of red and white wine and 6,000 bottles of organic wine. These production numbers will use 95,000 pounds of the 100,000 grape maximum allotment.
Maximize Profit = 17.60 Red + 16.60 White + 23.70 Organic
| Red | White | Organic | ||||
| Solution | 16,000 | 16,000 | 6,000 | |||
|---|---|---|---|---|---|---|
| Profit Per Bottle | 17.60 | 16.60 | 23.70 | $ 689,400 | ||
| Constraints: | ||||||
| Red wine ceiling | 1 | 16,000 | <= | 16,000 | ||
| White wine ceiling | 1 | 16,000 | <= | 16,000 | ||
| Organic wine ceiling | 1 | 6,000 | <= | 6,000 | ||
| Pounds Required Per Bottle | 2.5 | 2.5 | 2.5 | 95,000 | <= | 100,000 |
| LHS | Sign | RHS |
12 – Decision Tree
| City | Organic | Red | White |
|---|---|---|---|
| Los Angeles | $ 40,259 | $ 74,875 | $ 92,380 |
| San Diego | $ 73,562 | $ 79,468 | $ 98,481 |
| San Francisco | $ 52,353 | $ 145,018 | $ 110,843 |
| City | Organic | Red | White |
| Los Angeles | 1,342 | 3,744 | 4,619 |
| San Diego | 2,452 | 3,973 | 4,924 |
| San Francisco | 1,745 | 7,251 | 5,542 |
| TOTAL BOTTLES | 5,539 | 14,968 | 15,085 |
Based on the above decision tree, below is a table of marketing recommendations.
| 1 | Increase marketing efforts of organic wine in Los Angeles. A goal of 1,500 bottles of organic wine is a good place to start. |
|---|---|
| 2 | Continue with the current marketing of organic wine in San Diego. 2,500 bottles of organic wine sold should be the goal. |
| 3 | San Francisco’s organic wine marketing should be increased some. A goal of 2,000 bottles should be set. |
| 4 | Red wine marketing should be more focused in Los Angeles and San Diego, with more attention given to LA. Goals of 4,000 bottles should be set up. |
| 5 | Red wine is a big seller in San Francisco. Marketing should be increased to help sell 8,000 bottles. |
| 6 | White wine sales are pretty even between the 3 cities. Marketing efforts should be increased slightly to improve sales by 15%. |
Works Cited
Balakrishnan, N., Render, B., & Stair, R. (2012). Managerial Decision Modeling with Spreadsheets (3rd edition ed.). New York, New York, United States of America: Pearson.
Measures of Central Tendency. (n.d.). Retrieved February 11, 2018, from https://statistics.laerd.com/statistical-guides/measures-central-tendency-mean-mode-median.php
Kile, T. (2018, January 13). Milestone One: Data Analysis Part I. Milestone 1 – Kile.xlxs. Essex, Maryland, United States of America: Southern New Hampshire University.
Kile, T. (2018, January 28). Milestone Two: Data Analysis Part II. Milestone 2 – Kile.xlxs. Essex, Maryland, United States of America: Southern New Hampshire University.
Kile, T. (2018, February 12). Milestone Three: Data Analysis Part III. Milestone 3 – Kile.xlxs. Essex, Maryland, United States of America: Southern New Hampshire University.
Kile, T. (2018, February 14). Milestone Three: Optimization and Decision Making. Milestone 4 – Kile.xlxs. Essex, Maryland, United States of America: Southern New Hampshire University.