Management and information system EXCEL homework
BI Assignment - Southern Tech Sales
MGMT 4013 MIS TC1
Spring 2017
Southern Tech
Suzanne is the VP of Sales at Southern Tech, which markets security software and technical support to its customers. She is charged with understanding which areas of the company’s business are healthy and which are not and then determining marketing strategies to improve sales. Southern Tech competes more on quality and customer service than on price. While the company wants to strengthen relationships with current customers, it also wants to expand its customer base.
Suzanne recently hired you as a new business analyst. You just graduated from college with a degree in business. While earning your degree, you took some courses exposing you to new technologies and analysis techniques. Suzanne is eager to hear your thoughts and ideas on how to improve sales.
She gives you a spreadsheet listing each sales transaction for the last quarter, including the customer ID, the total amount of the sale, the region and industry in which the sale occurred, and the level of support purchased by the customer (immediate in person support, immediate live phone support, 48-hour phone response, 48-hour email response). She asks you to answer the questions below and to make recommendations for a marketing strategy based on those answers.
Which industry is responsible for the most revenue?
Which industry is responsible for the least revenue?
Which region is responsible for the most revenue?
Which region is responsible for the least revenue?
Which support level has the most revenue?
Which support level has the least revenue?
Notice that the data is in ‘list’ format. Check on data types for each column and remember what sort of descriptive statistics you can do with each type (text – counts only; numbers and currency – sums, averages, maximums, minimums, variance, and standard deviations). Begin by examining the spreadsheet for the data you can use to complete the task Suzanne has given you. Consider how you might examine the data to get other information useful to her.
You can expect some incomplete, inconsistent, or other form of bad data. You will have to decide how to deal with those problems, and you must document your decision within the spreadsheet. As always, look for data errors or outliers that might affect the analysis. (Remember that the Data Filter and its sort function is an easy way to check for extreme values. Blank values will float to the bottom of the list no matter how it is sorted.)
Perform a Pivot Table analysis on the data to respond to Suzanne’s request. Create at least three Pivot Tables. Remember to sort the Pivot tables and format the value fields appropriately to make the charts clear and easy to understand.
Create at least three Pivot Charts for these tables to better visualize and communicate the results of the analysis. These charts must be professional in appearance. They should have an appropriate title. They should not have a legend if none is needed (i.e., the analysis involves only one variable).
Carefully label your work. Worksheets should be named appropriately and in an informative way (not ‘Sheet1’, etc.), including those worksheets containing Pivot Tables/Charts. Create a separate worksheet to contain corrected data, if you find any, and for any calculated columns, if you create any. (Always leave a copy of your original data unchanged to allow for an audit trail.)
Create a Documentation worksheet using the examples in the Tegrity video posted in Blackboard and the example spreadsheet attached in the BI assignment folder. Include your course and section number at the top. List yourself as the author. In the appropriate sections of the Documentation worksheet, put the results of your analysis. Based on your findings, make recommendations to Suzanne based on your knowledge business and strategy. Provide clear evidence for your results and recommendations in terms of Pivot Charts. Small images copied from the relevant Pivot Chart would be useful here. Just remember to paste the copy of the chart as an image so that the chart in the Documentation worksheet does not remain interactive should you change the Pivot table to look at a different variable.
Be sure to name your file appropriately (example: TroboyKimBI.xlsx). Submit your file using the link in the BI Assignment folder in the Assignments area of Blackboard by the deadline listed in the Course Schedule.
Extra Credit Option
You can do an analysis to answer the following questions for up to 10 extra credit points.
What is the count of customers at each support level?
How many customers do we have in each industry?
How many customers do we have in each region?
Which customer generates the most revenue?
Which three customers generate the least revenue?
Hint: For the last two questions, you may want to use the ‘value filters’ in the sort options. There are so many customers that a list of all of them would be cumbersome. You can choose a sort option that lists the top customer (see ‘Top 10 and modify to ‘Top 1’). The procedure for finding the worst customer is similar.
