2173 Salk Avenue, Suite 250 Carlsbad, CA

support@assignmentprep.info

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

April 23, 2024

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.      

Struggling With a Similar Paper? Get Reliable Help Now.

Delivered on time. Plagiarism-free. Good Grades.

What is this?

It’s a homework service designed by a team of 23 writers based in Carlsbad, CA with one specific goal – to help students just like you complete their assignments on time and get good grades!

Why do you do it?

Because getting a degree is hard these days! With many students being forced to juggle between demanding careers, family life and a rigorous academic schedule. Having a helping hand from time to time goes a long way in making sure you get to the finish line with your sanity intact!

How does it work?

You have an assignment you need help with. Instead of struggling on this alone, you give us your assignment instructions, we select a team of 2 writers to work on your paper, after it’s done we send it to you via email.

What kind of writer will work on my paper?

Our support team will assign your paper to a team of 2 writers with a background in your degree – For example, if you have a nursing paper we will select a team with a nursing background. The main writer will handle the research and writing part while the second writer will proof the paper for grammar, formatting & referencing mistakes if any.

Our team is comprised of native English speakers working exclusively from the United States. 

Will the paper be original?

Yes! It will be just as if you wrote the paper yourself! Completely original, written from your scratch following your specific instructions.

Is it free?

No, it’s a paid service. You pay for someone to work on your assignment for you.

Is it legit? Can I trust you?

Completely legit, backed by an iron-clad money back guarantee. We’ve been doing this since 2007 – helping students like you get through college.

Will you deliver it on time?

Absolutely! We understand you have a really tight deadline and you need this delivered a few hours before your deadline so you can look at it before turning it in.

Can you get me a good grade? It’s my final project and I need a good grade.

Yes! We only pick projects where we are sure we’ll deliver good grades.

What do you need to get started on my paper?

* The full assignment instructions as they appear on your school account.

* If a Grading Rubric is present, make sure to attach it.

* Include any special announcements or emails you might have gotten from your Professor pertaining to this assignment.

* Any templates or additional files required to complete the assignment.

How do I place an order?

You can do so through our custom order page here or you can talk to our live chat team and they’ll guide you on how to do this.

How will I receive my paper?

We will send it to your email. Please make sure to provide us with your best email – we’ll be using this to communicate to you throughout the whole process.

Getting Your Paper Today is as Simple as ABC

No more missed deadlines! No more late points deductions!

}

You give us your assignments instructions via email or through our order page.

Our support team selects a qualified writing team of 2 writers for you.

l

In under 5 minutes after you place your order, research & writing begins.

Complete paper is delivered to your email before your deadline is up.

Want A Good Grade?

Get a professional writer who has worked on a similar assignment to do this paper for you