ISM 3011 - Assignment 2 Casandra Brewster is the manager of a large car business in the Florida panhandle

Subject:MS ExcelPrice:22.99 Bought6

Share With

ISM 3011 - Assignment 2

Casandra Brewster is the manager of a large car business in the Florida panhandle.  She has given you a subset of their sales data in a comma delimited format for you to analyze. The data is for the convertible sports cars sold in the first quarter (1 January – 31 March) of 2020.  The data set contains the make, model, year of production, the number of cylinders in the engine, the displacement in liters of the engine, a listing of whether the engine has a turbo charger or super charger, the engine’s horsepower, the type of transmission, the number of gears, the cost of the car, the car’s sales price, the date it entered inventory, the date it was sold, and its mileage.   The data set contains a mix of new and used cars.  A car with less than 50 miles on it is considered to be new. All your analysis should be done using formulas, functions, and other Excel tools.

 

Be sure to save your work as an Excel workbook and not as Comma Delimited text.

***Remember to save early, save often.

 

Data Preparation

 

Task 1: Import the file Q1_2020_sportscar_sales.txt into Excel.  Insert a row at the top of the data and add the following titles:

 

 

The DateIn and DateOut columns should be formatted as month/day/year.  The cost, price, and mileage should be formatted as a number with 0 decimal places and a comma separator.

 

Task 2: Create a column for the duration in stock. The title should be Duration and the values should be displayed as number with 0 decimal points.  The Duration column should be between the DateOut and the Mileage columns. Use a formula to calculate how long each car was in stock.

 

Task 3: Create a column for the profit from the sale.  The title should be Profit and the values should be displayed as a number with 0 decimal points and a comma separator.  The Profit column should be between the Price and DateIn columns.  Use a formula to calculate the profit for each sale.

 

Deliverables

 

Each deliverable should be on a separate tab in the workbook.  Each deliverable starts from the original version of the data set.

 

Deliverable 1: On a tab labeled Deliverable1 calculate the descriptive statistics for the data set.  The worksheet should have an appropriate descriptive title in row 1.  For the rest of the spreadsheet the A column should be used for labels and the B column to display the results.  The following data is requested:

                A) Count of new cars sold

                B) Count of used cars sold

                C) The maximum sale price for a car

                D) The minimum sale price for a car

                E) The average sale price for a car

                F) The median sale price for a car

                G) The most common model year of cars sold

                H) A count of the number of manual transmissions sold

                I) A count of the number of automatic transmissions sold

 

Deliverable 2: On a tab labeled Deliverable2 sort the data by transmission type and within each type by the number of gears. The transmission should be ascending order and the number of gears should be in descending order.

 

Deliverable 3: On a tab labeled Deliverable3 place a pivot table by maker with the count of how many cars were sold from that maker, the sum of their profit, and sum of their duration for that maker.

 

Deliverable 4: On a tab labeled Deliverable4 place a pivot table by year with the models sold in that year with their duration in stock and their profit.

 

Deliverable 5: On a tab labeled Deliverable5 place a pivot table by number of cylinders with the displacement under the number of cylinders.  Show the average horsepower and a count of the number of cars with that number of cylinders and displacement.

 

Deliverable 6: On a tab labeled Deliverable6 copy and paste all data from your initial worksheet.  Then create an XY scatterplot chart using the Duration and Profit. Title the graph and label the horizontal and vertical axis appropriately.