Assignment Task
Final Assessment Instructions:
You have been tasked to manipulate team data to create visual charts and/or calculations, that your team can use in their business plan to open a secondary car lot, based on the vehicle statistics from each make and model you want to sell . The report will demonstrate both graphical and numerical formats, the price of the vehicles, and a visual display of the values of the vehicles. You will create charts to display results based on horsepower and MPG, and which origin produces the most desirable models. Include all the formulas, functions and formats listed below along with any additional formats you see suitable to create your report and make it unique to you.
Complete the following steps in the order in which they appear.
Open Excel_Final_Assessment_Vehicles.xlsx & save in your documents folder with the name Vehicles_Inventory_(Your last name-Your Student Number).On the Vehicles sheet autofit the contents as neededApply Cell style Heading 1 to A1-H1Convert your data to a table using Table Style Dark 9 (Be sure to indicate your data has headers)Using custom sorting options, sort the inventory list first by MPG, in descending order, and then by Acceleration in Ascending order, and then by Sale Price in ascending order. (Besure to indicate your data has headers)Apply % format to the range G4:G149Apply freeze panes so the top row remains in view when you scroll to the bottom of the table.Create a formula in cell J3 that counts how many of these vehicles were made in Japan.(remember to use the entire data range in your formula & use absolute references when necessary)In Cell J4 create another formula that counts how many of these vehicles were made in Europe, and in cell J5 how many were made in the US.Change the border color around Cells J3-J5 so they each have their own color.Apply accounting format to H2:H407 (autofit the contents)Center the data in columns B:FIn Cell J9 enter a formula to calculate the average sale price of all models. Apply Currency format and auto fit the cell contents.On the Popular Make worksheet, autofit the contentsIn cell D2:D407 create a formula that calculates the total revenue made by how many vehicles were sold at the current sale price. Apply accounting format to the same range.Type Revenue Made in cell D1.In cell F8 create a formula to display the MAX amount of revenue made.In cell F9, create a formula to display the MIN amount of revenue made.In cell F10 create a formula to display the average amount of revenue made.Convert the data in A1:D407 to a table with a style of your own choice, be sure to add a total row and bold the data in the total row and freeze the top row.Apply the average calculation to total sold column in the total row.Using the Vehicles worksheet, filter your data to display only those vehicles from Europe origin.Create a 3D Clustered Column Chart to display the Europe Origin vehicles and sale prices only, move the chart to a new worksheet and name it Charts for Presentation.Change the title of the chart to “Your Name” -Made in Europe, change the tab color for the worksheet to standard green, and move the worksheet to the end of the workbook.AddChange the chart style to style 6 and change the category text to yellow.Change the values to a maximum bound of 400 000.00 and minimum of 100 000.00 and change the number format to currency.On the Purchase Cost Worksheet, in cell D2:D407 use PMT to calculate the monthly payments for each make and module based on the provided interest rate, the sale price and a payment term of 60 months. Be sure to use absolute references when needed, return a positive value with two decimal places only, and apply accounting format to the results.In cells E2:E407 create a formula to determine how much less the monthly payments will be after applying the employe discount rates found in cells F2:F407, (use brackets in the appropriate places), apply accounting format to the results.Create a Pivot table on a new worksheet, using the data from the Purchase Cost Worksheet, with the following criteria:Make Model in the rows, (filtered to show only models that start with the letter R)Monthly Payments in the Values (Accounting format two decimal places)Sale Price in the columns (Currency format show zero decimal places)Change the style of the Pivot table to “Orange Pivot Style Medium 17.”Insert a 3D, 100% Stacked Bar Pivot chart, moving the top left corner of the chart to cell A15.Change the style of the chart to Style 2, and change the Colors to Monochromatic Palette 7Move the legend to the bottom position on the chart, close all Pivot Chart editing fields, rename the sheet to Pivot Table & Chart, and apply the Standard Orange color to the worksheet tab.On the Purchase Cost worksheet, in cell J6:L7, use VLOOKUP to find the data requested in each column. (Be sure to use proper cell references if required, apply proper number formatting to the results)On the Popular Make worksheet apply the accounting number format to the Sale Price column. (Autofit contents as necessary)Apply conditional formatting to the Sale price to highlight in a color of your choice all prices greater than 400,000.00 and apply the 5-ratings bar icon set to the Total sold column.Group all worksheets, create a footer with the following field criteria:File name in the leftCurrent Date in the centerYour student number in the rightMove to the Header and add your First and Last name to the center field.Go back to Normal View and ungroup the worksheets.Place your student number under the Subject line in the file properties.Place your email address in the Comments Section of the file properties.Save your file and submit to the Blackboard course assignment folder, once graded SAT, proceed to the Final Akindi Test link in Blackboard, and answer the multiple-choice questions using your file to select the correct answers.
