SO 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 sales employee 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? Explain. 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 that takes 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 One Deliverable Data Analysis Part I Module Due One Grading 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: Sales Employee 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 sales employee 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 sales employee 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 sales employee 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 Data Analysis: Presentation of Answers Optimization and Decision Making: Variables and Constraints Optimization and Decision Making: Optimized Solution 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 Meets \"Proficient\" criteria and demonstrates sophisticated awareness of variables and constraints within decision making Meets \"Proficient\" criteria and proposal is particularly wellsupported or creative Identifies the variables and constraints, explaining why each is relevant Identifies the variables and constraints, but explanation is cursory or identification is not relevant 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 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 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 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%