Exam 1 Take home
Requirements:
You work for a used car wholesaler that provides dealer to dealer service. Your current data is
incomplete and corrupted and needs to be addressed. Listings have been moved to Excel for
cleaning and analysis prior to uploading the information to our listing website. Your job is to
clean the data, filter irrelevant listings, and answer all of the following questions.
Criteria:
– Ensure that all columns are adjusted to show values in all cells.
– Include all borders for all cells.
– Bold and italicize headers.
– Check column K (color) for structural errors. Label your final pivot chart worksheet with
all corrections Clean.
– Remove all listings that do not have data in column I (condition). Copy remaining listings
to a new worksheet. Label this worksheet BlanksRemoved. Place a comment in cell I1
of this new worksheet with the number of listings removed.
o Continue your work from this new worksheet
– Remove all convertibles from the data and move the remaining listings to a new sheet.
Label the new sheet NoConvertibles. In the new worksheet, insert a comment with the
number of listings removed in cell D1.
o Continue your work from this new worksheet.
– I am not interested in vehicles with the following makes (column C):
o BMW
o Cadillac
o Lexus
o Mercedes-Benz
– Provide a separate worksheet that excludes irrelevant makes, name it NoLuxury, and
insert a comment (in cell C1) on how many vehicles were removed.
o Continue your work from this new sheet.
– Create a scatterplot for odometer (column J) and selling price (column N). You can
highlight column J, then while holding the Ctrl key highlight column N. You can then
insert your scatterplot.
o Based on the scatterplot, can you identify any outliers based on odometer or
selling price? Highlight outliers rows in red.
– Given the current market for used vehicles, if these cars sold today, we would expect a
25% premium. What would the selling price be with a 25% increase in value? Calculate
these values in column O using the estimated value in column M as your basis, label
column O TodaysPrice.
– I will need to invest in basic maintenance for each vehicle. I can estimate that cost as
0.5% of the Estimated Value. Calculate the maintenance cost for each vehicle in column
P and label the column MaintenanceCost.
You work for a used car wholesaler that provides dealer to dealer service. Your current data is
incomplete and corrupted and needs to be addressed. Listings have been moved to Excel for
cleaning and analysis prior to uploading the information to our listing website. Your job is to
clean the data, filter irrelevant listings, and answer all of the following questions.
Criteria:
– Ensure that all columns are adjusted to show values in all cells.
– Include all borders for all cells.
– Bold and italicize headers.
– Check column K (color) for structural errors. Label your final pivot chart worksheet with
all corrections Clean.
– Remove all listings that do not have data in column I (condition). Copy remaining listings
to a new worksheet. Label this worksheet BlanksRemoved. Place a comment in cell I1
of this new worksheet with the number of listings removed.
o Continue your work from this new worksheet
– Remove all convertibles from the data and move the remaining listings to a new sheet.
Label the new sheet NoConvertibles. In the new worksheet, insert a comment with the
number of listings removed in cell D1.
o Continue your work from this new worksheet.
– I am not interested in vehicles with the following makes (column C):
o BMW
o Cadillac
o Lexus
o Mercedes-Benz
– Provide a separate worksheet that excludes irrelevant makes, name it NoLuxury, and
insert a comment (in cell C1) on how many vehicles were removed.
o Continue your work from this new sheet.
– Create a scatterplot for odometer (column J) and selling price (column N). You can
highlight column J, then while holding the Ctrl key highlight column N. You can then
insert your scatterplot.
o Based on the scatterplot, can you identify any outliers based on odometer or
selling price? Highlight outliers rows in red.
– Given the current market for used vehicles, if these cars sold today, we would expect a
25% premium. What would the selling price be with a 25% increase in value? Calculate
these values in column O using the estimated value in column M as your basis, label
column O TodaysPrice.
– I will need to invest in basic maintenance for each vehicle. I can estimate that cost as
0.5% of the Estimated Value. Calculate the maintenance cost for each vehicle in column
P and label the column MaintenanceCost.
– I will also have to pay a transportation cost for each vehicle. This cost is calculated with a
fixed rate of $500 and a variable cost of 1.25% of the vehicles Estimated Value.
Calculate this cost for each vehicle in column Q, and label column Q
TransportationCost. Translate this to a linear equation and include the equation as a
comment in cell Q1.
– At the bottom of columns O, P, and Q provide the average for that column.
– Label column R Commission. Use the following formula to determine commission on
each vehicle =rand(). Copy the formula to the entire column, then copy and paste so
that only the values remain.
– Change the number formatting so that column R is listed as a percentage.
– Label column S CommissionPayment, and calculate the value using the commission
and the Selling Price (column N).
– Calculate the difference between Estimated Value and Selling Price in column T.
Label column T Difference. Format column T so that vehicles that sold for more than
$1000 above the Estimated Value are shaded green.
– Present the data in descending order based on the Difference column.
Quality Check: Your completed exam (workbook) should have the following worksheets in this
order.
– Sheet 1
– Clean
– Blanks Removed
– No Convertibles
– No Luxury
fixed rate of $500 and a variable cost of 1.25% of the vehicles Estimated Value.
Calculate this cost for each vehicle in column Q, and label column Q
TransportationCost. Translate this to a linear equation and include the equation as a
comment in cell Q1.
– At the bottom of columns O, P, and Q provide the average for that column.
– Label column R Commission. Use the following formula to determine commission on
each vehicle =rand(). Copy the formula to the entire column, then copy and paste so
that only the values remain.
– Change the number formatting so that column R is listed as a percentage.
– Label column S CommissionPayment, and calculate the value using the commission
and the Selling Price (column N).
– Calculate the difference between Estimated Value and Selling Price in column T.
Label column T Difference. Format column T so that vehicles that sold for more than
$1000 above the Estimated Value are shaded green.
– Present the data in descending order based on the Difference column.
Quality Check: Your completed exam (workbook) should have the following worksheets in this
order.
– Sheet 1
– Clean
– Blanks Removed
– No Convertibles
– No Luxury