Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Excel module 6 sam project 1

28/10/2021 Client: muhammad11 Deadline: 2 Day

Shelly Cashman Excel 2016 | Module 6: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png Shelly Cashman Excel 2016 | Module 6: SAM Project 1a

Fish Chips Electronics

Creating, Sorting, and Querying a Table

GETTING STARTED
Open the file SC_EX16_6a_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as SC_EX16_6a_FirstLastName_2.xlsx by changing the “1” to a “2”.

0. If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file SC_EX16_6a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEP
Cyril Netreba works at Fish Chips Electronics, which sells inexpensive computers (called “Fish Chip systems”) to schools to help teach computer science and electrical engineering. Cyril wants to summarize some information on Fish Chips Electronics’ product data into tables to help him train a new sales associate.

Switch to the Systems worksheet. Select the range A1:H9 and format the range as a table (with headers), using the Table Style Medium 14. (Hint: Depending on your version of Office, the Table style may appear as Green, Table Style Medium 14.)

Use SystemsTable as the name of the table.

Fish Chips Electronics approved a new third-party sensor for use with the Fish Chip Systems.

Switch to the Sensors worksheet. Enter a new record into the end of the SensorsTable table (in row 15) as shown in Table 1 below:

Table 1: New SensorsTable Record

Sensor ID

Sensor Name

Fish Chip Classic Compatible

Fish Chip 2 Compatible

Fish Chip 3 Compatible

Cost

S487

Adi Humidity Sensor

Yes

Yes

No

$5.50

Sort the SensorsTable table from smallest to largest by the Cost field values.

On the Sensors worksheet, Cyril wants to display only sensors that are compatible with the Fish Chip 3 systems below the SensorsTable data.

Use an advanced filter to copy all the records for sensors compatible with the Fish Chip 3 systems from the SensorTable into a new range as described below:

a. Enter Yes into cell E19 to set up the value to filter on in the criteria range.

b. In the Advanced Filter, use the range A1:F15 as the List range, the range A18:F19 as the Criteria range, and copy the records to the range A21:F21. (Hint: 10 records will be copied from the SensorsTable data.)

Switch to the Displays worksheet. Apply Wrap Text formatting to the range E1:G1.

Remove the duplicate record in the DisplaysTable table.

LED displays are the most popular displays sold by Fish Chips Electronics and Cyril wants to highlight these displays in the table.

Filter the table to display only those records with a Type column value of LED.

At the beginning of the year, Fish Chips Electronics makes a bulk order of cases for each system they sell. Cyril wishes to show his sales associate a snap-shot of the number of cases that were ordered, sold, and are available at the warehouse for each Fish Chip system. Cyril already sorted the table containing this data, he just needs to insert subtotals into the table.

Switch to the Cases Subtotal worksheet. Convert the CasesSubtotal table (in the range A1:I24) into a range.

After converting the table to a range, insert subtotals into the range A1:I24 using the following options:

c. Subtotals should be inserted at each change in the System Compatibility value.

d. The subtotals should use the Sum function.

e. Subtotals should be added to the 2018 Inventory, Cases Sold, and Cases Available values only.

f. The subtotals should replace current subtotals and include a summary below the data.

Cyril wishes to highlight some additional data on the cases sold by Fish Chips Electronics. He first wants to identify how many options are available for each type of case.

Switch to the Cases worksheet and complete the following steps to the identify how many case options are available for each case type.

g. In cell C2, enter a formula using the COUNTIF function that counts the number of cases with the Unibody case type. Use Unibody as the criteria argument, and CasesTable[Body Type] as the range argument in your formula.

h. In cell C3, enter a formula using the COUNTIF function that counts the number of cases with the 2-Piece case type. Use 2-Piece as the criteria argument, and CasesTable[Body Type] as the range argument in your formula.

i. In cell C4, enter a formula using the COUNTIF function that counts the number of cases with the Compact case type. Use Compact as the criteria argument, and CasesTable[Body Type] as the range argument in your formula.

Complete the following steps to determine the total number of cases were sold for each body type.

