Vistabeans Coffee Shop Assignment for BUS 410 Research Methods
The instructions below specify creation of a report with 3 or more visualizations. Read the solution memo carefully to understand how it relates to the dataset provided. Replicate the six visualizations embedded in the memo. Upload the completed file by clicking on the link for this assignment.
Vistabeans Coffee Shop – Solution
Requirements
Assume you are Alma Nelson and use Tableau to analyze Vistabeans profitability. Your deliverables include:
Prepare a report with recommendations as to how the company can maximize profit. Your report should include outputs from Tableau. Your report should contain at least 3 visualizations to support your recommendations.
Deliver a presentation to Eli and Sarah summarizing how the company can maximize profit. Your presentation should include outputs from Tableau.
Recommendations will vary; however, the following recommendations are likely given the dataset.
Discontinue sales of low profit items.
Adjust marketing expenditures.
Investigate potential inventory and accounting errors.
Visualizations will also vary; however, the following visualizations are likely given the dataset.
Line graphs
Bar graphs
Map graphs
Report Formats will vary unless specific format instructions are provided.
Grading Rubrics will vary depending on the instructor’s actual assignment; however, the following grading rubric could be used for the case requirements.
Requirement
Point Allocation
Prepare a report with recommendations as to how the company can maximize profit. Your report should include outputs from Tableau. Your report should contain at least 3 visualizations to support your recommendations.
Points Possible = 50
Report is grammatically correct and contained at least three recommendations supported by at least three visualizations (50 points).
Report is grammatically correct and contained two recommendations supported by at least three visualizations (25 points).
Report contained less than two recommendations (0 points, even if supported by at least three visualizations).
Deliver a presentation to Eli and Sarah summarizing how the company can maximize profit. Your presentation should include outputs from Tableau.
Points Possible = 50
Evidence of preparation, clear and articulate, and use of at least three visualizations in presentation (50 points).
Evidence of preparation, clear and articulate, and use of two visualizations in presentation (25 points)
No Evidence of preparation, or not clear and articulate, or less than two visualizations in presentation (0 points)
Total Points Possible 100
Examples of Visualizations and Student Analyses
Examples of the visualizations and explanations that might be included in the presentations and reports are included on the following pages. Note: these examples are from actual student reports that were submitted as part of the case efficacy testing.
General Information:
This line graph illustrates sales and profits over each month divided up by regional market. The blue line represents 2012 while the orange represents 2013. Through it we can see that each year our sales and profits follow approximately the same pattern throughout the year. We can also see that our profits significantly increased from 2012 to 2013 without the same increase in sales. This is good news that we were able to increase profits even if our sales did not jump. This graph also shows that our southern region has the lowest amount of sales and profits, but that its profits are comparable to the others markets as a percentage of its sales. The Central and West markets have comparable sales amounts for each year, but the Central market is able to turn a better profit than the West. To maximize profit, we need to compare why the Central market has a higher profit than the West. We could look at pricing, costs they are incurring, and inventory turnover to name a few.
This line graph labeled Caffe Latte shows the inventory and sales of Caffe Latte in the two markets that it is available, South and West. I displayed the West market as a control to show what should be happening while the problem obviously is in the South market. Sales stay steady through the year of 2012 while the inventory decreases over the year to a negative number. It is not possible for us to have a negative inventory of any item so something is happening in the South market. We are still making sales so the inventory has to be there, but it seems like inventory purchases are not being recorded. This obviously would affect some of our numbers on our financial statements including Cost of Goods Sold if we are not recording how much it is costing for us to purchase inventory until the start of the following year when it looks like the inventory greatly increases.
Recommendation: Discontinue sales of low profit items.
This graph is labeled Green Tea. Green tea is not doing well for our company. For this graph I chose 3 other low selling products to do a side by side comparison to Green Tea. Green Tea sales are comparable to Caffe Latte and we actually sell more Green Tea than we do Amaretto and Regular Espresso. So why do we have a negative profit for Green Tea and not the others? The problem arises with our Cost of Goods sold and Total Expenses columns. Green Tea’s numbers are higher in both of these columns than the other 3 products. It may not look like a significant amount higher but since they are low selling products, each dollar higher counts. Since Cost of Goods Sold and Total Expenses are high, Green Tea is not able to turn a profit. Green Tea does not have an issue selling so if we want to keep it as a product we need to find a way to either decrease our expenses for it or increase our revenue. If we cannot find a way to decrease our costs, then we need to increase the selling price for each Green Tea. If this is not possible than we need to consider not offering Green Tea anymore since it is costing more than we are making.
Recommendation: Adjust marketing expenditures
The bar graph labeled Marketing of Caffe Mocha lists Sales, Marketing, and Profit for all of our products that are sold throughout the country. If we look at just Sales and Marketing, for the most part each individual product’s Marketing to Sales ratio is about the same. For every product we sell, use a rough percentage of each sales dollar to spend on marketing. I illustrated two products, Caffe Mocha and Decaf Espresso to show a flaw in this system. Decaf Espresso has close to the amount of sales as Caffe Mocha but has half the Marketing costs. Caffe Mocha’s marketing is costing us nearly double the percentage of sales as the other products. This in turn is making the profits decrease significantly. If marketing costs for Caffe Mocha were comparable to those of Decaf Espresso, then our profits would increase by nearly $10,000 for that product.
Recommendation: Investigate potential inventory and accounting errors
While looking at our inventory numbers, I noticed a sharp decrease in inventory in the South Market. Our inventory fell roughly $54,000. So I brought up our sales numbers for the same period, hoping to see an increase in sales that would correlate as to where our inventory went. As illustrated by the line graph labeled West Inventory vs. Sales, there was not one. We did not sell the products so where are they going? We need to look into this market further to see if this is happening in each city or just in one particular city. This region did have our highest inventory count, even after the decrease, but its sales are not significantly higher to reflect that need for that high of inventory. Its sales are similar to those of the Central market so its inventory should be similar also. This could be happening for a few reasons. The first could be that there is a fraud going on in the company where someone is ordering too much product and then stealing the excess. If this is the issue, we will need to do a fraud investigation and figure who is doing this and how. Another option could be that the product expired at the end of the year, therefore requiring us to dispose of it. If this is the case than we need to reconfigure the level of inventory that the South Market is carrying so that we are not ordering more product than we need.
The diagrams above show the highest profiting item and the lowest. As you can see in both states there is a negative profit in Nevada. So I looked at Nevada for these two items and found that little marketing was put into place for Colombian and they did not profit. But a larger amount of marketing was put into place for Green Tea and profit was even less. Sales are there for both items, and expenses were higher for Green tea. I think it would be profitable to remove green tea and use expenses from it to heighten the sales and profit of Colombian. Another thing you could do would be to raise prices by say 3%, if you sell the same amount of items as before, both your turnover and your profits have increased. The price increase will be low but possibly enough to boost profits depending on how many products you increase the price on.
1
The post Vistabeans Coffee Shop Assignment for BUS 410 Research Methods The instructions below appeared first on PapersSpot.
