Spreadsheet Assignment #4: Car Sales (Feedback/Formative)

PROGRAMS: MS Excel

SKILLS: Input Formula

As owner of Quality Cars, a local used car lot, you are reviewing your sales for the 2006 calendar year. You decide to use a spreadsheet to track the efforts of your three salesmen. Begin by inputting the follow sales information – note the columns in which you should input the information.

  • Cells B1, C1, and D1 should be merged into one. First highlight the three cells, then click on the Merge and Center icon.
  • In order to have the Cars Sold header (cell B2) appear on two lines, you must change the cell formatting and select the Wrap Text option on the alignment tab. It is here that you would also change the vertical alignment to Center for the other cells in the row.

  • Under the Cars Sold Total you should input a formula for the sum of each column. You can either use the same format as you used earlier, with the range equal to the individual cells separated by commas; or you can simply add the cells together in the formula bar.
  • You should format the cells in column A to be dates in the format shown. Note also that you only need to input the first two dates, then you can highlight them and drag the remainder. If you have a series of X’s appear, it is indicating that that contents don’t fit in the cell. Adjust the cell width to accommodate the contents.
  • Columns B through M should have border colours as shown – note the bold in row 15.
  • The average selling price for a car at Quality Cars is $7,500 (Which begs the question of the truth of the company name, but we’ll ignore that). Use formulas to calculate gross sales in the appropriate columns. Your formula should total the number of cars sold multiplied by 7500.
  • Sales personnel at Quality Cars are paid a 7% commission for the cars they sell. Use this information to calculate the monthly incomes in the appropriate columns using formulas. Your formula should total the gross sales multiplied by 0.07.
  • Be sure to change the cell formatting for the gross sales and commission columns so that they are all currency numbers with no decimals

Charts

  • Make a cylinder chart to include each salesman and their total number of cars sold.
  • Be sure to insert the chart as a separate sheet and title the sheet “Car Sales”
  • Adjust the colours of the cylinders to match the colours of the spreadsheet
  • Insert clipart images of vehicles anywhere on the chart sheet to dress it up a bit
  • Make a pie chart of the total commission made by each salesman
  • Insert the chart on a separate sheet titled “Commission”
  • Change the chart colours to reflect the colours used for the salesmen
  • Make a stacked 3D column chart of total gross sales according to month
  • Use the information from all three salesmen and add together their gross sales
  • Insert the chart as a separate sheet and change its name to “Gross Sales”
  • Change the chart colours to reflect the colours used for the salesmen

 

When you are satisfied with your assignment, hand in the completed document to the appropriate In Basket folder. (Remember to save it lastname,firstname…)