QSO-320 Milestone 4

As a business analyst, you have been asked by management to examine the data collected and analyzed in the previous modules. The objective is for you to help management decide on the right mix of wine bottles to sell based on newly derived profit information while considering the limitations of the particular types of grapes available for production.

While doing more research on wine production, you realize that it would take an average of 2.5 pounds of grapes to make a bottle of wine. In addition, the marketing department has advised you that the price per bottle that consumers are willing to pay based on comparable brands and types of wine in the market is as follows:

Price for Red Wine ($)	Price for White Wine ($)	Price for Organic Wine ($)
20.00	20.00	30.00

After discussing wine production with the operations manager, you also learn that the wineries that supply the grapes to produce the above types of wine can produce up to a total of 100,000 pounds of grapes for a six-month supply of wine bottles for the Los Angeles, San Diego, and San Francisco market, with the following expected distribution based on types of grapes:

Red wine ceiling	16,000 bottles
White wine ceiling	16,000 bottles
Organic wine ceiling	6,000 bottles

Note that the production cost per bottle remains the same as before, that is, 12% of sales or revenue for red wine, 17% of sales for white wine, and 21% for organic wine. With additional information you have gathered, you are now ready to answer the questions posed in the Module Four milestone.

Also note that as a business analyst, you are to examine a finite set of decision alternatives or possible decisions whose outcomes correspond to the possible future events known as states of future. While you can choose which alternative to recommend to management, you have no control over which state of nature will actually occur (Lawrence & Pasternack, 2002).

Remember that at each outcome node of a decision tree, you would calculate the expected payoff, using the probabilities of all possible outcomes at that node and the payoffs associated with those outcomes (Balakrishnan, Render & Stair, 2013). Likewise, at each decision node, you would select the alternative that provides the largest value (expected payoff), which is the profit for this business scenario.

To help management decide on which city (Los Angeles, San Diego, or San Francisco) to focus its marketing efforts, you will need to create a decision tree or table for each of the above cities with your estimation of the probabilities of each decision tree branch based on projected high, moderate, and low demand for the current wine production plan. Calculate the expected payoff for each outcome to quantify your assumptions and recommendations to management.

References

Balakrishnan, N., Render, B., & Stair, R. (2013). Managerial decision modeling with spreadsheets (3rd ed.). Upper Saddle River, NJ: Pearson Education, Inc.

Lawrence, J., & Pasternack, B. (2002). Applied management science: Modeling, spreadsheet analysis, and communication for decision making (2nd ed.). Hoboken, NJ: John Wiley & Sons, Inc.

Excel Function	Description	Example	What It Does
AVERAGE	Calculates the arithmetic mean by adding a group of numbers in given cells and then dividing by the count of those numbers	AVERAGE(A1:A6)	The average of 2, 3, 3, 5, 7, and 10 in cells A1 to A6 is 30 divided by 6, which is 5.
IF	Adds all numbers in a range of cells	IF (A2>5,B1+B2, B1-B2)	Adds cells B1 to B2 if cell A2 is greater than 5, otherwise subtracts cell B2 from B1 if A4 is less than or equal to 4
MEDIAN	Returns the median of the given numbers. The median is the number in the middle of a set of numbers (half the numbers have values that are greater than the median, the other half are less).	MEDIAN(A1:A6)	The mean of 2, 3, 3, 5, 7, and 10 in cells A1 to A6 is 30 divided by 6, which is 4.
MODE	Returns the most frequently occurring number in a group of numbers	MODE(A1:A6)	The mode of 2, 3, 3, 5, 7, and 10 in cells A1 to A6 is 3.
SUMIF	Adds all numbers in a range of cells based on one criterion	SUMIF(G4:G6,">50", H4:H6)	Sums the numbers in cells H4 to H6 only if the corresponding numbers in cells G4 to G6 are greater than 50.
SUMIFS	Adds all numbers in a range of cells, based on multiple criteria	SUMIFS(C9:C14, A9:A14, "=2013")	Adds all numbers in cells C9:C14, based on year 2013 found in cells A9:A14
SUMPRODUCT	Multiplies corresponding components in the given arrays, and returns the sum of those products	SUMPRODUCT(A9:A14, D9:D14)	Multiplies all the components of array A9:A14 by the components of array D9:D14 then adds the products
Notes:
	The above Excel functions are required to complete the first two milestones and the SUMPRODUCT function is useful in formulating and solving
	the problem sets and final project milestone in the linear programming portion of this course.
	Click on the next two tabs to see illustrated examples of the Excel functions.
	Other useful Excel functions can be found in your textbook (Balakrishnan, 2013) Appendix B, including the commands for creating and/or inserting
	different objects such as charts like line and column (see p. 564) to illustrate the results of the calculations to be included in your final business report.
	Also, refer to Module Seven supplementary resource Chapter 19: Getting Started Making Charts (Excel 2013 Bible) for further details.
	For details about creating a Pivot Table, which is a dynamic summary report generated from a database resided in a worksheet or in an external file, refer to Module Seven supplementary resource
	Chapter 33: Introducing Pivot Tables (Excel 2013 Bible).
	A pivot table can help transform multiple rows and columns of numbers into a meaningful presentation of the data.

