Question: Milestone Four Business Scenario
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 marketis as follows:
Price for Red Wine ($) 20.00
Price for White Wine ($) 20.00
Price for Organic Wine ($) 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 expecteddistribution 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.
Overview: You will now use the provided final project raw data set and Milestone Four Business Scenario, and help management decide on the right mix of wines to sell based on newly derived profit information, while considering the limitations of the particular types of grapes available for production.
Prompt: To complete this assignment, review the questions below and examine the Final Project Excel Functions document. Then, select a function that you believe will help you to answer the following questions:
1. What are the relevant decision variables? What are the constraints? Explain.
2. What is the right mix of sales for the optimized solution? Be sure to use the linear programming features in spreadsheet software and include a sensitivity analysis.
3. Create a decision tree or table for management to support your recommendation that takes into account your estimation of the probabilities and likely outcomes for alternative states of nature.
Information related to above question is enclosed below:
Attachment:- milestone1.rar