Business Analysis

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.

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.

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.

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:

For organic wine:

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.

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)).

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.

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.

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

12 – Decision Tree

Based on the above decision tree, below is a table of marketing recommendations.

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.