Attached, kindly find SNHU Course material for QSO-320 Management Science Spreadsheet Management - Milestone Four. Thank you!

QSO 320 Final Project Guidelines and Rubric Overview The final project for this course is the creation of a business analyst report containing data presented through the use of spreadsheet software. It is essential for business professionals to develop spreadsheet software skills for analyzing a variety of business situations using quantitative means. This project takes a hands-on approach by placing you in a real-world context. You will utilize technical concepts to interpret a business framework and apply selected quantitative techniques to arrive at accurate information that can be used for sound decision-making. Specifically, you will assume the role of a business analyst working for a winery in northern California. You have access to certain raw data and other provided information and must respond to questions from management. You will calculate and present your answers in a consumable way using spreadsheet software. There are two separate tasksone focusing on the analysis of a provided raw data set and another that requires optimization analysis to arrive at a solution to a provided business problem. Together, these two tasks will allow you to demonstrate your mastery of the course outcomes. The project is divided into four milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions. These milestones will be submitted in Modules One, Three, and Five. The final project will be submitted in Module Seven. In this assignment, you will demonstrate your mastery of the following course outcomes: Conduct basic data manipulation, calculations, and charting of business data through the accurate use of spreadsheet software Conduct descriptive and inferential statistical analyses of business data using spreadsheet software for informing business processes and operations Present business data analyses using spreadsheet software that clearly communicate meaningful information for key stakeholders Conduct optimization analyses of business data using spreadsheet software for reaching solutions that maximize business objectives Prompt Imagine that you are a business analyst working for a winery in northern California. In two different tasks, management will be asking you specific questions to which you must respond using spreadsheet software. The answers to each can be submitted in separate files or compiled into a single file, as you deem appropriate for clearly communicating with your key stakeholders. I. Data Analysis: Use the provided raw data set and select from the provided formulas, to answer the following questions from management: A. What were total sales by salesperson? B. What were the total sales of red wine in California over the last six months? Present this answer graphically and in a way that shows each different salesperson's sales. C. D. E. F. G. Which types of wine were the best and worst sellers? Present this answer graphically showing all wine sales by type of wine. For white and organic wine, what were the measures of central tendency based on the sales figures? Be sure to include all relevant statistics. How did each salesperson perform in terms of the person's respective sales figure? Present this answer using a table. What would the sales commission be for each month if each salesperson earned 5% of the sales? Present this information graphically. 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%? Now, deduct the sales commission discussed in question F and recalculate. Present the answers graphically. H. How would you summarize the provided data? Develop pivot tables using the cleansed data to illustrate. I. When presenting your answers to the questions above, did you: i. Present the information logically? ii. Choose tables and graphics that will best communicate the information to the relevant stakeholders? iii. Properly color, label, and proportion the graphics? II. Optimization and Decision Making: For the second task, you will now use the provided final project raw data set 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. A. What are the relevant decision variables? What are the constraints? Graph the constraints involved in this problem using spreadsheet software. B. What is the right mix of sales for the optimized solution? Be sure to use the linear programming features in the spreadsheet software and include a sensitivity analysis. C. Create a decision tree or table for management, to support your recommendation, taking into account your estimation of the probabilities and likely outcomes for alternative states of nature. D. When presenting your answers to the questions above, did you: i. Present the information accurately and logically? ii. Choose tables and graphics that will best communicate the information to the relevant stakeholders? iii. Properly color, label, and proportion the graphics? Milestones Milestone One: Data Analysis Part I In Module One, you will submit Milestone One: Data Analysis Part I, answering sections A, B, and C from Critical Element I above. Review the raw data provided in the Milestone One Rubric to complete this analysis. This milestone will be graded with the Milestone One Rubric. Milestone Two: Data Analysis Part II In Module Three, you will submit Milestone Two: Data Analysis Part II, answering sections E and F from Critical Element I above. Continue to review the raw data provided to complete this analysis. This milestone will be graded with the Milestone Two Rubric. Milestone Three: Data Analysis Part III In Module Five, you will submit Milestone Three: Data Analysis Part III, answering sections D, G, and H from Critical Element I above. This milestone will be graded with the Milestone Three Rubric. Milestone Four: Optimization and Decision Making In Module Five, you will submit Milestone Four, answering Critical Element Part II: Optimization and Decision Making. This milestone will be graded with the Milestone Four Rubric. Final Submission: Business Analyst Report In Module Seven, you will submit your final project. It should be a complete, polished artifact containing all of the critical elements of the final product. It should reflect the incorporation of feedback gained throughout the course. This submission will be graded with the Final Project Rubric. Deliverables Milestone Deliverable Module Due Grading One Data Analysis Part I One Graded separately; Milestone One Rubric Two Data Analysis Part II Three Graded separately; Milestone Two Rubric Three Data Analysis Part III Five Graded separately; Milestone Three Rubric Four Optimization and Decision Making Five Graded separately; Milestone Four Rubric Seven Graded separately; Final Project Rubric Final Submission: Business Analyst Report Final Project Rubric Guidelines for Submission: Your business analyst report must be 5-7 pages in length including spreadsheets. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Data Analysis: Sales by Salesperson Data Analysis: Total Sales of Red Wine Data Analysis: Best and Worst Sellers Data Analysis: Measures of Central Tendency Data Analysis: Salesperson Performance Data Analysis: Sales Commission Data Analysis: Total Profit Exemplary (100%) Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Proficient (85%) Correctly determines the total sales by salesperson Needs Improvement (55%) Determines the total sales by salesperson, but answer is incomplete or has inaccuracies Not Evident (0%) Does not determine the total sales by salesperson Value 8 Accurately and comprehensively presents the total sales of red wine in California over the last six months Accurately and comprehensively presents the best and worst sellers by types of wine Accurately and comprehensively determines the measures of central tendency Accurately and comprehensively presents the salesperson performance by sales figure in a table Accurately and comprehensively presents the sales commissions for each month based on the given information Accurately and comprehensively presents the total profit for each type of wine based on the given information Presents the total sales of red wine in California over the last six months, but answer is incomplete or has inaccuracies Does not present the total sales of red wine in California over the last six months 8 Presents the best and worst sellers by types of wine, but answer is incomplete or has inaccuracies Determines the measures of central tendency, but answer is incomplete or has inaccuracies Does not present the best and worst sellers by types of wine 8 Does not determine the measures of central tendency 7 Presents the salesperson performance by sales figure in a table, but answer is incomplete or has inaccuracies Presents the sales commissions, but answer is incomplete or has inaccuracies Does not present the salesperson performance by sales figure in a table 7 Does not present the sales commissions 7 Presents the total profit for each type of wine, but answer is incomplete or has inaccuracies Does not present the total profit for each type of wine 7 Data Analysis: Summary Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Meets \"Proficient\" criteria and presentation of information reflects sophisticated communication skills Accurately and comprehensively summarizes the cleansed data using pivot tables All answers to the Critical Element I questions are presented logically and properly formatted for effective communication with relevant stakeholders Summarizes the provided data using pivot tables, but summary is incomplete or has inaccuracies There are issues in the logic or formatting of the answers to the Critical Element I questions that would impede communication with relevant stakeholders Optimization and Decision Making: Variables and Constraints Meets \"Proficient\" criteria and answers demonstrate advanced proficiency in the use of spreadsheet software Accurately and comprehensively identifies and graphs the variables and constraints Identifies and graphs the variables and constraints, but graph is incomplete or has inaccuracies Optimization and Decision Making: Optimized Solution Meets \"Proficient\" criteria and proposal is particularly wellsupported or creative Optimization and Decision Making: Decision Tree or Table Meets \"Proficient\" criteria and decision tree or table demonstrates keen ability to analyze raw data set Optimization and Decision Making: Presentation of Answers Meets \"Proficient\" criteria and presentation of information reflects sophisticated communication skills Proposes a sound optimized solution based on correctly applied linear programming features Creates a decision tree or table for management that comprehensively and appropriately supports recommendations All answers to the Critical Element II questions are presented logically and properly formatted for effective communication with relevant stakeholders Proposes a solution, but it is not optimal, not sound, or not based on correctly applied linear programming features Creates a decision tree or table, but there are either gaps in detail or the recommendations are not wellsupported There are issues in the logic or formatting of the answers to the Critical Element II questions that would impede communication with relevant stakeholders Data Analysis: Presentation of Answers Does not summarize the provided data using pivot tables 8 There are significant issues in the logic or formatting of the answers to the Critical Element I questions that would make effective communication with the relevant stakeholders impossible Does not identify and graph the variables and constraints 8 Does not propose a solution 8 Does not create a decision tree or table 8 There are significant issues in the logic or formatting of the answers to the Critical Element II questions that would make effective communication with the relevant stakeholders impossible Total 8 8 100% Sales $7,451 $11,221 $9,525 $3,986 $11,667 $11,649 $9,010 $5,686 $9,121 $8,703 $4,369 $5,936 $9,990 $5,217 $5,582 $7,913 $8,581 $7,472 $4,716 $3,020 $11,552 $8,507 $8,573 $4,827 $11,146 $10,898 $10,424 $6,077 $4,908 $4,652 $7,498 $4,641 $10,440 $4,168 $4,031 $4,031 $7,498 $8,305 $4,788 $11,953 $11,482 $11,959 $8,681 $10,399 $11,310 $6,981 $8,758 City Date Sales RepType Wine Los AngelFeb Bill Red Los AngelJun Joe Organic San FrancJun Jane Red San DiegoMay Jane Organic San DiegoApr Bill White San FrancMar Jane Red San DiegoFeb Jane Red Los AngelJan Joe Red San FrancMar Jane Organic San FrancApr Jane Red San DiegoFeb Jane Organic Los AngelFeb Bill White San DiegoMay Joe Organic San DiegoApr Bill White San FrancApr Joe Red Los AngelJun Jane White San FrancJan Joe Red San DiegoFeb Bill Red San FrancFeb Joe White San DiegoJan Jane Organic San FrancApr Jane Red Los AngelMar Joe White San FrancMar Bill White San DiegoJun Jane Red San DiegoJun Joe Organic San DiegoApr Jane Red San FrancApr Bill White Los AngelApr Bill White Los AngelFeb Jane White San FrancJan Jane White San DiegoApr Joe Organic San DiegoJun Bill White San DiegoJan Jane White San DiegoMar Joe Red San FrancFeb Bill White San FrancFeb Jane White San DiegoApr Jane Organic San DiegoFeb Jane Red San FrancMay Jane White San DiegoMay Joe White San FrancJun Jane Red San DiegoJan Jane White San FrancJun Jane Organic Los AngelMar Joe Red Los AngelFeb Joe Red San FrancJun Jane Organic Los AngelMay Jane Organic $9,837 $4,276 $7,515 $10,497 $8,587 $7,373 $8,722 $5,607 $11,029 $6,262 $9,432 $6,685 $3,913 $7,642 $9,549 $9,014 $7,390 $3,390 $4,670 $8,535 $9,112 $8,197 $3,287 $6,388 $9,887 $6,326 $10,694 $9,779 $10,157 $5,286 $8,516 $7,177 $6,312 $5,546 $5,294 $7,879 $6,437 $6,061 $7,763 $5,761 $9,248 $10,919 $11,498 $8,950 $9,133 $9,332 $5,437 $7,914 San DiegoMay San FrancApr San FrancApr Los AngelMay San FrancJan Los AngelFeb San FrancJan San FrancJun San FrancJan San FrancMar San DiegoMay San DiegoApr San DiegoJan Los AngelMar San DiegoMar San DiegoFeb San FrancMay Los AngelFeb San FrancJun Los AngelJun San FrancMay San FrancJan Los AngelJan Los AngelApr San DiegoJan Los AngelFeb Los AngelJan San FrancJun San FrancMay San FrancJan Los AngelMay San DiegoApr Los AngelMay San DiegoFeb San FrancJan San FrancMar San FrancJun San DiegoJan San FrancJun Los AngelJun San FrancApr San FrancFeb Los AngelJan San DiegoMay Los AngelMar San FrancApr Los AngelFeb San DiegoFeb Bill Joe Bill Bill Bill Joe Joe Joe Bill Bill Bill Joe Joe Bill Jane Bill Jane Bill Joe Jane Bill Joe Jane Bill Joe Joe Joe Bill Joe Joe Bill Bill Joe Bill Jane Jane Bill Jane Jane Bill Joe Joe Bill Jane Joe Bill Joe Joe Red White Red Red White White Red White White Organic Organic Organic Red Organic White White Organic Red Organic Red Red Red Red White Red Organic White Red Red Red White White Organic Red White White Red Organic White Red Organic White White White White Red White White $8,559 $8,274 $3,877 $3,407 $5,605 Los AngelMay San FrancMar San DiegoJan San FrancJan San DiegoJun Jane Bill Jane Bill Jane Red White Organic Red Red QSO 320 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 market is 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 expected distribution based on types of grapes: Red wine ceiling White wine ceiling Organic wine ceiling 16,000 bottles 16,000 bottles 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. QSO 320 Milestone Four Guidelines and Rubric 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? Graph the constraints involved in this problem using spreadsheet software. 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, taking into account your estimation of the probabilities and likely outcomes for alternative states of nature. Guidelines for Submission: Submit a document that communicates your answers, using a graph where indicated. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Optimization and Decision Making: Variables and Constraints Optimization and Decision Making: Optimized Solution Proficient (100%) Accurately and comprehensively identifies and graphs the variables and constraints Needs Improvement (75%) Identifies and graphs the variables and constraints, but graph is incomplete or has inaccuracies Not Evident (0%) Does not identify and graph the variables and constraints Proposes a sound optimized solution based on correctly applied linear programming features Does not propose a solution 25 Optimization and Decision Making: Decision Tree or Table Creates a decision tree or table for management that comprehensively and appropriately supports your recommendations All answers to the questions are presented logically and properly formatted for effective communication with relevant stakeholders Proposes a solution, but it is not optimal, not sound, or not based on correctly applied linear programming features Creates a decision tree or table, but there are either gaps in detail or the recommendations are not wellsupported There are issues in the logic or formatting of the answers to the questions that would impede communication with relevant stakeholders Does not create a decision tree or table 25 There are significant issues in the logic or formatting of the answers to the questions that would make effective communication with the relevant stakeholders impossible Total 25 Optimization and Decision Making: Presentation of Answers Value 25 100%