Business 91L Sample Excel Exam   -     Created Spring 2011

Open a web browser.  Go to  http://bus91L.altervista.org/Data/ChallengeExamS11.xlsx

Save the file to the desktop, using your name as the file name, and then open it.

Go to the “SalesReport” sheet.

In cells B3, B4 and B5 enter your first name, last name, and section number.

When the instructions ask for a formulas or function you must use cell references. Example = B4-C4 is correct =85-30 is incorrect.

The SUM Function should only be used in cells F11, F12, F13, F14 and F15.

 

Excel Part One – Formulas and Functions

  1. In cell E5 enter a formula to display your last name, a comma, and your first name. The formula can either use the & symbol or the Concatenate function. Use cell references, not text, for your last name and your first name.
  1. In cell B10 enter a formula to find the profit on a single Milky Way candy bar (= price- cost.)
  1. Copy the formula from cell B10 into cells C10, D10 and E10.
  1. In cell B14 enter a formula to find the number of Milky Way candy bars that were sold (Starting inventory + new inventory - ending inventory.)
  1. Copy the formula from cell B14 into cells C14, D14 and E14.
  1. In cell B15 enter a formula to show the total profit earned from selling Milky Way candy.
  1. Copy the formula from cell B15 into cells C15, D15 and E15.
  1. In cell F8 enter an AVERAGE function to find the average sales price. (The average of cells B8, C8, D8 and E8.)
  1. Copy the function from cell F8 into cells F9 and F10.
  1. In cell F11 enter a SUM function to find the total number of candy bars on hand at the beginning of the week. (The sum of cells B11, C11, D11 and E11.)
  1. Copy the function from cell F11 into cells F12, F13, F14, and F15.
  1. In cell B16 enter a formula to show the profit on Milky Way bars as a percentage of the total profit. (Divide the profit in cell B15 by the total profit in cell F15.)  Use an absolute cell reference (using two $ signs as part of the cell reference) for the denominator.
  1. Copy the formula from cell B16 into cells C16, D16 and E16. (If you get the error message #DIV/0, fix the denominator in the formula in cell B16 and try again.)
  1. Change the formatting for cells B16, C16, D16 and E16 to show exactly one decimal place.
  1. Use the “Merge and Center” icon to merge cells A18, B18 and C18.
  1. Apply a thick box border to the merged cell.
  1. Add a Green Fill color to the merged cell.
  1. Click on cell A20. From the Data tab on the ribbon, select Data Validation. Add a data validation list using the items in cells B7, C7, D7 and E7.
  1. After you have created the validation list, enter the name of a candy bar into cell A 20.
  1. In Cell B20, enter an HLOOKUP function to find the average number of candy bars sold daily for the candy bar selected in cell A20. The formula will have the syntax =HLOOKUP(Cell,TableRange,RowNumber)/7. (Note replace the words Cell, TableRange, and RowNumber with values.)
  1. In cell C20 enter an IF Function to test if the number of sales in cell B20 is greater than 40. If the number sold is greater than 40, the function should display “Good” in cell C20.  If the number sold is not greater than 40, the function should display the word “Bad” in cell C20.

Excel Part Two  - Working with Data

 

Use information on the StudentData worksheet to answer these questions. Put your answers in cells B23, B24, B25, B26 and B27 in the SalesReport worksheet. 

  1. Sort the data by city. How many students live in Palo Alto? (Hint: Mouse over the cells containing the words Palo Alto and look at the status bar.)
  1. Apply a filter to the data.  How many students are Freshman Management majors? Remove the filter.
  1. Use the sort icon to sort the data by Major, Class and Phone. (Hint: you’ll need to use three levels of sorting. There is an option to add a level in the Sort dialog box.) How many Freshman Accounting students paid less than $50 each in phone bills?
  1. Sort the data by major. Apply subtotals to each change in major to find the total food bills. What was the total amount spent by Finance students? Remove the subtotals.
  1. Sort by major. Apply subtotals to each change in major to find the average phone bills. What was the average amount spent by Accounting students? Remove the subtotals.
  1. Using the StudentData worksheet as your source, build a Pivot Table report in a separate worksheet.

(Hint: the Pivot Table icon is on the Insert tab.)

    1. Create a report showing the total GPA for each major.
    1. Change the Value Field Settings to show the Average GPA.
    1. The numbers should be formatted to show exactly two decimal places.
    1. Sort the GPA’s in the order largest to smallest.

 

Excel Part Three – Charts

 

In a separate worksheet create a 3-D pie chart showing the names of the candy bars. Each wedge will represent the profit from sales of one candy bar.

  1. Select the cells containing information for building the chart. Data will be found in cells B15 to E15. Labels will be found in cells B7 to E7. Go to the Insert tab and create a 3-D Pie Chart.
  1. Move the pie chart into a separate chart sheet. Rename the new worksheet as “Candy”.
  1. Add a chart title. Call it “Candy Profits”. 
  1. Edit the title to include your name and section number on a second line.
  1. Add data labels for the pie wedges to show the candy bar names. Do not show any numbers in the labels.
  1. Remove the legend.
  1. Format all of the text in the chart with a 14 point font.
  1. Pull one of the wedges out from the center of the pie.
  1. Add a fill-effect to the plot area; using a texture with a light color.
  1. Add clipart, showing a candy bar, to the chart.