Group Assignment 1 Spreadsheet Modelling PDF
Document Details
Uploaded by EducatedFermat
Gopinath
Tags
Summary
This document contains a group assignment for spreadsheet modeling, focusing on analyzing second-hand car sales data from the CarDekho website. Students are asked to answer questions about average car prices, brand comparisons, and relationships between factors like price, mileage, fuel type, and transmission using Excel functions. A dashboard visualization is also required.
Full Transcript
Dear Friends, Please find below the Group Assignment. The data set and the group details are attached. The following dataset is from the website CarDekho, which is a second-hand car sale website. The dataset contains details of different vehicles that are sold, the year of the make, km driven, sel...
Dear Friends, Please find below the Group Assignment. The data set and the group details are attached. The following dataset is from the website CarDekho, which is a second-hand car sale website. The dataset contains details of different vehicles that are sold, the year of the make, km driven, selling price, fuel type, whether sold by owner/dealer, Automatic or Manual transmission, etc. Based on the data, you can answer the following questions: After importing the data, Remove Duplicates. 1\. How many total number of cars of Maruti are up for sale? - Use Text to column with space as delimiter to separate the make of the car from first column. 1000 Cars of Maruti are up for sale. To count the no. of cars of Maruti we can use COUNTIF Function. 2\. What is the average price of a Hyundai Petrol-based car up for sale? - Rs. 3,43,481.3 Use AVERAGEIFS (Price column as average range, criteria 1 range - Make column, criteria 1 "Hyundai", Criteria 2 range - Fuel Column, Criteria 2 "Petro" 3\. What is the comparison of average prices of the different brands? Use UNIQUE Function to make a list of brands (Starting from say L8:L36) and Use AVERAGEIFS (Price column as average range, criteria 1 range - Make column, criteria 1 - L8). Copy formula from L9:L36. We can Copy paste values of this table and sort for Price - Lowest to highest. 4\. Is there a difference between the average price of Petrol and Diesel-based makes? Make Table with brand name and average price. Use AVERAGEIF with Criteria 2 as Petrol and Criteria 2 as Diesel and take differences. 5\. Is there a relationship between the price of the car and the number of km driven? 6\. Do Automatic transmission vehicles get a better price than manual transmission vehicles? 7\. Does the ownership status (Dealer vs individual, first owner vs second owner, etc.) have an impact on the selling price? 8\. Summarize the dataset and create an intuitive and interactive dashboard to visualize the insights. Note: The submission has to be in the form of an Excel sheet. In Excel itself, you can provide an explanation for the calculation. Some questions (i.e., the dashboard) can be created after the final session. Thanks & Regards, Gopinath
