View the step-by-step solution to:

Employee# E1001 E1002 E1003 E1004 E1005 E1006 E1007 E1008 E1009 E1010 E1011 E1012 E1013 E1014 E1015 E1016 E1017 E1018 E1019 E1020 FirstName Kelly

Could you please solve part 2 only

this homework requires importing data from excel to access and create tables, internal controls.

create relationships, form, report, query. and so one

please solve as soon as possible,


Employee# FirstName LastName EmpAddress EmpCity EmpState EmpZip HireDate Salary/Wage Pay E1001 Kelly DelcasTllo 12416 Duchess Lane Omaha NE 68145 1/2/2015 Salary 100000 E1002 Cisco Ramon 1805 ±intah Dr Omaha NE 68125 1/2/2015 Salary 70000 E1003 Gloria Shumway 29251 Sandalwood Ct Papillion NE 68133 1/2/2015 Wage 19.25 E1004 Barry Allen 214 E Wyland Way Omaha NE 68451 1/2/2015 Salary 60000 E1005 Harrison Wells 1061 W Camden pl Elkhorn NE 68022 1/2/2015 Salary 60000 E1006 Oliver Queen 1247 S Baker St Council Blufs IA 51501 1/2/2015 Wage 19.25 E1007 Dominque Mansell 2124 Union Ave Omaha NE 68151 1/3/2015 Wage 18.75 E1008 Breana Ouk 10222 Sterling Ave Omaha NE 68154 2/26/2015 Wage 18.75 E1009 Genevieve Sudduth 5231 S196th St Omaha NE 68151 7/15/2015 Wage 18.75 E1010 Clark Kent 524 S Birch St Pla²smouth NE 92701 8/30/2015 Wage 16.5 E1011 Paule²e Mcraney 1031 N Magnolia Ave #330 Lincoln NE 68501 9/26/2015 Wage 18.75 E1012 Brigida Astudillo 18841 Deep Well Rd Omaha NE 68125 9/30/2015 Wage 19 E1013 Felicity Smoak 2311 Fig ±ree Drive Omaha NE 68124 9/30/2015 Wage 18.5 E1014 John Diggle 9898 Osborne Court Elkhorn NE 68022 9/30/2015 Wage 18.5 E1015 Chris Goolsby 3972 Barranca Pkwy Elkhorn NE 68022 10/1/2015 Wage 18.5 E1016 Steve Rogers 18973 Santa Catherine St Papillion NE 68133 10/1/2015 Wage 16 E1017 ±heo Gunder 2281 Marks Drive Papillion NE 68133 10/1/2015 Wage 18.25 E1018 ±ony Stark 545 Go²o Fremont NE 68025 11/1/2015 Wage 16 E1019 Nancy Knabe 4110 N Lakewood Blvd Omaha NE 68124 11/1/2015 Wage 18.25 E1020 Arabelle Sommer³eld 14431 Culver Dr Omaha NE 68124 12/1/2015 Wage 16
Background image of page 1
Vendor# VendorName VenContact VenAddress VenCity VenST VenZip VenPhone PymtTerms V20520 Gearbox 100 8th Ave. New York NY 10035 212-489-1558 30 V20825 Marantz 909 S. Washington Spring Valley TX 76258 940-731-0877 30 V20960 Thorens 23980 Maui Place St. Louis MO 63101 314-562-9634 30 V21959 Music Hall Audio 10332 5th Avenue New York NY 10003 212-345-1388 15 V24848 Henley Designs 111 Macarthur Blvd. Lakewood CO 80266 303-245-6955 30 V27549 Rega 571 Mount Pleasant Cri±enden NJ 07801 201-985-8954 45 Reba Forth Maisie Chri Janna Nicol Shizuko Led Kayleigh Ha Carisa Grow
Background image of page 2
Show entire document
Comprehensive Excel and Access Project Spring 2016 NOTE: There are three different parts, each with a different due date! You are always free to turn things early, just make sure you upload the correct file because Blackboard will not let you resubmit. In this assignment, you will use the skills you learned in the Excel AIS project, the Access DB projects, and Excel Pivot Tables assignment. Note that all of the requirements below are tasks you completed in the three main projects from this semester. You may need to refer to these projects for instructions and examples, if you need a refresher. After working for Spinyl Vinyl for just over one year, you accepted a position with one of SV’s vendors, Aural Sonic Industries (ASI). ASI is a distributor of vinyl records. Part I and II of this project will involve the purchases cycle for the vinyl record players. Part III with use data from the sales cycle for the vinyl records. Part I: Excel Download and save the Comprehensive Data File for Excel FINAL as YourLastName_CompExcel.xlsx The workbook contains several worksheets. Your objective is to format, create useful formulas, implement internal controls, and create a source document for the worksheets related to the Purchases Cycle , as directed below: 1. Format all Purchases Cycle worksheets as appropriate. This includes steps such as: a. Format as a table b. Format numbers as currency, where appropriate c. Ensure all data is visible in all columns d. Create centered titles in the first row of all worksheets 2. Create formulas in the Vinyl Record Inventory worksheet, format as appropriate. a. Line totals for Unit Cost, Unit Price b. Gross profit per product, gross profit % per product c. Sum totals for TOTAL inventory cost and price d. Averages for gross profit calculations 3. Internal controls in the Vinyl Player Inventory worksheet a. Data validation for Product #. This internal control should prevent someone from entering a Product # that is not the appropriate length and type. Appropriate messages (input message AND error message) must inform the individual of the acceptable input. b. Conditional formatting for Quantity. This internal control should highlight how close the quantity on hand of each product is to the reorder level. To do this, you will need to create a new column that
Background image of page 1
calculates the difference between the Quantity and the Reorder Level. You can then create a conditional formatting rule with an icon set that shows management any products that have a quantity below their reorder level, between 0 and 10 products of their reorder level, and equal to 10 or more than their reorder level. 4. Source document for the Purchases Cycle a. Create a Purchase Order worksheet. This is similar to the Sales Order or Invoice you created in the Excel AIS project. Name the worksheet Purchase Order Document b. The PO Doc should look professional, including the name and address of your company, and a logo. c. The PO Doc must include fields for the Vendor ID, Vendor Name and full Vendor Address that you are purchasing from, a PO Number, a PO Date, the Employee ID. d. The body of the PO Doc must include the necessary information for purchasing a product from a vendor, including ID, description information, price paid to the vendor, quantity ordered. e. To ease data entry and practice good internal control procedures, your PO must: i. Use a data validation drop down list for the Vendor ID. ii. Use a VLookup function for the Vendor Name, Contact, full Vendor address, and payment terms based on the Vendor ID. iii. Use a VLookup function for the Product artist and title and cost paid to Vendor, based on the Product ID. iv. Contain an appropriate formula to calculate Line Totals v. Contain a subtotal field, shipping calculation (standard shipping runs 5% of the purchases subtotal) vi. Total Balance vii. Data validation for the PO date to restrict it to TODAY only, it must stop the user from entering any other date. This means that the PO date must be validated for TODAY (this means you cannot just use =Today in the cell). Appropriate messages (input message AND error message) must be included to inform the user. viii. Balance due date (a formula based on the PO Date and the payment terms). You may need to format this cell as Date f. Use this document to create a new purchase. Rename the worksheet for the PO Number you will be creating next. Look at your current PO worksheet to determine the last PO number used. i. Input the sequentially correct PO number (note, this is the Input PO number that should be in the worksheet name. ii. Employee E1005 is making the purchase. iii. Input the correct date (whatever day you are working on this). iv. The purchase is from Vendor V24848 v. Your purchase is for product #P10030 and product #P10037. You will purchase enough of each product to bring each
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

Let me explain the... View the full answer

Internal Controls Implemented.docx

Internal Controls Implemented Table : Employees
Employees# Field Size=5
EmpState Field Size=2
EmpZip Field Size=6
Pay Validation Rule “>0” Payment should be greater than 0
Form : Employees...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.


Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online