(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

BIS 155 Data Analysis with Spreedsheet with LAB Final Exam Answers

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.

Part 1: The academic chair loves all charts; however, she instructs Michael to consider if all charts are appropriate for non-math majors. For example, Michael created some box and whisker diagrams also known as Box Plots and non-math majors may not understand how to read these types of charts so what other types of charts could Michael consider creating to display academic success rates for earned letter values including A, B, C, D, and F grades and why? 
Part 2: Michael also created some histogram charts which are simply customized column charts. Since normal column charts are used often even for non-math majors, would you suggest that Michael also show the histogram charts to the non-math majors? Why or why not?
Part 3: Michael also used some well-balanced creativity using color choices including light earthly tone reds and greens; however, should Michael consider that some audience members whether math or non-math majors may be colored blind? Why or why not?

(Points : 40)      



Question 3.3. 

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)      



Question 5.5. (TCO 9) You work for a small shoe factory where all the records are kept in a Microsoft Access Database. Your boss has asked you to create a report that has the following sections:

  1. Provide a chart showing the shoe type/model with most sales as well as the one with least.
  2. Identify costs per month for the making of the shoes per type/model.
  3. Show which month has the least sales to create a better marketing campaign.
  4. Identify which store sells the most shoes to create a final report.

Make sure to describe at least three different formulas or functions you will use to create the analysis needed for the sections above. Explain how you will provide the final report to your boss. (Points : 40)