Solved by verified expert :Question 1. 1. (TCO 1) The Professor’s computer crashed and
all that is left of the grades for the semester is this screenshot. You have
been hired to recreate this worksheet and rebuild all the formulas. Here is a
list of the formulas needed:
Part 1: Cell E8 – Average for grades titled Assignment 1
through Assignment 3.
Part 2: Cell I8 – Total of grades titled Lab 1 through Lab
3.
Part 3: Cell M8 – Total Points – The total points are based
on the averages for Assignments, Labs, Midterm, & Final — Be sure to use
the chart in cells O2:P6 for the weight of each category.
Part 4: Cell N8 – Letter Grade – write a formula that will
take the total points and assign a letter grade. The grading scale is included
below. Do not forget to use absolute references where needed.
(Points : 40)
Question 2. 2. (TCO 3) A retail chain keeps daily data with
summaries of the dollar amounts of sales for each of 151 different products
sold at 23 retail stores. The daily data is tracked by product and by store. At
the end of the day each store sends its data to the sales manager at the
corporate office. The data is sent in an Excel spreadsheet. Describe a data
consolidation strategy and at least two different types of visualization
techniques the sales manager can use to analyze the data received from all
stores. Be sure to include formulas that are to be used in the Excel
spreadsheet. (Points : 40)
Question 3. 3. (TCO 4) You are an analyst for the Vanguard
Mortgage Company has been using a spreadsheet created by you in the past which
functions well. The current worksheet you created currently contains the
customer’s account, the cost of the house, down payment, amount financed,
mortgage rate, loan years (e.g. 30 year), % financed, date financed and payoff
year. You have been tasked with enhancing the worksheet to include noted basic
summary statistics. Which sorts of summary statistical methods would you
include in the updated workbook? Describe at least six statistical functions
that you would use so that you could explain them to your supervisor. (Points :
40)
Question 4. 4. (TCO 5) Mark Jacobs, owner of Jacob’s
Mini-Mart currently operates two stores in Atlanta. His business has been very
successful, and he is now opening two new stores. He will no longer be able to
manage all stores himself, so he is hiring managers for three of the four
stores. He will continue to operate one store, and act as general manager over
the company. He is looking for an easy and efficient way to collect information
from the stores, and he would like your help with the following:
PART 1 Marc wants you to design a worksheet for his managers
to complete showing the sales by product category by month at each store. He
wants to be sure the managers don’t change the formulas in the worksheet.
Describes the characteristics of the worksheet.
PART 2 Describe the process Mark will use in combining the
completed worksheets into on workbook and creating the summary report. (Points
: 40)
Question 5. 5. (TCO 9) You have over 10,000 rows of data in
an Excel spreadsheet. You have multiple users to input data and run reports.
Explain 4 major advantages to moving this data into a database. (Points : 40)
