BIS 2003: Excel Knowledge Check (75 pts.) You are a new employee at BJ’

BIS 2003: Excel Knowledge Check (75 pts.)
You are a new employee at BJ’s Electronics. Management has just given you a set of Excel spreadsheets to be analyzed. The spreadsheets are as follows: Purchases, Products, Customers, Brands, Day of Week, and Payment Code. Management would like a number of reports created, but before that can be done, you must first have a deep understanding of the data that has been entered into the spreadsheets.
Step 1: Data Validation and Finalization (20 pts). For each of the following, validate that the data has been entered correctly or complete the data sheet using existing information. In case a match is not available, use a function to mark incorrect entries as “Invalid”. Use conditional formatting to mark “Invalid” messages in red font. Columns have already been set up on the sheets for your entry.
(1a) Use Product ID (on the “Purchases” sheet): search for “Product Name” information on “Products.”
(1b) Use Brand Code (on the “Products” sheet): search for “Brand Name” information on “Brands.”
(1c) Evaluate Customer Type (on the “Customers” Sheet – Must equal to A or I): leave correct entries blank.
(1d) Customer Account (on the “Customers” Sheet): produce a new customer account using 1) the first letter of the customer’s first name; 2) the customer’s full last name; 3) and the last two characters in the Customer ID.
Step 2: Data Correction (5 pts). Correct “Product ID” (Purchases sheet) in (1a) and “Brand Code” (Products sheet) in (1b). In the real world, you would return the incorrect data to the appropriate party for correction. Since we are dealing with a fictional company, just make the data correct based on your discretion.
Step 3: Additional Calculations Needed (25 pts). Management has requested that the “Purchases” sheet contain calculations to produce the following information. You MUST use Excel functions to answer each question.
(3a) Customer First and Last Name: based on the Customer ID, search “Customer” to show the customer’s first and last name. (Note: both names must appear together in one cell, and the first name must be listed first; there should also be a space between the first and last name).
(3b) Purchase Cost: based on Product ID, search “Products” to show the purchase cost. Please format data appropriately with one decimal place.
(3c) Total Purchase Cost: sum the costs (column 3b) and show the result as “Total Cost = $xxx” where xxx is the sum. (Note: this string must all appear in one cell.) Place this string in cell J38 directly below the “(3c)” title.
(3d) Discount: please show the following information:
1) Need Info: if a cell’s value in column Loyalty Level is not a number; Or
2) The actual discount amount a customer could receive, i.e., 10% of the last purchase (column 3b): in order to be eligible for a discount, a customer must have a 4 or higher loyalty level AND have spent $650 or more in the last purchase (column 3b); Or
3) Blank: if a customer is not eligible for a discount
Step 4. Reports (20 pts). Use pivot tables/charts to answer the following questions. Note: be sure to format data appropriately, for example, add $ dollar signs, and use proper labels. A table/chart must have a title; all new sheets must be renamed. Failure to use appropriate titles and names will result in points being deducted.
(4a) Use information on “Product” to show the count of the number of products and the average cost by Brand. Show the Brand Name, not the code. Create a pivot table only. Name the new sheet: 4a-Product Report.
(4b) Use information on “Purchases.” Show the total amount of money (purchase cost) spent on purchases by each product (show product names) and Day of Week. Create a pivot table and a chart. Style the table using White Medium 8. Name the new sheet – 4b-Purchase Report.
Step 5. Explanations (5 pts). For each report in step 4, briefly explain below the table what the report tells management and how management might use the report in decision making. You may cite numbers from your tables in your explanations.
Rename the Excel file to yourfirstname_yourlastname. Before turning in your quiz, make sure your sheets are in the following order. (1) Purchases, (2) Products, (3) Customers, (4) 4a-Product Report, (5) 4b-Purchase Report, and all other sheets. 
BIS 2003: Access Quiz (75 pts.)
Management has given you an Access database with the following tables: Employee, Job Title, Performance, Salary History, State, and Stock Option. Since this database is in its early stages of development, much work must be completed before management can receive helpful reports.
Step 1: Tables (10 pts). Two additional tables need to be added to the database as follows:
(1a) Create a “Market Segment” table – Include 2 fields, Segment Code (the primary key; text), and Segment Name (text). Data records should be entered as follows:
C, Central
E, Eastern Coast
NM, Northern Main
SM, Southern Main
W, Western Coast
(1b) Import the “Ethnic” table – import the “Ethnic” sheet from the CIS2003AccessQuizExcelFile.xlsx spreadsheet. Use “Ethnic code” as the primary key.
Step 2: Data Validation (10 pts).
(2a) Lookup Function. In the Employee table, use the lookup function to tie the Ethnic Code field to the Ethnic table (via Ethnic Code).
(2b) Validation. In the Performance table, add a validation rule and a validation message that will limit entries for the Percentage field to any value smaller than 0.1, inclusive.
Step 3: Relationships (5 pts). Relationships have already been established for some tables in the database. Create relationships so that Market Segment and Ethnic are connected to the rest of the database. Queries, forms, and reports cannot be started until this step is complete.
(3a) Market Segment  Employee: via Segment Code
(3b) Ethnic  Employee: vis Ethnic Code
Step 4: Queries (35 pts) . Management has requested several quick answers to questions of interest to the company. Complete queries for each of the following:
(4a) A count of female operation managers (Job code: 4D) in 2021. Your result should only show the total count. Rename this count field as “F OM Managers”. Save the query as – 4a-Female OM Managers 21.
• Hint: Use Employee and Salary History
(4b) User Input Box – Check employment details for a specific employee. Use a user input box to allow a user to enter an employee’s first name and last name, and as a result, receive: Last Name(Employee), First Name(Employee), Employee ID, Salary (Salary History), Job Title (Job Title), and Year(Salary History). Sort the results by year in descending order. Save the query as – 4b-Employee Details.
(4c) “Build” a New Field – The amount of stock dividend per record in 2022. Show Employee ID, Last Name (Employee), First Name (Employee), Stock Shares Issued (Stock Option), Year (Salary History), Job Title (Job Title), and the amount of dividend. Name this calculated field as Dividend. Format Dividend as Currency. Do NOT show the year field. Save the query as – 4c-Dividend Details.
• Hint: use Stock Shares Issued * 55(the hypothetical stock price per share) to calculate the stock dividend.
(4d) The average salary by job classification, ethnic name, and year. Sort by job classification first (ascending) and year second (ascending). Save the query as – 4d-Salary Details.
(4e) Each employee’s stock information in 2022. List the Employee ID, Last Name(Employee), First Name (Employee), Job Classification (Salary History), Job Title (Job Title), Performance Rating (Salary History), Stock Shares Issued (Stock Option), and Year(Salary History) (in that order) for the year of 2022. Do NOT show the year field. Sort by Employee ID (ascending). Save the query as – 4e-Stock Details 22.
Step 5: Forms (5 pts). Management would like to start by having one form produced. Additional forms will be added to the database later.
(5a) Performance Update Form – Create a form that will add information to the Performance table. Save the form as – 5a-Performance Update Form. Test your form by entering the following new code, name, and percentage, respectively: EXC, Exceptional, 0.06.
Step 6: Reports (10 pts) –Management would like to receive many reports in the future but has only requested one report at this time. Be sure to include an appropriate title.
(6a) Dividend Report – Create a report from the query in 4c. Please follow the steps below.
• Include all fields from 4c;
• Choose Landscape for the Orientation option;
• Show only one employee’s information per page of the report;
• Hint: Consider Employee ID Header (instead of Detail) as the starting point of a new page;
• A meaningful title must appear on every page of the report;
• Adjust your report’s layout, such as text alignment, visibility, etc.
• Save the report as – 6a-Dividend Report.
Rename the Access file to yourfirstname-yourlastname before submitting.