Maverick Motors Inc. (MMI) is automobile manufacturing company with production and sales throughout the world. MMI has asked your project team to assist in analyzing their performance and make suggestions to improve their revenue stream and optimize their product line.
Automobile manufacturing and sales is a complex and highly competitive business. The automotive industry has a broad global sales reach yet only 15 countries produce 88% of the world’s vehicles (http://marketrealist.com/2015/02/intense-competition-leads-low-profit-margins-automakers/). MMI is a fairly new and a smaller player in the automotive manufacturing market. However, they have some very popular brands and high customer satisfaction.
MMI currently offers four brands: Apechete, Jackson, Special, and Tatra. Each brand has several models and are listed below:
| Brand | Models Available |
| Apechete | Chare, Island, Pebble, Rack, Sparro, Stork, Summet, Trido |
| Jackson | Baller, Clock, Core, Crux, Elevator, Fraction, Mud, Roof, Salsa, Wood |
| Special | Gazelle, Performance, Sprinkles |
| Tatra | Advantage, Blooper, Jespie, Mortimer, Rambler |
Contribution Margin and Sales Volume from January – September 2015 are reflected below:
Contribution Margin:
| Brand | Q1 | Q2 | Q3 | Grand Total |
| Apechete | $ 1,441,796.25 | $ 784,707.25 | $ 717,039.95 | $ 2,943,543.45 |
| Jackson | $ 819,042.25 | $ 418,069.85 | $ 443,079.90 | $ 1,680,192.00 |
| Special | $ 183,537.70 | $ 95,224.50 | $ 90,445.90 | $ 369,208.10 |
| Tatra | $ 738,760.01 | $ 522,609.37 | $ 924,124.21 | $ 2,185,493.59 |
| Grand Total | $ 3,183,136.21 | $ 1,820,610.97 | $ 2,174,689.96 | $ 7,178,437.14 |
Sales Volume:
| Brand | Q1 | Q2 | Q3 | Grand Total |
| Apechete | 144 | 75 | 72 | 291 |
| Jackson | 120 | 60 | 60 | 240 |
| Special | 36 | 17 | 16 | 69 |
| Tatra | 123 | 104 | 188 | 415 |
| Grand Total | 423 | 256 | 336 | 1015 |
MMI is concerned that they have spread themselves too thin by selling in 30 countries. In addition, they would like to improve the margin on their current models and be sure they are optimizing their product lines.
To assist you with your analysis MMI has provided you with an Excel spreadsheet that reflects their sales and costs from January 2015 to September 2015. They have also provided you with their plan data and their marketing plan data. The worksheet titled “Information” in the file provides additional background information about the spreadsheet. After meeting together, you and the client have identified the following basic questions to begin with:
- Overall Performance Analytics:
- How is MMI performing globally (Hint: PivotTable, PivotChart, of net revenue by region and country)?
- How are various MMI brands performing?
- How are the various sales channels performing?
- How are the various models performing? What models generate the five highest and five lowest net revenue? (Hint: create separate PTs, with Value Filters on the Model field)
- Financial Analytics:
- What is the current contribution margin per model?
- What is the current contribution margin and sales volume per channel?
- What is the a) dollar change b) average change by quarters in variable cost per model?
- Why is it helpful to do both a and b?
- Operations Analytics
- What model options are the top and bottom sellers?
- How many average days on the lot are the various brands and models within each? Sort by longest to shortest.
- Are average days on lot affected by seasons?
These questions are aimed at giving you and the client a basic understanding of the data and the company’s current status. You will then need to identify additional questions for analysis. This is described in the Assignment Instructions docx.