Sales	City	Date	Sales Rep	Type Wine
$7,451	Los Angeles	Feb	Bill	Red
$11,221	Los Angeles	Jun	Joe	Organic
$9,525	San Francisco	Jun	Jane	Red
$3,986	San Diego	May	Jane	Organic
$11,667	San Diego	Apr	Bill	White
$11,649	San Francisco	Mar	Jane	Red
$9,010	San Diego	Feb	Jane	Red
$5,686	Los Angeles	Jan	Joe	Red
$9,121	San Francisco	Mar	Jane	Organic
$8,703	San Francisco	Apr	Jane	Red
$4,369	San Diego	Feb	Jane	Organic
$5,936	Los Angeles	Feb	Bill	White
$9,990	San Diego	May	Joe	Organic
$5,217	San Diego	Apr	Bill	White
$5,582	San Francisco	Apr	Joe	Red
$7,913	Los Angeles	Jun	Jane	White
$8,581	San Francisco	Jan	Joe	Red
$7,472	San Diego	Feb	Bill	Red
$4,716	San Francisco	Feb	Joe	White
$3,020	San Diego	Jan	Jane	Organic
$11,552	San Francisco	Apr	Jane	Red
$8,507	Los Angeles	Mar	Joe	White
$8,573	San Francisco	Mar	Bill	White
$4,827	San Diego	Jun	Jane	Red
$11,146	San Diego	Jun	Joe	Organic
$10,898	San Diego	Apr	Jane	Red
$10,424	San Francisco	Apr	Bill	White
$6,077	Los Angeles	Apr	Bill	White
$4,908	Los Angeles	Feb	Jane	White
$4,652	San Francisco	Jan	Jane	White
$7,498	San Diego	Apr	Joe	Organic
$4,641	San Diego	Jun	Bill	White
$10,440	San Diego	Jan	Jane	White
$4,168	San Diego	Mar	Joe	Red
$4,031	San Francisco	Feb	Bill	White
$4,031	San Francisco	Feb	Jane	White
$7,498	San Diego	Apr	Jane	Organic
$8,305	San Diego	Feb	Jane	Red
$4,788	San Francisco	May	Jane	White
$11,953	San Diego	May	Joe	White
$11,482	San Francisco	Jun	Jane	Red
$11,959	San Diego	Jan	Jane	White
$8,681	San Francisco	Jun	Jane	Organic
$10,399	Los Angeles	Mar	Joe	Red
$11,310	Los Angeles	Feb	Joe	Red
$6,981	San Francisco	Jun	Jane	Organic
$8,758	Los Angeles	May	Jane	Organic
$9,837	San Diego	May	Bill	Red
$4,276	San Francisco	Apr	Joe	White
$7,515	San Francisco	Apr	Bill	Red
$10,497	Los Angeles	May	Bill	Red
$8,587	San Francisco	Jan	Bill	White
$7,373	Los Angeles	Feb	Joe	White
$8,722	San Francisco	Jan	Joe	Red
$5,607	San Francisco	Jun	Joe	White
$11,029	San Francisco	Jan	Bill	White
$6,262	San Francisco	Mar	Bill	Organic
$9,432	San Diego	May	Bill	Organic
$6,685	San Diego	Apr	Joe	Organic
$3,913	San Diego	Jan	Joe	Red
$7,642	Los Angeles	Mar	Bill	Organic
$9,549	San Diego	Mar	Jane	White
$9,014	San Diego	Feb	Bill	White
$7,390	San Francisco	May	Jane	Organic
$3,390	Los Angeles	Feb	Bill	Red
$4,670	San Francisco	Jun	Joe	Organic
$8,535	Los Angeles	Jun	Jane	Red
$9,112	San Francisco	May	Bill	Red
$8,197	San Francisco	Jan	Joe	Red
$3,287	Los Angeles	Jan	Jane	Red
$6,388	Los Angeles	Apr	Bill	White
$9,887	San Diego	Jan	Joe	Red
$6,326	Los Angeles	Feb	Joe	Organic
$10,694	Los Angeles	Jan	Joe	White
$9,779	San Francisco	Jun	Bill	Red
$10,157	San Francisco	May	Joe	Red
$5,286	San Francisco	Jan	Joe	Red
$8,516	Los Angeles	May	Bill	White
$7,177	San Diego	Apr	Bill	White
$6,312	Los Angeles	May	Joe	Organic
$5,546	San Diego	Feb	Bill	Red
$5,294	San Francisco	Jan	Jane	White
$7,879	San Francisco	Mar	Jane	White
$6,437	San Francisco	Jun	Bill	Red
$6,061	San Diego	Jan	Jane	Organic
$7,763	San Francisco	Jun	Jane	White
$5,761	Los Angeles	Jun	Bill	Red
$9,248	San Francisco	Apr	Joe	Organic
$10,919	San Francisco	Feb	Joe	White
$11,498	Los Angeles	Jan	Bill	White
$8,950	San Diego	May	Jane	White
$9,133	Los Angeles	Mar	Joe	White
$9,332	San Francisco	Apr	Bill	Red
$5,437	Los Angeles	Feb	Joe	White
$7,914	San Diego	Feb	Joe	White
$8,559	Los Angeles	May	Jane	Red
$8,274	San Francisco	Mar	Bill	White
$3,877	San Diego	Jan	Jane	Organic
$3,407	San Francisco	Jan	Bill	Red
$5,605	San Diego	Jun	Jane	Red