Corellian Enterprises operates a chain of retail electronics stores called Tech Cantina (T.C.) all over the world. Performance has been suffering in the past few years and Bob Fett was recently brought in as the new CEO. Mr. Fett has extensive retail experience in other industries but is somewhat new to consumer electronics. He has hired you as a consultant to help him analyze the latest sales data and determine where the biggest problem spots are. Here is an excerpt from your initial interview with him:
“I just don’t understand what is going on with sales. T.C. has been chasing sales targets for the last decade and just can’t seem to meet them. I’ve been talking to some of the country and store managers and I haven’t been able to put together a consistent picture of what is driving our failure to stay on target.
Last year, total sales revenue was $90M below target. We know that there is a trend toward online purchases of consumer electronics, and we had factored that trend into our sales targets for the brick-and-mortar stores, but sales revenue from our stores has still been unexpectedly low. Many managers are reporting lower-than-expected sales volumes and some managers have mentioned discounting products to help them move inventory more quickly. Some managers have also reported that customers seem to be delaying or avoiding purchases of higher-priced items due to difficult local economic circumstances. I’m not sure which, if any, of these effects is driving our poor sales results and I don’t know whether the problems are concentrated in certain countries, product lines, or specific products. I find this lack of specific information disturbing.
I remember from my cost accounting class many years ago that sales revenue variances can be broken down to show how much of the overall variance is due to price concessions, changes in the product mix, or sales volume. I would like to see that type of analysis in your report.
We do have an online store and economic forces have worked in our favor to allow us to hit sales targets for the past three years. I have hired a solo e-commerce expert to analyze sales and market opportunities in the online sphere. Your focus will be on the brick-and-mortar stores only.”
Instructions:
Analyze the sales data provided by Mr. Fett to help him understand why sales revenue is unexpectedly low. You can choose either Excel or Tableau to summarize and visualize your information. If you use Tableau, you can create a dashboard or series of dashboards. Make sure that each chart within the dashboard is labeled clearly so that I can tell exactly what is being represented (I should be able to reproduce it myself based on what you are showing me – you can assume that I know the variance formulas). Please also include narrative information that guides me through your visuals and tells me “a story” about what is happening. The deliverable is your Tableau file or Excel workbook.
The following definitions and information are associated with the data:
Product Categories and Products
• Audio
o Car Stereos
o Receivers
• Cameras
o Point and Shoot
o SLR’s
o Video
• Computers
o Desktops
o Monitors
o Notebooks
o Tablets
• TV’s
o LCD
o LED
o Plasma
o Projection
• Video Players
o Blu ray
o DVD
Sales and Volume Information
• Actual Sales: Actual sales revenue (in $)
• Budged Sales: Budgeted sales revenue (in $)
• Actual Units Sold: Actual units sold
• Budgeted Units Sold: Budgeted units sold
• Actual units – Country: Actual units of all products sold in the country
• Budgeted units – Country: Budgeted units of all products sold in the country