Question
Imagine you are the Lead Business Analyst at Modern Motors , an automobile conglomerate that manufactures cars across a large range of price points. Given
Imagine you are the Lead Business Analyst atModern Motors, an automobile conglomerate that manufactures cars across a large range of price points. Given the scale of your firm, you have different brands for different consumer segments.
For the price-conscious working-class consumers, Modern Motors manufactures cars under the brandRocinante.The various models of Rocinante fall in the range of5-10 lakh.The brand is known for cars that arevalue for money and very attractive mileage.
For the consumers willing to purchase a luxury car, Modern Motors operates under the brandMarengo.Marengo models fall in the range of30-60 lakh.They are considered sports cars and havehigh horsepower and top speed.
Every time Modern Motors launches a new model, it advertises the model's specifications, such as price, mileage, and top speed, because consumers usually take these factors into account before purchasing cars.
Suppose your chief engineer has recently come up with two new models:Rocinante36 and Marengo32.He promises that they will outperform the existing competitors in the market.
Rocinante36is priced at7 lakhand has been designed to deliver a mileage of22 km/litreand a top speed of140 km/hr.
Marengo32is priced at41 lakhand has been designed to deliver a mileage of15 km/litreand a top speed of210 km/hr.
The chief engineer has built 20 prototypes of each of the two models to check if the cars are performing as per the desired specifications. If the cars are performing better than expected, then the engineer wants to revise the specifications. However, if they are performing worse than expected, then it is an indication that more investment is needed in the design to improve their performance.
The test drive results for the 20 prototypes of each of the two models are provided below titledData Exhibit 1.During the test, the mileage and the top speed is recorded for each of the prototypes. The data will be used for performing hypothesis testing for the claims made on the specifications.
Additionally, the chief engineer reported that the cost of manufacturing the Rocinante36 model would be6 lakh per carand that of the Marengo32 model would be33 lakh per car, including all the overhead expenses. Given the business scenario, you need to invest the total manufacturing cost upfront. The manufacturing cost will be recovered slowly by the next financial year as sales start happening. These details are summarised in the table given below.
Specifications | Rocinante36 | Marengo32 |
Price | 7 lakh | 41 lakh |
Manufacturing cost | 6 lakh | 33 lakh |
Mileage | 22 km/litre | 15 km/litre |
Top speed | 140 km/hr | 210 km/hr |
Currently, the company hasenough cash reserves to invest in only one model.Therefore, as the Lead Business Analyst, you need to carefully choose the right model and decide the number of cars that you want to produce. Since you believe in making data-based decisions, you ask your team to provide you with the sales data of all the past models released under the brands Rocinante and Marengo. The sales data is provided below titledData Exhibit 2.
Remember that you need to present your findings to the CEO by next week. You need to demonstrate these findings in an easy-to-understand presentation, which should also include the analysis that you have performed on the data.
The data in Exhibit 1 and Exhibit 2 will be used to predict the sales for Rocinante36 and Marengo32 and to make decisions regarding which model will generate maximum revenue for the company.
Project objective:
Perform hypothesis testing using MS Excel to verify the claims that the engineering team has made about the specifications (mileage and top speed) of the Rocinante36 and Marengo32 models. .
DataExhibit 1
Rocinante 36 | Marengo 32 | ||||
Car No. | Mileage (km/ltr) | Top speed (km/hr) | Car No. | Mileage (km/ltr) | Top speed (km/hr) |
1 | 21.2 | 151.9 | 1 | 15.04 | 211.3 |
2 | 21.79 | 147.6 | 2 | 15.41 | 207 |
3 | 21.87 | 145.6 | 3 | 15.43 | 206.2 |
4 | 22.71 | 136.1 | 4 | 15.92 | 213.4 |
5 | 22.52 | 139.7 | 5 | 15.2 | 204.2 |
6 | 21.41 | 146.1 | 6 | 15.22 | 208.7 |
7 | 22.71 | 139.6 | 7 | 14.6 | 200 |
8 | 21.71 | 143 | 8 | 14.41 | 210.9 |
9 | 19.95 | 136.4 | 9 | 14.79 | 208.5 |
10 | 20.65 | 146.6 | 10 | 14.81 | 214.2 |
11 | 22.86 | 139.9 | 11 | 15.61 | 215.8 |
12 | 21.12 | 136.6 | 12 | 15.76 | 215.8 |
13 | 22.8 | 148.5 | 13 | 14.97 | 215.2 |
14 | 20.89 | 143.4 | 14 | 14.71 | 218.7 |
15 | 22.49 | 134.2 | 15 | 15.55 | 208 |
16 | 20.94 | 140.5 | 16 | 15.19 | 212.2 |
17 | 20.37 | 137.8 | 17 | 15.36 | 219.7 |
18 | 22.72 | 135.1 | 18 | 15.93 | 216.4 |
19 | 20.54 | 138.8 | 19 | 14.54 | 205.8 |
20 | 21.14 | 132 | 20 | 14.76 | 209 |
DataExhibit 2
Rocinante Model
Cars | Sales (in 1,000 units) | Price (in lakh rupees) | Mileage (Km/ltr) | Top speed (Km/hr) |
Rocinante 1 | 171.877 | 6.1 | 15.8 | 168.2 |
Rocinante 2 | 139.796 | 6.1 | 12.1 | 149.6 |
Rocinante 3 | 178.947 | 9.9 | 17 | 173.4 |
Rocinante 4 | 140.022 | 5.8 | 11.6 | 170.6 |
Rocinante 5 | 186.476 | 10 | 17.2 | 175 |
Rocinante 6 | 192.123 | 6.5 | 17.6 | 173.1 |
Rocinante 7 | 175.085 | 5.5 | 16 | 184.6 |
Rocinante 8 | 146.882 | 8.4 | 13 | 175.7 |
Rocinante 9 | 202.847 | 6.6 | 19.3 | 166.7 |
Rocinante 10 | 149.933 | 8.8 | 13.3 | 175.4 |
Rocinante 11 | 171.579 | 9.5 | 15.8 | 150.9 |
Rocinante 12 | 201.512 | 9.2 | 19.2 | 140.5 |
Rocinante 13 | 142.604 | 8.1 | 11.7 | 150.2 |
Rocinante 14 | 135.158 | 5.4 | 11.2 | 146.2 |
Rocinante 15 | 107.322 | 5.6 | 7.8 | 151.2 |
Rocinante 16 | 199.699 | 6.3 | 19 | 143.2 |
Rocinante 17 | 202.875 | 5.8 | 19.7 | 178.9 |
Rocinante 18 | 204.406 | 7.7 | 19.5 | 168.2 |
Rocinante 19 | 107.331 | 7.2 | 7.5 | 164.7 |
Rocinante 20 | 190.345 | 8.4 | 18.1 | 182.7 |
Rocinante 21 | 108.419 | 8.4 | 7.9 | 165.7 |
Rocinante 22 | 150.249 | 5.7 | 12.6 | 137 |
Rocinante 23 | 155.882 | 5.5 | 12.9 | 173.9 |
Rocinante 24 | 100.98 | 8.4 | 7.6 | 165.1 |
Rocinante 25 | 182.679 | 5.6 | 16.7 | 160.1 |
Rocinante 26 | 166.752 | 9.9 | 15.5 | 155.6 |
Rocinante 27 | 121.561 | 6.2 | 9.5 | 155.4 |
Rocinante 28 | 174.256 | 9.9 | 16.3 | 161.9 |
Rocinante 29 | 119.018 | 6.5 | 9.1 | 146.8 |
Rocinante 30 | 169.842 | 7.1 | 15.3 | 149.5 |
Rocinante 31 | 198.311 | 9.6 | 19.1 | 146.5 |
Rocinante 32 | 204.875 | 5.8 | 19.4 | 178.9 |
Rocinante 33 | 119.561 | 6.2 | 9.4 | 175.4 |
Rocinante 34 | 203.875 | 5.9 | 19.4 | 175.9 |
Rocinante 35 | 118.561 | 6.1 | 9.4 | 165.4 |
Marengo Model
Cars | Sales (in 1,000 units) | Price (in lakh rupees) | Mileage (Km/ltr) | Top speed (Km/hr) |
Marengo 1 | 20.896 | 42.5 | 9.3 | 199.4 |
Marengo 2 | 31.048 | 36 | 9.7 | 235.2 |
Marengo 3 | 29.904 | 54.7 | 16.6 | 240.8 |
Marengo 4 | 28.792 | 42.7 | 11.7 | 232.5 |
Marengo 5 | 16.776 | 44.9 | 13.7 | 188.8 |
Marengo 6 | 18.928 | 35.5 | 9.6 | 184.2 |
Marengo 7 | 22.776 | 51.3 | 13.7 | 207.7 |
Marengo 8 | 36.824 | 30.4 | 12.6 | 249.5 |
Marengo 9 | 22.216 | 38.4 | 16.2 | 175.8 |
Marengo 10 | 35.456 | 32.2 | 9.6 | 245.6 |
Marengo 11 | 28.576 | 59.4 | 11.3 | 223.3 |
Marengo 12 | 33.648 | 50.8 | 13.6 | 240 |
Marengo 13 | 33.44 | 57.5 | 13.5 | 250 |
Marengo 14 | 34.912 | 44.6 | 11.1 | 247.4 |
Marengo 15 | 24.016 | 34.6 | 14.1 | 192 |
Marengo 16 | 20.992 | 56.4 | 13.4 | 201.2 |
Marengo 17 | 21.696 | 41.4 | 12.8 | 193.3 |
Marengo 18 | 17.832 | 53.7 | 16 | 202 |
Marengo 19 | 25.624 | 44.8 | 12.7 | 208.9 |
Marengo 20 | 21.92 | 32.4 | 13.6 | 176.9 |
Marengo 21 | 24.792 | 40.9 | 11.6 | 218.2 |
Marengo 22 | 22.256 | 44.1 | 11.4 | 190 |
Marengo 23 | 15.488 | 46.6 | 16.3 | 176.8 |
Marengo 24 | 21.64 | 31.4 | 14.5 | 178.9 |
Marengo 25 | 23.264 | 54.7 | 14.3 | 192.3 |
Marengo 26 | 19.328 | 37.9 | 9.4 | 184.1 |
Marengo 27 | 27.216 | 52.9 | 15.3 | 240.3 |
Marengo 28 | 23.096 | 42.7 | 15 | 192.1 |
Marengo 29 | 17.824 | 54.9 | 13.5 | 199 |
Marengo 30 | 19.144 | 44 | 11.8 | 181.1 |
Marengo 31 | 21.6 | 42.2 | 9.3 | 181.2 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started