Excel Practical Exam

Use Excel 2016 to complete the following exercises. Work is to be submitted electronically. Choose 3 out of the 4, or do all of the questions for extra credit. Each question should be done in its own file. Feel free to use your book and class notes. You may not work together on this exam.

  1. Fundraiser

The third annual spring term “Care for Critters” fundraiser was a tremendous success! There were three posts for the event: a table in front of the Commons; a booth at Gengras; and another at Konover. The booth at Commons made $105 from selling t-shirts, $30 from selling stickers, and the donation jar earned $23.75. At Gengras, the t-shirts brought in $85, stickers earned $10 and the donation jar collected an additional $43.95. Konover sold no t-shirts or stickers, but the donation jar netted a cool $19.67.

Create a spreadsheet with the appropriate labels, data, formulas and formatting. How much money did each item make? What was the total raised at each location? How much did the full fundraiser actually earn? What percent of the total donation came from each location?

  1. Buying a Car

You see an ad for a used car that you would like to buy. The ad says that the dealer will give you a $1000 trade in on your old vehicle. The asking price for the car (before trade-in) is $9,000. They will offer you a 4% interest rate for a 3-year loan. What is the amount you need to finance? Use the PMT function to determine what your monthly payments will be. Copy the work, and then use the Goal Seek tool to determine what the asking price of the car, before trade-in, would have to be to make a monthly payment of $200. Show both scenarios in your worksheet.

  1. Class Make-up

Create a chart for each bullet of data:

  • There are 28 students in a class. 10 are freshmen, 8 sophomores, 4 juniors, and 1 senior. 5 people did not answer the question;
  • There are 11 men and 17 women – do a pie chart for this.
  • If only 23 of the 28 students did the homework assignment, what percent of the class is that? Another pie chart.
  1. The Sales Report

Title / Year / Genre / 2016 Unit Sales / 2016 Unit Rentals
The Artist / 2011 / Drama / 5 / 20
Argo / 2012 / Drama / 15 / 150
Mutiny on the Bounty / 1935 / Drama / 3 / 2
Oliver! / 1968 / Musical / 20 / 200
In the Heat of the Night / 1967 / Drama / 4 / 10
The Sting / 1973 / Comedy / 8 / 15
The Godfather / 1972 / Drama / 15 / 40
Chariots of Fire / 1981 / Drama / 5 / 55
Ordinary People / 1980 / Drama / 2 / 15
Out of Africa / 1985 / Drama / 3 / 20
West Side Story / 1961 / Musical / 6 / 75
Lawrence of Arabia / 1962 / Drama / 4 / 50
My Fair Lady / 1964 / Musical / 10 / 150
The Sound of Music / 1965 / Musical / 25 / 400
Patton / 1970 / Drama / 10 / 20
You Can't Take it With You / 1938 / Comedy / 2 / 6
Gone with the Wind / 1939 / Drama / 14 / 65
Rebecca / 1940 / Drama / 1 / 3
Mrs. Miniver / 1940 / Drama / 3 / 8
Casablanca / 1943 / Drama / 45 / 150
Hamlet / 1948 / Drama / 3 / 30
Around the World in 80 Days / 1956 / Comedy / 1 / 50

Copy and paste the contents of the table below into a blank workbook. Name the Sheet1 tab “Data,” and then add a new sheet named report. Create a pivot table in cell A3 of the Report sheet. Add all of the field to the PivotTable. Move “Title” below “Genre” in the Row Labels area. Remove “Year” from the Values area.