Assignment:
Overview: It is essential for a business professional 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. In this milestone, you are provided a set of raw data, and from that data set, you must respond to questions from management. You will calculate and present your answers in a consumable way using spreadsheet software.
Prompt: Review the critical elements below and examine the Final Project Excel Functions spreadsheet. Then, select a function that you believe will help you answer the following questions based on an analysis of the final project data set provided.
Specifically, the following critical elements must be addressed:
1. How did each salesperson perform in terms of the person's respective sales figure? Draft this answer using a table.
2. What would the sales commission be for each month if each salesperson earned 5% of the sales? Draft this information graphically.
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. Which types of wine were the best and worst sellers? Present this answer graphically showing all wine sales by type of wine.
D. For white and organic wine, what were the measures of central tendency based on the sales figures? Be sure to include all relevant statistics.
E. How did each sales employee perform in terms of the person's respective sales figure? Present this answer using a table.
F. What would the sales commission be for each month if each salesperson earned 5% of the sales? Present this informationgraphically.
G. 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?
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.
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?
Attachment:- Total sales of Red Wine in California.rar