j. In cell E2, enter a formula using the SUMIF function that totals the value in the Cases Sold column for each case with a Unibody case type. Use Unibody as the criteria argument, CasesTable[Body Type] as the range argument, and CasesTable[Cases Sold] as the sum_range argument in your formula.

k. In cell E3, enter a formula using the SUMIF function that totals the value in the Cases Sold column for each case with a 2-Piece case type. Use 2-Piece as the criteria argument, CasesTable[Body Type] as the range argument, and CasesTable[Cases Sold] as the sum_range argument in your formula.

l. In cell E4, enter a formula using the SUMIF function that totals the value in the Cases Sold column for each case with a Compact case type. Use Compact as the criteria argument, CasesTable[Body Type] as the range argument, and CasesTable[Cases Sold] as the sum_range argument in your formula.

In the range H8:H30 (the Cases Sold column), create a new Icon Set conditional formatting rule as described below:

m. Use the 3 Signs icon set in your conditional formatting rule.

n. Display the green, circular icon in all cells with a Number type value greater than or equal to 60.

o. Display the yellow, triangular icon in all cells with a Number type value less than 60 and greater than or equal to 25.

p. Display the red, diamond icon in all cells with a Number type value less than 25.

Apply a custom sort on multiple fields to the CasesTable, so that the table is first sorted by the System Compatibility field in descending (Z to A) order, then by the Body Material field in ascending (A to Z) order, and finally by the Body Type field in ascending order.

Add a Total Row to the CasesTable. (Hint: The total row should appear in row 31 with a total for Cases Sold automatically appearing in cell H31.)

Using the total row, display the SUM of the 2018 Inventory in cell G31.

Upon review of the CasesTable, Cyril notices the Cases Available column needs to be added to the table.

Add a calculated column to the CasesTable (in the range A7:H30) as described below:

q. In cell I7, enter the text Cases Available as the calculated column’s heading.

r. In cell I8, enter a formula without a function using structured references that subtracts the value in the Cases Sold column from the value in the 2018 Inventory column. Use [2018 Inventory] and [Cases Sold] as the structured references in your formula.

The formula should autofill into the range I8:I30. If it does not, copy the formula into the range.

s. Using the table’s total row (in row 31), SUM the values in the Cases Available column.

Fish Chips Electronics also sells packaged sets that combine popular Fish Chip systems with an LED display, a case, and a simple project guide. The PackageTable on the Packages worksheet identifies the system and display included in each package by ID number rather than by name. Cyril wants to set up a simple lookup table to make it easier for his new sales associate to identify what is included in each package.

Switch to the Packages worksheet. In cell H3, create a formula using the VLOOKUP function to determine the type of system included with the package using the following parameters:

t. The formula should use cell J3 (the System ID) as the lookup_value, the range G7:H12 as the table_array, and 2 as the col_index_num.

u. Leave the optional range_lookup parameter blank.

In cell H5, create a formula using the VLOOKUP function to display the name of the package using the following parameters:

v. The formula should use cell H2 (the Package ID) as the lookup_value, the table named PackagesTable as the table_array, and 4 as the col_index_num.

w. Leave the optional range_lookup parameter blank.

Cyril also wants to highlight the number of packages associated with the latest iteration of the Fish Chip System – the Fish Chip 3 Mod C+.

In cell H16, create a formula using the DCOUNT function to count the number of packages associated with the Fish Chip 3 Mod C+ systems, using the range A1:D22 as the database, “System ID” as the field, and the range H14:H15 as the criteria.

In cell H17, create a formula using the DAVERAGE function to determine the average cost of a Fish Chip 3 Mod C+ package, using using the range A1:D22 as the database, “Cost” as the field, and the range H14:H16 as the criteria.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Systems Worksheet

Final Figure 2: Sensors Worksheet

Final Figure 3: Displays Worksheet

Final Figure 4: Cases Subtotal Worksheet

Final Figure 5: Cases Worksheet

Final Figure 6: Packages Worksheet

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Homework Tutor
A Grade Exams
Accounting & Finance Master
Coursework Helper
Smart Tutor
ECFX Market
Writer Writer Name Offer Chat
Homework Tutor

ONLINE

Homework Tutor

I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles.

