Question 1.1. (TCO 1) You work at ABC Electronic and this is your first day. Your supervisor has given you a task to inventory all of the small electronics such as mp3's, iPods and tablets in MS Excel. Your supervisor has a list of columns that are mandatory which are item name, bar code number, item quantity, cost per item and total cost for each item. Additionally, you must highlight, in yellow, any item quantity below 5 so a refill order can be placed. Explain how you will calculate the necessary values, format the titles, headings and the data contents of each column, and organize the data to facilitate management action. (Points : 40)
Question 2.2. (TCO 3) Michael is a professor of Mathematics with DeVry University and is going to prepare a series of statistical or charts which show the academic success rates of students for each earned letter grade including A, B, C, D, and F letter values. The charts will be presented to all math majors during a special scheduled session and then to all non-math majors during other multiple scheduled sessions. Before any presentation, Michael had a meeting with the academic chair, Sally, and the following concerns were expressed. You decide to help Michael create a document formalizing suggestions based on your extensive experience working with Microsoft Excel charts.
Ted has been retired the last few years and has passionately resumed golfing. He has recorded the round number, date, golf course, number of putts, and total score for each round in Excel as shown in the truncated diagram above. After almost three years of data collection and 300+ rounds of golf, Ted decides to analyze the data to determine if he has improved during this time.
Part 1: Ted would like to calculate the average scores for each golf course over the past three years. What Excel tool will allow you to do this? Be sure to specify the exact steps to obtain the desired results.
Part 2: Based on your response from part 1, what other Excel tool can be used to produce the same results? Again, be sure to specify the exact steps to obtain the desired results.
Part 3: Ted would also like to view only the rounds of golf where he had greater than 32 putts for the round. The number of putts should be displayed from highest to lowest. Please describe the steps to achieve this result.
Part 4: To determine if he has improved over the past 3 years, Ted would like to calculate a running average in column F. A running average displays the average for all rounds played to date. For example, the running average in cell F4 should show the average for the total scores for round numbers 1 through 3. Write this formula as if it were in Cell F2. Write the formula so that it may be copied down the column without modification.
(Points : 40)
Question 4.4. (TCO 5) You have been hired to analyze car sales for a company which owns 5 Toyota dealerships in different cities where they sell several models of Toyota. You have asked each dealer to send you monthly sales numbers by each model for the last 12 months in Excel Template you have provided. Explain the steps to consolidate and summarize the data to prepare a visual representation of the results so that they can be presented to the owners. (Points : 40)