$17 Chat With Writer
A Grade Exams

ONLINE

A Grade Exams

As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget.

$26 Chat With Writer
Accounting & Finance Master

ONLINE

Accounting & Finance Master

I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles.

$20 Chat With Writer
Coursework Helper

ONLINE

Coursework Helper

I am an experienced researcher here with master education. After reading your posting, I feel, you need an expert research writer to complete your project.Thank You

$47 Chat With Writer
Smart Tutor

ONLINE

Smart Tutor

I reckon that I can perfectly carry this project for you! I am a research writer and have been writing academic papers, business reports, plans, literature review, reports and others for the past 1 decade.

$31 Chat With Writer
ECFX Market

ONLINE

ECFX Market

As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget.

$36 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Randy tillim net worth - Market share simulation allround answers - Leadership style interview for a company's newsletter course hero - 130 obsidian way hercules ca - Business Ethics Assignment 2 Week 4 - Forward and backward pass in ms project - Qualitative Research - Bachelor of pharmaceutical science honours - How has an understanding of consumer behavior helped coppertone - Http www splcenter org get informed hate map - A article in the economist noted that - The redistricting game worksheet answers - Swales definition of a discourse community - Ben tapp maryfield station - Abnormal psychology case study examples - A b 4 expansion - Cinderella rockerfella tonight's the night - Action Research Survey - Help 2 - Help - IT Governance - Political speech - Guide to computer forensics and investigations 5th pdf - Nature/nurture - Egyptian civiliaztion - Mass civilisation and minority culture - Natural selection favours lighter pigmented skin in northern regions - 3 Discussions and on line observations - Check high mounted brake lamp - Finance discussion 4 - Irs po box 1214 charlotte nc - Wk 6 forum 2 Catry - The whole business with kiffo and the pitbull essay - Ktea 3 academic skills battery - Developing Personas Assignment - English-Romantic novels - Supporting people's cultural and spiritual needs - The mean incubation time of fertilized eggs is - Pre-Calculus homework ONLINE 100 to start - Jacob allusion merchant of venice - What does psi stand for dominos - Spotty handed villainesses annotated - Types of teams in the public services - Due in 20 hours - Paper - Marketing chateau margaux pdf - Jean piaget formal operational stage - Bellofram regulator type 70 - 1 turtledove rise greenough - INter CAST LOVe ^&}}+91-7023339183||: problems solution MOLviji - If the sequence of one strand of dna is cattacggg - A portrait of linear algebra 3rd edition pdf - Straight line method bond amortization calculator - Biology 1 - Tell the wolves i m home sparknotes - What does agnes hailstones tattoo mean - Sea cadet uniform guide - The book thief basement - 87 1/2 percent as a fraction - Sun sensitive paper sunscreen experiment - What is pressure measured in - Test - Research paper - A christmas carol stave 5 questions and answers - Lord capulet quotes to juliet about marriage - Cwv topic 7 review - Development Plan - One of your customers is delinquent on his accounts - Applied business research - Contingency Planning - Discussion - Elizabethan poetry essay - Functional health assessment example - Food chain food web and ecological pyramids ppt - Multiple linkage model of leadership - Client related risk factors - This lullaby sarah dessen pdf - Takt time lean manufacturing pdf - Jarvis v swan tours ltd 1973 qb 233 case report - Command to check crashinfo on cisco router - TEST POST QUESTION FROM ADMIN2 - The smallest bilby and the easter games - Bargaining power of suppliers in restaurant industry - Mobile accelerate plan telstra - Japanese restaurant business plan sample - Example of acknowledgement for ojt student - What word does jefferson use to describe rights - Moonshine bandits get loose dancer - Honors chemistry balancing equations worksheet answers - The arc of ambition defining the leadership journey pdf - Ceres gardening company case - Leadership in nursing powerpoint presentation - Wireless sensor network communication architecture - Romeo and juliet newspaper assignment - Mr barton maths m1 - Cattell's phd was earned with - Crucible pdf act 1 - Write a of review summary from the biobased products research article included below - My mistress eyes are nothing like the sun - Operation Security - Efficiency of dc shunt motor